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:


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

  • Have you tried running st_overlaps?
    – Matt
    Commented Jun 8, 2017 at 11:42
  • 1
    See gis.stackexchange.com/q/243565/18189. Note that ST_Overlaps is false if polygon A is contained within polygon B.
    – dbaston
    Commented May 18, 2018 at 1:19

1 Answer 1

select *
  from b.mypolygons as t1, b.mypolygons as t2
  where ST_Overlaps(t1.geom, t2.geom) = true
    and t1.my_id < t2.my_id

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.

  • if you really only need the count, then change first line to: select count(*)
    – Martin
    Commented May 17, 2018 at 9:08
  • if ST_Overlaps(geom,geom) is too slow, consider ST_DWithin(geom, geom,0) and always remember to build spatial index first.
    – Martin
    Commented Jun 7, 2018 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.