Geolocation Search with SQL Server
As some of you may know, as a side project, I’m building an app that involves geo-location and social media. When I went to build the geo-location part of the app my requirements were pretty simple. I have people in my application with lots of GPS data and given a specific GPS coordinate, maybe where I’m standing now, I need to search all those people and see who is nearby. So how to do this? Well, you obviously don’t want to do this in the application, right? You’d have to pull all that data back and do a search in memory. The database is a much better place, and fortunately SQL Server 2008 (and now SQL Azure) have geospatial support. Here’s a great MSDN article with more information. And this is a simplified version of the Stored Procedure I came up with that will search a database table (Location) for all records within a specified distance (meters) of a target lat/lon position:
CREATE PROCEDURE SearchLocation ( @SearchLatitude decimal(12, 8), @SearchLongitude decimal(12, 8), @Distance decimal(12, 8)) AS BEGIN SET NOCOUNT ON SELECT l.UserId FROM Location l WHERE l.LocationPosition.STDistance(geography::Point(@SearchLatitude, @SearchLongitude, 4326)) < @Distance END
