Spatial join attributes based on closest point in PostGIS

Question

I have two point tables from which I want to extract a value from one table to the other, based on the nearest distance of those two points.

t1               t2
id | value       id
1  | 100         1
2  | 105         2
3  | 120         3
4  | 102         4
etc

So if t2 with id 1 is closest to t1 with id 2 it should get the value 105. The t2 table has more entries than t1 and all points should get a value.

Using the following query I am able to get the distance to the nearest point:

SELECT round(ST_Distance(
 (SELECT ST_AsText(t2.wkb_geometry) FROM t2 WHERE id = 1),
 (SELECT ST_AsText(t1.wkb_geometry) FROM t1
ORDER BY ST_AsText(t1.wkb_geometry) <->
    (select ST_AsText(t2.wkb_geometry) FROM t2 where id = 1) LIMIT 1 )
 ))
as Distance;

How would I add the corresponding t1 values bases on the closest point to t2 for all the t2 rows?

Answer 1

Run

SELECT a.id,
       b.value,
       -- ST_Distance(a.wkb_geometry::GEOGRAPHY, b.wkb_geometry::GEOGRAPHY) as dist,
       a.wkb_geometry
FROM t2 AS a
JOIN LATERAL (
  SELECT value
  FROM t1
  ORDER BY a.wkb_geometry <-> t2.wkb_geometry
  LIMIT 1
) AS b
ON true;

It's obligatory to have spatial indexes in place on both wkb_geometry columns.

If maximum accuracy is of importance, use a cast to geography, i.e.

... ORDER BY a.wkb_geometry::GEOGRAPHY <-> t2.wkb_geometry::GEOGRAPHY ...

at a slight increase of execution time.

Uncomment the distance calculation if you need distance in meters.

Note: the cast to geography does only work if your geometry columns are in EPSG:4326! If they are, it's a good idea to use the cast at least for the distance calculation.

This is a typical KNN query; you can find plenty of rerence on this board or elsewhere on the net for more detail on the general matter using PostGIS.

Possibly related:

• How to UPATE with LATERAL Nearest-Neighbor query?