[GIS] PostGIS Multipolygon – holes

Question

I have MULTIPOLYGON in my PostGIS database to store countries (with islands etc.). But I need to add holes to my borders for lakes. How can I store multipolygon with holes?

I am using ST_SetSRID(ST_Multi(ST_GeomFromText('POLYGON(...)')), 4326) to create multipolygon

Answer 1

You can use ST_Difference to create the holes. It will subtract the lakes from your countries.

Let's say your lakes are in another table called lakes and you've already stored your countries. You would do something like

UPDATE countries SET geom = ST_Difference(geom, 
       (SELECT ST_Union(l.geom) FROM lakes As l 
         WHERE ST_Intersects(l.geom, countries.geom) );