# 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?

## 1 Answer

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!) – geozelot Oct 10 '19 at 10:00