Wo ist die nächste Haltestelle? Welche Linien fahren im Umkreis von 500 m um die Schule? Die Stadt Wien veröffentlicht alle Haltepunkte des öffentlichen Verkehrs als Open Data auf data.gv.at. Wir holen diese Daten nach PostgreSQL mit PostGIS, stellen räumliche Abfragen, geocodieren Adressen und bauen daraus eine Karte in QGIS.
Alles Schritt für Schritt für Ubuntu, macOS und Windows (WSL 2), mit einem ✅ Test nach jedem Schritt. Davor klären wir, was Koordinaten eigentlich bedeuten: 16.357 48.186, 1820867 6137793 und 1858 338579 sind derselbe Punkt – die Spengergasse – in drei Koordinatensystemen. Wer das verwechselt, misst 50 % zu viel oder landet im Jemen.
Inhalt
- Was wir bauen
- Koordinaten sind nicht gleich Koordinaten: WGS 84, Mercator und EPSG
- Die Daten: „Öffentliches Verkehrsnetz Haltestellen Wien“
- Schritt 1: PostgreSQL installieren
- Schritt 2: PostGIS installieren
- Schritt 3: Rolle
gisuserund Datenbankhsanlegen - Schritt 4: Daten von data.gv.at herunterladen
- Schritt 5: Import in eine Staging-Tabelle
- Schritt 6: Zieltabelle mit Geometrie und Index
- Schritt 7: Räumliche Abfragen
- Schritt 8: Projektionen in PostGIS nachgemessen
- Schritt 9: Alles als Skript – der Gesamttest
- Schritt 10: Adressen geocodieren mit Nominatim
- Schritt 11: Anwendungsfall QGIS – Views als Kartenlayer
- Schritt 12: Funktionen, Analyse-Views und Daten der Stadt Wien
- Alle Dateien
- Windows: das Ganze mit WSL 2
- Aufräumen
- Quellen
Was wir bauen
data.gv.at → „Öffentliches Verkehrsnetz Haltestellen Wien“ (WFS, Layer OEFFHALTESTOGD)
│ curl (CSV, Koordinaten in EPSG:4326)
▼
haltestellen.csv 10.428 Haltepunkte, z. B. "POINT (16.3843 48.1835)"
│ psql \copy (als Rolle gisuser)
▼
haltestellen_import Staging-Tabelle, alles text
│ INSERT … SELECT ST_GeomFromText(shape, 4326)
▼
haltestellen geometry(Point, 4326) + GiST-Index (Datenbank hs)
│
├─ SQL: nächste Haltestellen (<->), Umkreis (ST_DWithin), Linien (text[] @>)
├─ adressen ← Nominatim (Geocodierung)
└─ Views v_stationen, v_adressen_umkreis, v_naechste_ubahn → QGIS
| Komponente | Werkzeug |
|---|---|
| Datenbank | PostgreSQL 18 (für 17: Versionsnummer in den Paketnamen ersetzen) |
| Geo-Erweiterung | PostGIS 3.6 |
| Rolle / Datenbank | gisuser / hs |
| Daten | Öffentliches Verkehrsnetz Haltestellen Wien (Stadt Wien, CC BY 4.0) |
| Werkzeuge | curl, jq, psql, QGIS |
Koordinaten sind nicht gleich Koordinaten: WGS 84, Mercator und EPSG
Eine Koordinate ist nur zusammen mit ihrem Koordinatenreferenzsystem (CRS) eindeutig. Die Systeme sind im EPSG-Register nummeriert; PostGIS nennt die Nummer SRID und kennt über 8.000 davon (Tabelle spatial_ref_sys).
EPSG:4326 – WGS 84: die Erde in Grad
EPSG:4326 ist das System von GPS: geografische Länge (−180° bis 180°) und Breite (−90° bis 90°) auf dem Ellipsoid WGS 84. Das ist keine Kartenprojektion, sondern eine Position auf einer gekrümmten Fläche. Unsere Daten kommen so: POINT (16.3843 48.1835). Zwei Fallen:
- Grad sind keine Meter. Ein Breitengrad ist überall ca. 111 km, ein Längengrad in Wien nur 111 km × cos(48,2°) ≈ 74 km. Eine mit Pythagoras in 4326 berechnete „Entfernung“ ist eine Zahl in Grad und als Entfernung wertlos. Abhilfe: der PostGIS-Typ
geography, der auf dem Ellipsoid in Metern rechnet. - Die Reihenfolge. EPSG und Google Maps schreiben Breite, Länge (
48.18563, 16.35713); PostGIS, GeoJSON, KML und WKT schreiben x, y = Länge, Breite:ST_MakePoint(16.35713, 48.18563). Vertauscht landet die Spengergasse im Jemen, 4.568 km entfernt (Schritt 8).
Das Problem der Projektion: Mercator
Bildschirm und Papier sind flach, die Erde nicht. Jede Kartenprojektion rechnet Länge/Breite in ebene x/y um, und keine schafft das ohne Verzerrung. Man wählt, was erhalten bleibt: Winkel (winkeltreu), Flächen (flächentreu) oder Entfernungen entlang bestimmter Linien.
Mercator (1569) ist eine winkeltreue Zylinderprojektion: Ein Zylinder berührt die Erde am Äquator, die Längengrade werden zu senkrechten Linien mit gleichem Abstand. Dadurch wird alles in Ost-West-Richtung um den Faktor 1/cos(Breite) gedehnt. Damit Winkel stimmen, wird Nord-Süd um denselben Faktor gestreckt; die Breitenkreise rücken zu den Polen hin auseinander.
x = R · λ y = R · ln( tan(π/4 + φ/2) ) λ = Länge, φ = Breite (Bogenmaß) Maßstabsfaktor k = 1 / cos φ Äquator: 1,00 Wien (48,2°): 1,50 Oslo (60°): 2,00 Pol: ∞
Die Grafik oben zeigt daneben die einfache Zylinderprojektion mit einer Lichtquelle im Erdmittelpunkt. Die ist nicht winkeltreu; Mercator ist rein mathematisch so konstruiert, dass ein kleiner Kreis auf der Erde auf der Karte ein Kreis bleibt, nur größer:
Oben werden die Kreise im Norden zu flachen Ellipsen, unten bleiben sie Kreise, werden aber riesig: Grönland erscheint so groß wie Afrika, das 14-mal größer ist. In Wien ist jede Strecke auf einer Mercator-Karte 1,5-mal, jede Fläche 2,25-mal zu groß.
EPSG:3857 – Web Mercator, das System der Online-Karten
Google hat 2005 für Google Maps eine vereinfachte Variante gewählt: WGS-84-Koordinaten mit den Kugelformeln projiziert (Radius 6.378.137 m). Geodätisch ist das falsch – die EPSG nahm das System zunächst nicht auf, es lief unter der inoffiziellen Nummer 900913 („google“ in Leetspeak). Heute heißt es EPSG:3857 „WGS 84 / Pseudo-Mercator“; gegenüber dem ellipsoidischen Mercator (EPSG:3395) liegt Wien darin 32 km weiter nördlich. Trotzdem verwenden es praktisch alle Online-Karten (Google Maps, OpenStreetMap, Bing, Apple, basemap.at):
- Winkeltreu: Kreuzungen bleiben rechtwinkelig, Gebäude behalten ihre Form, Norden ist oben.
- Quadratische Welt: Bei ±85,05° abgeschnitten ist die Weltkarte ein Quadrat. Zoomstufe 0 ist eine Kachel mit 256×256 Pixel, jede weitere Stufe teilt jede Kachel in vier; jede Kachel hat eine Adresse
z/x/y. - Die Einheit heißt „Meter“, stimmt aber nur am Äquator. In Wien ist ein 3857-Meter 0,67 m lang. Zum Messen ist 3857 ungeeignet (Schritt 8).
OpenStreetMap, Google Maps, Google Earth – welches System, und warum nicht austauschbar?
| Daten gespeichert in | Anzeige | Lizenz | |
|---|---|---|---|
| OpenStreetMap | WGS 84 (EPSG:4326), 7 Nachkommastellen (≈ 1 cm) | Kacheln in EPSG:3857 | ODbL: frei nutzbar mit Namensnennung, abgeleitete Datenbanken wieder offen |
| Google Maps | WGS 84 (LatLng, Breite zuerst) | Kacheln in EPSG:3857 | Proprietär: Daten und Kacheln nur über Google-APIs |
| Google Earth | WGS 84; KML ist immer EPSG:4326 (Länge, Breite, Höhe) | 3D-Globus, keine Kartenprojektion | Proprietär |
OSM und Google Maps passen auf den ersten Blick zusammen (4326 rein, 3857 raus). Austauschbar sind sie trotzdem nicht:
- Daten ≠ Darstellung. 4326 beschreibt eine Position auf dem Ellipsoid, 3857 eine Bildschirmkarte. Rohdaten ohne Umrechnung auf eine Karte zu legen verschiebt oder verzerrt sie. Google Earth hat gar keine flache Karte; eine Earth-Ansicht wird nie zur Kachel.
- Unterschiedliche Geometrie. OSM-Straßen wurden von Freiwilligen digitalisiert, Googles aus anderen Quellen. Beide in WGS 84, aber oft einige Meter nebeneinander.
- Lizenzen. OSM-Daten darf man herunterladen, speichern und weiterverarbeiten. Google-Daten und -Kacheln darf man nicht außerhalb der Google-APIs speichern oder mit fremden Karten kombinieren.
Österreich: MGI, Gauß-Krüger – und warum NÖ Atlas und DORIS eigene Systeme haben
Der NÖ Atlas rechnet intern mit EPSG:31259 (calcCrs=31259 im Quelltext), die Kartenlinks von DORIS (Oberösterreich) tragen srs=31255, die Stadt Wien gibt ihre Bounding Boxes in EPSG:31256 an. Der Grund:
- Kataster: Die österreichische Vermessung beruht auf dem Datum MGI (Militärgeographisches Institut; Bessel-Ellipsoid 1841, Fundamentalpunkt Hermannskogel). Flächenwidmung, Leitungskataster, Straßenbau liegen in MGI vor und müssen zentimetergenau zum Kataster passen.
- Anderes Datum, andere Zahlen: MGI und WGS 84 verwenden verschiedene, gegeneinander verschobene Ellipsoide. Dieselben Zahlen
16.35713 48.18563bezeichnen in MGI einen Punkt 105 m neben dem WGS-84-Punkt (Schritt 8). - Gauß-Krüger statt Mercator: Der Zylinder liegt quer und berührt die Erde entlang eines Meridians (transversale Mercator-Projektion). Österreich ist in drei Streifen geteilt, benannt nach der Länge östlich von Ferro: M28 (Vorarlberg, Tirol), M31 (Salzburg, Oberösterreich, Kärnten), M34 (Niederösterreich, Wien, Burgenland). Nahe am Mittelmeridian ist die Verzerrung vernachlässigbar, die Koordinaten sind Meter.
- Deshalb hat jedes Land sein System: Oberösterreich liegt in M31 → DORIS verwendet 31255 (GK Central). Niederösterreich und Wien liegen in M34 → NÖ Atlas 31259 (GK M34, mit 750 km Rechtswert-Offset), Wien 31256 (GK East, ohne Offset). Für ganz Österreich gibt es 31287 (Austria Lambert).
| EPSG | Name | Einheit | Wofür |
|---|---|---|---|
| 4326 | WGS 84 | Grad | GPS, Datenaustausch, GeoJSON/KML, unsere Speicherung |
| 3857 | WGS 84 / Pseudo-Mercator | „Meter“ (nur am Äquator echt) | Anzeige: OSM, Google Maps, basemap.at |
| 4312 | MGI | Grad | Geografische Koordinaten im österreichischen Landessystem |
| 31254 / 31255 / 31256 | MGI / Austria GK West / Central / East | m | Gauß-Krüger M28 / M31 / M34 – Wien: 31256, DORIS: 31255 |
| 31257 / 31258 / 31259 | MGI / Austria GK M28 / M31 / M34 | m | wie oben, mit Offset 150/450/750 km – NÖ Atlas: 31259 |
| 31287 | MGI / Austria Lambert | m | Ganz Österreich auf einer Karte |
Faustregel: Speichern in 4326, messen mit geography oder in einem metrischen Landessystem (31256 für Wien), anzeigen in 3857. Umrechnen kann PostGIS mit ST_Transform; QGIS tut es beim Anzeigen automatisch.
Die Daten: „Öffentliches Verkehrsnetz Haltestellen Wien“
Der Datensatz Öffentliches Verkehrsnetz Haltestellen Wien enthält alle Haltepunkte von Straßenbahn, Bus, U-Bahn, S-Bahn, Badner Bahn und Nachtbussen in Wien und Umland. data.gv.at ist der Katalog; die Daten liefert der Geodienst der Stadt Wien als WFS (Web Feature Service). srsName=EPSG:4326 lässt den Server in WGS 84 umrechnen, outputFormat=csv liefert CSV (alternativ json oder shape-zip). Lizenz CC BY 4.0, Namensnennung „Datenquelle: Stadt Wien – data.wien.gv.at“.
| Spalte | Beispiel | Bedeutung |
|---|---|---|
OBJECTID | 16995666 | Eindeutige Nummer des Haltepunkts |
SHAPE | POINT (16.3843 48.1835) | Geometrie als WKT (Well-Known Text), Länge vor Breite, EPSG:4326 |
HTXT / HTXTK | Arsenal | Name (lang/kurz) |
HLINIEN | 12A, 14A | Linien, kommagetrennt |
LTYP | 2 | Verkehrsmittel (siehe unten) |
WEBLINK1 | https://anachb.vor.at?… | Fahrplanauskunft |
Eine offizielle Legende für LTYP gibt es im Datensatz nicht; aus den Linien ergibt sich:
| LTYP | Verkehrsmittel | Beispiel-Linien |
|---|---|---|
| 1 | Straßenbahn | 1, 2, 62, D |
| 2 | Autobus (Wiener Linien) | 12A, 14A, 59A |
| 3 | Regionalbus | 150, 272, 540 |
| 4 | U-Bahn | U1–U6 |
| 5 | S-Bahn / Regionalzug | S45, REX6 |
| 9 | Badner Bahn | WLB |
| 10–13 | Nachtbus | N25, N62 |
Eine Haltestelle wie „Laurenzgasse“ besteht aus mehreren Haltepunkten (je Richtung, je Verkehrsmittel): 10.428 Zeilen verteilen sich auf 3.647 Namen.
Schritt 1: PostgreSQL installieren
PostgreSQL 18 aus den offiziellen Quellen von postgresql.org. Wer schon PostgreSQL 17 hat, ersetzt im Folgenden 18 durch 17.
Ubuntu
Über das offizielle PostgreSQL-APT-Repository (PGDG) – nur dort gibt es PostGIS für die aktuelle PostgreSQL-Version (Anleitung):
sudo apt update sudo apt install -y postgresql-common curl sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh # richtet apt.postgresql.org ein sudo apt install -y postgresql-18
✅ Test:
pg_lsclusters sudo -u postgres psql -c "SELECT version();"
Erwartet: 18 main 5432 online … und PostgreSQL 18.x on x86_64-pc-linux-gnu …. Bei einer anderen Version kommt das Paket aus dem Ubuntu-Repository: prüfen, ob /etc/apt/sources.list.d/pgdg.sources existiert.
macOS
Mit Homebrew (brew.sh); das PostGIS-Paket passt dort zu PostgreSQL 17 und 18.
brew install postgresql@18 brew services start postgresql@18 # postgresql@18 ist "keg-only" → Pfad setzen echo 'export PATH="$(brew --prefix)/opt/postgresql@18/bin:$PATH"' >> ~/.zshrc source ~/.zshrc
Bei Homebrew heißt der Superuser nicht postgres, sondern wie dein macOS-Benutzer.
✅ Test:
brew services list | grep postgresql which psql psql postgres -c "SELECT version();"
Erwartet: postgresql@18 started, …/opt/postgresql@18/bin/psql und PostgreSQL 18.x on aarch64-apple-darwin …. Zeigt which psql einen anderen Pfad, wird ein falscher Client verwendet → PATH prüfen.
Schritt 2: PostGIS installieren
PostGIS ergänzt PostgreSQL um die Typen geometry (rechnet in der Ebene, in den Einheiten des CRS) und geography (rechnet auf dem Ellipsoid, in Metern), räumliche Indizes und Funktionen wie ST_Distance, ST_DWithin, ST_Transform.
Ubuntu
sudo apt install -y postgresql-18-postgis-3
✅ Test:
sudo -u postgres psql -c "SELECT name, default_version FROM pg_available_extensions WHERE name LIKE 'postgis%';"
Erwartet: eine Zeile postgis | 3.6.x. Ist die Liste leer, wurde PostGIS für eine andere PostgreSQL-Version installiert.
macOS
brew install postgis brew services restart postgresql@18
✅ Test:
psql postgres -c "SELECT name, default_version FROM pg_available_extensions WHERE name LIKE 'postgis%';"
Erwartet: eine Zeile postgis | 3.6.x.
Schritt 3: Rolle gisuser und Datenbank hs anlegen
Wir arbeiten nicht als Superuser, sondern als Login-Rolle gisuser, Eigentümerin der Datenbank hs. Nur die Extension muss einmalig ein Superuser aktivieren.
# Ubuntu sudo -u postgres psql # macOS (Homebrew) psql postgres
In der psql-Shell (Passwort anpassen):
CREATE ROLE gisuser WITH LOGIN PASSWORD 'gis123'; CREATE DATABASE hs OWNER gisuser; \c hs CREATE EXTENSION IF NOT EXISTS postgis; \dx \q
Seit PostgreSQL 15 gehört das Schema public dem Datenbank-Eigentümer; gisuser braucht keine weiteren GRANTs.
✅ Test:
# 1) Login als gisuser über TCP (so verbinden sich auch die Skripte)
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT current_user, current_database(), postgis_version();"
# 2) Luftlinie Spengergasse → Stephansplatz in Metern
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT round(ST_Distance(
ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326)::geography,
ST_SetSRID(ST_MakePoint(16.37208, 48.20849), 4326)::geography)) AS meter;"
# 3) Darf gisuser Tabellen mit Geometrie anlegen?
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "CREATE TABLE t_test (p geometry(Point, 4326)); DROP TABLE t_test;"
Erwartet: gisuser | hs | 3.6 USE_GEOS=1 USE_PROJ=1 USE_STATS=1, 2774 m und CREATE TABLE / DROP TABLE ohne Fehler. Peer authentication failed heißt: ohne localhost verbunden – die URL wie angegeben verwenden.
Schritt 4: Daten von data.gv.at herunterladen
Die URL steht auf der Datensatz-Seite bei der Ressource „CSV“. Die Anführungszeichen sind nötig, sonst deutet die Shell & als „im Hintergrund ausführen“.
mkdir -p ~/haltestellen cd ~/haltestellen curl -fsSL -o haltestellen.csv "https://data.wien.gv.at/daten/geo?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:OEFFHALTESTOGD&srsName=EPSG:4326&outputFormat=csv"
Ist data.wien.gv.at nicht erreichbar: Kopie vom 5. Oktober 2026 auf diesem Blog – haltestellen.csv (2 MB; Datenquelle: Stadt Wien – data.wien.gv.at, CC BY 4.0). Als ~/haltestellen/haltestellen.csv speichern.
✅ Test:
wc -l haltestellen.csv head -3 haltestellen.csv file haltestellen.csv
Erwartet: ca. 10429 Zeilen (die Zahl ändert sich mit jeder Aktualisierung), Kopfzeile FID,OBJECTID,SHAPE,HTXT,HTXTK,HLINIEN,DIVA_ID,LTYP,WEBLINK1,SE_ANNO_CAD_DATA, dann Zeilen wie …,POINT (16.3843… 48.1835…),Arsenal,Arsenal,69A,,2,…; file meldet CSV text. Steht XML mit ServiceException in der Datei, ist die URL falsch.
Schritt 5: Import in eine Staging-Tabelle
Zuerst 1:1 in eine Tabelle, deren Spalten alle text sind; Typumwandlung und Geometrie folgen in SQL, wo sich Fehler gezielt finden lassen. \copy (mit Backslash) ist ein psql-Befehl: Die Datei wird vom Client gelesen, braucht keine Superuser-Rechte und muss nicht auf dem Server liegen. Der Pfad ist relativ zum Startverzeichnis von psql.
Datei 01_staging.sql in ~/haltestellen:
DROP TABLE IF EXISTS haltestellen_import;
CREATE TABLE haltestellen_import (
fid text,
objectid text,
shape text, -- z. B. 'POINT (16.3843 48.1835)'
htxt text,
htxtk text,
hlinien text,
diva_id text,
ltyp text,
weblink1 text,
se_anno_cad_data text
);
\copy haltestellen_import FROM 'haltestellen.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8')
cd ~/haltestellen psql "postgresql://gisuser:gis123@localhost:5432/hs" -f 01_staging.sql
✅ Test:
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT count(*) AS zeilen, count(DISTINCT htxt) AS namen, count(DISTINCT shape) AS punkte FROM haltestellen_import;" psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT htxt, hlinien, ltyp, shape FROM haltestellen_import WHERE htxt LIKE 'Spengergasse%';"
Erwartet: COPY 10428, dann 10428 | 3647 | 7079 und Spengergasse | 14A | 2 | POINT (16.3577… 48.1846…). Kaputte Umlaute (Straße) → ENCODING prüfen.
Schritt 6: Zieltabelle mit Geometrie und Index
ST_GeomFromText(shape, 4326) macht aus dem WKT-Text eine Geometrie mit SRID. Die Spalte ist als geometry(Point, 4326) typisiert; alles andere lehnt PostGIS ab. Die Linien werden ein Array (text[]), damit sich nach einer Linie suchen lässt.
Datei 02_haltestellen.sql:
DROP TABLE IF EXISTS haltestellen;
CREATE TABLE haltestellen (
id integer PRIMARY KEY,
name text NOT NULL,
linien text[],
ltyp smallint,
weblink text,
geom geometry(Point, 4326) NOT NULL
);
INSERT INTO haltestellen (id, name, linien, ltyp, weblink, geom)
SELECT objectid::integer,
htxt,
regexp_split_to_array(hlinien, '\s*,\s*'),
ltyp::smallint,
weblink1,
ST_GeomFromText(shape, 4326)
FROM haltestellen_import;
-- räumlicher Index für geometry (KNN-Suche mit <->)
CREATE INDEX haltestellen_geom_idx ON haltestellen USING gist (geom);
-- räumlicher Index für Meter-Abfragen mit geography (ST_DWithin in m)
CREATE INDEX haltestellen_geog_idx ON haltestellen USING gist ((geom::geography));
-- Index für "welche Haltestellen hat Linie X?"
CREATE INDEX haltestellen_linien_idx ON haltestellen USING gin (linien);
ANALYZE haltestellen;
psql "postgresql://gisuser:gis123@localhost:5432/hs" -f 02_haltestellen.sql
✅ Test:
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT count(*), ST_SRID(geom) AS srid, GeometryType(geom) AS typ FROM haltestellen GROUP BY 2, 3;"
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS ungueltig,
count(*) FILTER (WHERE NOT ST_Intersects(geom, ST_MakeEnvelope(16.1, 47.9, 16.7, 48.4, 4326))) AS ausserhalb
FROM haltestellen;"
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "\d haltestellen"
Erwartet: 10428 | 4326 | POINT, 0 | 0 (kein Punkt außerhalb eines Rechtecks um Wien – vertauschte Koordinaten fielen hier auf) und die Tabellenbeschreibung mit geom | geometry(Point,4326) und drei Indizes.
Schritt 7: Räumliche Abfragen
Bezugspunkt ist die HTL Spengergasse: ST_MakePoint(16.35713, 48.18563), Länge zuerst (woher die Zahlen kommen, zeigt Schritt 10). Die Abfragen in einer psql-Sitzung ausführen: psql "postgresql://gisuser:gis123@localhost:5432/hs"
a) Die nächsten 5 Haltepunkte. Der Operator <-> sortiert über den GiST-Index nach Entfernung (K-Nearest-Neighbour), ohne alle Distanzen zu berechnen. Die Meter kommen von geography:
SELECT name,
array_to_string(linien, ',') AS linien,
round(ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326)::geography)) AS meter
FROM haltestellen
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326)
LIMIT 5;
✅ Test:
name | linien | meter ------------------------------------------+---------+------- Spengergasse | 14A | 116 Fendigasse | 12A | 184 Siebenbrunnengasse (Ramperstorffergasse) | 12A,14A | 191 Fendigasse | 12A,14A | 209 Bacherplatz | 59A | 296
b) Alles im Umkreis von 500 m, pro Name zusammengefasst. ST_DWithin mit geography nimmt den Radius in Metern und nutzt haltestellen_geog_idx:
SELECT name,
string_agg(DISTINCT l, ',' ORDER BY l) AS linien,
round(min(ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326)::geography))) AS meter
FROM haltestellen, unnest(linien) AS l
WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326)::geography, 500)
GROUP BY name
ORDER BY meter;
✅ Test:
Erwartet: 12 Haltestellen von Spengergasse | 14A | 116 bis Kohlgasse | 12A | 496. EXPLAIN vor der Abfrage zeigt Bitmap Index Scan on haltestellen_geog_idx.
c) Alle U4-Stationen. @> („enthält“) auf dem Array nutzt den GIN-Index:
SELECT name, ST_AsText(geom) FROM haltestellen WHERE linien @> ARRAY['U4'] AND ltyp = 4 ORDER BY ST_Y(geom) DESC; -- von Nord nach Süd
✅ Test:
Erwartet: 19 Stationen, beginnend mit Heiligenstadt, Spittelau, Friedensbrücke.
Schritt 8: Projektionen in PostGIS nachgemessen
ST_Transform(geom, srid) rechnet eine Geometrie in ein anderes Koordinatensystem um. Damit prüfen wir die Theorie vom Anfang.
a) Derselbe Punkt in sieben Systemen
SELECT srid, ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326), srid)) AS koordinaten FROM unnest(ARRAY[4326, 3857, 31256, 31259, 31287, 32633, 4312]) AS srid;
✅ Test:
srid | koordinaten -------+---------------------------------------------- 4326 | POINT(16.35713 48.18563) ← Grad (WGS 84) 3857 | POINT(1820867.38 6137792.80) ← Web Mercator 31256 | POINT(1858.49 338579.23) ← GK East (Wien): 1,9 km östlich von 16°20′ 31259 | POINT(751858.49 338579.23) ← GK M34 (NÖ Atlas): + 750 km Offset 31287 | POINT(624783.13 480631.83) ← Austria Lambert 32633 | POINT(600871.05 5337823.01) ← UTM 33N 4312 | POINT(16.35833 48.18613) ← MGI: andere Grad-Zahlen für denselben Ort
b) Spengergasse → Stephansplatz
WITH p AS (
SELECT ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326) AS a, -- Spengergasse
ST_SetSRID(ST_MakePoint(16.37208, 48.20849), 4326) AS b -- Stephansplatz
)
SELECT round(ST_Distance(a, b)::numeric, 5) AS grad_4326,
round(ST_Distance(a::geography, b::geography)) AS m_geography,
round(ST_Distance(ST_Transform(a, 3857), ST_Transform(b, 3857))) AS m_3857,
round(ST_Distance(ST_Transform(a, 31256), ST_Transform(b, 31256))) AS m_31256,
round(ST_Distance(ST_Transform(a, 31287), ST_Transform(b, 31287))) AS m_31287
FROM p;
✅ Test:
grad_4326 | m_geography | m_3857 | m_31256 | m_31287 -----------+-------------+--------+---------+--------- 0.02731 | 2774 | 4165 | 2774 | 2774
geography, Gauß-Krüger und Lambert stimmen auf den Meter überein. Web Mercator liefert 4165 m statt 2774 m, genau der Faktor 1,50 aus 1/cos(48,2°).
c) „500 m Umkreis“ in vier Varianten
SELECT
(SELECT count(*) FROM haltestellen
WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326)::geography, 500)) AS richtig_geography,
(SELECT count(*) FROM haltestellen
WHERE ST_DWithin(ST_Transform(geom, 31256), ST_Transform(ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326), 31256), 500)) AS gk_east_31256,
(SELECT count(*) FROM haltestellen
WHERE ST_DWithin(ST_Transform(geom, 3857), ST_Transform(ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326), 3857), 500)) AS falsch_3857,
(SELECT count(*) FROM haltestellen
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326), 500)) AS falsch_grad;
✅ Test:
richtig_geography | gk_east_31256 | falsch_3857 | falsch_grad
-------------------+---------------+-------------+-------------
20 | 20 | 8 | 10428
In 3857 sind „500 m“ in Wien nur 333 m: 8 statt 20 Haltepunkte. Mit geometry in 4326 heißt 500 „500 Grad“: die ganze Erde, alle 10.428 Zeilen. Keine der Varianten gibt eine Fehlermeldung.
d) Vertauschte Achsen und falsches Datum
-- Breite/Länge vertauscht
SELECT round(ST_Distance(ST_SetSRID(ST_MakePoint(48.18563, 16.35713), 4326)::geography,
ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326)::geography) / 1000) AS km_daneben;
-- dieselben Zahlen als MGI (4312) statt WGS 84 (4326) interpretiert
SELECT round(ST_Distance(ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4326)::geography,
ST_Transform(ST_SetSRID(ST_MakePoint(16.35713, 48.18563), 4312), 4326)::geography)) AS m_daneben;
✅ Test:
Erwartet: 4568 km (16,36° N / 48,19° O liegt im Jemen) und 105 m Datumsversatz. Koordinaten aus NÖ Atlas oder DORIS daher nie direkt mit GPS-Daten mischen, sondern mit dem richtigen SRID versehen und per ST_Transform umrechnen.
Schritt 9: Alles als Skript – der Gesamttest
Die Stadt Wien aktualisiert den Datensatz regelmäßig; das Skript hs_import.sh fasst Schritt 4–6 wiederholbar zusammen (im selben Ordner wie die SQL-Dateien):
#!/usr/bin/env bash
# Importiert "Öffentliches Verkehrsnetz Haltestellen Wien" (data.gv.at) nach PostgreSQL/PostGIS
set -euo pipefail
DB_URL="${DB_URL:-postgresql://gisuser:gis123@localhost:5432/hs}"
URL="https://data.wien.gv.at/daten/geo?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:OEFFHALTESTOGD&srsName=EPSG:4326&outputFormat=csv"
cd "$(dirname "$0")"
echo "1) Download ..."
curl -fsSL -o haltestellen.csv "$URL"
echo " $(($(wc -l < haltestellen.csv) - 1)) Zeilen"
echo "2) Staging-Tabelle + \\copy ..."
psql "$DB_URL" -v ON_ERROR_STOP=1 -q -f 01_staging.sql
echo "3) Zieltabelle mit Geometrie ..."
psql "$DB_URL" -v ON_ERROR_STOP=1 -q -f 02_haltestellen.sql
echo "4) Kontrolle:"
psql "$DB_URL" -c "SELECT count(*) AS haltepunkte, count(DISTINCT name) AS namen, ST_SRID(geom) AS srid FROM haltestellen GROUP BY srid;"
set -euo pipefail bricht beim ersten Fehler ab; -v ON_ERROR_STOP=1 lässt psql auch bei SQL-Fehlern stoppen.
✅ Test:
cd ~/haltestellen chmod +x hs_import.sh ./hs_import.sh
Erwartet: 1) Download … 10428 Zeilen, 2) und 3) ohne Fehler, Kontrolle 10428 | 3647 | 4326. Die Abfragen aus Schritt 7 liefern danach dieselben Ergebnisse.
Schritt 10: Adressen geocodieren mit Nominatim
Geocodierung macht aus einer Adresse eine Koordinate, Reverse Geocoding aus einer Koordinate eine Adresse. OpenStreetMap betreibt dafür Nominatim.
a) Im Browser. nominatim.openstreetmap.org/ui/search.html?q=spengergasse+20 zeigt die Treffer auf der Karte; details liefert Centre Point (lat,lon) 48.1855989,16.3571681, also Breite zuerst. Zu „spengergasse 20“ gibt es mehrere Treffer (Schule, Kunstwerk, Parkbank): Geocodierung ist eine Suche mit Rangfolge, kein Nachschlagen.
b) Als API. Unter /search kommt dasselbe als JSON: /ui/search.html durch /search ersetzen und format=jsonv2 anhängen. Zum Zerlegen von JSON in der Shell dient jq:
# Ubuntu / WSL sudo apt install -y jq # macOS (ab macOS 15 vorinstalliert, sonst:) brew install jq
UA="hs-import-unterricht/1.0 (HTL Spengergasse, DBI)"
curl -s -A "$UA" "https://nominatim.openstreetmap.org/search?q=spengergasse+20&format=jsonv2&limit=3" \
| jq -c '.[] | {lat, lon, category, type, display_name}'
✅ Test:
{"lat":"48.1855989","lon":"16.3571681","category":"amenity","type":"school","display_name":"HTBLuVA Spengergasse, 20, Spengergasse, Matzleinsdorf, Margareten, … Wien, 1050, Österreich"}
{"lat":"48.1861022","lon":"16.3566942","category":"tourism","type":"artwork","display_name":"Maria Theresia, 20, Spengergasse, …"}
{"lat":"48.1860463","lon":"16.3567293","category":"amenity","type":"bench","display_name":"20, Spengergasse, …"}
| Parameter / Feld | Bedeutung |
|---|---|
q=… | Freitextsuche; Sonderzeichen URL-codiert, am einfachsten mit curl --get --data-urlencode "q=Schönbrunner Straße 1, Wien" |
street=, city=, postalcode=, country= | Strukturierte Suche statt q (nicht mischen) |
format=jsonv2 | Ausgabeformat (auch geojson, xml) |
limit=1 | Nur den besten Treffer |
countrycodes=at | Nur Österreich |
addressdetails=1 | Adresse zusätzlich in Einzelteilen (road, house_number, postcode …) |
lat, lon | Koordinaten in EPSG:4326, als Text und getrennt |
display_name | Was Nominatim tatsächlich gefunden hat – immer kontrollieren |
Reverse Geocoding über /reverse:
curl -s -A "$UA" "https://nominatim.openstreetmap.org/reverse?lat=48.18563&lon=16.35713&format=jsonv2&zoom=18" | jq -r '.display_name'
✅ Test:
Erwartet: 20, Spengergasse, Matzleinsdorf, Margareten, … Wien, 1050, Österreich
c) Die Spielregeln. Der öffentliche Server wird von der OSM-Community betrieben; die Nominatim Usage Policy ist verbindlich:
- Höchstens 1 Anfrage pro Sekunde, nicht parallel. Für eine Klasse hinter derselben Schul-IP gilt das Limit gemeinsam.
- Eigene Kennung im
User-Agent, die die Anwendung beschreibt. Standardkennungen von Bibliotheken werden abgewiesen, ebenso Platzhalter wie…@example.org: Antwort403 Forbidden. - Ergebnisse speichern statt dieselbe Adresse wiederholt abzufragen – das macht die Tabelle unten.
- Kein Massen-Geocoding. Für tausende Adressen einen eigenen Nominatim-Server oder einen kommerziellen Dienst verwenden.
- Namensnennung: ODbL, „© OpenStreetMap contributors“.
d) Geocodieren in die Datenbank. Tabelle mit Adressen, geom anfangs NULL. Datei 03_adressen.sql:
DROP TABLE IF EXISTS adressen;
CREATE TABLE adressen (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
adresse text NOT NULL UNIQUE,
treffer text, -- display_name von Nominatim (zur Kontrolle!)
geom geometry(Point, 4326) -- NULL = noch nicht geocodiert
);
INSERT INTO adressen (adresse) VALUES
('Spengergasse 20, Wien'),
('Stephansplatz 1, Wien'),
('Schloss Schönbrunn, Wien'),
('Praterstern, Wien'),
('Gibtsnichtgasse 999, Wien');
geocode.sh holt alle Zeilen ohne Koordinaten, fragt Nominatim und schreibt zurück. Die Werte gehen als psql-Variablen (-v) hinein; :'name' maskiert psql als SQL-Text, wichtig bei Apostrophen in Ortsnamen.
#!/usr/bin/env bash
# Geocodiert alle Zeilen von "adressen" ohne Koordinaten über Nominatim (OpenStreetMap)
set -euo pipefail
DB_URL="${DB_URL:-postgresql://gisuser:gis123@localhost:5432/hs}"
UA="hs-import-unterricht/1.0 (HTL Spengergasse, DBI)" # Pflicht: eigene, aussagekräftige Kennung
psql "$DB_URL" -At -F $'\t' -c "SELECT id, adresse FROM adressen WHERE geom IS NULL ORDER BY id" |
while IFS=$'\t' read -r id adresse; do
json=$(curl -fsS -A "$UA" --get "https://nominatim.openstreetmap.org/search" \
--data-urlencode "q=$adresse" -d format=jsonv2 -d limit=1 -d countrycodes=at)
if [ "$(jq 'length' <<<"$json")" -eq 0 ]; then
echo "KEIN TREFFER: $adresse"
else
lon=$(jq -r '.[0].lon' <<<"$json")
lat=$(jq -r '.[0].lat' <<<"$json")
name=$(jq -r '.[0].display_name' <<<"$json")
psql "$DB_URL" -q -v id="$id" -v lon="$lon" -v lat="$lat" -v name="$name" <<'SQL'
UPDATE adressen
SET geom = ST_SetSRID(ST_MakePoint(:lon, :lat), 4326), -- Länge zuerst!
treffer = :'name'
WHERE id = :id;
SQL
echo "OK: $adresse → $lon $lat"
fi
sleep 1 # Nominatim-Regel: höchstens 1 Anfrage pro Sekunde
done
cd ~/haltestellen psql "postgresql://gisuser:gis123@localhost:5432/hs" -f 03_adressen.sql chmod +x geocode.sh ./geocode.sh
✅ Test:
OK: Spengergasse 20, Wien → 16.3571681 48.1855989 OK: Stephansplatz 1, Wien → 16.3734772 48.2081643 OK: Schloss Schönbrunn, Wien → 16.3115684 48.1849894 OK: Praterstern, Wien → 16.3930785 48.2197676 KEIN TREFFER: Gibtsnichtgasse 999, Wien
Ein zweiter Aufruf fragt nur noch die Adresse ohne Treffer ab. curl: (22) … error: 403 heißt: Kennung abgelehnt oder Limit überschritten.
e) Nächste Haltestelle je Adresse. CROSS JOIN LATERAL führt die KNN-Suche aus Schritt 7 einmal pro Adresse aus:
SELECT a.adresse,
h.name AS naechste_haltestelle, array_to_string(h.linien, ',') AS linien,
round(ST_Distance(a.geom::geography, h.geom::geography)) AS meter
FROM adressen a
CROSS JOIN LATERAL (
SELECT name, linien, geom
FROM haltestellen
ORDER BY geom <-> a.geom
LIMIT 1
) h
WHERE a.geom IS NOT NULL
ORDER BY a.id;
✅ Test:
adresse | naechste_haltestelle | linien | meter --------------------------+------------------------------------------+-----------+------- Spengergasse 20, Wien | Spengergasse | 14A | 112 Stephansplatz 1, Wien | Stephansplatz | 3A | 100 Schloss Schönbrunn, Wien | Schloss Schönbrunn (Schönbr. Schloßstr.) | N60 | 261 Praterstern, Wien | Praterstern (Anitta-Müller-Cohen-Platz) | 82A,SV900 | 65
Die „nächste Haltestelle“ ist der nächste Haltepunkt, beim Stephansplatz also der Bus 3A; wer nur U-Bahn will, ergänzt WHERE ltyp = 4. Die Spalte treffer zeigt, was Nominatim gefunden hat – bei unscharfen Adressen kann das ein anderes Objekt sein.
Schritt 11: Anwendungsfall QGIS – Views als Kartenlayer
QGIS lädt PostGIS-Tabellen und Views direkt als Layer: Jede Zeile mit Geometrie wird zu Punkt, Linie oder Fläche. Die Logik bleibt in SQL; ändert sich eine Adresse in der Datenbank, zeigt QGIS nach F5 die neue Karte. Drei Views ergeben eine Erreichbarkeitskarte:
| View | Geometrie | Zeigt |
|---|---|---|
v_stationen | Punkt (Mittelpunkt aller Haltepunkte einer Station) | Eine Zeile pro Station mit allen Linien und einer Kategorie (U-Bahn, S-Bahn, Straßenbahn, Badner Bahn, Bus) fürs Symbol |
v_adressen_umkreis | Fläche (Kreis, 500 m) | Umkreis um jede Adresse aus Schritt 10 mit Anzahl der Stationen und Linien darin |
v_naechste_ubahn | Linie | Luftlinie von jeder Adresse zur nächsten U-Bahn-Station mit Entfernung |
Zwei Dinge braucht QGIS: einen eindeutigen Schlüssel (bei Views die Spalte id, beim Laden anzugeben) und einen erkennbaren Geometrietyp: ST_Centroid(…) liefert nur „Geometrie“; der Cast ::geometry(Point, 4326) legt Typ und SRID fest, und die View erscheint in geometry_columns.
Datei 04_views.sql:
-- 1) Eine Zeile pro Station: Haltepunkte zusammenfassen
DROP VIEW IF EXISTS v_stationen CASCADE;
CREATE VIEW v_stationen AS
SELECT min(id) AS id, -- QGIS braucht einen eindeutigen Schlüssel
name,
count(*) AS haltepunkte,
array_to_string(array_agg(DISTINCT l ORDER BY l), ', ') AS linien,
bool_or(ltyp = 4) AS ubahn,
bool_or(ltyp = 1) AS strassenbahn,
bool_or(ltyp = 5) AS sbahn,
bool_or(ltyp IN (2, 3)) AS bus,
bool_or(ltyp BETWEEN 10 AND 13) AS nachtbus,
CASE WHEN bool_or(ltyp = 4) THEN 'U-Bahn'
WHEN bool_or(ltyp = 5) THEN 'S-Bahn'
WHEN bool_or(ltyp = 1) THEN 'Straßenbahn'
WHEN bool_or(ltyp = 9) THEN 'Badner Bahn'
ELSE 'Bus' END AS kategorie, -- "höchstes" Verkehrsmittel → Symbol in QGIS
ST_Centroid(ST_Collect(geom))::geometry(Point, 4326) AS geom
FROM haltestellen, unnest(linien) AS l
GROUP BY name;
-- 2) Umkreis um jede Adresse (500 m, als Fläche) mit Anzahl der Stationen darin
DROP VIEW IF EXISTS v_adressen_umkreis;
CREATE VIEW v_adressen_umkreis AS
SELECT a.id, a.adresse,
(SELECT count(*) FROM v_stationen s
WHERE ST_DWithin(s.geom::geography, a.geom::geography, 500)) AS stationen_500m,
(SELECT string_agg(DISTINCT l, ', ' ORDER BY l)
FROM haltestellen h, unnest(h.linien) AS l
WHERE ST_DWithin(h.geom::geography, a.geom::geography, 500)) AS linien_500m,
ST_Buffer(a.geom::geography, 500)::geometry(Polygon, 4326) AS geom
FROM adressen a
WHERE a.geom IS NOT NULL;
-- 3) Luftlinie von jeder Adresse zur nächsten U-Bahn-Station
DROP VIEW IF EXISTS v_naechste_ubahn;
CREATE VIEW v_naechste_ubahn AS
SELECT a.id, a.adresse, s.name AS station, s.linien,
round(ST_Distance(a.geom::geography, s.geom::geography)) AS meter,
ST_MakeLine(a.geom, s.geom)::geometry(LineString, 4326) AS geom
FROM adressen a
CROSS JOIN LATERAL (
SELECT name, linien, geom FROM v_stationen
WHERE ubahn
ORDER BY geom <-> a.geom
LIMIT 1
) s
WHERE a.geom IS NOT NULL;
unnest(linien) löst das Array auf, array_agg(DISTINCT …) sammelt die Linien aller Haltepunkte einer Station ohne Doppelte. bool_or(ltyp = 4) ist wahr, sobald ein Haltepunkt eine U-Bahn ist; das CASE wählt das „höchste“ Verkehrsmittel als Kategorie. Die Haltepunkte einer Station liegen höchstens einige hundert Meter auseinander, der Schwerpunkt ist also ein brauchbarer Stationsmittelpunkt.
cd ~/haltestellen psql "postgresql://gisuser:gis123@localhost:5432/hs" -f 04_views.sql
✅ Test:
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT f_table_name, type, srid FROM geometry_columns WHERE f_table_name LIKE 'v\\_%';" psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT kategorie, count(*) FROM v_stationen GROUP BY 1 ORDER BY 2 DESC;" psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT adresse, station, meter FROM v_naechste_ubahn ORDER BY id;"
f_table_name | type | srid
--------------------+------------+------
v_adressen_umkreis | POLYGON | 4326
v_naechste_ubahn | LINESTRING | 4326
v_stationen | POINT | 4326
kategorie | count
-------------+-------
Bus | 2935
Straßenbahn | 477
S-Bahn | 120
U-Bahn | 99
Badner Bahn | 16
adresse | station | meter
--------------------------+---------------+-------
Spengergasse 20, Wien | Pilgramgasse | 829
Stephansplatz 1, Wien | Stephansplatz | 120
Schloss Schönbrunn, Wien | Schönbrunn | 597
Praterstern, Wien | Praterstern | 117
Aus 10.428 Haltepunkten werden 3.647 Stationen, 99 davon mit U-Bahn. Von der Spengergasse ist die Pilgramgasse (U4) die nächste, 829 m Luftlinie.
Abfrage: die nächsten 10 Stationen zu einer Adresse
Mit adressen (Schritt 10) und v_stationen lässt sich die KNN-Suche aus Schritt 7 auf Stationen statt Haltepunkte anwenden – sonst erscheint „Fendigasse“ zweimal:
SELECT s.name, s.kategorie, s.linien,
round(ST_Distance(s.geom::geography, a.geom::geography)) AS meter
FROM adressen a
CROSS JOIN LATERAL (
SELECT * FROM v_stationen ORDER BY geom <-> a.geom LIMIT 20 -- Vorauswahl über den Index
) s
WHERE a.adresse = 'Spengergasse 20, Wien'
ORDER BY meter
LIMIT 10;
✅ Test:
name | kategorie | linien | meter ------------------------------------------+-----------+---------------+------- Spengergasse | Bus | 14A | 112 Siebenbrunnengasse (Ramperstorffergasse) | Bus | 12A, 14A | 193 Fendigasse | Bus | 12A, 14A | 198 Bacherplatz | Bus | 12A, 14A, 59A | 316 Reinprechtsdorfer Straße/Arbeitergasse | Bus | 12A, 14A, 59A | 329 Scalagasse | Bus | 14A | 372 Laurenzgasse | Bus | N62 | 419 Bacherplatz (Margaretenstraße) | Bus | 59A | 429 Laurenzgasse (Tiefgeschoß) | Bus | 1, 62, WLB | 432 Siebenbrunnenfeldgasse | Bus | 12A | 473
ORDER BY geom <-> a.geom LIMIT 20 liest über den GiST-Index nur die nächsten Zeilen statt alle 3.647 zu sortieren. Warum 20 und nicht 10? <-> misst in Grad, und ein Längengrad ist in Wien um ein Drittel kürzer als ein Breitengrad: Die Index-Reihenfolge ist nach Osten und Westen „zu optimistisch“. Mit LIMIT 10 direkt im Index fehlen die Laurenzgasse (419 m) und ihr Tiefgeschoß (432 m), dafür stehen Stationen mit 550 und 559 m in der Liste. Deshalb: mehr Kandidaten holen, nach echten Metern sortieren, dann abschneiden. In Schritt 12 wird daraus eine Funktion.
QGIS installieren
QGIS Installation Guide – Langzeitversion 3.44 LTR oder aktuelles 4.x, beides funktioniert.
| System | Installation | Direkter Download (qgis.org) |
|---|---|---|
| Ubuntu / WSL | Debian/Ubuntu (eigenes APT-Repository). Unter WSL ist die Windows-Version einfacher; sie verbindet sich über 127.0.0.1 mit der Datenbank in WSL (W5). | – |
| Windows | Windows | QGIS-OSGeo4W-3.44.15-1.msi (579 MB) |
| macOS | macOS | qgis_ltr_final-3_44_15.dmg (1,5 GB) |
Fertiges Projekt öffnen
Die Projektdatei haltestellen.qgz (in haltestellen-skripte-v2.zip) enthält alle Layer in Gruppen (Erreichbarkeit, Funktionen, Analyse, Stadt Wien (OGD), Rohdaten; nur die erste ist anfangs eingeblendet), Symbole und Beschriftungen und verbindet sich mit 127.0.0.1:5432, Datenbank hs, Benutzer gisuser, Passwort gis123 (Schritt 3 unverändert vorausgesetzt). Herunterladen, in QGIS Projekt → Öffnen:
✅ Test:
Erwartet: Alle Stationen Wiens als Punkte, fünf orange Kreise und rote Linien zur nächsten U-Bahn auf der OSM-Karte. Fragt QGIS nach einem Passwort, stimmt gis123 nicht mehr. Liegen die Punkte im Jemen, sind Länge und Breite vertauscht (Schritt 6 bzw. 10).
Was QGIS an die Datenbank schickt
Im Projekt steht kein SQL. Jeder Layer kennt nur Tabelle, Geometriespalte und Schlüssel (table="public"."v_stationen" (geom) key='id'); der PostgreSQL-Provider von QGIS baut daraus bei jedem Zeichnen, Klicken oder Filtern ein SELECT. Mitgeschnitten auf dem PostgreSQL-Server – beim Zeichnen nur die Spalten für Symbol und Beschriftung und nur der sichtbare Ausschnitt (&& ist der Index-Operator „Bounding-Box überlappt“, hier zahlt sich der GiST-Index aus Schritt 6 aus):
BEGIN READ ONLY;
DECLARE qgis_1 BINARY CURSOR FOR
SELECT st_asbinary("geom",'NDR'), "id", "kategorie"::text, "name"::text
FROM "public"."v_stationen"
WHERE ("geom" && st_makeenvelope(16.29, 48.1675, 16.42, 48.2325, 4326))
AND ("kategorie" IN ('U-Bahn','S-Bahn','Straßenbahn','Badner Bahn','Bus'));
FETCH FORWARD 2000 FROM qgis_1;
Beim Identifizieren eines Objekts alle Spalten, aber nur eine Zeile:
SELECT st_asbinary("geom",'NDR'), "id", "name"::text, "haltepunkte", "linien"::text, …
FROM "public"."v_stationen"
WHERE ("name" = 'Spengergasse');
Beim Öffnen des Projekts gehen zuvor Metadaten-Abfragen über die Leitung: postgis_version(), Rechteprüfungen (has_table_privilege) und bei Views SELECT count(distinct "id") = count("id") FROM "public"."v_stationen" – deshalb braucht jede View die eindeutige Spalte id.
Wo man das in QGIS sieht:
- Ansicht → Bedienfelder → Entwicklungswerkzeuge, Reiter Abfrageprotokoll: jedes
SELECTlive mit Laufzeit, sobald man die Karte verschiebt. - Layer-Eigenschaften → Information: die Datenquelle, also das, was QGIS über den Layer kennt.
- Layer-Eigenschaften → Quelle → Objektfilter: eine eigene
WHERE-Bedingung (z. B.ubahn), die QGIS an seine Abfragen anhängt. - Datenbank → DB-Verwaltung → SQL-Fenster: eigenes SQL, etwa die Abfrage der nächsten 10 Stationen, mit Als neuen Layer laden direkt auf die Karte – für Abfragen, die nicht als View in der Datenbank liegen sollen.
Serverseitig zeigt das PostgreSQL-Log dieselben Abfragen:
# als Superuser: alle Statements von gisuser protokollieren psql postgres -c "ALTER ROLE gisuser SET log_statement = 'all';" # … in QGIS Karte verschieben, dann: tail -f /var/log/postgresql/postgresql-18-main.log # Ubuntu / WSL tail -f /opt/homebrew/var/log/postgresql@18.log # macOS (Homebrew) # wieder abschalten psql postgres -c "ALTER ROLE gisuser RESET log_statement;"
✅ Test:
Abfrageprotokoll öffnen, Karte verschieben.
Erwartet: eine neue Zeile SELECT … FROM "public"."v_stationen" WHERE ("geom" && st_makeenvelope(…)) pro Layer und Bewegung; dieselben Zeilen im PostgreSQL-Log mit LOG: statement: BEGIN READ ONLY;DECLARE qgis_… BINARY CURSOR FOR SELECT ….
Von Hand: Datenbank verbinden und Views laden
Zwei Dinge vorweg: Die Verbindungsliste in der Datenquellenverwaltung ist leer, auch wenn haltestellen.qgz offen ist – gespeicherte Verbindungen sind QGIS-Einstellungen am Rechner, keine Projektdaten; die Projekt-Layer tragen ihre Verbindungsdaten selbst mit. Und der Eintrag PostgreSQL (Elefant) steht in der linken Liste der Datenquellenverwaltung zwischen den Dateiformaten (GeoPackage, SpatiaLite) und den Web-Diensten; derselbe Dialog ist im Browser-Panel unter PostgreSQL → Rechtsklick → Neue Verbindung erreichbar.
- Layer → Datenquellenverwaltung (Strg+L) → PostgreSQL → Neu: Name
hs, Host127.0.0.1, Port5432, Datenbankhs, unter Authentifizierung → Basic Benutzergisuser, Passwortgis123. Verbindung testen → OK. - Verbinden: Im Schema
publicerscheinen Tabellen und Views mit Geometrietyp. Bei den Views in der Spalte Objekt-IDidwählen; ohne Schlüssel kann QGIS keine Objekte auswählen oder identifizieren. - Die drei Views markieren → Hinzufügen. Reihenfolge im Layer-Panel von oben:
v_naechste_ubahn,v_stationen,v_adressen_umkreis. - Hintergrund: im Browser-Panel unter XYZ Tiles auf OpenStreetMap doppelklicken, Layer nach unten ziehen.
v_stationengestalten: Eigenschaften → Symbolisierung → Kategorisiert, Wertkategorie, Klassifizieren; Bus klein (1,5), U-Bahn groß (4). Beschriftungen:name, maßstabsabhängig ab 1:10.000.v_adressen_umkreis: Deckkraft 30 %, Beschriftungadresse || '\n' || stationen_500m || ' Stationen'.v_naechste_ubahn: gestrichelt, Beschriftungstation || ' (' || meter || ' m)'.
Live: Adresse ergänzen, Karte aktualisieren
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "INSERT INTO adressen (adresse) VALUES ('Karlsplatz, Wien');"
./geocode.sh
✅ Test:
In QGIS F5 drücken.
Erwartet: Ein Kreis am Karlsplatz (22 Stationen im Umkreis) und eine 90 m kurze Linie zur U-Bahn-Station Karlsplatz. Die Views rechnen bei jeder Anzeige neu.
Probe aufs Exempel: Rechts unten auf das Projekt-KBS klicken und EPSG:4326 wählen – die Kreise werden zu Ellipsen. Mit EPSG:3857 oder EPSG:31256 sind es wieder Kreise. Das Messwerkzeug misst ellipsoidisch, also wie geography, unabhängig vom angezeigten KBS.
Schritt 12: Funktionen, Analyse-Views und Daten der Stadt Wien
Alles, was QGIS anzeigt, kommt über Port 5432 aus der Datenbank. Neben Tabellen und Views geht das auch mit Funktionen: QGIS kann eine beliebige SELECT-Abfrage als Layer laden (Query-Layer), also auch SELECT * FROM meine_funktion('Parameter'). Die Logik – und die Parameter – stehen dann im SQL, nicht in QGIS.
Funktion: die nächsten n Stationen zu einer Adresse
naechste_stationen(adresse, n) nimmt den Adressnamen aus Schritt 10 und liefert die n nächsten Stationen mit Rang, Entfernung und zwei Geometrien: den Stationspunkt und die Luftlinie von der Adresse dorthin. Die zweite Funktion stationen_im_umkreis(lon, lat, meter) braucht keine gespeicherte Adresse, sondern eine Koordinate. Dazu zwei Analyse-Views: v_linien (Einzugsgebiet jeder Linie als gepufferte konvexe Hülle um ihre Haltepunkte) und v_dichte (Stationen je 500-m-Sechseck, gerechnet in EPSG:31256, weil ein Sechseckgitter nur in einem metrischen System gleich große Zellen hat). Datei 06_funktionen.sql:
-- Funktionen und Views, die QGIS direkt über Port 5432 abfragt
-- 1) Die nächsten n Stationen zu einer gespeicherten Adresse (Name wie in Tabelle adressen)
CREATE OR REPLACE FUNCTION naechste_stationen(p_adresse text, p_n integer DEFAULT 10)
RETURNS TABLE (
rang integer,
station text,
kategorie text,
linien text,
meter integer,
geom geometry(Point, 4326), -- die Station
verbindung geometry(LineString, 4326) -- Luftlinie Adresse → Station
)
LANGUAGE sql STABLE AS $$
SELECT row_number() OVER (ORDER BY s.meter)::integer AS rang,
s.name, s.kategorie, s.linien, s.meter, s.geom,
ST_MakeLine(a.geom, s.geom)::geometry(LineString, 4326)
FROM adressen a
CROSS JOIN LATERAL (
SELECT name, kategorie, linien, geom,
round(ST_Distance(geom::geography, a.geom::geography))::integer AS meter
FROM v_stationen
ORDER BY geom <-> a.geom -- KNN über den Index (in Grad, nur Vorauswahl) ...
LIMIT p_n * 2 -- ... deshalb doppelt so viele holen
) s
WHERE a.adresse ILIKE p_adresse -- 'Spengergasse%' reicht
AND a.geom IS NOT NULL
ORDER BY s.meter -- ... und nach echten Metern sortieren
LIMIT p_n;
$$;
-- 2) Stationen im Umkreis einer beliebigen Koordinate (Länge, Breite, Radius in m)
CREATE OR REPLACE FUNCTION stationen_im_umkreis(p_lon double precision, p_lat double precision, p_meter integer DEFAULT 500)
RETURNS TABLE (id integer, station text, kategorie text, linien text, meter integer, geom geometry(Point, 4326))
LANGUAGE sql STABLE AS $$
SELECT s.id, s.name, s.kategorie, s.linien,
round(ST_Distance(s.geom::geography, p::geography))::integer,
s.geom
FROM v_stationen s,
(SELECT ST_SetSRID(ST_MakePoint(p_lon, p_lat), 4326) AS p) q
WHERE ST_DWithin(s.geom::geography, p::geography, p_meter)
ORDER BY 5;
$$;
-- 3) Einzugsgebiet je Linie: konvexe Hülle um alle Haltepunkte, 50 m gepuffert (immer ein Polygon)
DROP VIEW IF EXISTS v_linien;
CREATE VIEW v_linien AS
SELECT row_number() OVER (ORDER BY linie)::integer AS id,
linie,
count(*) AS haltepunkte,
count(DISTINCT name) AS stationen,
min(ltyp) AS ltyp,
ST_Buffer(ST_ConvexHull(ST_Collect(geom))::geography, 50)::geometry(Polygon, 4326) AS geom
FROM haltestellen, unnest(linien) AS linie
GROUP BY linie;
-- 4) Versorgungsdichte: Stationen je 500-m-Sechseck (für eine Heatmap in QGIS)
DROP VIEW IF EXISTS v_dichte;
CREATE VIEW v_dichte AS
WITH hex AS (
SELECT (ST_HexagonGrid(500, ST_Transform(ST_SetSRID(ST_Extent(geom), 4326), 31256))).*
FROM haltestellen
)
SELECT row_number() OVER ()::integer AS id,
count(s.id) AS stationen,
count(s.id) FILTER (WHERE s.ubahn) AS ubahn,
ST_Transform(hex.geom, 4326)::geometry(Polygon, 4326) AS geom
FROM hex
JOIN v_stationen s ON ST_Contains(hex.geom, ST_Transform(s.geom, 31256))
GROUP BY hex.geom;
Der Trick in naechste_stationen: ORDER BY geom <-> a.geom sortiert über den Index, aber in Grad – und ein Grad Länge ist in Wien kürzer als ein Grad Breite. Die Index-Reihenfolge weicht deshalb von der Meter-Reihenfolge leicht ab. Die Funktion holt darum die doppelte Kandidatenzahl über den Index und sortiert dann nach echten Metern.
cd ~/haltestellen psql "postgresql://gisuser:gis123@localhost:5432/hs" -f 06_funktionen.sql
✅ Test:
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT rang, station, kategorie, linien, meter FROM naechste_stationen('Spengergasse%');"
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT count(*) FROM stationen_im_umkreis(16.35717, 48.18560, 500);"
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT linie, stationen FROM v_linien WHERE linie ~ '^U[1-6]$' ORDER BY linie;"
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT count(*) AS zellen, max(stationen) FROM v_dichte;"
rang | station | kategorie | linien | meter
------+------------------------------------------+-----------+---------------+-------
1 | Spengergasse | Bus | 14A | 112
2 | Siebenbrunnengasse (Ramperstorffergasse) | Bus | 12A, 14A | 193
3 | Fendigasse | Bus | 12A, 14A | 198
4 | Bacherplatz | Bus | 12A, 14A, 59A | 316
5 | Reinprechtsdorfer Straße/Arbeitergasse | Bus | 12A, 14A, 59A | 329
6 | Scalagasse | Bus | 14A | 372
7 | Laurenzgasse | Bus | N62 | 419
8 | Bacherplatz (Margaretenstraße) | Bus | 59A | 429
9 | Laurenzgasse (Tiefgeschoß) | Bus | 1, 62, WLB | 432
10 | Siebenbrunnenfeldgasse | Bus | 12A | 473
count: 12 U1 23 · U2 21 · U3 20 · U4 19 · U6 22 Stationen zellen: 1070, max: 24
Funktionen als Query-Layer in QGIS
Im Menü Datenbank → DB-Verwaltung die Verbindung hs öffnen, SQL-Fenster (F2) und eingeben:
SELECT * FROM naechste_stationen('Spengergasse%', 10);
Ausführen zeigt die Tabelle. Darunter „Als neuen Layer laden“ ankreuzen, Spalte mit eindeutigen Werten rang, Geometriespalte geom (oder verbindung für die Luftlinien), Laden. QGIS speichert dann als Datenquelle des Layers nicht eine Tabelle, sondern das SELECT in Klammern:
table="(SELECT * FROM naechste_stationen('Spengergasse%', 10))" (geom) key='rang'
Das steht so auch in haltestellen.qgz (Gruppe Funktionen). Anderen Parameter? Rechtsklick auf den Layer → Datenquelle ändern, Spengergasse% durch Karlsplatz% ersetzen – oder in der DB-Verwaltung neu laden.
✅ Test:
Erwartet: Zehn nummerierte Punkte um die Spengergasse mit Luftlinien zum Stern der Adresse. Im Abfrageprotokoll (Schritt 11) erscheint bei jedem Zeichnen SELECT … FROM (SELECT * FROM naechste_stationen(…)) WHERE ("geom" && st_makeenvelope(…)) – QGIS filtert auch Funktionsergebnisse nach dem Kartenausschnitt.
Daten der Stadt Wien als zusätzliche Layer
Derselbe WFS liefert hunderte weitere Layer (GetCapabilities listet sie). Vier davon passen zu den Haltestellen; wien_layer.sh lädt sie als GeoJSON und 05_wien_layer.sql importiert sie über eine jsonb-Staging-Tabelle mit jsonb_array_elements und ST_GeomFromGeoJSON – alles Datenquelle: Stadt Wien – data.wien.gv.at, CC BY 4.0:
| WFS-Layer | Tabelle | Geometrie | Zeilen | Attribute |
|---|---|---|---|---|
| BEZIRKSGRENZEOGD | wien_bezirke | MultiPolygon | 23 | bezirk, label (I.–XXIII.), flaeche_m2 |
| OEFFLINIENOGD | wien_linien | MultiLineString | 4.919 | linien, linien_arr (text[]), ltyp (gleiche Codes wie haltestellen), ltyptxt |
| SCHULEOGD | wien_schulen | Point | 806 | name, adresse, bezirk, art_txt, skz |
| FAHRRADABSTELLANLAGEOGD | wien_radabstell | Point | 8.511 | bezirk, adresse, anzahl (Stellplätze), art_txt |
wien_linien ist das Gegenstück zu unseren Haltepunkten: die Linienführung als Geometrie, mit ltyptxt als Klartext der LTYP-Codes (1 Straßenbahn, 2 Stadtbus, 3 Regionalbus, 4 U-Bahn, 5 S-Bahn/Zug, 6 Badner Bahn, 10–13 Nacht- und Rufbus) – das bestätigt die Zuordnung vom Anfang. Zwei Views verknüpfen die neuen Tabellen mit den Haltestellen (Teil von 05_wien_layer.sql):
-- Haltepunkte je Bezirk (Choroplethenkarte)
CREATE VIEW v_wien_bezirk_stationen AS
SELECT b.id, b.bezirk, b.label,
count(h.id) AS haltepunkte,
count(DISTINCT h.name) AS stationen,
count(DISTINCT h.name) FILTER (WHERE h.ltyp = 4) AS ubahn_stationen,
round(b.flaeche_m2 / 1e6, 2) AS km2,
round(count(h.id) / (b.flaeche_m2 / 1e6), 1) AS haltepunkte_pro_km2,
(SELECT count(*) FROM wien_schulen s WHERE ST_Contains(b.geom, s.geom)) AS schulen,
(SELECT coalesce(sum(anzahl), 0) FROM wien_radabstell r WHERE ST_Contains(b.geom, r.geom)) AS radstellplaetze,
b.geom::geometry(MultiPolygon, 4326) AS geom
FROM wien_bezirke b
LEFT JOIN haltestellen h ON ST_Contains(b.geom, h.geom)
GROUP BY b.id, b.bezirk, b.label, b.flaeche_m2, b.geom;
-- Jede Schule und ihr nächster Haltepunkt (Luftlinie)
CREATE VIEW v_wien_schule_station AS
SELECT s.id, s.name AS schule, s.art_txt, s.adresse,
h.name AS station, array_to_string(h.linien, ', ') AS linien,
round(ST_Distance(s.geom::geography, h.geom::geography)) AS meter,
ST_MakeLine(s.geom, h.geom)::geometry(LineString, 4326) AS geom
FROM wien_schulen s
CROSS JOIN LATERAL (SELECT name, linien, geom FROM haltestellen ORDER BY geom <-> s.geom LIMIT 1) h;
cd ~/haltestellen chmod +x wien_layer.sh ./wien_layer.sh # Download + Import; "./wien_layer.sh --no-dl" importiert vorhandene JSON-Dateien
✅ Test:
psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT f_table_name, type FROM geometry_columns WHERE f_table_name LIKE 'wien%' OR f_table_name LIKE 'v_wien%' ORDER BY 1;" psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT bezirk, label, haltepunkte, stationen, ubahn_stationen, km2, haltepunkte_pro_km2, schulen FROM v_wien_bezirk_stationen ORDER BY haltepunkte_pro_km2 DESC LIMIT 3;" psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT round(avg(meter)) AS schnitt, max(meter) AS maximum FROM v_wien_schule_station;"
wien_bezirke MULTIPOLYGON · wien_linien MULTILINESTRING · wien_radabstell POINT · wien_schulen POINT
v_wien_bezirk_stationen MULTIPOLYGON · v_wien_schule_station LINESTRING
bezirk | label | haltepunkte | stationen | ubahn_stationen | km2 | haltepunkte_pro_km2 | schulen
--------------+-------+-------------+-----------+-----------------+------+---------------------+---------
Neubau | VII. | 151 | 35 | 5 | 1.61 | 93.9 | 20
Innere Stadt | I. | 204 | 72 | 7 | 2.87 | 71.1 | 15
Alsergrund | IX. | 178 | 46 | 7 | 2.97 | 60.0 | 21
schnitt: 135 maximum: 607
Neubau hat die höchste Haltepunktdichte Wiens; eine Wiener Schule ist im Schnitt 135 m, höchstens 607 m vom nächsten Haltepunkt entfernt. Im Projekt liegen diese Layer in der Gruppe Stadt Wien (OGD): Bezirke mit Dichte-Beschriftung, U-Bahn- und Straßenbahnlinien (aus wien_linien, gefiltert über ltyp), Schulen mit Luftlinie zur Haltestelle, Fahrradabstellanlagen.
Zum Weiterbauen: wien_linien.linien_arr und haltestellen.linien sind beide text[] – WHERE linien_arr @> ARRAY['U4'] liefert die Linienführung der U4, ST_DWithin zwischen Schulen und Linien die Schulen entlang einer Linie, und wien_radabstell gegen v_stationen die Stationen ohne Radabstellplatz im Umkreis.
Alle Dateien
haltestellen-skripte-v2.zip enthält 01_staging.sql bis 06_funktionen.sql, hs_import.sh, geocode.sh, wien_layer.sh, haltestellen.qgz und eine README mit der Reihenfolge. Nach ~/haltestellen entpacken; die Daten holen die Skripte selbst (CSV-Kopie siehe Schritt 4).
Windows: das Ganze mit WSL 2
Unter Windows läuft PostgreSQL im Windows-Subsystem für Linux (WSL 2), einer von Windows verwalteten Linux-VM mit Ubuntu 24.04. Schritt 1–12 (Ubuntu) gelten darin unverändert; hier steht nur, was unter Windows zusätzlich zu tun ist, und wie pgAdmin oder QGIS unter Windows die Datenbank in WSL erreichen.
- Alles in WSL: PostgreSQL, PostGIS,
curl,jq,psqlund die Skripte laufen in Ubuntu; sie verbinden sich mitlocalhost:5432. - Windows 11 (22H2+): Netzwerkmodus
mirrored; Windows und WSL teilen sich127.0.0.1. Windows 10: Standardmodus NAT mit localhost-Forwarding, funktioniert in der Regel ebenfalls über127.0.0.1:5432. - An der PostgreSQL-Konfiguration ändert sich im Normalfall nichts. Das Projekt liegt im Linux-Dateisystem unter
~/haltestellen, nicht unter/mnt/c/….
In den Codeblöcken steht, wo ein Befehl läuft: # PowerShell (Windows) oder # in WSL (Ubuntu).
W1: WSL 2 und Ubuntu 24.04 installieren
Voraussetzung: Windows 10 ab Build 19041 oder Windows 11, Virtualisierung im BIOS/UEFI aktiv. In einer PowerShell als Administrator:
# PowerShell (als Administrator) wsl --install -d Ubuntu-24.04 # danach Windows neu starten, falls verlangt wsl --update
Nach dem Neustart fragt Ubuntu nach einem Linux-Benutzernamen und Passwort; das Passwort braucht man für sudo.
✅ Test:
# PowerShell wsl --version wsl -l -v
Erwartet: WSL-Version 2.x; Ubuntu-24.04 mit VERSION 2. Wird wsl --version nicht erkannt → wsl --update. Steht VERSION 1 → wsl --set-version Ubuntu-24.04 2.
W2: systemd aktivieren und Netzwerkmodus setzen
Damit PostgreSQL als Dienst mit der VM startet, muss in WSL systemd laufen. Prüfen mit cat /etc/wsl.conf; fehlt [boot] systemd=true, ergänzen:
# in WSL (Ubuntu) sudo tee -a /etc/wsl.conf > /dev/null <<'EOF' [boot] systemd=true EOF
Netzwerkmodus (nur Windows 11 22H2+): in der Windows-Datei %UserProfile%\.wslconfig, die anfangs nicht existiert. Beim Speichern mit Notepad „Alle Dateien“ wählen, damit die Datei nicht .wslconfig.txt heißt.
# PowerShell – öffnet bzw. erzeugt die Datei notepad "$env:USERPROFILE\.wslconfig"
# Inhalt von %UserProfile%\.wslconfig (nur Windows 11 22H2+) [wsl2] networkingMode=mirrored
Beide Änderungen wirken erst nach einem vollständigen Neustart der VM:
# PowerShell wsl --shutdown # ca. 8 Sekunden warten, dann Ubuntu wieder öffnen wsl -d Ubuntu-24.04
✅ Test:
# in WSL (Ubuntu) ps -p 1 -o comm= systemctl is-system-running wslinfo --networking-mode # nur bei neueren WSL-Versionen
Erwartet: systemd; running oder degraded (in WSL häufig und harmlos); mirrored bzw. nat.
W3: PostgreSQL, PostGIS und die Datenbank in WSL
Schritt 1–3 (Ubuntu) in der WSL-Shell ausführen, samt Tests. Ein Stolperstein: Ein natives Windows-PostgreSQL (z. B. EDB-Installer aus einem früheren Kurs) belegt ebenfalls Port 5432 – im Mirrored-Modus kann das WSL-PostgreSQL den Port dann nicht belegen, im NAT-Modus landen Windows-Programme beim Windows-PostgreSQL. Vorher prüfen:
# PowerShell Get-Service *postgres*
Läuft ein Dienst wie postgresql-x64-16: Stop-Service postgresql-x64-16; Set-Service postgresql-x64-16 -StartupType Manual (als Administrator).
✅ Test:
# in WSL (Ubuntu) pg_lsclusters systemctl is-enabled postgresql sudo ss -ltnp | grep 5432 psql "postgresql://gisuser:gis123@localhost:5432/hs" -c "SELECT postgis_full_version();"
Erwartet: 18 main 5432 online; enabled; 127.0.0.1:5432 und [::1]:5432 mit Prozess postgres; PostGIS-Version mit PROJ="9.x". Nach wsl --shutdown und erneutem Öffnen muss der Cluster wieder online sein, sonst läuft systemd nicht (W2).
W4: Import und Dateien in WSL
Schritt 4–12 (Ubuntu) unverändert in der WSL-Shell. Windows-spezifisch ist nur der Umgang mit Dateien:
- Das Projekt gehört ins Linux-Dateisystem (
~/haltestellen); unter/mnt/c/…ist jeder Zugriff langsam. Im Explorer erreicht man den Ordner über\\wsl.localhost\Ubuntu-24.04\home\<linux-user>\haltestellen;explorer.exe .in WSL öffnet das aktuelle Verzeichnis. - Zeilenenden: Mit einem Windows-Editor angelegte Skripte haben CRLF-Zeilenenden und brechen mit
$'\r': command not foundab. Abhilfe:sudo apt install -y dos2unix,dos2unix *.sh *.sql– oder VS Code mit der Erweiterung „WSL“ (code .in WSL).
✅ Test:
# in WSL (Ubuntu) cd ~/haltestellen && pwd file hs_import.sh 01_staging.sql 02_haltestellen.sql ./hs_import.sh
Erwartet: /home/<linux-user>/haltestellen; file meldet kein CRLF; das Skript endet mit 10428 | 3647 | 4326.
W5: Zugriff von Windows – pgAdmin, QGIS, listen_addresses, pg_hba.conf
pgAdmin oder QGIS unter Windows verbinden sich von außerhalb der VM. Drei Ebenen greifen ineinander: das Netzwerk Windows ↔ WSL, listen_addresses (auf welchen Adressen nimmt PostgreSQL Verbindungen an) und pg_hba.conf (wer darf sich wie anmelden).
a) Der Normalfall: 127.0.0.1
| Feld | Wert | Hinweis |
|---|---|---|
| Host | 127.0.0.1 | Nicht localhost: Windows löst das oft zu ::1 auf, und IPv6-Loopback wird im Mirrored-Modus nicht unterstützt |
| Port | 5432 | |
| Datenbank | hs | |
| Benutzer | gisuser | Nicht postgres: der Superuser hat unter Ubuntu kein Passwort → password authentication failed |
| Passwort | gis123 |
Das braucht keine Änderung an PostgreSQL: Die Verbindung kommt von 127.0.0.1, und dafür steht in der Standard-pg_hba.conf schon host all all 127.0.0.1/32 scram-sha-256.
✅ Test:
# PowerShell
Test-NetConnection 127.0.0.1 -Port 5432
Get-NetTCPConnection -LocalPort 5432 -State Listen -ErrorAction SilentlyContinue |
Select-Object LocalAddress, OwningProcess,
@{n='Prozess'; e={(Get-Process -Id $_.OwningProcess).ProcessName}}
-- im Query Tool von pgAdmin bzw. in der DB-Verwaltung von QGIS SELECT version(), postgis_version(), inet_client_addr(), current_user;
Erwartet: TcpTestSucceeded : True. Erscheint ein Prozess postgres, ist das ein Windows-PostgreSQL, das „gewinnt“. Die Abfrage zeigt … on x86_64-pc-linux-gnu (Linux; ein Windows-PostgreSQL meldet compiled by Visual C++), 127.0.0.1 und gisuser.
b) Nur falls nötig: über die WSL-IP (NAT-Modus)
Klappt 127.0.0.1 im NAT-Modus nicht (z. B. VPN), verbindet man über die IP der VM (wsl hostname -I in PowerShell). Dafür zwei gezielte Änderungen:
# in WSL (Ubuntu) # 1) postgresql.conf: auf allen Adressen lauschen – Plural: listen_addresses sudo sed -i "s/^#\?listen_addresses.*/listen_addresses = '*'/" /etc/postgresql/18/main/postgresql.conf # 2) pg_hba.conf: nur unsere DB, unsere Rolle, das private WSL-Netz, mit Passwort echo "host hs gisuser 172.16.0.0/12 scram-sha-256" | sudo tee -a /etc/postgresql/18/main/pg_hba.conf sudo systemctl restart postgresql # listen_addresses braucht einen Neustart
Nie trust als Methode, nie 0.0.0.0/0 als Adresse. listen_addresses = '*' nur im NAT-Modus; im Mirrored-Modus hat WSL die echten Netzwerkadressen des Rechners und wäre aus dem LAN erreichbar.
✅ Test:
# in WSL (Ubuntu) pg_lsclusters sudo ss -ltnp | grep 5432 sudo -u postgres psql -c "SELECT line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"
Erwartet: Cluster online (bei down: /var/log/postgresql/postgresql-18-main.log, oft ein Tippfehler wie listen_address); 0.0.0.0:5432; die neue Zeile host | {hs} | {gisuser} | 172.16.0.0 | scram-sha-256 mit leerer Spalte error.
| Symptom | Ursache | Lösung |
|---|---|---|
connection refused auf localhost:5432 | WSL-VM läuft nicht, oder localhost → ::1 | Ubuntu-Fenster öffnen; 127.0.0.1 verwenden |
role "gisuser" does not exist | Windows-PostgreSQL auf Port 5432 „gewinnt“ | Get-Service *postgres*, Dienst stoppen |
password authentication failed for user "postgres" | postgres hat kein Passwort | Als gisuser verbinden |
In WSL: Peer authentication failed | psql ohne Host → Unix-Socket → peer | psql -h localhost -U gisuser -d hs bzw. die URL |
no pg_hba.conf entry for host "172.…" | Verbindung über WSL-IP ohne passende Regel | Zeile aus b) ergänzen, systemctl reload postgresql |
type "geometry" does not exist | PostGIS in dieser Datenbank nicht aktiviert | Als Superuser: \c hs, CREATE EXTENSION postgis; |
$'\r': command not found | Windows-Zeilenenden | dos2unix hs_import.sh |
Aufräumen
Als Superuser (Ubuntu: sudo -u postgres psql, macOS: psql postgres):
DROP DATABASE IF EXISTS hs; DROP ROLE IF EXISTS gisuser;
Quellen
- Daten: Öffentliches Verkehrsnetz Haltestellen Wien – Datenquelle: Stadt Wien – data.wien.gv.at, CC BY 4.0
- PostgreSQL: postgresql.org/download, PostGIS: postgis.net/documentation, QGIS: Installation Guide
- Koordinatensysteme: epsg.io/4326, epsg.io/3857, epsg.io/31256, epsg.io/31259; Landes-GIS: NÖ Atlas, DORIS
- Geocodierung: Nominatim Search API, Usage Policy – Daten © OpenStreetMap contributors, ODbL
- Kartendaten der Projektionsabbildung: Natural Earth (gemeinfrei); WSL: Netzwerk in WSL