Smathermather's Weblog

Remote Sensing, GIS, Ecology, and Oddball Techniques

Archive for August, 2009

PostGIS Gripe—Limits to Postgre’s B-tree indexing—Followup

Posted by smathermather on August 18, 2009

In an earlier post, I griped about the limits to Postgres B-tree indexing, in that for large PostGIS geometries, I couldn’t create a unique constraint for PostGIS geometry.  Abe Gillespie (blame me not him for any mistakes  in what follows), suggested that I hash the geometry and create an index on that.  In a follow-up e-mail, he suggested a series of tests in a trigger statement, first test on the bbox, then on the hashed geometry, then finally on the geometry itself upon insert, to determine if the geometry is unique or not.

geometry_decision_tree

Well, I’ve completed the first suggestion, but not yet the second.  Here’s my trigger statement:

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

     UPDATE reservation_boundaries_public_private_cm SET hash = MD5(ST_AsBinary(the_geom));

   END IF;
 RETURN NEW;
END;
$dissolver$ LANGUAGE plpgsql;

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

So, each time I insert a new geometry, the field “hash” is updated with an MD5 hashed version of the geometry.  Now we want to test against this so we add a constraint:

ALTER TABLE public.reservation_boundaries_public_private_cm ADD CONSTRAINT geom_uniq UNIQUE (hash);

And it works a charm. The only problem is that if I were to get a hash collision, I would reject a record and not mean to. I’m not sure the statistical chances of this, but implementing the above flow chart would prevent such a possibility.  If my reading is right, the chances of a hash collision is nearly 1 in 2^128, so maybe I won’t worry this year… .

Posted in PostGIS | Tagged: , , , | Leave a Comment »

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?

Posted in PostGIS | Tagged: , , , | 3 Comments »