- 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
Last active
September 27, 2021 17:59
-
-
Save jsanz/63df8934109cde52960271d5369e1253 to your computer and use it in GitHub Desktop.
FOSS4G 2021 - PostGIS versioning workshop
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
data | |
.env |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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"; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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