如何返回邮政编码列表 ## 英里半径内的所有实例

SQL Server 2008 添加了一些很酷的新空间类型，并为 SQL 开发人员提供了更强大的操作空间数据的方法，但它仍然让我无法有效地返回，例如，仅返回长列表的 ## 英里半径内的位置邮政编码（20 到 15,000 个不同的邮政编码）。

有没有一种简单的方法可以做到这一点？想到的唯一可能的解决方案似乎有点可怕，因为创建了笛卡尔积，因此计算量非常可笑......

如果有帮助的话，我擅长创建 CLR SP 和函数（我认为它会......）。

我不太关心如何找到 2 点（或地理类型）之间的距离，而是“给定位置是否在所提供列表中的任何邮政编码（地理点）的 ## 英里范围内？”这里最复杂的部分是要搜索的 zip 列表。

Thanks.

我必须实现地理位置搜索，经过大量研究后，我决定使用 sql2008 地理。您需要一张填充了纬度/经度的邮政编码表。该表应如下所示：

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

我从以下位置下载了国际邮政编码列表地名网 http://www.geonames.org/并将其导入为 tmp_GeoNames。然后我运行以下脚本将数据插入到我的邮政编码表中并创建空间索引。 （我必须添加自己的 StateID 列并填充它，但您可以跳过该部分并将其从脚本中删除。）

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

最后，我创建了一个接受纬度/经度并返回特定范围内的所有邮政编码的函数。因为它使用空间索引，所以速度非常快。

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

我意识到这并不完全是您正在寻找的，但它可以转换为您的目的。我发现使用邮政编码比城市更有效和准确，因为城市可以跨越许多邮政编码，因此向最终用户返回错误的数据。

这一切都以美国为中心，但可以很容易地转换为国际使用。我计划在将来的某个时候这样做，但目前还没有这个需要。