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

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, on开发者_如何学Cly 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.

I had to implement a geolocation search and after much research I settled on using sql2008 geography. You need a table of zip codes populated with lat/long. The table should look something like this:

CREATE TABLE [dbo].[PostalCodes](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [StateID] [bigint] NOT NULL,
    [PostalCode] [varchar](10) NOT NULL,
    [Latitude] [decimal](16, 12) NULL,
    [Longitude] [decimal](16, 12) NULL,
    [GeographyLocation] [geography] NULL,
    [CreatedOn] [datetime] NOT NULL,
    [LastUpdated] [datetime] NOT NULL,
    [GeographyLocation_temp] [varchar](100) NULL,
 CONSTRAINT [PK_PostalCode] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I downloaded an international list of postalcodes from GeoNames.org and imported it as tmp_GeoNames. Then I ran the following script to insert the data into my zipcode table and create the spacial index. (I had to add my own StateID column and populate it but you can skip that part and remove it from the script.)

INSERT INTO PostalCodes
(StateID, PostalCode, Latitude, Longitude)
SELECT DISTINCT StateID, PostalCode, Latitude, Longitude FROM temp_GeoNames where stateID is not null

UPDATE PostalCodes
SET GeographyLocation_temp= 'POINT(' + CONVERT(VARCHAR(100),longitude)
+' ' +  CONVERT(VARCHAR(100),latitude) +')'

UPDATE PostalCodes
SET GeographyLocation  =  geography::STGeomFromText(GeographyLocation_temp,4326)

CREATE SPATIAL INDEX  SIndx_SpatialTable_geography_col1
   ON PostalCodes(GeographyLocation);

Finally I created a function that accepts a lat/long and returns all the zipcodes within a certain range. Because it uses the spacial index it's very fast.

CREATE FUNCTION [dbo].[PostalCode_SelectNearest]
(
    @Latitude [decimal](16, 12)
    ,@Longitude [decimal](16, 12)
    ,@RangeInMiles int
)
RETURNS @PostalCodes Table (PostalCode varchar(10) PRIMARY KEY NOT NULL, DistanceInMiles FLOAT NULL)
AS
BEGIN
    --Create geography point based on Lat/Long passed ... careful, the values passed are reversed from normal thinking
    DECLARE @g geography;
    SET @g = geography::STGeomFromText('POINT(' +
                                         CONVERT(varchar,@Longitude) + ' ' +
                                         CONVERT(varchar,@Latitude) + ')', 4326);
    --Select the nearest Postal Codes
    INSERT INTO @PostalCodes (PostalCode, DistanceInMiles)
    SELECT PostalCode, GeographyLocation.STDistance(@g)/1609.344 as DistanceInMiles
    FROM PostalCodes
    WHERE GeographyLocation.STDistance(@g)<=(@RangeInMiles * 1609.344)

    RETURN;
END

I realize this isn't exactly what you are looking for but it could be converted to your purposes. I found using zipcodes to be far more efficient and accurate than cities because cities can span many zipcodes and therefore return data that is erroneous to the end user.

This is all very US centric but could easily but converted to international use. I'm planning to do this at some point in the future but have not had the need yet.

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)

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

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

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

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.

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.