Let's say I have 2
VIEWS. Each has a geometry column with polygons that overlap, approximately like so:
I'd like to delete the portions of of the green polygons where there is any overlap with the grey ones, approximately like so:
VIEWS have polygons that extend out of the boundaries of the example images to the left and right, respectively.
I took a shot at this with the following query (based on a similar example from a book):
SELECT my_value, ST_Intersects(g1.geom1, g1.geom2) As they_intersect , ST_Difference(g1.geom1, g1.geom2) As difference_polygon FROM (SELECT view_green.geom As geom1, view_grey.geom As geom2, view_green.my_value As my_value FROM view_green, view_grey) AS g1;
(keeping a value associated with each polygon as well)
The output from that is the same as the
VIEW and does not work (I'm using openlayers to visualize at the
VIEWS/results). So as a test, I wrote a query to return only the overlapping polygons and delete the areas that overlap:
SELECT view_green.my_value, view_green.timestamp, CASE WHEN (view_green.timestamp < view_grey.timestamp) THEN ST_Difference(view_green.geom, view_grey.geom) END FROM view_green, view_grey WHERE ST_Intersects(view_grey.geom, view_green.geom);
(You'll notice the
.timestamp because I'm trying to delete overlap from a view that is 'older'. Eventually I'd like to have a
TRIGGER similar to this solution and skip the timestamp comparison)
The result of that query does not work fully but performs the deletion correctly (with st_difference()) on only the first polygon, approximately like so:
Where am I going wrong?