PostGIS - when I add a polygon, delete overlapping areas in other layers

I'm starting to work with PostGIS and I'd like to solve the following problem:

In my database, I have multiple land use class layers, for example: "residential", "industrial", "commercial". The polygons in the layers don't overlap. Some areas are not covered by any layer, which means that the area is undeveloped land.

Now, let's say that I want to simulate a change of the landuse: a new "residential" area polygon is created instead of a part of an industrial area and surrounding undeveloped land (see picture).

I know that to insert the new polygon to the "residential" table, I must write a query like this:

INSERT INTO residential (name, the_geom) 

   VALUES ('new development', ST_GeomFromText('POLYGON((-71.1776585052917 42.3902909739571,
   -71.1776820268866 42.3903701743239,
   -71.1776063012595 42.3903825660754,
   -71.1775826583081 42.3903033653531,
   -71.1776585052917 42.3902909739571))'))

Now, my question: When I insert the new polygon, how do I make sure that all overlapping areas (whole polygons or parts of polygon) in all other layers are deleted?

Answer

This is a really nice application for a PostgreSQL trigger. To set up a trigger in PostgreSQL, you do two things:

1. Create a user-defined function that is run whenever a trigger is called (can be a row insert, update, or delete)


2. Use a CREATE TRIGGER statement to bind that function to a particular table for a particular operation (in this case, INSERT).

Here's how this might work in your case. (This example only operates on a single table. It sounds like you have separate tables for different land uses. You could consider combining these into a single table and using a field to differentiate land use, or you could extend the example below create a trigger on each of the three tables, that would remove overlaps in the other tables.)

Create a user-defined function

CREATE OR REPLACE FUNCTION residential_remove_overlap() RETURNS trigger AS
$$ BEGIN
    -- Run this statement whenever the trigger is called:
    UPDATE residential SET the_geom = ST_Difference(the_geom, new.the_geom)
        WHERE ST_Intersects(the_geom, new.the_geom);


    -- Run this after clipping to remove any polygons that were completely
    -- covered by the new polygon
    DELETE FROM residential WHERE ST_IsEmpty(the_geom);

    -- Return the new record so that it continues through the INSERT/UPDATE
    -- statement unmodified.
    RETURN NEW;
END;
$$

language 'plpgsql';

Bind the trigger to your table

DROP TRIGGER IF EXISTS residential_remove_overlap_trigger ON residential;
CREATE TRIGGER residential_remove_overlap_trigger 
    BEFORE INSERT ON residential
    FOR EACH ROW EXECUTE PROCEDURE residential_remove_overlap();

Test it

CREATE TABLE residential (id int, the_geom geometry);


-- insert function definition and CREATE TRIGGER code


INSERT INTO residential VALUES (1, ST_GeomFromText('POLYGON ((0  0, 1   0, 1   1, 0   1, 0   0))'));
INSERT INTO residential VALUES (2, ST_GeomFromText('POLYGON ((.5 0, 1.5 0, 1.5 1, 0.5 1, 0.5 0))'));

SELECT id, ST_AsText(the_geom) FROM residential;

-- Returns:

-- 1;"POLYGON((0.5 0,0 0,0 1,0.5 1,0.5 0))"
-- 2;"POLYGON((0.5 0,1.5 0,1.5 1,0.5 1,0.5 0))"

INSERT INTO residential VALUES (3, ST_GeomFromText('POLYGON ((0  0, 2   0, 2   2, 0   2, 0   0))'));
SELECT id, ST_AsText(the_geom) FROM residential;
-- Returns:
-- 3;"POLYGON((0 0,2 0,2 2,0 2,0 0))"

This only covers the case of inserting a new polygon to the table, but this function could be modified to handle an UPDATE in the same way.