Wichtig

Übersetzen ist eine Gemeinschaftsleistung Sie können mitmachen. Diese Seite ist aktuell zu 89.09% übersetzt.

16.4. Lesson: Spatial Queries

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(geom,'SRID=4326;POINT(33 -34)') < 2;

Ergebnis:

 id |     name     | house_no | street_id |   phone_no    |   geom
----+--------------+----------+-----------+---------------+---------------
  6 | Fault Towers |       34 |         3 | 072 812 31 28 | 01010008040C0
(1 row)

Bemerkung

geom value above was truncated for space on this page. If you want to see the point in human-readable coordinates, try something similar to what you did in the section „View a point as WKT“, above.

Woher wissen wir, das die obige Abfrage alle Punkte innhalb von 2 Grad ausgibt? Warum nicht 2 Meter oder irgendeine andere Einheit?

Antwort

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
  (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     |
  geom      | geometry              |
Indexes:
  "people_pkey" PRIMARY KEY, btree (id)
  "people_geo_idx" gist (geom)  <-- new spatial key added
  "people_name_idx" btree (name)
Check constraints:
  "people_geom_point_chk" CHECK (st_geometrytype(geom) = 'ST_Point'::text
  OR 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.

Antwort

CREATE INDEX cities_geo_idx
  ON cities
  USING gist (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.geom) as point
  FROM building a, region b
    WHERE st_within(a.geom, b.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.geom
    FROM building a, region b
      WHERE st_within(a.geom, b.geom)
      AND b.name = 'KwaZulu';

Fügen Sie die Sicht als Layer in QGIS ein:

../../../_images/kwazulu_view_result.png

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.geom, b.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.geom
    FROM region a, region b
      WHERE st_touches(a.geom, b.geom)
      AND a.name = 'Hokkaido';

In QGIS:

../../../_images/adjoining_result.png

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(geom, 100) as 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:

../../../_images/hokkaido_buffer.png

Auswahl mit Hilfe des Puffers:

CREATE VIEW vw_hokkaido_buffer_select AS
  SELECT b.gid, b.name, b.geom
    FROM
    (
      SELECT * FROM
        vw_hokkaido_buffer
    ) a,
    region b
    WHERE ST_INTERSECTS(a.geom, b.geom)
    AND b.name != 'Hokkaido';

In this query, the original buffer view is used as any other table would be. It is given the alias a, and its geometry field, a.geom, is used to select any polygon in the region table (alias b) that intersects it. However, Hokkaido itself is excluded from this select statement, because we don’t want it; we only want the regions adjoining it.

In QGIS:

../../../_images/hokkaido_buffer_select.png

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.geom
    FROM region a, region b
      WHERE ST_DISTANCE (a.geom, b.geom) < 100
      AND a.name = 'Hokkaido'
      AND b.name != 'Hokkaido';

Das Ergebnis ist dasselbe und der Zwischenschritt der Pufferbildung entfällt:

../../../_images/hokkaido_distance_select.png

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.geom, b.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.geom, b.geom)) as text,
        ST_SHORTESTLINE(a.geom, b.geom) AS 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.geom, b.geom)) as text,
         ST_LONGESTLINE(a.geom, b.geom) AS 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.geom) as geom
    FROM road a
      WHERE a.id = 1;

CREATE VIEW vw_region_centroid AS
  SELECT a.gid as gid, ST_CENTROID(a.geom) as geom
    FROM region a
      WHERE a.name = 'Saskatchewan';

SELECT ST_PERIMETER(a.geom)
  FROM region a
    WHERE a.name='Queensland';

SELECT ST_AREA(a.geom)
  FROM region a
    WHERE a.name='Queensland';

CREATE VIEW vw_simplify AS
  SELECT gid, ST_Simplify(geom, 20) AS geom
    FROM road;

CREATE VIEW vw_simplify_more AS
  SELECT gid, ST_Simplify(geom, 50) AS 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.geom)) AS geom
    FROM building a
    GROUP BY a.name;

16.4.5. Zusammengefasst

Wir haben gesehen, wie man räumliche Objekte mit Hilfe der neuen Datenbankfunktionen in QGIS abfragen kann.

16.4.6. Was als nächstes?

Als Nächstes werden wir die Strukturen komplexerer Geometrien untersuchen und wie man sie mit Hilfe von PostGIS erstellt.