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.

Answer 1

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:

• Is it ok to group by geometry in PostGIS?
• One-to-many spatial join with results in one row in QGIS