Counting stacked polygons in PostGIS

Question

In my data set I have cases where I have multiple polygons stacked upon top of each other. I assume that those polygons have equal geometries but since there are a lot of them I am not sure 100%. Data is in a PostGIS database.

I am looking for PostGIS solution for counting those polygons inside procedure.

For now I came up with this but it looks like it takes too much time. I join polygons to the same data set and I use current_record to set id of polygon data set.

select count(*) into stacked_count from polygons p1
join polygons p2
on st_equals(current_record.wkb_geometry, p2.wkb_geometry)
where p1.ogc_fid=current_record.ogc_fid;

In a procedure I loop over every polygon and I do some calculations, so every current_record is a defined as %ROWTYPE of the polygon dataset.

If you expect them to be (binary) equal, you could SELECT geom, COUNT(*) FROM ... GROUP BY geom; — geozelot, Aug 05 '21 at 17:42
Does this table already have a spatial index? That would speed things up. — Konan Pruiksma, Aug 05 '21 at 17:56

score 1 · Answer 1 · answered May 09 '22 at 15:25

Let's assume there is a polygon layer called 'poly_test', see image below.

Extending the idea from @geozelot it is possible to count the polygons via grouping by their geometry.

With the following expression:

SELECT
    GROUP_CONCAT("id") AS ids,
    GROUP_CONCAT("uniqueID") AS uniques,
    COUNT(*) AS NumGeom, -- COUNT(*)
    geometry AS geom
FROM
    "poly_test"
GROUP BY
    geometry

it is possible to get this output:

Alternatively, apply the query, that implies the ST_AsBinary() function, however, can be also used with the ST_AsText():

SELECT
    GROUP_CONCAT("id") AS ids,
    GROUP_CONCAT("uniqueID") AS uniques,
    COUNT(ST_AsBinary(geometry)) AS BinGeom, -- COUNT(*)
    geometry AS geom
FROM
    "poly_test"
GROUP BY
    ST_AsBinary(geometry)

References:

Counting stacked polygons in PostGIS

1 Answers1