PostGIS – Using CASE to Select Between Two Geometry Functions

postgis

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 🙂

Best Answer

  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.

Related Question