7

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.

  • Are you wanting ST_ConvexHull? postgis.org/docs/ST_ConvexHull.html – kttii Jun 23 '16 at 16:10
  • I used ST_ClusterWithin() to create the centroid, now I need to aggregate the field values of only the points within the cluster. – RyanDalton Jun 23 '16 at 16:16
  • can you provide a picture of what you want to accomplish? – kttii Jun 23 '16 at 16:21
  • 1
    Unfortunately, you can't do this without joining the clusters back to the inputs - it's a fundamental limitation of using an aggregate. (Similarly, the max() aggregate can't tell you anything else about the row where the maximum value was found.) The solution is to use a window-based clustering function, of which some options will be available in 2.3. – dbaston Jun 24 '16 at 14:47
  • 1
    You could join the clusters back to the inputs after the fact (@raphael's answer looks like it should do the trick) or consider compiling PostGIS from source and testing the windowing functions. – dbaston Jun 24 '16 at 18:43
3

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.

  • this function gets me really close. However, I keep getting errors when including "ORDER BY table.amenity" in the str_aggr() function. If I remove that, it almost works. I then an error that I need to include "unclustered.geom" in the GROUP BY clause at the end. If I do this, the result is fine for features that share the same exact coordinates, but does not return geom for features that are clustered but do NOT share same coordinates. – RyanDalton Jun 24 '16 at 19:49
  • @RyanDalton, I just updated, which should solve your issues – raphael Jun 24 '16 at 20:14
  • Thanks for trying to update. It's having trouble on "ST_Centroid(gc) AS geom". I tried changing the field to "clusters.gc", but no luck. – RyanDalton Jun 24 '16 at 20:41
  • @RyanDalton My bad, didn't realize ST_ClusterWithin returned an array, hence needing the unnest call in the clusters CTE. That should now have ST_Centroid acting on geomcollections – raphael Jun 24 '16 at 20:47
  • @raphael I don't understand unnest(gc) in unclustered table. gc is not an array so you can't make an unnest() on it. – Nicolas Boisteault Jun 1 '17 at 13:02

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.