计算一个表的in到另一个引用表的最近距离，而不连接引用表

Question

在这一点上我真的很困惑:所以我试图从https://gist.github.com/Miserlou/c5cd8364bf9b2420bb29 (我将其转换为csv并上传到我们的SQL server，让我们称之为City_table)中每个ID最接近的城市的人口排名。

City_table有每个城市的纬度和经度，以及每个城市的人口排名，而ID_table有每个ID的纬度和经度。我不能加入City_table，因为我需要计算每个ID到每个城市的距离，并取其最小值。

下面的计算得到从一个位置到另一个位置的距离，并转换为英里：

(ACOS(COS(RADIANS(90-CAST(ID_table.GEO_LATITUDE AS REAL)))*COS(RADIANS(90-City_table.latitude))+SIN(RADIANS(90-CAST(ID_table.GEO_LATITUDE AS REAL)*SIN(弧度(90-城市表宽))*COS(弧度(CAST(ID_table.GEO_LONGITUDE AS REAL)- (-City_table.longitude)*6371)*0.621371

简单地说，ID_table有一个ID、纬度和经度。City_table具有纬度、经度、城市和根据最高人口排名。我需要从City_table获取离ID位置最近的城市排名。

我真的不知道该怎么做，真的很感谢任何人的帮助。

我正在尝试完成下面这样的事情(承认语法是不正确的，只是我想要实现的想法) @hastrb

SELECT A.ID,City_table.rank,
       FOR EACH CITY IN City_table{(ACOS(COS(RADIANS(90-CAST(ID_table.GEO_LATITUDE AS REAL)))*COS(RADIANS(90-City_table.latitude))+
       SIN(RADIANS(90-CAST(ID_table.GEO_LATITUDE AS REAL)))*SIN(RADIANS(90-City_table.latitude)) *
       COS(RADIANS(CAST(ID_table.GEO_LONGITUDE AS REAL)-(-City_table.longitude))))*6371)*0.621371} AS DISTANCE
FROM ID_table A
WHERE ROW_NUMBER()OVER(PARTITION BY A.ID ORDER BY DISTANCE ASC) = '1'

所以我最终弄明白了这一点，但为了将来有人遇到同样的问题，我想出了一个解决方案(尽管可能不是最好的)，但它是有效的：

WITH X
AS
(
SELECT A.ID, A.CITY, A.[STATE], B.[Rank], B.City AS CITY_TABLE_CITY, B.State AS CITY_TABLE_STATE,

       ((ACOS(COS(RADIANS(90-CAST(A.GEO_LATITUDE AS REAL)))*COS(RADIANS(90-B.latitude))+   
                                SIN(RADIANS(90-CAST(A.GEO_LATITUDE AS REAL)))*SIN(RADIANS(90-B.latitude))*
                                COS(RADIANS(CAST(A.GEO_LONGITUDE AS REAL)-B.longitude)))*6371)*0.621371) AS DISTANCE,

       ROW_NUMBER()OVER(PARTITION BY A.ID ORDER BY((ACOS(COS(RADIANS(90-CAST(A.GEO_LATITUDE AS REAL)))*COS(RADIANS(90-B.latitude))+   
                                SIN(RADIANS(90-CAST(A.GEO_LATITUDE AS REAL)))*SIN(RADIANS(90-B.latitude))*
                                COS(RADIANS(CAST(A.GEO_LONGITUDE AS REAL)-B.longitude)))*6371)*0.621371) ASC) AS DISTANCE_NUMBER
FROM ID_TABLE A
FULL OUTER JOIN CITY_TABLE B ON B.latitude<>A.GEO_LATITUDE
)
SELECT * 
FROM X
WHERE DISTANCE_NUMBER='1' AND DISTANCE IS NOT NULL
ORDER BY ID

Answer 1

我强烈建议(正如John Cappelletti已经提到的)使用地理类型。我不得不做一些与您类似的事情，我的参考表包含了位置的地理位置。我将它连接到主查询"ON 1=1“。这样，对于主查询的每一行，您将从locations表中获得一个位置(请记住，如果您正在处理的表很大，速度将会很慢！)。在任何情况下，查询都如下所示：

--The next line declares a reference location (some lat/long example)!
DECLARE @reference_point geography=geography::STGeomFromText('POINT(-84.206230 33.897247)', 4326);

SELECT t.LocationName,
       t.PointGeom.STDistance(@reference_point) / 1609.34 AS [DistanceInMiles]
FROM
(
    SELECT LocationName,
           geography::Point(ISNULL(Geom.STY, 0), ISNULL(Geom.STX, 0), 4326) AS [PointGeom],
           Geom,
           Geom.STX AS [Longitude],
           Geom.STY AS [Latitude]
    FROM MyLocationsTable
) AS t;