1

I have a large polygon table and a large point table in a postgis enabled PostgreSQL database. Each point has a unique reference number (double type). I want to spatial join the points to the polygons, but where multiple points are found within a polygon, I want ALL the reference numbers to be carried across into a new column in the polygon table (a string column called ref_numb_list). Multiple reference numbers would be delimited with commas.

This functionality is available in ArcGIS spatial joins where you're able to set a 'join' merge rule for an output column. But I need to do this in PostgreSQL, via pgAdmin.

2

You can perform the spatial join, then group the result by polygon ID and use string_agg to aggregate the point ids.

WITH pt AS (
SELECT 1 as id, 'point(-75.5 47.2)'::geometry geom
  union
SELECT 2 as id, 'point(-75.6 47.3)'::geometry geom
),
poly AS (SELECT 1 as id, 'polygon((-70 51, -81 51, -81 40,-70 40, -70 51))'::geometry geom)
SELECT poly.id polyID,  string_agg(pt.id::text,',') ptIDs
FROM pt, poly
WHERE st_intersects(poly.geom, pt.geom)
GROUP BY poly.id;

 polyid | ptids
--------+-------
      1 | 1,2
(1 row)

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.