无需关联参考表：计算ID最近距离及匹配对应城市人口排名

Got it, let's tackle this problem step by step. Since we can't join the tables directly, we'll need to calculate geographic distances between each ID's coordinates and every city, then pick the closest one to get its population rank. Here's how to do it in SQL Server:

Step 1: Assumed Table Structures #

First, let's define the table structures (since you didn't provide them explicitly, these are realistic assumptions):

• City_table: city_id (INT), latitude (FLOAT), longitude (FLOAT), population_rank (INT)
• ID_table: id (INT), id_latitude (FLOAT), id_longitude (FLOAT)

Step 2: Use SQL Server's Spatial Functions for Distance Calculation #

SQL Server has built-in geography functions that handle coordinate math far more accurately than manual formulas. We'll convert raw latitude/longitude values into GEOGRAPHY points, then use STDistance() to compute the shortest distance between two points.

Step 3: Full Solution Query #

This query uses CTEs to break down the problem, calculates distances for every ID-city pair, then picks the closest city for each ID using a window function:

WITH CityGeography AS (
    SELECT
        city_id,
        population_rank,
        -- Convert city coordinates to a geography point (SRID 4326 = standard WGS84 GPS system)
        GEOGRAPHY::Point(latitude, longitude, 4326) AS city_geo
    FROM City_table
),
IDGeography AS (
    SELECT
        id,
        GEOGRAPHY::Point(id_latitude, id_longitude, 4326) AS id_geo
    FROM ID_table
),
IDCityDistances AS (
    SELECT
        ig.id,
        cg.city_id,
        cg.population_rank,
        -- Distance in meters (divide by 1000 for km, 1609.34 for miles)
        ig.id_geo.STDistance(cg.city_geo) AS distance_meters,
        -- Rank cities by distance for each ID (closest = rank 1)
        ROW_NUMBER() OVER (PARTITION BY ig.id ORDER BY ig.id_geo.STDistance(cg.city_geo) ASC) AS distance_rank
    FROM IDGeography ig
    CROSS JOIN CityGeography cg
)
SELECT
    id,
    city_id AS closest_city_id,
    population_rank AS closest_city_population_rank,
    ROUND(distance_meters / 1000, 2) AS distance_km -- Format distance for readability
FROM IDCityDistances
WHERE distance_rank = 1;

Key Details Explained: #

• GEOGRAPHY::Point(lat, lon, 4326): Converts raw coordinates into a spatial point that SQL Server can use for distance calculations.
• STDistance(): Returns the shortest distance between two geography points in meters. Adjust the division factor if you need miles or other units.
• ROW_NUMBER() OVER (PARTITION BY ig.id ...): Assigns a unique rank to each city for every ID, ordered by distance. We filter for distance_rank = 1 to get only the closest city.

Step 4: Optimize for Large Tables #

If your tables have thousands of rows, a full cross join will be slow. Try these optimizations:

• Add Spatial Indexes: Create indexes on the geography columns to speed up distance calculations:

  CREATE SPATIAL INDEX SIX_City_table_CityGeo ON City_table (city_geo)
  USING GEOGRAPHY_GRID WITH (GRIDS = (LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH));
  

• Filter Nearby Cities First: Instead of pairing every ID with every city, filter cities within a reasonable radius first to reduce calculations:

  -- Adjust the IDCityDistances CTE to use a filtered join instead of cross join
  SELECT
      ig.id,
      cg.city_id,
      cg.population_rank,
      ig.id_geo.STDistance(cg.city_geo) AS distance_meters,
      ROW_NUMBER() OVER (PARTITION BY ig.id ORDER BY ig.id_geo.STDistance(cg.city_geo) ASC) AS distance_rank
  FROM IDGeography ig
  JOIN CityGeography cg
      -- Only consider cities within 50km (adjust radius as needed)
      ON ig.id_geo.STDistance(cg.city_geo) < 50000
  

内容的提问来源于stack exchange，提问作者Bjorno