2

I'm looking for guidance on a pgSQL query to dissolve polygons in PostGIS that are within 200 m of each other, have the same creation date and are above a threshold area.

I am trying

SELECT 
  ST_Union(ST_Buffer(geom, 100)) as bffrd, 
  ST_Union(geom) as parcelgeom, "Date", 
  STRING_AGG("name",',') AS namez, 
  STRING_AGG("Desc",',') AS Descz 
FROM mapz.polygons 
WHERE ST_Area(geom) >'20000' 
GROUP BY "Date" ;

However this query does what it says and aggregates the polygons by date, not being restricted to adjacent polygons. So I am getting a multipolygon feature with polygons very distant from the target which have erroneously been included.

For the most part, the polygons of interest do not overlap. All the training guides I have seen discuss joins between tables, whereas all these polygons are in the one table. Also, the resulting polygons should retain their same size, I am not trying to increase their size, only select and aggregate them into multipolygons based them on the buffer.

I think I am seeking how to include ST_Buffer(geom, 100) in the Group By but that won't run as a query for me.

  • 2
    The problem with the criterium "lie within 200m" is that it is non-deterministic. There could be a set of polygons which like within 200m of some polygon but not all polygons. If you want the entire set, then perhaps a clustering approach like DBSCAN is needed? – dr_jts Nov 2 at 6:46
  • Hi @dr_jts I think I see where you are coming from, the issue you are exploring is not about clustering but rather a logic issue, that I want to associate polygons with each other but they are not differentiated from each other in the query. So the query sees them all as A. Lets add syntax of .n, which the query doesn't see. So the query would be trying to aggregate A.1 to A.2 then it would see A.2 and want to relate that back to A.1 as if it were a new feature . So you have touched on another issue which I didn't realise. – BJW Nov 2 at 6:51
  • You need to use either ST_ClusterWithin (inside a subquery to handle it separately per date, and with an additional intersection because ST_ClusterWithin drops all other columns), or a recursive CTE. – CL. Nov 2 at 9:53
2

Your goal is an inherent clustering, and an essential application of the DBSCAN algorithm; and with ST_ClusterDBSCAN being a window function, it is trivial to categorize by attributes.

Running

SELECT "Date",
       STRING_AGG("name", ',') AS "namez",
       STRING_AGG("Desc", ',') AS "Descz",
       ST_Union(geom) AS geom
FROM   (
  SELECT "name",
         "Desc",
         "Date",
         geom,
         ST_ClusterDBSCAN(geom, 100, 1) OVER(PARTITION BY "Date") AS _clst
  FROM   mapz.polygons
  WHERE  ST_Area(geom) > 20000
) q
WHERE  _clst IS NOT NULL
GROUP BY
       "Date", _clst
;

will create cluster ids in _clst for polygons larger than the area threshold, that are within eps distance from each other (see the links for details), and dissolves them.

Note that, with the area threshold applied, the DBSCAN algorithm will not consider smaller polygons when clustering, meaning that three polygons will not get clustered if the polygon in the middle has area lesser than the threshold, even if they would have formed a valid cluster by spatial relation of all three.

This assumes that your geometries are projected in a suitable projection (having meter as units).


For more info on the cluster id assignment and the required grouping hierarchy:

| improve this answer | |
  • Having trouble getting this to run, initially it ran endlessly on the original dataset, now with a smaller set I am getting errors ERROR: FILTER is not implemented for non-aggregate window functions – BJW Nov 2 at 21:55
  • Some progress, I removed the filter (and the string aggregations) and I can get the query to run, returning ~24K rows in 20 sec. However QGIS won't load the raw query as a spatial layer bc it doesn't recognise the geometry. SELECT "Date", ST_Multi(ST_Collect(geom)) AS geom FROM ( SELECT "Date", geom, ST_ClusterDBSCAN(geom, 100, 1) OVER(PARTITION BY "Date") AS _clst FROM tests.polygonz ) q WHERE _clst IS NOT NULL GROUP BY "Date", _clst ;. – BJW Nov 2 at 22:25
  • 2
    @BJW ah, right, naturally it doesn't implement an aggregate filter; I updated the query, just filter in a WHERE statement, it should get evaluated before passing to the cluster function. Use ST_Union to dissolve invalid (overlapping) MultiPolygons rather than ST_Collect. – geozelot Nov 2 at 23:56
  • brilliant! That does it! Fast enough too. I am just working on the aggregate functions but I think I have them sorted with STRING_AGG( DISTINCT "name" in there. – BJW Nov 3 at 2:18

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.