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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.