如何返回所有实例在##英里半径邮编的列表 [英] How To Return All Instances Within ## Mile Radius of a List of Zipcodes

问题描述

SQL Server 2008中增加了一些很酷的新空间分型，并给予SQL开发了很多功能更强大的操纵空间数据的方式，但它仍然逃避我怎么efficently回报，例如，仅是##英里半径内的位置拉链一长串（20〜也许15000截然不同邮编）。

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).

有没有一种简单的方法来做到这一点？想到的惟一可行的办法似乎是有点吓人，由于创造了cartiasian产品，因此计算的荒谬数...

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...

我善于创造CLR SP的和函数是否有帮助（我假设它会...）。

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

我不那么关心如何发现2个点之间的距离（或地理类型），而在是在任何提供的一系列邮编（地理分）的##英里范围内的指定位置？这里的复杂的部分是拉链的名单四处搜寻。

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.

感谢。

推荐答案

我不得不实现一个地理位置的搜索和大量的研究后，我使用SQL2008地理结算。你需要的填充纬度/长拉链码表。该表应该是这个样子：

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

我从网上下载的 GeoNames.org 和进口它作为tmp_GeoNames。然后我跑到下面的脚本将数据插入到我的邮编表，并创建空间索引。 （我不得不添加自己的STATEID列，并填充它，但你可以跳过这一部分，从脚本删除它。）

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.

这篇关于如何返回所有实例在##英里半径邮编的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！