1

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
  SELECT
    fo.objectid,
    (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 :-)

5
  1. Correct way of using CASE:

    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

EDIT:

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. – John Powell Oct 10 '16 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 '16 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 '16 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 '16 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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