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