Abfragen in GeoSQL

Für jede Vektorebene wird eine gleichnamige Tabelle angelegt. Diese Master-Datenbank enthält dabei lediglich die Referenzen auf die geladene Ebene, es werden keinerlei Daten direkt importiert.

Die Implementierung erfolgt über "Virtual Tables" in SQLite. SQLite implementiert weitgehend den SQL Standard SQL92. Eine Beschreibung der allgemeinen Funktionen finden Sie hier.

Die verfügbaren Funktionen wurden um Geometriefunktionen erweitert. Die meisten orientieren sich dabei an der OpenGis Spezifikation für Simple Features.

Beachten Sie, dass bei Sonderzeichen die Tabellennamen maskiert sein müssen, z.B. bei Sublayern für WFS Dienste. Der Tabellenname kann dabei in [] eingeschlossen werden, gleiches gilt für Spaltennamen.

Verfügbare SQL Funktionen

Name Beschreibung
Funktionen
Usage(void|{1,2}) Gibt die verfügbare Befehle aus, leer = dann Plain-Text, 1 = HTML Tabelle, 2 = Markdown
ST_AsText(geom) Gibt die EWKT Repräsentation einer Geometrie aus.
ST_AsGeoJSON(geom) Gibt die GeoJSON Repräsentation einer Geometrie aus.
ST_AsWKB(geom) Gibt die WKB (binär) Repräsentation eine Geometrie aus.
ST_AsEWKB(geom) Gibt die EWKB (binär, mit SRID) Repräsentation eine Geometrie aus.
ST_GeometryType(geom) Gibt den Namen des Typs aus: ST_Point, ST_Multipoint, ST_Polygon, ST_MultiPolygon, ST_Linestring, ST_MultiLinestring, ST_GeometryCollection.
ST_GeometryTypeCode(geom) Gibt den internen Type-Code zurück: Point= 1, Multipoint = 2, Polygon = 3, MultiPolygon = 4, Linestring = 5, MultiLinestring = 6, GeometryCollection = 7.
ST_Relate(geom,geom) Gibt die Intersection-Matrix zurück.
ST_Relate(geom,geom,intersectionMatrix) Gibt bool zurück, ob die intersectionMatrix zutrifft.
ST_Intersects(geom,geom) Gibt bool zurück, ob die Geometrien sich schneiden.
ST_Disjoint(geom,geom) Gibt bool zurück, ob die Geometrien sich nicht schneiden.
ST_Touches(geom,geom) Gibt bool zurück, ob die Geometrien sich berühren.
ST_Equals(geom,geom) Gibt bool zurück, ob die Geometrien identisch sind.
ST_Contains(geom,geom) Gibt bool zurück, ob a in b enthalten ist.
ST_Within(geom,geom) Gibt bool zurück, ob die Geometrien identisch sind.
ST_Buffer(geom,bufferDistance) Puffert eine Geometrie, gibt die neue Geometrie zurück.
ST_Split(inGeom,cutter) Teilt eine Geometrie, gibt die Ergebnisgeometrie zurück.
ST_Transform(geom,targetEpsgCode) Reprojeziert die Geometrie, gibt die neue Geometrie zurück.
ST_SetSrid(geom,newSrid) Setzt eine neue SRID (EpsgCode) für die Geometrie ohne Reprojektion.
ST_Srid(geom) Gibt die SRID (EpsgCode) einer Geometrie zurück.
ST_Area(geom) Berechnet die Flächengröße einer Geometrie.
ST_Distance(geom,geom) Berechnet den Abstand zweier Geometrien.
ST_Length(geom) Berechnet die Länge einer Geometrie.
ST_Snap(geom,geom) Fängt die Punkte auf die der Referenzgeometrie.
ST_Snap(geom,geom,tolerance) Fängt die Punkte auf die Referenzgeometrie mit der angegebenen Toleranz.
ST_NPoints(geom) Ermittelt die Gesamtanzahl aller Stützpunkte einer Geometrie
ST_NParts(geom) Ermittelt die Gesamtanzahl aller Parts einer Geometrie
ST_NRings(geom) Gibt die Gesamtanzahl der Ringe eines Polygon/Multipolygon oder null zurück.
ST_NumInteriorRings(geom) Gibt die Gesamtanzahl der Innen-Ringe eines Polygon oder null zurück.
ST_NumGeometries(geom) Ermittelt die Gesamtanzahl aller Geometrien einer Multi-Geometrie
ST_X(geom) Gibt den X - Wert einer Punkt Geometrie zurück (sonst null)
ST_Y(geom) Gibt den Y - Wert einer Punkt Geometrie zurück (sonst null)
ST_MakePoint(x,y) Erstellt eine Geometrie aus X/Y.
ST_MakePoint(x,y,epsg) Erstellt eine Geometrie aus X/Y und EpsgCode.
ST_GeomFromText(wktOrEWkt) Erstellt eine Geometrie aus WKT oder EWKT.
ST_GeomFromText(wktOrEWkt,epsg) Erstellt eine Geometrie aus WKT oder EWKT und EpsgCode.
ST_GeomFromGeoJSON(geoJson) Erstellt eine Geometrie aus GeoJSON.
ST_GeomFromGML(GML) Erstellt eine Geometrie aus GML.
ST_Intersection(geom,geom) Berechnet die Schnittmenge zweier Geometrien, gibt die neue Geometrie zurück.
ST_Difference(geom,geom) Differenz zweier Geometrien.
ST_SymDifference(geom,geom) symetrische Differenz zweier Geometrien.
ST_Union(geom,geom) Führt zwei Geometrien zu einer (oder mehreren) neuen Geometrien zusammen.
ST_SimplifyPreserveTopology(geom,tolerance) Vereinfacht eine Geometrie (Stützpunktreduktion) , gibt die neue Geometrie zurück.
ST_Simplify(geom,tolerance) Vereinfacht eine Geometrie (Stützpunktreduktion) , gibt die neue Geometrie zurück.
ST_LineMerge(geom) Konvertiert eine Multiline in Line.
ST_Centroid(geom) Berechnet den Schwerpunkt einer Geometrie, gibt einen Punkt zurück
ST_PointOnSurface(geom) Berechnet den Schwerpunkt einer Geometrie, gibt einen Punkt zurück, bei Fläche ist garantiert, dass sich der Punkt in einer Fläche befindet.
ST_MakeValid(geom) Sehr einfache Make-Valid Implementierung, No-Op für nicht-Polygone. Gibt die neue Geometrie zurück.
DateTimeToString(dateTime) Konvertiert DateTime Wert in Text-Darstellung (Ausgabe erfolgt immer in lokaler Zeit im Format dd.mm.YYYY hh:mm:ss
ParseISODateString(isoString) Wandelt einen ISO DateTime-String in DateTime um, mit Z oder Timezone-Offset wird UTC angenommen, sonst lokale Zeit.
DateTimeToISOString(dateTime) Konvertiert DateTime Wert in Text-Darstellung gemäß ISO.
DateTimeGetYear(dateTime) Ermittelt das Jahr aus einem DateTime
DateTimeGetMonth(dateTime) Ermittelt den Monat aus einem DateTime
DateTimeGetDay(dateTime) Ermittelt den Tag des Monats aus einem DateTime
Now() Aktuelles Datum+Uhrzeit im internen Format
ToJson(bson) Konvertiert ein binäres Json-Objekt in seine Textrepräsentation.
MD5(any) Berechnet die MD5 der Eingangsdaten.
OV(bson,propertyName) Ruft eine Eigenschaft aus einem Objekt ab. Sie sollten das Ergebnis per CAST typisieren. Array Elemente sind auch über den Namen anzusprechen. Bsp.: modellart.0.AA_Modellart.advStandardModell
GP_MBRValue(gpGeom,pos) interne Verwendung
Aggregatfunktionen
ST_Union(geom[]) Union, Aggregat Version
ST_MBR(geom[]) MBR, Aggregat Version
Table-Valued-Funktionen
ST_DumpCollection(geom) Konvertiert eine GeomtryCollection in eine Liste der Einzelgeometrien. Rückgabe ist eine Tabelle mit der Spalte "geom" und "idx"
ST_Dump(geom) Konvertiert eine MultiXXX-Geom in eine Liste der Einzelgeometrien. Rückgabe ist eine Tabelle mit der Spalte "geom" und "idx"
cdoGazetteer(exp,cardoSrv,apiKey) Sucht einen Ort per cardo Gazeteer Dienst. Rückgabe ist eine Tabelle mit den Spalten "title","componentTitle ","componentType","sort", "location" (und rowId). Die cardoUrl besteht nur aus Protokoll und Host-Name (http://Ihrcardo.de)
Diese Liste ist das Ergebnis vonSELECT Usage(2). Alle Funktionsnamen sind case-insensitiv.

Hinweise zu Datums/Zeitangaben

Funktionen die Zeiten verwenden, z.B. ParseISODateString, interpretieren eine Zeitangabe im ISO Format (wobei der Standard nicht 100% umgesetzt ist). Besonderes Augenmerk ist dabei auf Lokale / UTC Zeitangaben zu richten.

Das allgemeine Format ist: YYYY-MM-DDThh:mm:ss

YYYY= Jahreszahl, MM = Monatszahl (mit 01 beginnende), DD = Monatstag.

hh: Stunde des Tages (0 .. 24), mm = Minute (0 ... 59) und ss = Sekunde (0..59)

Trenner zwischen Datum und Zeit ist ein T.

Bsp.: (alle Angaben ergeben "02.01.2001 00:00:00" in CET)

  • Lokale Zeit: 2001-01-02T00:00:00
  • UTC: 2001-01-01T23:00:00Z
  • UTC: 2001-01-02T00:00:00+01:00

oder, Sommerzeit.: (alle Angaben ergeben "03.05.2001 00:00:00" in CEST)

  • Lokale Zeit: 2001-05-03T00:00:00
  • UTC: 2001-05-02T22:00:00Z
  • UTC: 2001-05-02T23:00:00+01:00

D.H. ohne Z oder Timezone-Offset wird eine lokale Zeit angenommen. Mit Z ist die Angabe in UTC, mit Timezone-Offset werden die mehr Stunden (positiv oder negativ) gegenüber UTC angegeben.

Lokale Zeit ... ist unbestimmt und wird nicht weiter transformiert. Bei der Konvertierung von UTC in lokal ist CET oder CEST das Ergebnis.

Angaben ohne Zeit (2001-05-02) sind ebenfalls gültig.

Intern erfolgt die Speicherung der Zeit in Anzahl der Nanosekunden seit 0, d.h. die Kleinste Zeitangabe ist 0-0-0. Nicht alle Datenquellen sind damit kompatibel.

Bei der internen Generierung werden alle Werte immer in UTC konvertiert. Damit funktionieren dann die Mathematischen Operatoren auch dann, wenn die Eingangsdaten aus verschiedenen Zeitzone stammen.

SELECT 
-- ergibt "06.11.2018 21:39:35" (lokale Ausgabe)
now() ,

-- ergibt "02.01.2001 00:00:00" (Ausgabe immer lokal)
ParseIsoDateString('2001-01-01T23:00:00Z'),

-- ergibt: true
ParseIsoDateString('2001-01-02T00:00:00') 
    == ParseIsoDateString('2001-01-01T23:00:00Z')

Beispielabfragen

Fragestellung: "Ermittle die Gesamtlänge der Strassen je Klasse"

Die Datenquelle der Ebene "strassen" ist eine Shapedatei.

SELECT 
  SUM(ST_Length(ShapeGeometry)),
  COUNT(Class),
  Class,
FROM 
   strassen
GROUP BY Class

Fragestellung: "Ermittle die Gewässereinzugsgebiete, die im Flurstück 1/2 der Gemarkung "Gemarkung A" liegen."

Die Datenquelle der Ebene "L39" ist eine Shapedatei, die Flurstücke (Ebene L282) sind in einer PostgreSQL Tabelle gespeichert.

SELECT EZG.OWK_NAME AS name,
       EZG.OWK_ID AS id,
       EZG.GUELTIGK AS gueltigkeit,
       EZG.ShapeGeometry AS Einzugsgebiet,
       flst.geom AS Flurstueck
FROM 
    L39 EZG
INNER JOIN 
        L282 flst ON MATCH(flst.geom, EZG.ShapeGeometry)
AND ST_Intersects(flst.geom, EZG.ShapeGeometry)
WHERE 
    flst.gemarkung = 'Gemarkung A'  AND flst.fbz = '1/2'

Fragestellung: Gib eine Auflistung der Einzelgeometrien der INSERT Elemente einer DXF Datei zurück.

SELECT 
  ST_AsText(g.geom),
  g.rowid,
  typeName,
  LayerName,
  LineNumber,
  Handle,
  LineTypeName,
  ColorNumber
FROM
  dxf,
  ST_DumpCollection(dxf.CadGeom) g
WHERE 
  typename='INSERT'

Fragestellung: Löse alle Multi-Polygone der Shapedatei L2 in seine Einzel-Polygone auf.

SELECT  
 d.*,
 g.ShapeRowId,
 g.IsoPhone
FROM 
  L2 g,
  ST_Dump(g.ShapeGeometry) d

Optimierungen

Jede Tabelle stellt intern 2 versteckte Spalten zur Verfügung. Die erste Spalte ist immer der Tabellenname selber, die zweite versteckte Spalte ist eine Pseudospalte für die Angabe einer Zeilenbegrenzung (Limit).

Der Zugriff auf den Tabellennamen wird als Filterausdruck direkt an die zugrundeliegende Datenquelle übergeben. Durch die Bereitstellung dieser versteckten Spalten wird eine sog. Table-Valued-Function bereitgestellt.

D.h. neben der Abfrage der Tabelle in der klassischen FROM Klausel kann der Datenabruf auch in Form eines Funktionsaufrufs erfolgen.

Gegeben sei eine Ebene mit dem Namen „Strassen“ und den Spalten „class“ und „Widmung“. Um alle Straßen mit der Class 7 abzurufen kann:

SELECT * FROM strassen WHERE class = 7

oder

SELECT * FROM strassen('class == 7') 

angegeben werden.

In dem konkreten Falle gibt es bei der Ausführung keinen Unterschied. Der Abfrageplan wird so generiert, dass auf die zugrunde liegende Quelle direkt die Abfrage mit der dargestellten Bedingung ausgeführt wird.

Anders sieht dies aus, wenn die Abfragen mehrere Klauseln umfassen oder Wertelisten übergeben werden.

SELECT * FROM strassen WHERE class = 7 or class = 6

Führt zum zweimaligen Datenabruf an der Quelle, einmal mit dem Filter "class = 7" und einmal mit dem Filter "class = 6"

Direkt als Funktionsargument geschrieben:

SELECT * FROM strassen('class == 7 or class == 6 ') 

wird der vollständige Ausdruck einmal in der Datenquelle abgesetzt.

Generell gilt, dass es sich dabei um eine Optimierungsmöglichkeit handelt. Die Ergebnisse sind in beiden Varianten identisch.

Die Funktion des Limitausdrucks verhält sich analog. Wird das Limit in der Funktion angegeben, wird dies bereits auf Seiten der Datenquelle beschränkt.

Im anderen Fall werden erst alle Daten abgerufen und dann gefiltert. Je nach Datenquelle (z.B. bei WFS Diensten ohne Streaming-Support) kann es hier deutliche Laufzeitunterschiede geben.

SELECT * FROM strassen('class == 7 or class == 6 ') LIMIT 1

oder

SELECT * FROM strassen('class == 7 or class == 6 ',1)

Die Funktionsargumente sind optional. Wird ein Parameter ausgelassen, kann NULL übergeben werden.

Beispiele:

SELECT * FROM strassen('In(class,6,5)')
SELECT * FROM strassen(NULL,1)
SELECT * FROM strassen(NULL,NULL)
SELECT * FROM strassen()
SELECT * FROM strassen

TODO: Limit ist z.Z. nur für WFS Layer implementiert.

Filteroperatoren und Funktionen

Im Filterausdruck sind folgende Operatoren und Funktion möglich.

Operator Beschreibung
== Gleichheit
=~ Gleichheit, ohne Beachtung Groß/Kleinschreibung (nur für Strings)
!= Ungleich
> Größer
< Kleiner
>= Größer gleich
<= Kleiner gleich
~~ Like, ohne Beachtung der Groß/Kleinschreibung. Als Platzhalter: % (beliebige Zeichen) und _ (Einzelnes Zeichen)
!~ Not Like
or Logisches OR Alternativ kann || verwendet werden
and Logisches UND Alternativ kann && verwendet werden
In In-Liste Das erste Argument muss der Spaltennamen sein, danach wird eine Werte-Liste (Komma getrennt) mit den zum Spaltentyp passenden Argumenten erwartet. Die Liste muss eine feste Länge pro Aufruf haben
ST_Intersects Intersects, dass erste Argument muss der Name einer Geometrie Spalte sein, dass 2. Argument eine Geometrie (WKT)
ST_Filter BBOX Vergleich

Beachte:

  • Funktionsnamen sind case-sensitiv (Beachtung der Groß/Kleinschreibung)
  • Zeichenliterale innerhalb der Filter müssen in doppelten Anführungszeichen angegeben werden.

Zuletzt geändert: 07.11.2018 07:35:39 (erstmals erstellt 12.03.2017)