Find closest polygon from point and get its attributes?

Question

Given a set of points and a set of polygons, I am trying to find for each point what the closest polygon is and bring over the attributes from that polygon to the point (together with the actual distance). Ideally, I would end up with the original set of points with 2 more fields: 'distance' and 'polygon_id'.

I'm trying to do so with the following query (using ST_DWithin to limit the calculations)

CREATE TABLE test AS
SELECT t1.point_id, t1.geom, t2.polyg_id, (ST_Distance(t1.geom, t2.geom)) as distance
FROM points t1, polygs t2
WHERE ST_Dwithin(t1.geom, t2.geom, 100)
ORDER BY (ST_Distance(t1.geom, t2.geom)) ASC
LIMIT 1

This query is just returning one record though. The 'LIMIT 1' is acting on the whole result. How can I make that 'LIMIT 1' act on the results for each segment individually?

Answer 1

For a (K)NN search, the running table must be parsed in a loop; PostgreSQL offers the LATERAL statement that can be appended to any JOIN to do so (among other useful things). Specifically, it passes the current row of the parent table (left hand side of the join) to the LATERAL subquery (right hand side of the join), where it's column values can be accessed and evaluated.

PostGIS has the <-> distance operator (informally known as KNN operator) that is designed to use the spatial index when used as parameter to the ORDER BY statement, and is the core concept of high performance spatial (K)NN searches:

CREATE TABLE test AS
  SELECT t1.point_id,
         t1.geom,
         t2.polyg_id,
         ST_Distance(t1.geom, t2.geom) AS distance
  FROM points AS t1
  CROSS JOIN LATERAL (
    SELECT polyg_id,
           geom
    FROM   polygs
    WHERE ST_Dwithin(geom, t1.geom, 100)   -- will likely be unnecessary
    ORDER BY
           t1.geom <-> geom
    LIMIT 1
  ) AS t2
;

Notes:

• a spatial index on polygs is mandatory
• always run VACUUM ANALYZE on all tables involved (or the whole DB if you have that moment), after larger changes to the data/index or prior to potentially long running queries, to update the table statistics used by the planner to determine the best approach