Database triggers

From Open source mapmaking technologies
Jump to: navigation, search

Database triggers

We can take advantage of some nice features of PostgreSQL+PostGIS.

In this exercise, we want to define two triggers:

  • The first one is used to compute the area of the building. So, whenever the users adds or changes buildings, the area is computed and stored in the field area;
  • The second triggers is fired when the user creates or changes a point.

Creating a new buildings table to define triggers

We will use pgAdmin III to define a trigger. To play with triggers we will create a new table with buildings in Wuhan.

CREATE TABLE wuhanbuildings AS
SELECT buildings.* 
FROM buildings, chn_adm3
WHERE name_2 = 'Wuhan'
AND st_contains(chn_adm3.wkb_geometry, buildings.wkb_geometry);
ALTER TABLE wuhanbuildings ADD CONSTRAINT wuhanbuildings_pkey PRIMARY KEY(ogc_fid);
CREATE SEQUENCE buildseq;
ALTER TABLE wuhanbuildings ALTER COLUMN ogc_fid SET DEFAULT nextval('buildseq');
ALTER TABLE wuhanbuildings ALTER COLUMN ogc_fid SET NOT NULL;
ALTER SEQUENCE buildseq OWNED BY wuhanbuildings.ogc_fid;
select setval('buildseq', (select max(ogc_fid)+1 from wuhanbuildings), false);

Adding a new field to wuhanbuildings table

For this exercise, we need an additional field area, double precision type.

Use pgAdmin III to add the field to the table.

We can also add the new field using the following SQL expression:

ALTER TABLE wuhanbuildings ADD COLUMN area double precision;

Open pgAdmin III

To update the new area field with the buildings area, we can write the following query:

UPDATE wuhanbuildings SET area = st_area(wkb_geometry);

It uses the function st_area that calculates de area of a given geometry.

But this update only happens when we call the SQL statement. We want to update each building's area whenever its geometry changes.

For that, we use database triggers.

The trigger itself is an event that runs under certain conditions. We start by defining a function that the trigger will call. SO, instead of just defining the trigger, we are defining the trigger and the function it calls.

CREATE OR REPLACE FUNCTION computes_building_area()
  RETURNS TRIGGER AS
$BODY$
   BEGIN      
       NEW.area := st_area(NEW.wkb_geometry); 
       RETURN NEW;
   END;
$BODY$
   LANGUAGE 'plpgsql'

Now that we already have a fuction to compute the building's area, we can define the trigger.

CREATE TRIGGER update_building_area
  BEFORE INSERT OR UPDATE
  ON wuhanbuildings
  FOR EACH ROW
  EXECUTE PROCEDURE computes_building_area()

Now that we already defined the trigger and the function it calls, use the pgAdmin III Object browser panel to check the new function and trigger. You might need to use 'refresh', if you don't see the new function and trigger.

Test the trigger

Switch to QGIS, and add the wuhanbuildings table.

Toggle edit to on on wuhanbuildings layer. Add two or three new buildings. Afterwards, toggle edit to off and save the editions.

Inspect the new buildings. The area should be filled.

Edit the wuhanbuildings layer again, and change the geometry of one of the buildings you made. Save the changes and check that the area also changed.

Our trigger fires on insert or update.

Exercise

Study the following code. Test it and ensure that you understand what it does.

ALTER TABLE points ADD COLUMN district character varying(75);

CREATE OR REPLACE FUNCTION computes_district()
  RETURNS TRIGGER AS
$BODY$
  BEGIN      
    SELECT name_3 INTO NEW.district
    FROM chn_adm3
    WHERE st_contains(wkb_geometry, NEW.wkb_geometry);
    RETURN NEW;
  END;
$BODY$
  LANGUAGE plpgsql;

CREATE TRIGGER update_district
  BEFORE INSERT OR UPDATE
  ON points
  FOR EACH ROW
  EXECUTE PROCEDURE computes_district();

If you update the table outside QGIS, remember to drop and reload it again in QGIS, so that new columns can be loaded too.