2

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?

4

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
| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.