# Find closest polygon from point and get its attributes?

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?

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
• the note about database maintenance (VACUUM ANALYZE) might be of high interest concerning your other question about multiple joins (I did a quick test on 2.5M lines vs 250K polygons with my EXISTS construct and had results in under a minute on a mid tech machine!) Oct 10, 2019 at 10:00