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.

Voraussetzung ist, dass die Quelle genau eine PK Spalte definieren muss. Sonst können die virtuellen Tabellen nicht erstellt werden. Im Idealfall ist die Spalte ein ganzzahliger Datentyp.

Das sollten Sie beachten Es gibt ein Feature in SQLite, wo das Verhalten bei der Verwendung von Aggregatfunktionen anders als gewohnt ist. Der Artikel unter Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause beschreibt und begründet dies recht ausführlich.

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(format) 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, wenn die Intersection-Matrix zutrifft.
ST_Intersects(geom,geom) Gibt bool zurück, wenn die Geometrien sich schneiden.
ST_IntersectsISB(geom,geom) Ergebnis von ST_Relate mit der Intersection-Matrix T******** (ISB=Ignore Shared Boundary)
ST_Disjoint(geom,geom) Gibt bool zurück, wenn die Geometrien sich nicht schneiden.
ST_Touches(geom,geom) Gibt bool zurück, wenn die Geometrien sich berühren.
ST_Equals(geom,geom) Gibt bool zurück, wenn die Geometrien identisch sind.
ST_Contains(geom,geom) Gibt bool zurück, wenn b komplett innerhalb a liegt (Gegenteil von st_within).
ST_Within(geom,geom) Gibt bool zurück, wenn a komplett innerhalb b liegt (Gegenteil von st_contains).
ST_Buffer(geom,bufferDistance) Puffert eine Geometrie, gibt die neue Geometrie zurück. BufferDistance ist in der Einheit der CRS der Geometrie.
ST_Buffer(geom,bufferDistance,endCap) Puffert eine Geometrie, gibt die neue Geometrie zurück. endCap ist einer der Werte EndCapRound, EndCapFlat oder EndCapSquare. bufferDistance ist in der Einheit der CRS der Geometrie.
ST_BufferMeters(geom,bufferDistance) Puffert eine Geometrie, gibt die neue Geometrie zurück. BufferDistance wird hier immer in Meter umgerechnet.
ST_BufferMeters(geom,bufferDistance,endCap) Puffert eine Geometrie, gibt die neue Geometrie zurück. endCap ist einer der Werte EndCapRound, EndCapFlat oder EndCapSquare. BufferDistance wird hier immer in Meter umgerechnet.
ST_Split(inGeom,cutter) Teilt eine Geometrie, gibt die Ergebnisgeometrie zurück.
ST_Transform(geom,targetEpsgCode) Reprojiziert 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 (Line/Multiline, sonst 0).
ST_Perimeter(geom) Berechnet den Umfang einer Geometrie (Polygon/Multipolygon, sonst 0.
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 Innenringe 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) symmetrische 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 Multi-Line 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) No-Op für nicht-Polygone. Gibt die neue Geometrie zurück.
ST_IsValid(geom) Prüft, ob die Geometrie gültig.
ST_InvalidReason(geom) Gibt eine Beschreibung eines Geometrie-Fehlers, oder NULL zurück, wenn die Geometrie valide ist.
ST_BBoxIntersects(geom,geom) True, wenn die BBox der Geometrien sich überlagern.
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 (immer in lokaler Zeit)
DateTimeGetMonth(dateTime) Ermittelt den Monat aus einem DateTime (immer in lokaler Zeit)
DateTimeGetDay(dateTime) Ermittelt den Tag des Monats aus einem DateTime (immer in lokaler Zeit)
Now() Aktuelles Datum+Uhrzeit im internen Format
ToJson(bson) Konvertiert ein binäres Json-Objekt in seine Textrepräsentation.
FromJson(json) Konvertiert Json-String zu Object.
MD5(any) Berechnet die MD5 der Eingangsdaten.
HtmlEncode(any) HTML Encode der Eingangdaten
HtmlEncode(any,level) HTML Encode der Eingangdaten, der Level ist ein Flag 0...7 (LtGt = 0, SingleQuotes = 1, DoubleQuotes = 2, Ampersand = 4)
HtmlEncode(any,level,newLineToBr) HTML Encode der Eingangdaten, der Level ist ein Flag 0...7 (LtGt = 0, SingleQuotes = 1, DoubleQuotes = 2, Ampersand = 4), newLineToBr ist 0=keine Ersetzung, 1=Insert oder 2=Replace
Ceil(number) Die kleinste ganze Zahl größer oder gleich der übergebenen Zahl (Aufrunden).
Floor(number) Gibt den größten Integer zurück, der kleiner oder gleich der gegeben Nummer ist. (Abrunden)
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
UPPER(txt) UPPER - Überschreibung, Unicode-fähig
LOWER(txt) LOWER - Überschreibung, Unicode-fähig
Aggregatfunktionen
ST_Union(geom[]) Union, Aggregat Version
ST_UnionAgg(geom[]) Union, Aggregat Version (identisch mit ST_Union, für DuckDB Komp.)
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 Gazetteer 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 GUID Datentyp

Datentypen vom Typ GUID werden als String behandelt. Das Format ist dabei immer ohne geschweifter Klammern und UpperCase, u.U. zeigen Datenbanktools die Daten anders an.

Bei Vergleichen gilt dies zu beachten:

Bsp. GUID Anzeige in DBeaver: "a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11" in GeoSQL "A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11"

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')

DateTime Koexistenz mit SQLite internen Funktionen

Die SQLite internen Methoden (DateTime, Date) geben einen Text der ein Subset ISO-8601 darstellt zurück. Intern: DateTime('now') gibt "2020-05-20 20:45:00" zurück. Hier sind zwei Dinge problematisch, es wird nicht zwischen UTC und Lokal unterschieden, und das T-Trennzeichen zwischen Date und Time fehlt.

Wir haben diese Methode intern jetzt überarbeitet, so dass ein ISO konformer String ausgegeben wird.

Für Zeitberechnungen sind die Methoden recht praktisch, aber es muss auf Grund der unterschiedlichen Formate beachtet werden, dass die Konvertierung korrekt durchgeführt wird.

Für eine korrekte Verwendung gilt die einfache Regel:

  • der internen dateTime Funktion immer das Ergebnis von DateTimeToISOString übergeben

SELECT dateTime(DateTimeToISOString(Now()),'+1 day')

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

Hier geht es zur Auflistung der Operatoren und Funktion der Filter.

Ab Version 7.5.4

... kommt SQLite 3.38 zum Einsatz. Dort wurde intern die Möglichkeit eingeführt die Werte einer IN-Klauseln als eine Abfrage abzusetzten.

Bisher führte eine Abfrage der Art ... WHERE COL IN(1,2,3) intern zu drei einzelnen Abfrage (COL = 1 , COL = 2 etc.)

Jetzt werden diese 3 Werte in einer Abfrage an die Backend-Datenquelle übergeben.

Leider führt die Verwendung von NOT zu einem Full-Tablescan, d.h. der Constraint wird nicht auf die Datenquelle angewendet.

Bsp.: ... WHERE COL NOT IN(1,2,3) wird nicht als Filter in der Datenquelle angewandt, es werden alle Zeilen abgerufen.


Zuletzt geändert: 13.03.2024 12:40:13 (erstmals erstellt 12.03.2017)