Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

PostGIS Triggers

Posted by smathermather on August 12, 2009

Yup, I’m going for hits with the above title.  So, I’ve been playing with triggers in PostGIS (PostgreSQL) trying to streamline a process that I go through quarterly.  We have a geographic boundary layer for our public entity:

boundary_1

The green area is the area with public access.  The purple without.  I want to maintain a version of the boundary with this very simple distinction, and another version of it in which the boundary is dissolved.  Triggers seem ideal here.  When I update the first, I get a version of the second (see below)boundary_2

The command to do this is as follows:

CREATE TABLE public.reservation_boundaries_public_private_cm_dissolved
 AS
 SELECT res, ST_Union(the_geom) AS the_geom
  FROM public.reservation_boundaries_public_private_cm
   GROUP BY res;

So far, so good (sorry about the awful table names).  Now it needs to run each time there is an insert on the original table.  Here is what I came up with:

DROP TRIGGER dissolver ON public.reservation_boundaries_public_private_cm;

CREATE OR REPLACE FUNCTION dissolve_bounds() RETURNS TRIGGER AS $dissolver$

BEGIN
 IF(TG_OP='INSERT') THEN

 DROP TABLE IF EXISTS public.reservation_boundaries_public_private_cm_dissolved;

 CREATE TABLE public.reservation_boundaries_public_private_cm_dissolved
 AS
 SELECT res, ST_Union(the_geom)
   AS the_geom
   FROM public.reservation_boundaries_public_private_cm
   GROUP BY res;

 END IF;
 RETURN NEW;
END;

$dissolver$ LANGUAGE plpgsql;

CREATE TRIGGER dissolver
  AFTER INSERT ON public.reservation_boundaries_public_private_cm
  FOR EACH STATEMENT EXECUTE PROCEDURE dissolve_bounds();

My only complaint here is speed– it takes about 45 seconds (instead of close to 0.3 seconds) now to insert all my records.  I hoped that I could speed it up with the addition of

FOR EACH STATEMENT EXECUTE PROCEDURE

instead of

FOR EACH RECORD EXECUTE PROCEDURE

but the difference in speed is undetectable.  Any suggestions on how to speed this puppy up?

3 Responses to “PostGIS Triggers”

  1. […] 4 out of 6 of my triggers for maintaining boundary data in PostGIS.  This is a follow-up to this post.  If you haven’t read my PostGIS trigger post, follow this link […]

  2. Cupertino Miranda said

    This post is old but here goes my comment.

    Dropping the table for each insert you perform is quite dense.
    I guess I would instead update the row that would geometrically intersect with the inserted geometry and perform the union with the adding row geometry.

    The example query can be:

    UPDATE public.reservation_boundaries_public_private_cm_dissolved
    SET the_geom = ST_Union(new_row.the_geom, the_geom)
    WHERE ST_Intersect(new_row.the_geom);

    • Agreed. I think in the end I did a truncate instead, since truncate is not undoable, and thus fast, but you’re right, a table drop is a very slow way to do this. For this dataset, views might be a viable option as well, but I haven’t done any testing of performance impacts this would have (if any).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: