SQL-Referenz#

Die Pipeline aus importieren, in Quelltabellen laden und konvertieren dient dazu, verschiedenste Ausgangsformate schrittweise in die Simplex4TwIS-Struktur zu überführen. Nachdem die importierten Daten in die Quelltabellen geladen wurden, sind sie in der zugrundeliegenden Datenbank vom Typ Postgresql vorhanden und können mittels SQL-Ausdrücken in die Simplex4TwIS-Strukturen überführt werden.

Bei dieser Überführung, die in den Konvertern stattfindet, gibt es Aspekte, die fest vorgegeben sind, und Aspekte, die je nach Daten und Anwendungsfall flexibel mit SQL gestaltet werden können.

Die folgende Seite dient dazu, diese Aspekte vorzustellen, damit Sie wissen, wo und wie Sie SQL-Ausdrücke bei der Konvertierung nutzen können und müssen.

Unterschiede beim Zugriff auf Quelltabelle oder Objekttabelle#

In den Konvertern wird an bestimmten Stellen entweder auf eine Quelltabelle oder auf die Datenbanktabellen zugegriffen, in denen die Objekte abgespeichert sind.

  • Wenn auf eine Quelltabelle zugegriffen wird, wird diese in einem Auswahlfeld angegeben, und danach wird diese Quelltabelle in den Formularfeldern, in denen auf sie zugegriffen wird, mit der Variable q referenziert. Dies geschieht in den Filterdefinitionen und in Definitionen für Attributwerte. Da jeweils auf einzelne Spalten einer Tabelle verwiesen wird, geschieht dies mit der Punktnotation q.<Spaltenname>.

  • Wenn Objekttabellen referenziert werden, wird die Variable obj oder obj<Nummer> (obj1, obj2, obj3...) verwendet. Auf welche Objekttabelle diese Variable dabei jeweils verweist, ergibt sich aus den Klassen, für die der Konverter angelegt wird. Diese Objekttabellenvariablen müssen zum Beispiel bei den Konvertierungsbedingungen für Verbindungen verwendet werden.

Im Folgenden werden einige Beispiele für SQL-Ausdrücke in den verschiedenen Formularfeldern der Konverter beschrieben.

Filter#

Mittels des Filters auf der Quelltabelle q können Regeln festgelegt werden, um beim Konvertieren bestimmte Zeilen der Quelltabelle zu überspringen und nicht zu Objekten zu konvertieren.

Verfügbare Variablen: q

Einige Beispiele dazu:

Verneinung und Prüfung auf NULL#

q.<Quelltabellenspaltenname> IS NOT NULL

Dieser Filter sorgt dafür, dass Quelltabellenzeilen, deren Eintrag in der Spalte <Quelltabellenspaltenname> NULL (= leer) ist, nicht konvertiert werden.

Vergleichsoperator#

int(q.<Quelltabellenspaltenname>) > 10

Dieser Filter sorgt dafür, dass nur Quelltabellenzeilen konvertiert werden, in denen der Zahlwert in der Spalte <Quelltabellenspaltenname> größer als 10 ist.

Mehrere Vergleichsoperatoren und logischer Operator#

int(q.<Quelltabellenspaltenname>) > 10 AND int(q.<Quelltabellenspaltenname>)< 20

Bei diesem Filter müssen zwei Bedingungen gleichzeitig erfüllt sein: der Zahlwert in der Spalte <Quelltabellenspaltenname> muss größer als 10 und kleiner als 20 sein.

Konverter für Attribute#

In den Formularfeldern für die Standard- und Sachattribute wird jeweils eine Bildungsvorschrift angegeben, wie aus den Quelltabelleninhalten und eventuellen konstanten Werten der Attributinhalt gebildet wird.

Verfügbare Variablen: q

Einige Beispiele dazu:

Verkettung von Zeichenketten#

'Typ-' || q.<Quelltabellenspaltenname> : 'Typ-'

Ein in Anführungszeichen gesetzter Textbaustein wird hier allen Attributen, die mit diesem Konverter gebildet werden, vorangesetzt. Mit dem Operator || werden zwei Zeichenketten miteinander verbunden.

Verkettung von Zeichenketten mit zwei Spalten#

q.<Quelltabellenspaltenname1> || '-' || q.<Quelltabellenspaltenname2>

Auch wenn die Einträge aus mehreren Quellstabellenspalten hintereinander in ein Attribut geschrieben werden sollen, müssen diese per || verbunden werden. Wenn nur ein || verwendet wird, werden die beiden Einträge direkt nacheinander eingefügt, ohne Leerstelle oder Bindestrich. Deshalb wurde in diesem Beispiel noch ein separater Bindestrich eingefügt, um die beiden Einträge besser lesbar voneinander zu trennen.

Vorgabewert für NULL-Werte#

coalesce(q.<Quelltabellenspaltenname>, 'Standardwert')

Die Funktion COALESCE gibt das erste ihrer Argumente zurück, das nicht NULL ist. Das kann oft nützlich sein, um bei der Ausgabe für NULL-Werte einen Vorgabewert einzusetzen. Hier wird, wenn der Eintrag in der Quelltabellenspalte NULL ist, stattdessen die Zeichenkette ‚Standardwert‘ in das Attribut eingetragen.

Fallunterscheidung#

CASE WHEN q.<Quelltabellenspaltenname> < 10 THEN 'klein' ELSE  'groß' END

Mit der Fallunterscheidung mit CASE können abhängig von bestimmten Quelltabelleneinträgen unterschiedliche Attributeinträge gebildet werden.

Zeichenkettenmanipulation#

LPAD(q.<Quelltabellenspaltenname>, 10, '0')

Füllt die Zeichenkette aus der Quelltabellenspalte auf eine Länge von 10 Zeichen mit vorangestellten Nullen auf.

Joinbedingungen für Attribute#

Bei der Konvertierung von Sachattributen muss angegeben werden, welche Quelltabellenzeile für welches Objekt ausgewertet werden soll. Dies geschieht mithilfe der Joinbedingung. Verfügbare Variablen: q, obj

Einige Beispiele dazu:

Direkte Konvertierung eines Attributs#

obj.ndx = q.proc_id

Die ist ein häufiger Fall. Der eindeutige Aktualisierungsschlüssel (ndx) des Objekts entspricht einem in der Quelltabelle angegebenen Index (hier proc_id aus dem Minimalbeispiel).

Befüllung des Attributs mit Daten aus mehreren Quelltabellenspalten mit Rahmenbedingungen#

obj.ndx=LPAD(q.land::text,2,'0') || q.regierungsbezirk::varchar(1)

Dieses Beispiel ist etwas komplexer. Hier muss der eindeutige Aktualisierungsschlüssel des Objekts der Verkettung von Einträgen aus zwei Quelltabellenspalten entsprechen, wobei die Länge des Eintrags aus q.land auf 2 Zeichen festgelegt ist und ggf. vorn mit Nullen aufgefüllt wird. Der Ausdruck q.regierungsbezirk::varchar(1) bedeutet, dass der Eintrag aus q.regierungsbezirk mit einer Länge von genau einem Zeichen abgespeichert wird. Das ndx muss also insgesamt 3 Zeichen enthalten.

Joinbedingungen für Verbindungen#

Bildungsvorschrift für linke und rechte Joinbedingung. Bei der Konvertierung einer Verbindung geht es darum, Regeln zu finden, welche Objekte aus beiden Seiten der Verbindung (obj1 und obj2) verbunden werden sollen. Um die entsprechenden Verbindungen zu finden, wird für das Objekt 1 die linke Joinbedingung angegeben, die für jede Zeile der Quelltabelle festlegt, welches Objekt der linken Klasse der Verbindung ausgewählt wird. Ebenso definiert die rechte Joinbedingung, welches Objekt der rechten Klasse der Verbindung ausgewählt wird.

Verfügbare Variablen: q, obj1, obj2

Einige Beispiele dazu:

Direkte Konvertierung einer Verbindung#

obj1.ndx=q.<Quelltabellenspaltenname1> obj2.ndx=q.<Quelltabellenspaltenname2>

Dies ist das einfachste Beispiel für Verbindungskonvertierungsbedingungen: die eindeutigen Aktualisierungsschlüssel (ndx) der beiden zu verbindenden Objekte entsprechen direkt den Einträgen in jeweils einer Quelltabellenspalte.

Geografische Lage#

ST_within(st_transform(obj1.geometrie, 25833),st_transform(q.geom, 25833)) obj2.ndx=q.id::text

Dies ist ein Beispiel für eine Verbindung, die aufgrund einer geografischen Zugehörigkeit gebildet wird. Der Name der Verbindung könnte sein: „Objekt-liegt-in“. In Erweiterung des Minimalbeispiels könnte hier eine Verbindung zwischen den Sporthallen und einer zusätzlichen Klasse „Hochwasserbereiche“ hergestellt werden. Die Quelltabelle der Hochwasserbereiche enthält als q.geom die Fläche eines potentiellen Hochwasserbereichs und als q.id eine ID des Hochwasserbereichs.

Mit der PostGIS-Funktion ST_within wird in der linken Join-Bedingung festgelegt, dass zwischen einer Sporthalle (Objekt 1) und einem Hochwasserbereich eine Verbindung erstellt wird, wenn die Punktgeometrie der Sporthalle innerhalb der Fläche des Hochwasserbereichs liegt. Die rechte Joinbedingung sagt aus, dass als rechtes Objekt der Verbindung der Hochwasserbereich ausgewählt wird, dessen ID der id in der Quelltabelle entspricht.

Weiterführende Literatur#

Die Funktionsvielfalt der möglichen SQL-Ausdrücke übersteigt den Umfang dieser Einführung. Daher sollen die angeführten Beispiele nur eine Vorstellung davon vermitteln, welche Arten von Funktionen und Operatoren mit SQL genutzt werden können.

Weitere Funktionen und logische und mathematische Operatoren finden Sie in der offiziellen englischen Dokumentation zu PostgreSQL unter https://www.postgresql.org/docs/current/functions.html.

Zusätzlich zu den PostgreSQL-Funktionen können auch Funktionen zur Analyse und Bearbeitung von Geoobjekten genutzt werden, die in PostGIS enthalten sind, einer Erweiterung für PostgreSQL für geografische Objekte. Das deutschsprachige PostGIS-Handbuch finden Sie unter https://postgis.net/docs/manual-dev/de/.