How to include aggregate field values of clustered points

Question

I am creating a map that has point features representing things like campgrounds, picnic areas, boat launches, etc. In cases where many points are clustered in an area (representing amenities for the same park or campground), I'd like to create a single point that includes the values of each point in the cluster, so that I can symbolize it as described in Group and align icons in QGIS atlas.

I created the centroid of the point cluster using ST_ClusterWithin() described in Spatial clustering with PostGIS.

The string_agg function mentioned in How to concatenate strings of a string field in a PostgreSQL 'group by' query seems to fit the bill for aggregating the "amenity" field values. However, I need to "group by" the spatial cluster of points, not tabular values.

Conceptually, it would look something like below, but the values returned in agg_amenity would be grouped by the points each cluster. Right now, it just aggregates values from all points. Concept code is below:

SELECT row_number() OVER () AS gid,
    f.agg_amenity,
    ST_NumGeometries(f.gc) AS numgeom,
    ST_Centroid(f.gc) AS geom
   FROM ( SELECT string_agg(table.amenity, ';') AS agg_amenity,
            unnest(ST_ClusterWithin(table.geom, 250)) AS gc
           FROM table) f;

The input looks something like:

| gid | name   | site_name      | amenity    | geom  |
| 1   | Park A | park camground | campground | point |
| 2   | Park A | park picnic    | picnic     | point |
| 3   | Park A | park swim      | swimming   | point |
| 4   | Park B | park camground | campground | point |
| 5   | Park B | park camground | groupsite  | point |

And the output should look like this:

| gid | name   | amenity                        | geom                        |
| 101 | Park A | 'campground, picnic, swimming' | point (centroid of cluster) |
| 102 | Park B | 'campground, groupsite'        | point (centroid of cluster) |

How can I combine spatial clustering and aggregation of the amenity field (or multiple fields, if possible)? I am using Postgres 9.5 and PostGIS 2.2.

Answer 1

You're missing a GROUP BY to specify aggregation of the rows.

WITH clusters AS(
    SELECT unnest(ST_ClusterWithin(table.geom, 250)) AS gc
       FROM table)
, unclustered AS( --unpacking GeomCollections
    SELECT row_number() OVER () AS cluster_id, (ST_DUMP(gc)).geom AS geom 
       FROM clusters)
SELECT cluster_id as gid, 
    string_agg(table.amenity, ';' ORDER BY table.amenity) AS agg_amenity,
    COUNT(1) AS numgeom,
    ST_Centroid(ST_Collect(geom)) AS geom
    FROM table
    INNER JOIN unclustered USING(geom)
    GROUP BY cluster_id

An aside, I haven't compared performance between the two functions, but I feel like my clustering function (also the furthest down answer on the question you linked to) produces more useful output rows {gid, geom, and a cluster_id} than the now built_in function. If you're getting multiple of the same kind of amenity within your clusters you'll want to GROUP BY cluster_id, amenity before aggregating.