Postgresql – PostGIS: Cutting Holes in Self Geometry

postgresql

I have a table of geometries in PostGIS, some of which overlap. When I display the geometries, this has the unfortunate consequence of blending the two stacked geometries together and producing an unwanted color in my application.

How can I step over all geometries, identify all that have "children", and cut them out of the "parent"? I created a temporary column called newgeom, as to not clobber my real geometry (geom) for testing, and tried, to no avail:

UPDATE zones a SET newgeom = ST_Difference(b.geom, a.geom) FROM zones b WHERE ST_Contains(b.geom, a.geom) AND a.id != b.id;

Best Answer

I was able to achieve a sub-optimal solution with the help of Craig and Alexandros in the above comments.

  1. Identify which polygons are children, and flag them as such:

    UPDATE zones a SET parent = b.id FROM zones b WHERE ST_Contains(b.geom, a.geom) AND a.id != b.id;

  2. Perform two queries in TileMill for both parental, and children geometries

    Parents: (SELECT * from zones WHERE parent IS NULL)
    Children:(SELECT * from zones WHERE parent IS NOT NULL)

  3. Apply a comp-op to the parental layer only and ensure that the children geometry layer is "above" the parental one in the layers list so that it is draw on top.

Unfortunately, this is still not perfect. Children geometries still cannot have an opacity value, otherwise the parent color will blend, and cause it to take on another color.

Related Question