5

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

enter image description here

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?

14

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.

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.