THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Server Proximity Search

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 (aka SQLFool) tagged me so look forward to that

 

 

Published Wednesday, February 11, 2009 10:28 PM by Denis Gobo

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

gu said:

I was wondering, how important is it to have a spatial index being created on table where we have a column of geography type where we are only keeping Lon and Lat values?

January 27, 2010 10:34 PM
 

Andries said:

How do you use the above query from a point to all other points an a column to calculte all places's distance to one place.vrooyaj1@telkom.co.za

December 19, 2013 6:05 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement