Wednesday, February 25, 2009

SQL Server Proximity Search

Posted by Vishnureddy at 7:42 PM

Share this Post and Be Awesome

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

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  tagged me so look forward to that

Love to hear what you think! Thanks Would make us Smile :)

Receive all updates via Facebook. Just Click the Like Button Below else Hit close icon


Search Entire Site

Custom Search
back to top