I want to be able to use a polygon in a database to remove all features and parts of features that falls within the polygon. Not as part of a query transferring the database to another database. I want to remove the features and parts of features inside of the database I am working in. Could someone point me to a example of how to do this with a postgis query? I want to be able to erase the parts of features that overlap the polygon.
Assuming you have two tables "a" and "b".
Table "a" is the table to be cut.
CREATE TABLE a ( id serial PRIMARY KEY, geom geometry(MultiPolygon,31370) );
Table "a" is of type multipolygon because we don't know yet if some polygons will be separated in multiple parts afther the "cookie cut".
CREATE TABLE b ( id serial PRIMARY KEY, geom geometry(Polygon) );
Table "b" holds your cutting polygons.
If you only want to select and see the result you can write this query in the DBManager:
SELECT row_number() over() id, a.id a_id, -- use ST_Multi to force multipolygons: ST_Multi(ST_Difference(a.geom, cutter.geom)) geom FROM a, -- Create one big cutter to avoid duplicate results: (SELECT ST_Collect(b.geom) geom FROM b) cutter WHERE NOT ST_Contains(cutter.geom, a.geom);
If you want to really update your table to remove contained parts and to cut overlapping parts, you can write:
-- remove parts contained in the "cutter" DELETE FROM a USING b WHERE ST_Contains(b.geom, a.geom); -- cut the other parts UPDATE a SET geom = ST_Multi(ST_Difference(a.geom, cutter.geom)) FROM (SELECT ST_Collect(b.geom) geom FROM b) cutter;
Here is the rest of table "a":