Calculation of Closest Distance of one table's IDs to another reference table without joining in reference table

Question

Really confused on this: so I'm trying to get the rank of the city in terms of population from https://gist.github.com/Miserlou/c5cd8364bf9b2420bb29 (I converted this into a csv and uploaded to our SQL server, let's call it City_table) that each ID in ID_table is closest to.

City_table has the latitude and longitude of each city as well as the rank of each city in terms of population, and the ID_table has the latitude & longitude of each ID. I can't join in City_table, because I'll need to calculate the distance from each ID to each city, and take the minimum of that.

The calculation below gets the distance from one location to another and converts to miles:

(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

To recap, ID_table has an ID, latitude, and longitude. City_table has a latitude, longitude, city, and rank according to highest population. I need to get the rank of the city from City_table that is closest to the ID's location.

I really just don't know how to do this and would really appreciate any help.

I'm trying to accomplish something like the following (acknowledging the syntax isn't right, just the idea of what I think I'm trying to accomplish) @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'

So I ended up figuring this out, but for future reference if anyone runs into this same problem, I figured out a solution (albeit maybe not the best one) but it works:

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

@hastrb yeah see above, i edited the original question to include an idea of what I think I should be doing. — Bjorno
Two suggestions. 1) Use the geography type 2) Post a small sample and desired results. — John Cappelletti

Alexandre Alexandre · Accepted Answer · 2018-09-25T14:04:21

I strongly suggest (as already mentioned by John Cappelletti) to use the geography type. I had to do something similar as you, where my reference table contained the geography of the locations. I joined it to the main query "ON 1=1". That way, for each row of your main query, you will have a location from your locations table (just keep in mind that if the tables you're dealing with are large this will be slow!). In any case, the query looks something like this:

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

Calculation of Closest Distance of one table's IDs to another reference table without joining in reference table

1 Answers