Tracking history in PostGIS databases with Triggers

Well, you must have come across some projects that needed tracking of changes in the database or you might be working on one right now.Tracking changes (history recording) is vital in most projects more so where spatial data is involved.Whether you are using MySQLPostGIS or Oracle Spatial tracking of history in data should be a capability that the database platform should support.

I personally use PostGIS and this article briefly describes how to use triggers to track history of records in a table in a database. This is mostly helpful when a third party application is being used to manipulate data stored in the database. In this case, we use PostGIS database and QGIS for data manipulation.In a scenario where only these two software exist, triggers come in handy to help in our task.

First, we will create index in the table that we want to track its changes.The table will be under the public schema public.parcels where parcels is the name of the table.

We will use this code;

CREATE INDEX idx_parcels_geom ON public.parcels USING GIST(GEOM);

Then, we will create a copy-table of the original table that we will use to store history.

This table will have extra fields to store the history : ( hid, created, created_by, deleted, deleted_by, modified ). These fields are dependent on your needs.

CREATE TABLE public.parcels_history

(

  gid integer NOT NULL DEFAULT nextval(‘parcels_gid_seq’::regclass),

  id integer,

  percel_no integer,

  party character varying(50),

  address character varying(50),

  landuse character varying(50),

  block_numb numeric(10,0),

  water character varying(50),

  electricit character varying(50),

  lr_arrears numeric(10,0),

  area_ha double precision,

  id_no character varying(50),

  years_of_a numeric(10,0),

  land_rate numeric(10,0),

  land_rent numeric(10,0),

  rent_defau numeric(10,0),

  geom geometry(MultiPolygon),

  hid serial PRIMARY KEY,

  created timestamp,

  created_by varchar(25),

  deleted timestamp,

  deleted_by varchar(25),

  modified boolean

);

We will then create index in the table we’ve just created.

CREATE INDEX idx_parcels_history_geom ON public.parcels_history USING GIST(GEOM);

We also write a trigger function that we will use in our history tracking;

CREATE OR REPLACE FUNCTION public.parcels_history_tracker()

  RETURNS trigger AS 

$parcels_history_tracker$

    BEGIN

        — INSERT

        IF (TG_OP = ‘INSERT’) THEN

            INSERT INTO public.parcels_history

                (gid,id, percel_no, party, address,landuse, block_numb, water,electricit,lr_arrears,area_ha,id_no,years_of_a,land_rate,land_rent,rent_defau,created, created_by, modified, geom)

            VALUES

                (NEW.gid,NEW.id, NEW.percel_no, NEW.party, NEW.address, NEW.landuse, NEW.block_numb, NEW.water,NEW.electricit,NEW.lr_arrears,NEW.area_ha,NEW.id_no,NEW.years_of_a,NEW.land_rate,NEW.land_rent,NEW.rent_defau, current_timestamp,

                 current_user, FALSE, NEW.geom);

            RETURN NEW;

        — UPDATE

        ELSIF (TG_OP = ‘UPDATE’) THEN

            UPDATE public.parcels_history

                SET deleted = current_timestamp, deleted_by = current_user, modified = TRUE

                WHERE deleted IS NULL and gid = OLD.gid;

 

            INSERT INTO public.parcels_history

                (gid,id, percel_no, party, address, landuse,block_numb, water,electricit,lr_arrears,area_ha,id_no,years_of_a,land_rate,land_rent,rent_defau, created, created_by, modified,geom)

            VALUES

                (NEW.gid,NEW.id, NEW.percel_no, NEW.party, NEW.address, NEW.landuse, NEW.block_numb, NEW.water,NEW.electricit,NEW.lr_arrears,NEW.area_ha,NEW.id_no,NEW.years_of_a,NEW.land_rate,NEW.land_rent,NEW.rent_defau, current_timestamp,

                 current_user, FALSE, NEW.geom);

            RETURN NEW;

        — DELETE

        ELSIF (TG_OP = ‘DELETE’) THEN

            UPDATE public.parcels_history

                SET deleted = current_timestamp, deleted_by = current_user

                WHERE deleted IS NULL and gid = OLD.gid;

            RETURN NULL;

        END IF;

    END;

$parcels_history_tracker$

Finally, we create the trigger that will be executed whenever a new record is added or updated or deleted.

DROP TRIGGER IF EXISTS trg_parcels_history_tracker ON public.parcels;

CREATE TRIGGER trg_parcels_history_tracker AFTER INSERT OR UPDATE OR DELETE ON public.parcels

FOR EACH ROW EXECUTE PROCEDURE public.parcels_history_tracker();

We just created a trigger and allocated it to our table. Every time a record in the parcels table is created, updated or deleted, a copy of information will be recorded in the history table.In this scenario, we are able to track changes in our data at any time.

To check the details, try changing the data in the original table and open the history table in your GIS and view the details.

I guess by now, you’ve learned a new trick and tip in data management.

Enjoy this video!!

Wanjohi Kibui
About Wanjohi Kibui 18 Articles
A GIS Developer, Consultant and Author.Passionate about Geospatial technologies. To read more about his work, visit http://www.lifeingis.com Access video Tutorials on YouTube
  • A great tutorial here!

  • Jay Makorani

    Nice one

  • Michael Simiyu

    With the already developed guidelines for NLIMS, as a country,is just a mater of time then we will be competing Mozambique
    and Rwanda