Tabellensynchronisation über gid (nur fehlende Records einfügen)

Es werden nur Records von einer temporären Tabelle eingefügt deren gids in der Mastertabelle noch nicht existieren:

INSERT INTO nest.gebaeude
  (SELECT * FROM nest.gebaeude_temp t
    WHERE not exists(SELECT 1 FROM nest.gebaeude g WHERE g.gid = t.gid))

Siehe auch: PostgreSQL Dokumentation über SQL Subqueries mit "Exists"

String-Replace

UPDATE nest.liegenschaften
   SET kataster_nr=replace(kataster_nr,'-','');

Siehe auch: PostgreSQL Dokumentation über String Functions and Operators

Interlis-OID von gid Sequenz generieren - Nummer zu String Formattierung

SELECT 'ch11h8mwRSPP' || to_char(gid,'FM0000') AS oid FROM verkehr.rspp;

Bei obiger Abfrage wird eine Interlist OID generiert. Die ersten 8 Zeichen ist der Präfix für die Stadt Uster, der Postfix ist frei vergebbar, muss aber eindeutig sein. Man kann sie aus dem Organisations-Präfix, einem Themen Präfix und einer fortlaufenden Nummer generieren. PostgreSQL bietet Formattierungoptionen für führende Nullen wie im Beispiel.

Siehe auch: PostgreSQL Dokumentation über Data Type Formatting.

Konvertierung von Multi zu Single-Geometrien

am Beispiel von MultiLinestring zu Linestring

UPDATE hydrologie.grundwasser_isohypse_hochwasser_daten
  SET the_geom = (ST_Dump(the_geom)).geom;

ST_Dump ist eine Funktion die eine Geometrie nimmt und ein Set von Postgis geometry_dump Strukturen zurück gibt. Geometry_dump besteht aus 2 Eigenschaften: geom und path. Geom ist eine Geometrie und path ist ein multi-dimensionales, integer basiertes, Feld das den Index in der Multigeometrie oder GeometryCollection repräsentiert. Bei MultiGeometrien ist der Pfad eindimensional {1}, bei Single-Geometrien ist der Pfad ein leeres Feld, bei GeometryCollections gibt es potentiell mehrere Feld-Elemente. ST_Dump ist nützlich um MultiGeometrien in SingleGeometrien umzuwandeln.

Siehe auch: BostonGIS ST_Dump Snippet

Domäne im Nachhinein verändern

Domänen, in Kombination mit check constraints, sind praktisch um in QGIS automatisch die Auswahllisten zu generieren. Man kann sie zudem im Nachhinein ändern, ohne dass man die Domäne und abhängigen Objekte löschen muss.

Domäne erstellen

CREATE DOMAIN natur.naturschutzobjekt_objekttyp
  AS text
 DEFAULT 'Inventarobjekt'::text
 CONSTRAINT natur_naturschutzobjekt_objekttyp_check CHECK
   ((VALUE = ANY (ARRAY['Diverses'::text, 'Inventarobjekt'::text, 'Inventarergänzung'::text, 'Schutzobjekt'::text, 'Vertragsobjekt'::text, 'Zonenplan'::text])));

Domäne modifizieren

Dies geschieht in 2 Teilen: zuerst den check constraint mit "drop" löschen, danach einen neuen, modifizierten mit "add" erstellen:

ALTER DOMAIN natur.naturschutzobjekt_objekttyp
  DROP CONSTRAINT natur_naturschutzobjekt_objekttyp_check;


ALTER DOMAIN natur.naturschutzobjekt_objekttyp
 ADD CONSTRAINT natur_naturschutzobjekt_objekttyp_check CHECK
  ((VALUE = ANY (ARRAY['Inventarobjekt'::text, 'Schutzobjekt'::text, 'Vertragsobjekt'::text, 'Zonenplan'::text])));;

Konkave Hülle berechnen

Wird zum Beispiel beim Grünkataster verwendet.  Dabei werden Punkte, Linien und Flächen zusammengefasst, die konkave Hülle davon berechnet und danach noch ein Puffer darum generiert:

SELECT ga.gid, ga.id, ga.uster_nr,
   ST_Buffer(
     ST_ConcaveHull(
       ST_Collect(Array(
         SELECT ST_Union(the_geom) FROM natur.gruenanlage_flaechen gafl WHERE gafl.uster_nr = ga.uster_nr AND ST_IsValid(gafl.the_geom)
           UNION 
         SELECT the_geom FROM natur.gruenanlage_punkte gapkt WHERE gapkt.uster_nr = ga.uster_nr
           UNION
         SELECT the_geom FROM natur.gruenanlage_linien galin WHERE galin.uster_nr = ga.uster_nr)
     ),0.97)
   ,4) AS the_geom
 FROM natur.gruenanlage ga
 GROUP BY ga.uster_nr, ga.gid, ga.id, ga.uster_nr
 ORDER BY ga.gid ASC;

Achtung: bei der Zusammenfassung von Polygonen empfiehlt sich zuerst ein ST_Union. Da kommt es weniger zu Fehlern als wenn man es direkt an ST_ConcaveHull übergibt. Der Wert für die Konkavität ist zwischen 1 und 0. 1 wäre ident mit der konvexen Hülle, 0.99 bereits etwas konkav - je tiefer desto konkaver. ST_IsValid kann als Filter angewendet werden wenn sich invalide Geometrien in der Tabelle befinden (z.b. Flächen mit 2 Punkten oder Selbstüberschneidungen).