SQL Server Proximity Search

Denis Gobo — Thu, 12 Feb 2009 02:28:00 GMT

George, a good friend of mine created a blog post showing how you can do a SQL Server Zipcode Latitude/Longitude proximity distance search without using the geography data type. I searched the internet to see if anyone had something similar with the geography data type available in 2008 and could not find anything that showed some useful stuff. Yes there is a lot available with geometry and polygons but nothing I was looking for. George challenged me and I did the 2008 version.

There is nothing really complicated. if you run this

DECLARE @g geography;

DECLARE @h geography;

SET @h = geography::STGeomFromText('POINT(-77.36750 38.98390)', 4326);

SET @g = geography::STGeomFromText('POINT(-77.36160 38.85570)', 4326);

SELECT @g.STDistance(@h)/1609.344;

You will see that the distance in miles between those two points is 8.8490611480890067

In the end the code runs between 15 and 60 millisecond to get all the zipcodes within 20 miles of zipcode 10028, pretty impressive if you ask me

All the code including sample data for all the zip codes in the US can be found here

SQL Server 2008 Proximity Search With The Geography Data Type

The reason I did not post it here is because George created the 2000 version so it would be weird if the 2008 version was somewhere else, but no worries I will have a post here tomorrow about "what do you wish you knew when you were starting?" Michelle Ufford (aka SQLFool) tagged me so look forward to that

Denis Gobo : geography, geospatial

SQL Server Proximity Search