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