Einfache Abfragen auf die OSM-Daten

Nachdem wir jetzt alle Daten, die im OpenStreetMap-Projekt von Österreich gesammelt wurden, in der Datenbank haben, können wir sie abfragen.

Jetzt können wir schon grafische SQL-Werkzeuge wie PGAdmin oder SQuirrel SQL verwenden, die psql-Befehlszeile ist nicht mehr notwendig. Einfach eine Verbindung zur PostGIS-Datenbank mit dem User gis und dem dazugehörigen Paßwort anlegen.

Zuerst einige klassische Datenbankabfragen ohne Geokomponente:

Bundesländer in Österreich

Der Tag “boundary=administrative” kennzeichnet in OpenStreetMap ein Polygon, das eine Grenze darstellt. Es gibt Konventionen, was die sogenanten admin_levels dabei bedeuten (siehe OSM-Wiki). Wir brauchen hier admin_level=4, die Bundesländergrenzen.

SELECT DISTINCT name
FROM osm_austria_polygon
WHERE boundary='administrative'
  AND admin_level = '4'
ORDER BY name

Ergebnis:

name
Bayern
Burgenland
Kärnten
Niederösterreich
Oberösterreich
Salzburg
Steiermark
Tirol
Vorarlberg
Wien

Warum ist Bayern dabei? Nun, Geofabrik garantiert nicht, daß immer nur die Daten eines Landes im OSM-Dump dabei sind. Grenzregionen können immer wieder in die Daten hineinrutschen. (Wenn man diesen Test in einigen Wochen macht, kann es passieren, daß Bayern nicht mehr im Ergebnis enthalten ist.)

Wir beheben diesen Fehler später.

Häufigste Namen von Restaurants in Österreich

amenity=restaurant ist der häufigste Tag für Restaurants, wobei Fast-Food-Restaurants, Cafés usw. andere Tags haben. (Siehe OSM-Wiki)

SELECT name, COUNT(osm_id) AS anzahl
FROM osm_austria_point
WHERE amenity = 'restaurant'
  AND name <> ''
GROUP BY name
ORDER BY anzahl DESC
LIMIT 10

Ergebnis:

name anzahl
Kirchenwirt 43
Gasthof zur Post 16
Pizzeria 13
Gasthaus 11
Dorfwirt 10
Interspar Restaurant 9
Asia 9
Pizzeria Venezia 9
Mensa 8
Gasthof Post 7

Abfrage mit GIS-Funktionen

Haltestellen des öffentlichen Verkehrs in der Nähe

Hier verwenden wir zum ersten Mal die Funktion ST_Distance. Sie berechnet die kürzeste Distanz zwischen zwei Objekten.

WITH fh_technikum AS (
    -- Ort des FH Technikum Wien
    SELECT way
    FROM osm_austria_polygon
    WHERE name = 'FH Technikum Wien'
)
SELECT DISTINCT p.name, 
    CASE WHEN p.highway = 'bus_stop' THEN 'Bushaltestelle'
         WHEN p.railway = 'subway_entrance' THEN 'U-Bahn-Station'
         ELSE 'Straßenbahnhaltestelle'
    END AS art,
    ROUND(ST_Distance(p.way, fh.way)) AS "Entfernung (m)"
FROM osm_austria_point p
INNER JOIN fh_technikum fh ON ST_Distance(p.way, fh.way) < 300
WHERE (highway = 'bus_stop' OR railway IN ('subway_entrance', 'tram_stop'))
ORDER BY ROUND(ST_Distance(p.way, fh.way));
name art Entfernung (m)
Höchstädtplatz Straßenbahnhaltestelle 34
Höchstädtplatz Straßenbahnhaltestelle 64
Höchstädtplatz Bushaltestelle 74
Höchstädtplatz Bushaltestelle 89
Höchstädtplatz Straßenbahnhaltestelle 113
Dresdner Straße Straßenbahnhaltestelle 173
Dresdner Straße Bushaltestelle 181
Dresdner Straße Bushaltestelle 199
Dresdner Straße (Meldemannstraße) Bushaltestelle 228
Pasettistraße U-Bahn-Station 235
Dresdner Straße U-Bahn-Station 237
Dresdner Straße (Leithastraße) Bushaltestelle 262
Dresdner Straße (Pasettistraße) Bushaltestelle 267

Die Konstruktion mit “WITH” ist eine sogenannte Common Table Expression. Das ist ein relativ neuer Teil des SQL-Standards und ermöglicht, viele Abfragen einfacher zu formulieren oder ihre Performance zu erhöhen.

In der CTE wird die Position des Technikums abgefragt. Dann werden Haltestellen-Objekte innerhalb von 300 m (die Funktion rechnet in Karteneinheiten) in der OSM-Tabelle gesucht und nach Entfernung sortiert ausgegeben.

Die notwendigen Tags sind highway=bus_stop sowie railway=subway_entrance und railway=tram_stop.

Abfrage mit geografischen Daten als Ergebnis

Koordinaten des Riesenrades

Was passiert, wenn wir das Geodaten-Feld “way” auch ausgeben?

SELECT name, way, ST_AsText(way) AS fläche
FROM osm_austria_polygon
WHERE name = 'Riesenrad';
name way fläche
Riesenrad 01030000…08C1D41 POLYGON((627460.39 484102,627462.56 484132.27,627462.77 484135.11,627464.77 484163.02,627473.01 484162.43,627468.63 484101.41,627460.39 484102))

Die Daten in der Spalte “way” sind hier abgekürzt. Dies ist die interne, binäre Repräsentation eines geometrischen Objekts in der PostGIS-Datenbank. Daraus läßt sich mit der Funktion ST_AsText eine etwas lesbarere Liste der Punkte, die die Fläche definieren, gewinnen. (Es ist aber in vielen Fällen besser, ST_AsEWKT zu verwenden, da dann auch das Koordinatensystem ausgegeben wird.)

Die Ausgabe der Geodaten wird dann für die Anzeige in Karten relevant.

Weiter: Views und Tabellen