3

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.

  • Please edit the question to clarify whether you want "Erase" functionality (remove only those parts of features which overlap) or just a simple "Delete" of all features which overlap. – Vince May 15 '16 at 19:36
3

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.

Here are both layers shown in QGIS (a: blue, b: transparent orange): Tables a and b (cutter)

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);

Result:

Result from SELECT

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":

Table a after cut

  • DELETE FROM table_cutting_happens_in – exploregis May 16 '16 at 1:15
  • Never mind my previous comment. It's past the 5 minutes limit to fix it. – exploregis May 16 '16 at 1:29
  • I tried to do your code sample and I could not get it to work. So I need to understand how it works better. What is the STMulti for? – exploregis May 16 '16 at 1:32
  • ST_Multi is to force a multipart geometry. We need to use it because we don't know in advance if some polygons will be cut in multiple parts after the "cutter" has been applied, and it's better to avoid mixed geometry type, so we convert everything to multipolygons; – thibautg May 16 '16 at 8:58
  • 1
    Thanks for answering my question with the detail I needed to understand. – exploregis May 17 '16 at 3:16

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.