如何为临时表每行匹配最近OSM节点（单SQL查询实现）

Got it, let's solve this problem properly. Since you're stuck needing a single query (no multi-statement support) and UNION isn't playing nice with ORDER BY for your use case, window functions are the perfect tool here—they let you group results by each point in TempPoints and pick only the closest match from the Points table.

Core Solution #

We'll use a CTE (Common Table Expression) to first calculate distances between every point in TempPoints and all Points entries, then rank those distances per TempPoints record, and finally filter to keep only the top-ranked (closest) match:

WITH RankedDistances AS (
    SELECT
        tp.lat,
        tp.lon,
        p.id AS closest_osm_node_id,
        ST_Distance(POINT(tp.lon, tp.lat), p.geo_point) AS distance,
        -- Rank distances for each TempPoints entry, closest first
        ROW_NUMBER() OVER (
            PARTITION BY tp.lat, tp.lon  -- Use a unique TempPoints ID if available instead!
            ORDER BY ST_Distance(POINT(tp.lon, tp.lat), p.geo_point) ASC
        ) AS distance_rank
    FROM TempPoints tp
    CROSS JOIN Points p
)
-- Select only the closest match for each TempPoints point
SELECT
    lat,
    lon,
    closest_osm_node_id,
    distance
FROM RankedDistances
WHERE distance_rank = 1;

Key Notes to Avoid Headaches #

1. Use a Unique Partition Key (If Possible)
   If your TempPoints table has a primary key (like temp_id), replace tp.lat, tp.lon in the PARTITION BY clause with that ID. This prevents grouping errors if two points in TempPoints have identical lat/lon values.

2. Double-Check Coordinate Order
   Most spatial systems (like WGS84) expect POINT(longitude, latitude)—make sure you're not flipping lat/lon here, or your distance calculations will be wrong!

3. Optimize for Large Datasets
   A full CROSS JOIN will crawl with big tables. Fix this with two tweaks:

   • Add a spatial index on Points.geo_point (e.g., CREATE INDEX idx_points_geo ON Points USING GIST(geo_point);).
   • Filter nearby points first with ST_DWithin to cut down on unnecessary distance calculations:

     WITH RankedDistances AS (
         SELECT
             tp.lat,
             tp.lon,
             p.id AS closest_osm_node_id,
             ST_Distance(POINT(tp.lon, tp.lat), p.geo_point) AS distance,
             ROW_NUMBER() OVER (
                 PARTITION BY tp.lat, tp.lon
                 ORDER BY ST_Distance(POINT(tp.lon, tp.lat), p.geo_point) ASC
             ) AS distance_rank
         FROM TempPoints tp
         -- Only join with Points within 1km (adjust the distance to fit your data)
         JOIN Points p ON ST_DWithin(POINT(tp.lon, tp.lat), p.geo_point, 1000)
     )
     SELECT lat, lon, closest_osm_node_id, distance
     FROM RankedDistances
     WHERE distance_rank = 1;
     

   This drastically reduces the number of rows processed, making the query run way faster for large datasets.

Why This Beats UNION #

UNION is designed to combine separate result sets, but your goal is to pair each individual point in TempPoints with its closest match—window functions let you group and rank results per TempPoints entry directly, no need to run multiple queries and stitch them together.

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