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?