Skip to content

Instantly share code, notes, and snippets.

@jsanz
Last active Sep 27, 2021
Embed
What would you like to do?
FOSS4G 2021 - PostGIS versioning workshop

Let's version some data

  • Follwing along https://pad.oslandia.net/s/HkgZF67-t
  • Get cadastre data
  • Load into PostGIS
  • First logging using QGIS DBManager "Use logging" feature, check dbmanager_logging.sql script
ALTER TABLE "public"."batiments2" ADD "id_hist" serial, ADD "time_start" timestamp, ADD "time_end" timestamp, ADD "user_role" varchar;
ALTER TABLE "public"."batiments2" DROP CONSTRAINT "batiments2_pkey", ADD PRIMARY KEY ("id_hist");
CREATE VIEW "public"."batiments2_current" AS SELECT "id_hist","fid","commune","nom","type","created","updated","wkb_geometry" FROM "public"."batiments2" WHERE "time_end" IS NULL;
CREATE OR REPLACE FUNCTION "public"."batiments2_at_time"(timestamp)
RETURNS SETOF "public"."batiments2_current" AS
$$
SELECT "id_hist","fid","commune","nom","type","created","updated","wkb_geometry" FROM "public"."batiments2" WHERE
( SELECT CASE WHEN "time_end" IS NULL THEN ("time_start" <= $1) ELSE ("time_start" <= $1 AND "time_end" > $1) END );
$$
LANGUAGE 'sql';
CREATE OR REPLACE FUNCTION "public"."batiments2_update"()
RETURNS TRIGGER AS
$$
BEGIN
IF OLD."time_end" IS NOT NULL THEN
RETURN NULL;
END IF;
IF NEW."time_end" IS NULL THEN
INSERT INTO "public"."batiments2" ("fid","commune","nom","type","created","updated","wkb_geometry", "time_start", "time_end") VALUES (OLD."fid",OLD."commune",OLD."nom",OLD."type",OLD."created",OLD."updated",OLD."wkb_geometry", OLD."time_start", current_timestamp);
NEW."time_start" = current_timestamp;
NEW."user_role" = current_user;
END IF;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION "public"."batiments2_insert"()
RETURNS trigger AS
$$
BEGIN
if NEW."time_start" IS NULL then
NEW."time_start" = now();
NEW."time_end" = null;
NEW."user_role" = current_user;
end if;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
CREATE RULE "batiments2_del" AS ON DELETE TO "public"."batiments2"
DO INSTEAD UPDATE "public"."batiments2" SET "time_end" = current_timestamp WHERE "id_hist" = OLD."id_hist" AND "time_end" IS NULL;
CREATE TRIGGER "batiments2_update" BEFORE UPDATE ON "public"."batiments2"
FOR EACH ROW EXECUTE PROCEDURE "public"."batiments2_update"();
CREATE TRIGGER "batiments2_insert" BEFORE INSERT ON "public"."batiments2"
FOR EACH ROW EXECUTE PROCEDURE "public"."batiments2_insert"();
CREATE OR REPLACE RULE "_DELETE" AS ON DELETE TO "public"."batiments2_current" DO INSTEAD
DELETE FROM "public"."batiments2" WHERE "fid" = old."fid";
CREATE OR REPLACE RULE "_INSERT" AS ON INSERT TO "public"."batiments2_current" DO INSTEAD
INSERT INTO "public"."batiments2" ("fid","commune","nom","type","created","updated","wkb_geometry") VALUES (NEW."fid",NEW."commune",NEW."nom",NEW."type",NEW."created",NEW."updated",NEW."wkb_geometry") RETURNING "id_hist","fid","commune","nom","type","created","updated","wkb_geometry";
CREATE OR REPLACE RULE "_UPDATE" AS ON UPDATE TO "public"."batiments2_current" DO INSTEAD
UPDATE "public"."batiments2" SET "fid" = NEW."fid","commune" = NEW."commune","nom" = NEW."nom","type" = NEW."type","created" = NEW."created","updated" = NEW."updated","wkb_geometry" = NEW."wkb_geometry" WHERE "fid" = NEW."fid";
version: '3.7'
services:
db:
image: postgis/postgis:13-master
volumes:
- pgdata:/var/lib/postgresql/data:z
networks:
- foss4g
environment:
- POSTGRES_PASSWORD=foss4g
- POSTGRES_HOST_AUTH_METHOD=trust
- PGDATA=/var/lib/postgresql/data/pgdata
ports:
- 5432:5432
gdal:
image: osgeo/gdal:latest
volumes:
- ./data:/tmp/data
networks:
- foss4g
command: bash
networks:
foss4g:
name: foss4g
volumes:
pgdata:
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment