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.