I have a single table containing the geometries of ~3 million polygons and I am looking to calculate the number of polygons that overlap with another.
I know that you are able to do this in QGIS using the Topology Checker Plugin in Plugin Manager, I have used this when comparing much smaller numbers of polygons however I don't see how this would handle such a large amount of polygons. Also, from my research you are unable to use the results i.e. get a list of id's/display a map
After deciding the Topology Checker Plugin wouldnt work I found this:
http://postgis.17.x6.nabble.com/Method-to-remove-overlaps-in-a-layer-td4621793.html
I followed the steps and created the topology for all of the polygons successfully however my relation table wasn't populated and therefore the final step didn't work:
SELECT r.element_id as face, array_agg(r.topogeo_id) as geoms
FROM topo3.relation r
WHERE r.element_type = 3 -- 3 == face
GROUP BY face
HAVING count(r.topogeo_id) > 1;
I am running out of ideas and was wondering if anyone could possibly help
Best Answer
Assuming my_id is integer or other type where '<' makes sense. If not use '<>', but the you'll get 2 records out for each overlap.