PostgreSQL: Spatial join points to polygon, listing point attributes where multiple are joined

Question

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.

Answer 1

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)