1

I have a set of overlapping polygons and would like to record which polygons are overlapping at each intersection. I have followed the instructions at:
boundlessgeo.com/2014/10/postgis-training-creating-overlays/

So I now have a set of polygons recording all the unique intersections between all the overlapping polygons (and have added the count of the number of overlaps) but I am stuck at how to indicate which polygons overlapped at that area. So for example if I had 26 polygons labelled a-z I'd like to end up with a table with 26 columns for each intersection showing a 1 or a 0 as to whether it was overlapping at that intersection. enter image description here

Code used so far

*1 break up intersecting polygons

CREATE TABLE boundariesd AS SELECT ST_Union(ST_ExteriorRing(geom)) AS geom FROM uniond;

*2 create unique polygons from intersections

CREATE SEQUENCE polyseqd; CREATE TABLE polysd AS SELECT nextval('polyseqd') AS id, (ST_Dump(ST_Polygonize(geom))).geom AS geom FROM boundariesd;

*3 get the count of intersecting polygons

ALTER TABLE polysd ADD COLUMN count INTEGER DEFAULT 0; UPDATE POLYSD set count = p.count FROM ( SELECT count(*) AS count, p.id AS id
FROM polysd p JOIN uniond c ON ST_Contains(c.geom, ST_PointOnSurface(p.geom)) GROUP BY p.id ) AS p WHERE p.id = polysd.id;

John Barça suggested using crosstabN() which looks appropriate but still not clear to me how to implement it.

  • 1
    You are looking for crosstab in Postgres. However, this is going to get large really quickly, if you table is going to be n * n in the number of input polygons. You could also use array_agg, which will produce an array of intersected polygons for each input polygon-. – John Powell Nov 20 '14 at 10:43
  • 1
    I wonder if it would help if you explain why you wish to do this. Maybe there's a better method for achieving the same end than one as complex as yours. What are these polygons and how many or what types of overlaps do you need to determine? – Martin F Nov 20 '14 at 21:26
  • I agree with @martinf. I initially thought this was interesting, but I can't see how it would scale. If you could explain a bit more about what you are trying to achieve, it would help. A sparse matrix sounds like a better solution? – John Powell Nov 24 '14 at 14:22

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.