16.4. Lesson: Räumliche Abfragen
Räumliche Abfragen unterscheiden sich nicht von anderen Datenbankabfragen. Man kann die Geometriespalte wie jede andere Spalte in der Datenbank verwenden. Durch die Installation von PostGIS in unserer Datenbank kommen weitere zusätzliche Funktionen zur Abfrage der Datenbank hinzu.
Ziel dieser Lektion: Verständnis über die Implementierung von räumlichen Funktionen im Vergleich mit „normalen“ nicht räumlichen Funktionen.
16.4.1. Räumliche Operationen
Wenn man wissen möchte, welche Punkte innerhalb eines Abstandes von 2 Grad zu einem Punkt (X,Y) sind, kann man folgende Abfrage verwenden:
select *
from people
where st_distance(the_geom,'SRID=4326;POINT(33 -34)') < 2;
Ergebnis:
id | name | house_no | street_id | phone_no | the_geom
----+--------------+----------+-----------+---------------+---------------
6 | Fault Towers | 34 | 3 | 072 812 31 28 | 01010008040C0
(1 row)
Bemerkung
Der obige Wert von the_geom wurde aus Platzgründen abgeschnitten. Wenn man den Punkt in vom Menschen lesbaren Koordinaten sehen möchte, kann man so ähnlich vorgehen wie oben unter „Einen Punkt als WKT anzeigen“.
Woher wissen wir, das die obige Abfrage alle Punkte innhalb von 2 Grad ausgibt? Warum nicht 2 Meter oder irgendeine andere Einheit?
Answer
The units being used by the example query are degrees, because the CRS that the layer is using is WGS 84. This is a Geographic CRS, which means that its units are in degrees. A Projected CRS, like the UTM projections, is in meters.
Remember that when you write a query, you need to know which units the layer’s CRS is in. This will allow you to write a query that will return the results that you expect.
16.4.2. Räumliche Indexe
Wir können auch räumliche Indexe definieren. Ein räumlicher Index beschleunigt Ihre räumlichen Abfragen stark. Um einen räumlichen Index über eine Geometriespalte zu erstellen, geht man wie folgt vor:
CREATE INDEX people_geo_idx
ON people
USING gist
(the_geom);
\d people
Ergebnis:
Table "public.people"
Column | Type | Modifiers
-----------+-----------------------+----------------------------------------
id | integer | not null default
| | nextval('people_id_seq'::regclass)
name | character varying(50) |
house_no | integer | not null
street_id | integer | not null
phone_no | character varying |
the_geom | geometry |
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
"people_geo_idx" gist (the_geom) <-- new spatial key added
"people_name_idx" btree (name)
Check constraints:
"people_geom_point_chk" CHECK (st_geometrytype(the_geom) = 'ST_Point'::text
OR the_geom IS NULL)
Foreign-key constraints:
"people_street_id_fkey" FOREIGN KEY (street_id) REFERENCES streets(id)
16.4.3. Try Yourself
Verändern Sie die Tabelle cities, so dass die Geometriespalte einen räumlichen Index erhält.
Answer
CREATE INDEX cities_geo_idx
ON cities
USING gist (the_geom);
16.4.4. Demonstration der räumlichen Funktionen von PostGIS
Um die räumlichen Funktionen von PostGIS zu demonstrieren, werden wir eine neue Datenbank mit (fiktionalen) Daten erstellen.
Erstellen Sie zu Beginn eine neue Datenbank (verlassen Sie die psql Konsole zuerst):
createdb postgis_demo
Denken Sie daran, die PostGIS Erweiterungen zu installieren:
psql -d postgis_demo -c "CREATE EXTENSION postgis;"
Importieren Sie als Nächstes die Daten aus dem Verzeichnis exercise_data/postgis/. Sehen Sie sich dazu die Anleitung in der vorherigen Lektion an, aber denken Sie daran, dass Sie eine neue PostGIS Verbindung zu der neuen Datenbank erstellen müssen. Sie können mit Hilfe des Terminals oder des DB Managers importieren. Importieren Sie die Dateien in die folgenden Datenbanktabellen:
points.shp nach building
lines.shp nach road
polygons.shp nach region
Laden Sie diese drei Datenbanklayer wie gewohnt mit Hilfe des PostGIS-Layer hinzufügen Dialogs in QGIS ein. Beim Öffnen der Attributtabellen sehen wir, dass beide ein Feld id und ein Feld gid haben, die beim PostGIS Import erzeugt wurden.
Nachdem die Tabellen importiert wurden, können wir PostGIS zur Abfrage der Daten verwenden. Gehen Sie zurück zur Konsole (Kommandozeile) und geben folgendes Kommando ein:
psql postgis_demo
Wir werden einige dieser Auswahlanweisungen durch die Erstellung von Sichten demonstrieren, so dass Sie sie in QGIS öffnen und die Ergebnisse sehen können.
Auswahl nach der Lage
Wir erhalten alle Bauwerke in der Region KwaZulu:
SELECT a.id, a.name, st_astext(a.the_geom) as point
FROM building a, region b
WHERE st_within(a.the_geom, b.the_geom)
AND b.name = 'KwaZulu';
Ergebnis:
id | name | point
----+------+------------------------------------------
30 | York | POINT(1622345.23785063 6940490.65844485)
33 | York | POINT(1622495.65620524 6940403.87862489)
35 | York | POINT(1622403.09106394 6940212.96302097)
36 | York | POINT(1622287.38463732 6940357.59605424)
40 | York | POINT(1621888.19746548 6940508.01440885)
(5 rows)
Oder wir erstellen eine Sicht daraus:
CREATE VIEW vw_select_location AS
SELECT a.gid, a.name, a.the_geom
FROM building a, region b
WHERE st_within(a.the_geom, b.the_geom)
AND b.name = 'KwaZulu';
Fügen Sie die Sicht als Layer in QGIS ein:
Nachbarn auswählen
Geben Sie eine Liste aller Namen von Regionen aus, die an die Hokkaido Region angrenzen:
SELECT b.name
FROM region a, region b
WHERE st_touches(a.the_geom, b.the_geom)
AND a.name = 'Hokkaido';
Ergebnis:
name
--------------
Missouri
Saskatchewan
Wales
(3 rows)
Als Sicht:
CREATE VIEW vw_regions_adjoining_hokkaido AS
SELECT b.gid, b.name, b.the_geom
FROM region a, region b
WHERE TOUCHES(a.the_geom, b.the_geom)
AND a.name = 'Hokkaido';
In QGIS:
Achten Sie auf die fehlende Region (Queensland). Das kann an einem Topologiefehler liegen. Solche Artefakte können auf potentielle Fehler in den Daten hinweisen. Um das Problem zu lösen ohne sich in möglichen Problemen des Datensatzes zu verzetteln, können wir einen Puffer verwenden:
CREATE VIEW vw_hokkaido_buffer AS
SELECT gid, ST_BUFFER(the_geom, 100) as the_geom
FROM region
WHERE name = 'Hokkaido';
Damit wird ein Puffer von 100 Metern um die Region Hokkaido erstellt.
Die dunklere Fläche ist der Puffer:
Auswahl mit Hilfe des Puffers:
CREATE VIEW vw_hokkaido_buffer_select AS
SELECT b.gid, b.name, b.the_geom
FROM
(
SELECT * FROM
vw_hokkaido_buffer
) a,
region b
WHERE ST_INTERSECTS(a.the_geom, b.the_geom)
AND b.name != 'Hokkaido';
In dieser Abfrage wird die Sicht mit Puffer genauso wie eine normale Tabelle verwendet. Die Sicht erhält den Alias a und ihr Geometriefeld a.the_geom wird verwendet, um alle Polygone in der Tabelle region (Alias b) zu selektieren die sie überschneiden. Hokkaido ist dagegen aus der Auswahl ausgeschlossen, da wir nur die angrenzenden Regionen selektieren wollen.
In QGIS:
Es ist auch möglich, alle Objekte innerhalb eines vorgegebenen Abstandes auszuwählen, ohne vorher einen Puffer zu erstellen:
CREATE VIEW vw_hokkaido_distance_select AS
SELECT b.gid, b.name, b.the_geom
FROM region a, region b
WHERE ST_DISTANCE (a.the_geom, b.the_geom) < 100
AND a.name = 'Hokkaido'
AND b.name != 'Hokkaido';
Das Ergebnis ist dasselbe und der Zwischenschritt der Pufferbildung entfällt:
Auswahl eindeutiger Werte
Anzeige einer Liste der eindeutigen Stadtnamen für alle Gebäude in der Region Queensland:
SELECT DISTINCT a.name
FROM building a, region b
WHERE st_within(a.the_geom, b.the_geom)
AND b.name = 'Queensland';
Ergebnis:
name
---------
Beijing
Berlin
Atlanta
(3 rows)
Weitere Beispiele …
CREATE VIEW vw_shortestline AS
SELECT b.gid AS gid,
ST_ASTEXT(ST_SHORTESTLINE(a.the_geom, b.the_geom)) as text,
ST_SHORTESTLINE(a.the_geom, b.the_geom) AS the_geom
FROM road a, building b
WHERE a.id=5 AND b.id=22;
CREATE VIEW vw_longestline AS
SELECT b.gid AS gid,
ST_ASTEXT(ST_LONGESTLINE(a.the_geom, b.the_geom)) as text,
ST_LONGESTLINE(a.the_geom, b.the_geom) AS the_geom
FROM road a, building b
WHERE a.id=5 AND b.id=22;
CREATE VIEW vw_road_centroid AS
SELECT a.gid as gid, ST_CENTROID(a.the_geom) as the_geom
FROM road a
WHERE a.id = 1;
CREATE VIEW vw_region_centroid AS
SELECT a.gid as gid, ST_CENTROID(a.the_geom) as the_geom
FROM region a
WHERE a.name = 'Saskatchewan';
SELECT ST_PERIMETER(a.the_geom)
FROM region a
WHERE a.name='Queensland';
SELECT ST_AREA(a.the_geom)
FROM region a
WHERE a.name='Queensland';
CREATE VIEW vw_simplify AS
SELECT gid, ST_Simplify(the_geom, 20) AS the_geom
FROM road;
CREATE VIEW vw_simplify_more AS
SELECT gid, ST_Simplify(the_geom, 50) AS the_geom
FROM road;
CREATE VIEW vw_convex_hull AS
SELECT
ROW_NUMBER() over (order by a.name) as id,
a.name as town,
ST_CONVEXHULL(ST_COLLECT(a.the_geom)) AS the_geom
FROM building a
GROUP BY a.name;
16.4.5. In Conclusion
Wir haben gesehen, wie man räumliche Objekte mit Hilfe der neuen Datenbankfunktionen in QGIS abfragen kann.
16.4.6. What’s Next?
Als Nächstes werden wir die Strukturen komplexerer Geometrien untersuchen und wie man sie mit Hilfe von PostGIS erstellt.