FOSS4G 2021 - PostGIS versioning workshop

.gitignore

data
.env

README.md

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

dbmanager_logging.sql

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";

docker-compose.yml

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: