Let's say I have 2 VIEWS. Each has a geometry column with polygons that overlap, approximately like so:

enter image description here

I'd like to delete the portions of of the green polygons where there is any overlap with the grey ones, approximately like so:

enter image description here

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

CASE WHEN (view_green.timestamp < view_grey.timestamp)        
     THEN ST_Difference(view_green.geom, view_grey.geom)        
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:

enter image description here

Where am I going wrong?

1 Answer 1


I have had similar problems with formulating erase/difference queries. I have found that dissolving/st_union the geometry you are doing the cutting with is the best way to go.

 SELECT green.*,
                                 (SELECT st_union(gray.shape)
                                  FROM gray
                                  WHERE st_intersects(green.shape, gray.shape))), green.shape) AS geom
FROM green;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.