How To Return All Instances Within ## Mile Radius of a List of Zipcodes

Question

SQL Server 2008 has added some cool new Spacial types and given SQL Developers a lot more powerful ways of manipulating spacial data but it still eludes me how to efficently return, for example, only the locations that are within ## mile radius of a long list of zips (20 to maybe 15 thousand distinct zipcodes).

Is there a simple way to do this? The only possible solution that comes to mind seems to be somewhat scary due to the cartiasian product created and therefore the rediculous number of calculations...

I am adept at creating CLR SP's and Functions if that helps (as I assume it will...).

I'm not so concerned with how to find the distance between 2 points (or geography types), rather the "is the given location within ## miles of any of the zipcodes (geography points) in the supplied list?" The complex part here is the list of zips to search around.

Thanks.

Answer 1

Well I do have a sql function that will perform those scary calculations for you that actually isn't that slow. But here is a link as well as to how to perform the query using the new features in sql 2008: http://msdn.microsoft.com/en-us/magazine/dd434647.aspx

EDIT: More Links:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-2008-proximity-search-with-th

Answer 2

Also consider how accurate this needs to be... For small radii, (where great circle math is not required), it might be sufficient to just get all locations within a square that is that many miles on a side... This can be done with just a filter, without any calculations, if you have the latitudes and longitudes of each zip code. And the number of rows returned will be off (it will be too many) only by the factor 1 - pi/4, which is only about 21%

foreach given position (Tgt lat/long)
- assuming the radius is in nautical miles (6080 feet),
and latitude and longitude are measured in total minutes
(i.e. 30deg, 10 minutes = 1810 minutes)

then:

Select * From theTable
   Where Latitude  Between TgtLat - radius 
                    And TgtLat + radius
     And Longitude Between TgtLong - radius/Cos(TgtLat)
                    And TgtLong + radius/Cos(TgtLat)

Answer 3

Check out the GeoNames Webservice. I've used this and it worked well.

http://www.geonames.org/export/client-libraries.html

Answer 4

I've done something very similar with Oracle Spatial, so take my answer with a grain of salt since I'm not familiar with MSSQL's spatial features:

I'm going to go on the assumption that you have data representing the polygons that encompass each ZIP code, all you have to do is take your list of ZIP codes, combine their polygons and then ask for all records that fall within the combined polygon or within x miles of its edges. Some spatial packages have a "multi-gon" type that will allow you to combine and operate on non-contiguous areas (non-adjacent ZIP codes in your case).

If all you have is center points for the ZIP codes, you can do the same thing: combine the points and look for anything within x miles. The pitfall here is that some ZIP codes can be very large and you'd lose out on some records that are within your criteria but not within x miles of the center points.

It sounds hairy, but the indexing systems used for spatial data are pretty efficient.

HTH.

Answer 5

With whatever solution you decide to go with, you are going to need a zip code database. Here is one, download it and import it into a table.