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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: