PostGIS Tips och Trix

Bakgrund

PostGIS (som egentligen är namnet på spatialtillägget till PostgreSQL) är en av möjliga databaser för HAJK. Lämpliga versionsnummer på dessa kan vara PostgreSQL v13.2 och senare respektive PostGIS 3.1 och senare.

Administration

För att administrera PostGIS-databaser behöver man ha ett bra adminstrationsverktyg, som t ex pgAdmin eller DBeaver. PgAdmin följer oftast med i samband med att man installerar PostgreSQL. DBeaver anses kanske vara ett lite mer allroundverktyg men är lite svårare att lära sig, men det är en smaksak.

Tips

Behörigheter

För att HAJK och t ex Geoserver ska fungera mot PostGIS tabeller är det viktigt att de har fått rätt och tillräckliga behörigheter. Detta gäller även t ex sekvenser (sequence - funktion för att generera nya löpande objekt-idnummer) och vyer (views). Om man exempelvis använder HAJKS editeringsverktyg för att skapa nya objekt, måste objektets tabell och sekvens ha redigeringsrättighet. Undvik dock att låta HAJK/geoserver använda postgres (databas-administratörskontot) som användare mot PostGIS, eftersom denna kan göra "allt" med databasen. Använd hellre ett anpassat redigeringskonto som i princip endast tillåts skriva och läsa i avsedda scheman och tabeller.

Triggerfunktioner och Triggers

Dessa kan vara användbara i många fall. En triggerfunktion är en exekvering som utförs automatiskt i samband med en händelse i databasen, . En trigger är händelsen som utlöser triggerfunktionen, t ex när man skriver i en tabell. Ett användbart exempel är om man vill registrera tidpunkten för när ett objekt skapas och/eller redigeras. De kan också användas om man vill skapa en backup av objektet i en annan tabell innan det redigeras (inga kodexempel på detta ännu).

SQL kodexempel Triggerfunktioner

Nedan två exempel på triggerfunktioner för att sätta tidpunkten när objektet (raden) skapas (och därmed också "revideras" om man så vill ha det så) respektive när objektet förändras (revideras).

insert_skapaddat_revdat

BEGIN
NEW.skapaddat = now();
NEW.revdat = now();
RETURN NEW;
END;

update_revdat

BEGIN
IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
NEW.revdat = now();
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;

När triggerfunktionerna är skapade kan man knyta dem till triggers från önskade tabeller i schemat. Notera att datumfälten måste ha samma namn som definerats i triggerfunktionerna (t ex skapaddat resp revdat) och ha datatypen timestamp (förslagsvis med with time zone).

SQL kodexempel Triggers

insert_skapaddat_revdat_mintabell

CREATE TRIGGER insert_skapaddat_revdat_mintabell
BEFORE INSERT
ON mittschema.mintabell
FOR EACH ROW
EXECUTE FUNCTION mittschema.insert_skapaddat_revdat();

update_revdat_mintabell

CREATE TRIGGER update_revdat_mintabell
BEFORE UPDATE
ON mittschema.mintabell
FOR EACH ROW
EXECUTE FUNCTION mittschema.update_revdat();