Einführungstutorial in SQLite/Spatialite

SQLite ermöglicht u.a. die Speicherung von Geodaten in einem datei-basierendem weit verbreitetem relationalem Datenbanksystem (ohne große Client-Server Architektur) – mit der Erweiterung Spatialite stehen noch mehr Möglichkeiten zur Verfügung. Die nun vorhandene Unterstützung durch ESRI’s ArcGIS 10.2.x macht das Format noch interessanter – auch hinsichtlich des bei der OGC im Draft vorliegenden GeoPackage (siehe: http://isticktoit.net/?p=525).

Das folgende Tutorial soll einen Schnelleinstieg bieten und Lust auf mehr machen 🙂 – SQL-Vorkenntnisse sind von großem Vorteil (sollte aber auch ohne verständlich sein). Fundierte GIS-Kenntnisse sollten vorhanden sein.

Die Zutaten:

– ArcGIS 10.2.x oder QGIS 2.x

Geodaten (Flächen/Punkte)

Spatialite-GUI (siehe LINK)

Spatialite-SB mit Geodaten

Im Idealfall digitalisiert man sich selbst schnell 2-3 Polygone und Punkte – oder importiert bestehende Geodaten in eine Spatialite-DB (über ArcGIS, QGIS oder Shapefile-Import von Spatialite-GUI). Infos dazu unter http://isticktoit.net/?p=525

Getting started: Wo/Wie sind meine Geodaten in Spatialite abgelegt ?

Verbindet man sich in der Sptialite-GUI mit dem Datenbankfile mit den eigenen Geodaten, so sieht man schnell die Struktur (siehe Screenshot) – natürlich lohnt sich auch mal ein simples:

SELECT * FROM Poly1

sptiallite1

Getting deeper…for the non-SQL-people – beinahe das selbe nur “detailierter” definiert:

SELECT NAME_DE, geometry AS geom FROM Poly1

sptiallite2

Und noch ein Tropfen dazu für die Nicht-SQL-Kenner:

Now just the Feature “Test1”…

SELECT NAME_DE , geometry AS geom FROM Poly1 WHERE NAME_DE="Test1"

sptiallite3

A liitle modification:

SELECT NAME_DE AS name, geometry AS geom FROM Poly1 WHERE name="Test1"

Getting Spatial…ich will meine Geometrie “sehen” !

Wir haben oben immer nur eine kryptische Geometrie-Spalte gesehen – nun wollen wir aber die Koordinaten der Stützpunkte sichtbar machen:

SELECT NAME_DE AS name , astext(geometry) AS geom FROM Poly1

sptiallite4

Voila´ – astext(geometry) liefert das gewünschte Ergebnis.

Now let’s get more spatial 🙂 – mit den Geodaten in der DB arbeiten (Geoprocessing)

Spatialite ermöglicht auch zahlreiche Analysen mit den Geodaten aus der Datenbank heraus.

Ein einfaches Beispiel stellt die Berechung der Mittelpunkt(Schwerpunkt)-Koordinaten der Flächen dar – wiederum über ganz einfaches SQL:

SELECT centroid(geometry) AS geom FROM Poly1

sptiallite5

Hmmm….:-( I want the coodinates as numbers ! Das wissen wir schon 🙂

SELECT NAME_DE AS areaname, asText(centroid(geometry)) AS geom FROM Poly1

sptiallite6

better Style: asWKT (= OGC Well known text)

SELECT NAME_DE AS areaname, asWKT(centroid(geometry)) AS geom FROM Poly1

Wie groß sind nun unsere Grundstücke ?

SELECT NAME_DE AS areaname, AREA(geometry) AS flaeche FROM Poly1

sptiallite7

Nachdem uns beide Grundstücke gehören, wollen wir auch noch gleich die Summe der Flächen:

SELECT SUM(AREA(geometry)) AS flaeche FROM Poly1

sptiallite8

Es geht nicht ohne – Arbeiten mit räumlichen Referenzsystemen in Spatialite

Kommen wir auf unsere Flächenmittelpunkte zurück – wir hätten diese gerne in GCS-WGS84. Voraussetzung für TRansformationsoperatoren ist die korrekte Abspeicherung der Geodaten mit dem entsprechenden CRS (EPSG-Code) in der Datenbank (erledigt im Normalfall die GIS-Software – gestestet mit QGIS 2.2 und ArcGIS 10.2.2)

…also die centroids in GCS-WGS84 (EPSG 4326)

SELECT NAME_DE AS areaname, asWKT(centroid(transform(geometry,4326))) AS geom FROM Poly1

sptiallite9

Wir sehen also: Mit transform(geometry, Ziel-EPSG) lässt sich das erledigen !

SQL-Basics: Order Features…

SELECT NAME_DE AS areaname, asWKT(centroid(transform(geometry,31258))) AS geom FROM Poly1 ORDER BY areaname

Seitenblick auf die Tabelle spatial_ref_sys:

SELECT SRID(geometry) AS sridgeom, ref_sys_name AS srsname, srid FROM Points1, spatial_ref_sys WHERE sridgeom = srid

Spatial queries – räumliche Abfragen

Natürlich lassen sich auch räumliche Abfragen zwischen Layern ausführen – beispielsweise wollen wir nun wissen welche Punkte in der Fläche “Test2” liegen:

SELECT * FROM Poly1, Points1 WHERE WITHIN(Points1.geometry, Poly1.geometry) AND Poly1.NAME_DE = "Test2"

SQL-Basics: Views…

CREATE VIEW poly1areas AS SELECT NAME_DE AS areaname, area(geometry) AS flaeche FROM Poly1

sptiallite10

 

Views: but you can’t export geom…

Want to create new geodata?: Neue Feature-Class (Geodatensatz) aus einer Abfrage erstellen

Die oben angeführten Beispiele machen oftmals erst dann richtig Freude, wenn man daraus einen neuen Geodatensatz (FeatureClass) generieren kann. Natürlich wollen wir die Mittelpunkte der Flächen als neuen Geodatensatz haben 🙂

CREATE TABLE areacenters AS SELECT NAME_DE AS areaname, centroid(geometry) AS geom FROM Poly1

sptiallite11

Hat funktioniert ! – ABER: Wir vermissen die korrekte “geometry”-Spalte – ein einfaches “Recover geometry column” in Sptialite-GUI genügt (siehe Screenshot)..

sptiallite12

Nun schauen wir uns unsere Sptialite-Datenbank in einer GIS-Software an – siehe da, die Mittelpunkt-Fetaure sind korrekt vorhanden:

sptiallite13

 Links:

SQLite/Spatialite in ArcGIS 10.2.x und QGIS 2.x

Spatialite: https://www.gaia-gis.it/fossil/libspatialite/index

Spatialite Cookbook: http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/index.html#family