I'm trying to cut holes in forest polygons, where there are lakes.

My PostGIS/PostgreSQL code look like this:

CREATE TABLE samp.forest_with_holes AS
    (CASE ST_Intersects(fo.geom, la.geom)
      WHEN TRUE THEN ST_Difference(fo.geom, so.geom)
      ELSE fo.geom
    END CASE;) as geom
  from samp.forest as fo, samp.lake as la;

But I get the error message:

ERROR:  syntax error at or near "CASE"
LINE xx:     END CASE;) as geom

Can anybody help me to see why this is not working - and please suggest a better way :-)

1 Answer 1

  1. Correct way of using CASE:

      WHEN ST_Intersects(fo.geom, la.geom) THEN ST_Difference(fo.geom, la.geom)
      ELSE fo.geom
    END as geom
  2. Better query:

    SELECT fo.objectid, COALESCE(ST_Difference(fo.geom, ST_Collect(la.geom)), fo.geom) AS geom
    FROM samp.forest AS fo 
    LEFT JOIN samp.lake AS la ON ST_Intersects(fo.geom, la.geom)
    GROUP BY fo.objectid


Coalesce gives you the first non-null input and since the difference between a geom and null results in null it will return the next value: 'fo.geom'. The ST_Collect aggregate is needed to make sure you cut multiple holes when there is more than 1 lake in the same forest polygon.

  • 1
    It's a good answer although you might want to explain why it is a better query. Oct 10, 2016 at 14:05
  • ad. 1) I had to add an SELECT DISTINCT to get the result I want. But otherwise it works like a charm :-)
    – Martin
    Oct 11, 2016 at 6:14
  • ad. 2) I have to agree... It works, but I don't know why? Plus, It doesn't include my fo.objectid column, and I can't figure out where to put it...
    – Martin
    Oct 11, 2016 at 6:15
  • I've added a brief explanation, but please check the manual for Coalesce yourself as well. Also I fixed an omission in the query (ST_Collect) since you shouldn't have to use DISTINCT. It meant you were losing information when there was more than 1 lake in the forest polygon.
    – tilt
    Oct 11, 2016 at 9:10

Your Answer

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

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