A brief introduction to PostgreSQL/PostGIS trigger functions

Trigger functions are a useful tool for DB administrators since they can be used to automatically populate fields when rows are inserted, updated, or deleted. Trigger functions can be used to do such things as generate unique IDs that contain concatenated string and number fields, perform calculations on inputted fields, populate metadata fields (e.g. modified by, modified date), or to insert or update values in other tables (e.g. auditing). With PostgreSQL, the user can define any SQL statement to automatically input values into a field, and with the PostGIS extension one can also add spatial queries. This is where it gets interesting since it is possible to add any value relating to the new geometry, such as length, area, perimeter, centroid coordinates , etc. Not only that, one can also query other tables in the database and check whether or not a new geometry intersects another table or calculate the distance from the new geometry to another table — bottom line, powerful stuff. The possible applications are endless, so the following is really just a starting point. This tutorial is by no way a complete introduction to the subject of database trigger functions, but it aims to demonstrate a few basic examples using a PostgreSQL/PostGIS geodatabase.

The task

This tutorial demonstrates two fundamental types of  PostgreSQL trigger functions. The first part focuses on the creation of basic metadata fields within a table. These fields may include the user who created the field, the date created, the last user who updated the field, the last date when the field was modified, and perhaps a count of the number of edits over time. The second part demonstrates how more complex, spatial queries can be used to populate fields with length, area, perimeter, and the proximity or intersection of the new row geometry to another layer (e.g. how close is a new polygon to a road).

Let’s get started…

Before you start

It would probably be good to have a schema called ‘test’ in your database, or be sure to change the schema name in the following queries appropriately. Also, one will need to have a polygon boundary table (municipal or county boundaries) and a line table (e.g. roads). This tutorial assumes that all these layers and the trigger_tutorial tables created in Step 1 below use the same CRS — it is not absolutely required, but makes the SQL scripts easier to read (i.e. less st_transform’s cluttering up your code). In this example, EPSG 26917 is used, because that’s how I roll. More on this in Step 1.

Step 1: Create the table

Start by creating a table called trigger_tutorial in a schema called test. This table will have the MultiPolygon geometry type. The CREATE TABLE query below has a CRS defined as NAD83 UTM zone 17N (EPSG: 26917), but make it any CRS — we’ll be transforming coordinates to WGS 1984 (EPSG:4326) in Step 3, so anything BUT the world geodetic system is fine. Ultimately, the table needs to contain all the fields that will be populated by the trigger function.

CREATE TABLE test.trigger_tutorial ( 
id serial primary key, 
geom geometry(MultiPolygon, 26917), 
centre_northing decimal, 
centre_easting decimal,
centre_latitude decimal, 
centre_longitude decimal,  
latlong_dms varchar, 
area_ha decimal,  
municipality varchar,  
distance_to_nearest_road_m decimal, 
date_created timestamp with time zone DEFAULT now(), 
created_by character varying DEFAULT "current_user"(), 
date_modified timestamp with time zone, 
modified_by character varying
);

Two metadata fields are generated by default, namely date_created and created_by. The rest of the fields, except for the ID of course, are generated from the trigger function.

Step 2: Trigger function for metadata fields

To make it easier, let’s just generate the metadata fields, namely modified_by that will contain the database username of the user who last modified the row and modified_date will contain the time and date of the last modification.

CREATE FUNCTION test.tutorial_trigger_function() RETURNS trigger AS $$

BEGIN

 NEW.date_modified = now();
 NEW.modified_by = "current_user"();
 
 RETURN NEW;
END;

$$ language plpgsql;

The function uses the plpgsql procedural language, which is one of many that can be used. The procedure updates the fields by using the special variable NEW, which holds the new row that was just created/modified on INSERT or UPDATE. See the documentation for other procedures and a full list of special variables.

Now that the function test.metadata_trigger_function() is defined, it needs to be applied to the test.trigger_tutorial table. To do so, one must create a TRIGGER like this:

CREATE TRIGGER tutorial_trigger
 BEFORE INSERT OR UPDATE
 ON test.trigger_tutorial
 FOR EACH ROW
 EXECUTE PROCEDURE test.tutorial_trigger_function();

One executed, on INSERT or UPDATE the defined function will update the predefined fields (i.e. modified_by and modified_date). Easy peasy, right? Let’s add the spatial queries to the trigger function to populate the other fields.

Step 3: Adding spatial queries to trigger functions

One thing I found helpful is using PGAdmin to change existing trigger functions. It’s as easy as opening the properties of the function to see and modify the existing code. It definitely helps during the debugging/learning/experimentation process. So, to add the new polygon’s centroid coordinates, add the following lines to the function:

NEW.centre_northing = st_y(st_centroid(NEW.geom)); 
NEW.centre_easting =st_x(st_centroid(NEW.geom));

Next, to add the coordinates in latitude and longitude to the centre_latitude and centre_longitude fields, respectively, just add the following lines to the function:

NEW.centre_latitude = st_y(st_centroid(st_transform(NEW.geom,4326))); 
NEW.centre_longitude = st_x(st_centroid(st_transform(NEW.geom,4326)));

There are times when you need lat/long coordinates in degrees-minutes-seconds (DMS), especially when working in the aeronautics sector. PostGIS has great function called ST_AsLatLonText that outputs the DMS of a point as a string. Here’s how to add the centroid coordinates of the new polygon in DMS:

NEW.centre_latlong_dms = ST_AsLatLonText(st_centroid(st_transform(NEW.geom,4326)));

One might also want to calculate the area of the new polygon on the fly. To calculate the area of the new polygon in hectares, add the following to the function:

NEW.area_ha = st_area(NEW.geom)/10000;

One may also want to determine if the new polygon, or in this case the centroid of the new polygon, intersects with certain boundaries (note that I used the centroid to simplify this query since a polygon may intersect a number of municipal boundaries). In the following, the query determines the municipality in which the point is located by using st_within and returns the municipality name (assuming there is a column called muni_name). This fairly simple query highlights the importance of data type when designing trigger functions. If one tries to insert a string into a decimal field, an error will occur and the field will not be populated.

NEW.municipality = (SELECT muni_name 
FROM boundaries.municipalities 
WHERE ST_Within(st_centroid(NEW.geom), geom));

Lastly, the following query calculates the distance from the new polygon to the nearest road. My geodatabase contains a fairly large road layer. If the st_expand function is not used in PostGIS, a similar query would calculate the distance from EVERY road to the new polygon, then, after several long… minutes, it would output the result. To avoid this onerous task, st_expand extracts a bounding box to be used in the query. A WITH query is used to extract roads that are within a 1km radius of the new polygon, then st_distance is used to determine the nearest road from this smaller sample set. This query highlights how important it is for each query to return a single row and column. Hence, adding LIMIT 1 is required to return only the row with the shortest distance to a road.

NEW.distance_to_nearest_road_m = ( 
WITH road_selection AS ( 
SELECT a.* FROM transportation.roads as a 
WHERE st_intersects(a.geom,(ST_expand(NEW.geom, 1000))) ) 
SELECT min(st_distance(NEW.geom,b.geom)) as min_distance 
FROM road_selection b 
ORDER BY min_distance 
LIMIT 1);

After all this, the trigger function should look something like the following:

CREATE FUNCTION test.tutorial_trigger_function() RETURNS trigger AS $$ 

BEGIN 

NEW.centre_northing = st_y(st_centroid(NEW.geom)); 
NEW.centre_easting =st_x(st_centroid(NEW.geom));
NEW.centre_latitude = st_y(st_centroid(st_transform(NEW.geom,4326))); 
NEW.centre_longitude = st_x(st_centroid(st_transform(NEW.geom,4326)));
NEW.centre_latlong_dms = ST_AsLatLonText(st_centroid(st_transform(NEW.geom,4326)));
NEW.area_ha = st_area(NEW.geom)/10000;
NEW.municipality = (SELECT muni_name 
 FROM boundaries.municipalities 
 WHERE ST_Within(st_centroid(NEW.geom), geom));
NEW.distance_to_nearest_road_m = ( WITH road_selection AS ( 
SELECT a.* FROM transportation.roads as a 
WHERE st_intersects(a.geom,(ST_expand(NEW.geom, 1000))) ) 
SELECT min(st_distance(NEW.geom,b.geom)) as min_distance 
FROM road_selection b 
ORDER BY min_distance 
LIMIT 1);
NEW.date_modified = now(); 
NEW.modified_by = "current_user"(); 

 RETURN NEW; 
END; 

$$ language plpgsql;

 

Test the function thoroughly in QGIS and if no errors pop up and the fields populate as expected, buy yourself a beer and bask is your glory. Evil laugh is optional, but highly recommended. Mwahahaha!

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *