2

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

SS_Rebelious
  • 5,621
  • 3
  • 27
  • 62
mon_mome
  • 21
  • 2

1 Answers1

-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.

Martin
  • 550
  • 4
  • 16
  • if you really only need the count, then change first line to: select count(*) – Martin May 17 '18 at 09:08
  • if ST_Overlaps(geom,geom) is too slow, consider ST_DWithin(geom, geom,0) and always remember to build spatial index first. – Martin Jun 07 '18 at 21:27