Datenbankabfrage für ein Straßenverzeichnis

Zwar keine konkrete Lösung für deine Frage, aber:

wie macht denn Maposmatic dies, wenn es ein Straßenverzeichnis erstellt? ist ja opensource ...

brogo wrote:

Leider kenne ich mich nicht mit SQL aus. Ich denke man müßte irgendwie die Funktion ST_WITHIN oder ST_INTERSECTS nutzen. Die Ausgabe sollte neben dem Raster-Segment auch den Namen und vielleicht noch weitere Tags (z.B. amenity) enthalten.

Wie müßte eine solche Abfrage lauten? Bzw. man braucht ja wohl zwei, eine für die Punkte und eine für die Wege.

nicht ganz einfach zu beantworten - gerade wenn dein know-how nicht das Beste sein sollte.

Auf jeden Fall brauchst dupostgresq mit postGIS - ohne postgis geht hier garnix.
Dann müsstet du ein Grid (Gitter) erzeugen und das dann bei der Abfrage (Welche Strassen sind in diesem Rechteck?) verwenden. mit st_within und st_intersect bist du auf jeden Fall auf dem richtigen Weg

Nur: gemacht hab ich das auch noch nicht.

Gruss
walter

Alle Straßen in einem rechteckigen Bereich abzufragen, ließe sich leicht via Overpass-Api erreichen.
Die Abfrage würde direkt gegen den OSM-Datenbestand laufen und ein XML-Ergebnis erzeugen.
Würde dir so etwas helfen ?

Gruß Klaus

Wie ähnlich ist denn eine osm2pgsql-Datenbank dem osmosis-Schema? Falls die Straßen als "LINESTRING" und Punkte als "POINT" als in einer Spalte von Typ geometry liegen, kann ich helfen. Ich will aber nicht verwirren, falls osmosis-Datenbanken ganz anders aufgebaut sind...

Mit einer DB im osm2pgsql-Schema und postgis würde das ungefähr so gehen, vielleicht hilfts:

osm=>␣select␣distinct(name)␣from␣osm_line␣where␣highway␣is␣not␣null␣and␣name␣is␣not␣null␣and␣ST_PointOnSurface(way)␣&&␣ST_SetSRID(ST_MakeBox2D(ST_Point(1287326,6132767),ST_Point(1287893,6133242)),900913);
name
---------------------
Adelheidstraße
Agnesstraße
Georgenstraße
Hiltenspergerstraße
Josephsplatz
Schwarzmannstraße
Tengstraße
(7␣rows)

• 1287326,6132767 und 1287893,6133242 sind die beiden Ecken meines Ausschnitts, in Mercatorprojektion (900913). Das wird bei Dir vielleicht anders sein, dann einfach Gradangaben verwenden und 4326 statt 900913.
• Statt "Mittelpunkt" habe ich "irgendeinen Punkt" genommen (ST_PointOnSurface).
• "ST_SetSRID(ST_MakeBox2D(ST_Point(" hab ich aus der postgis-Doku abgeschrieben.
• "&&" heisst "liegt in dieser Bounding Box".
• "distinct" war nötig, weil viele Straßen als mehrere Stücke vorkommen.
• "way" ist bei mir die Spalte mit der Geometrie.

Grüße, Max

Moin!

setz dich mal mit Wolfgang in Verbindung - der hat gestern erzählt das er etwas bei der Lübecker Radwegekarte gemacht hat.

Gruß Jan

wambacher wrote:

Auf jeden Fall brauchst dupostgresq mit postGIS - ohne postgis geht hier garnix.

Das habe ich. Ich sprach ja bereits von einer DB mit osmosis-snapshot-Schema.

wambacher wrote:

Dann müsstet du ein Grid (Gitter) erzeugen [...]

Ist auch vorhanden.

wambacher wrote:

[...] und das dann bei der Abfrage (Welche Strassen sind in diesem Rechteck?) verwenden. mit st_within und st_intersect bist du auf jeden Fall auf dem richtigen Weg

Und genau bei diesem Punkt brauche ich Hilfe.

maxbe wrote:

Wie ähnlich ist denn eine osm2pgsql-Datenbank dem osmosis-Schema? Falls die Straßen als "LINESTRING" und Punkte als "POINT" als in einer Spalte von Typ geometry liegen, kann ich helfen. Ich will aber nicht verwirren, falls osmosis-Datenbanken ganz anders aufgebaut sind...

Es gibt folgende Tabellen:

osm_sh=#␣␣\d
Liste␣der␣Relationen
Schema␣|␣␣␣␣␣␣␣Name␣␣␣␣␣␣␣␣|␣␣␣Typ␣␣␣|␣Eigent³mer
--------+-------------------+---------+------------
public␣|␣geography_columns␣|␣Sicht␣␣␣|␣postgres
public␣|␣geometry_columns␣␣|␣Sicht␣␣␣|␣postgres
public␣|␣nodes␣␣␣␣␣␣␣␣␣␣␣␣␣|␣Tabelle␣|␣postgres
public␣|␣relation_members␣␣|␣Tabelle␣|␣postgres
public␣|␣relations␣␣␣␣␣␣␣␣␣|␣Tabelle␣|␣postgres
public␣|␣schema_info␣␣␣␣␣␣␣|␣Tabelle␣|␣postgres
public␣|␣spatial_ref_sys␣␣␣|␣Tabelle␣|␣postgres
public␣|␣users␣␣␣␣␣␣␣␣␣␣␣␣␣|␣Tabelle␣|␣postgres
public␣|␣way_nodes␣␣␣␣␣␣␣␣␣|␣Tabelle␣|␣postgres
public␣|␣ways␣␣␣␣␣␣␣␣␣␣␣␣␣␣|␣Tabelle␣|␣postgres
(10␣Zeilen)

Die Tabelle 'nodes' sieht so aus:

osm_sh=#␣\d␣nodes
Tabelle␣+public.nodes½
Spalte␣␣␣␣|␣␣␣␣␣␣␣␣␣␣␣␣␣Typ␣␣␣␣␣␣␣␣␣␣␣␣␣|␣Attribute
--------------+-----------------------------+-----------
id␣␣␣␣␣␣␣␣␣␣␣|␣bigint␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|␣not␣null
version␣␣␣␣␣␣|␣integer␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|␣not␣null
user_id␣␣␣␣␣␣|␣integer␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|␣not␣null
tstamp␣␣␣␣␣␣␣|␣timestamp␣without␣time␣zone␣|␣not␣null
changeset_id␣|␣bigint␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|␣not␣null
tags␣␣␣␣␣␣␣␣␣|␣hstore␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|
geom␣␣␣␣␣␣␣␣␣|␣geometry(Point,4326)␣␣␣␣␣␣␣␣|
Indexe:
"pk_nodes"␣PRIMARY␣KEY,␣btree␣(id)
"idx_nodes_geom"␣gist␣(geom)

Die Tabelle 'ways' sieht so aus:

osm_sh=#␣\d␣ways
Tabelle␣+public.ways½
Spalte␣␣␣␣|␣␣␣␣␣␣␣␣␣␣␣␣␣Typ␣␣␣␣␣␣␣␣␣␣␣␣␣|␣Attribute
--------------+-----------------------------+-----------
id␣␣␣␣␣␣␣␣␣␣␣|␣bigint␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|␣not␣null
version␣␣␣␣␣␣|␣integer␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|␣not␣null
user_id␣␣␣␣␣␣|␣integer␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|␣not␣null
tstamp␣␣␣␣␣␣␣|␣timestamp␣without␣time␣zone␣|␣not␣null
changeset_id␣|␣bigint␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|␣not␣null
tags␣␣␣␣␣␣␣␣␣|␣hstore␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|
nodes␣␣␣␣␣␣␣␣|␣bigint[]␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣␣|
linestring␣␣␣|␣geometry(Geometry,4326)␣␣␣␣␣|
bbox␣␣␣␣␣␣␣␣␣|␣geometry(Geometry,4326)␣␣␣␣␣|
Indexe:
"pk_ways"␣PRIMARY␣KEY,␣btree␣(id)
"idx_ways_bbox"␣gist␣(bbox)
"idx_ways_linestring"␣gist␣(linestring)

Die beiden letzten Spalten 'linestring' und 'bbox' sind bei diesem Schema optional. Ich habe sie mal hinzugefügt. Ansonsten müßte man ja die Geometrie über 'way_nodes' und 'nodes' holen, was sicherlich aufwendiger ist.

Du hast in Deiner Abfrage eine Boundbox manuell abgefragt. Ich würde ja gerne auf das bestehende Raster zurückgreifen und die Abfrage auch umgekehrt machen. Also nachfragen in welchen Raster der abgefragte Weg liegt.

Pseudocode:

wähle␣Weg␣wo␣highway=*␣und␣name␣nicht␣NULL␣ist
bilde␣Mittelpunkt␣von␣highway
prüfe␣in␣welchem␣Bereich␣mit␣Tag␣raster=*␣der␣Mittelpunkt␣liegt
gebe␣raster-Value␣und␣name␣von␣highway␣aus

Christian

Liegt Dein Raster in Form von einzelnen Strichen für X- und Y-Achse vor oder in Form von Kästchen um die "Planquadratfläche A3"

Max

maxbe wrote:

Liegt Dein Raster in Form von einzelnen Strichen für X- und Y-Achse vor oder in Form von Kästchen um die "Planquadratfläche A3"

Ich habe Kästchen erstellt, also geschlossene Ways. Ich dachte, das wäre so einfacher, als mehrere Linien abzufragen.

Christian

brogo wrote:

Ich habe Kästchen erstellt, also geschlossene Ways. Ich dachte, das wäre so einfacher, als mehrere Linien abzufragen.

Dann ist vermutlich "linestring && ST_MakePolygon(kästchen);" der richtige Ausdruck fürs Ende der Abfrage.

Übrigens würde ich mir wenig Mühe mit Mittelpunkt vom Weg suchen geben. Es wäre Zufall, wenn ein Weg in einem Stück über mehrere Rasterkästchen geht. Du wirst viel öfter Stücke eines Wrges in mehreren FEldern finden...

brogo wrote:

und die Abfrage auch umgekehrt machen. Also nachfragen in welchen Raster der abgefragte Weg liegt.

Ich verwende Abfragen, in welcher Gemeinde ein Punkt liegt, die z.B. sehen so aus:

select␣osm_id,name␣from␣osm_polygon␣where␣admin_level='8'␣and␣st_contains(way,(select␣way␣from␣osm_point␣where␣osm_id=1353897948));

Du müsstest statt "select way from osm_point..." halt was passenderes einsetzen, statt "from osm_polygon" irgendwas, was Dein Gitter beschreibt. Und bei "st_contains(way,...." wäre "st_contains(ST_MakePolygon(way),...." einzusetzen, weil Dein Gitter ja noch keine Fläche ist.

also irgendwie so (ungeprüft):

SELECT␣id,tags␣FROM␣ways␣WHERE␣xxx␣AND␣␣St_Contains(ST_MakePolygon(linestring),(SELECT␣ST_PointOnSurface(linestring)␣FROM␣ways␣WHERE␣id=12345678));

mit "xxx"=irgendeiner Abfrage, die Gitter von den anderen Objekten in "ways" unterscheidet.

Grüße, Max

Also ich habe es jetzt hinbekommen. :-)

Ich habe das Gitter aber vorher in eine separate Tabelle 'grid' kopiert. Das hat auch den Vorteil, daß man die OSM-Daten einfach aktualisieren kann und man nicht auf Konflikte mit den IDs achten muß.
Die Abfrage sieht dann so aus:

SELECT␣DISTINCT␣w.tags::hstore␣->␣'name'␣as␣strassenname,␣g.tags::hstore␣->␣'name'␣as␣feld
FROM␣ways␣␣w,␣grid␣g
WHERE␣ST_CENTROID(w.linestring)␣&&␣g.linestring␣AND␣(w.tags::hstore␣?␣'name'␣)␣;

Das Ganze bedarf dann natürlich noch manueller Nacharbeit. So gibt es bei uns z.B. in mehreren Dörfern Straßen mit gleichen Namen.

Um Punkte abzufragen sähe die Abfrage so aus:

SELECT␣DISTINCT␣n.tags::hstore␣->␣'amenity'␣as␣amenity,␣n.tags::hstore␣->␣'name'␣as␣name,␣g.tags::hstore␣->␣'name'␣as␣feld
FROM␣nodes␣n,␣grid␣g
WHERE␣ST_CENTROID(n.geom)␣&&␣g.linestring␣AND␣(n.tags::hstore␣?␣'amenity'␣)␣;

Christian

brogo wrote:

Also ich habe es jetzt hinbekommen. :-)

Eine Warnung vor st_centroid muss ich noch loswerden: Das gibt den Schwerpunkt zurück. Der liegt sicher neben der Straße und bei stark gekrümmten Straßen könnte der ziemlich auch weit weg liegen, vielleicht sogar in einem Gitterfeld, das die Straße gar nicht berührt...

Grüße, Max

maxbe wrote:

brogo wrote:

Also ich habe es jetzt hinbekommen. :-)

Eine Warnung vor st_centroid muss ich noch loswerden: Das gibt den Schwerpunkt zurück. Der liegt sicher neben der Straße und bei stark gekrümmten Straßen könnte der ziemlich auch weit weg liegen, vielleicht sogar in einem Gitterfeld, das die Straße gar nicht berührt...

Grüße, Max

stimmt mit dem centroid, der kann schon mal mächtig daneben liegen. Bei dem osmosis / snapshot-Schema, das die wenigsten von euch einsetzten, gibt es für jeden way auch gleich die passende bbox. Das ist die Box, die genau um den Weg herum liegt. Damit geht eine Abfrage der Art "ist ein Stück des Weges in diesem Quadranten?" rasend schnell, weil nur ganz wenige Werte verglichen werden müssen. Ob es das bei osm2pgsql gibt, ist mir nicht bekannt; schaut mal nach.

Gruss
walter

brogo wrote:

Ich denke für Wege ist es sinnvoll vorher einen Mittelpunkt zu ermitteln und diesen zu benutzen. Somit sollte man für jedes Objekt genau ein Raster-Segment erhalten.

Sollte alternativ auch mit osmconvert gut klappen: Optionen "--all-to-node" und "--csv=".

Wege (und Relationen) werden dann zu Knoten umgewandelt. Die Koordinaten dieser Weg-Ersatzknoten liegen nicht im Zentrum des jeweiligen Wegs, sondern auf einem der Wegpunkte, so dass die Zuordnung noch sinnvoll möglich ist. Sollte jedenfalls so sein, habs schon lang nicht mehr probiert. :-)

Die CSV-Option erzeugt am Ende eine Liste, die sich praktisch überall wieder importieren – oder wie hier gefordert – in ein Raster überführen lässt.

P.S.: osmconvert arbeitet nicht auf der Basis der Datenbank, sondern auf der Basis der OSM-Datei. Das heißt, man sollte den Schritt vor dem Import durchziehen.

maxbe wrote:

Eine Warnung vor st_centroid muss ich noch loswerden: Das gibt den Schwerpunkt zurück.

OK, also nimmt man besser, wie auch in Deinem Bespiel ST_PointOnSurface. Dieser Punkt liegt dann zwar auf der Linie, kann aber irgendwo sein, in der Mitte irgendwo an den Enden oder sonstwo, richtig? Kann man das beeinflussen? Oder ich lasse das ganz weg, wie Du ja schon vorgeschlagen hast.

Eine weitere Frage habe ich noch, die mir wahrscheinlich am ehesten noch Walter mit dem gleichen Schema beantworten kann:

Ich habe eine weiter Abfrage gemacht die mir die linestrings der Gemeindegrenzen liefert:

SELECT␣w.linestring␣from␣relations␣r,␣relation_members␣rm,␣ways␣w
WHERE␣r.id␣=␣382443␣AND␣r.id␣=␣rm.relation_id
AND␣rm.member_id␣=␣w.id
ORDER␣BY␣rm.sequence_id
;

Wie kann ich diese Linestrings weiternutzen, um eine nächste Abfrage zu starten? Es gibt ja den Befehl ST_MakePolygon, den maxbe auch schon genutz hat, aber die Syntax ist mir nicht klar.

Christian

brogo wrote:

OK, also nimmt man besser, wie auch in Deinem Bespiel ST_PointOnSurface. Dieser Punkt liegt dann zwar auf der Linie, kann aber irgendwo sein, in der Mitte irgendwo an den Enden oder sonstwo, richtig? Kann man das beeinflussen?

Jup, der PointOnSurface liegt irgendwo auf dem Strich. Zwar nicht zufällig bei jeder Berechnung wo anders, aber auch nicht vorhersehbar. Vielleicht wäre ST_Line_Interpolate_Point(w.linestring,0.5) das richtige, um den Mittelpunkt einer Straße zu erfahren. Ausprobiert hab ichs noch nicht.

Grüße, Max

brogo wrote:

Wie kann ich diese Linestrings weiternutzen, um eine nächste Abfrage zu starten? Es gibt ja den Befehl ST_MakePolygon, den maxbe auch schon genutz hat, aber die Syntax ist mir nicht klar.

ich mach das etwa so:

select␣ST_Multi(ST_BuildArea(ST_Union(linestring)))␣geom
from␣(
SELECT␣w.linestring
from␣relations␣r,␣relation_members␣rm,␣ways␣w
WHERE␣r.id␣=␣382443
AND␣r.id␣=␣rm.relation_id
AND␣rm.member_id␣=␣w.id
)␣www
;

d.h. um "dein Select" kommt ein ST_Union, das die Linestrings zusammenfasst.
ob du da nun eventuell auch ein makePolygon nimmst, bleibt dir überlassen. so klappt es jedenfalls bei mir.

der sort ist übrigens hier unnötig. Achtung: nicht getestet, hab es bei mir rauskopiert und an deine Query angepasst.

Gruss
Walter

wambacher wrote:

d.h. um "dein Select" kommt ein ST_Union, das die Linestrings zusammenfasst.
ob du da nun eventuell auch ein makePolygon nimmst, bleibt dir überlassen. so klappt es jedenfalls bei mir.

Funktioniert. Vielen Dank!

Noch zum Verständis: Was soll "geom" in der ersten Zeile? Ohne dieses funktioniert die Abfrage auch. "www" am Ende ist dann ein Alias, oder? Ich kann ihn durch alles unbelegte ersetzen.

wambacher wrote:

der sort ist übrigens hier unnötig.

OK, ich dachte es wäre besser, damit die Wege in der richtigen Reihenfolge vorliegen.

Ich habe übrigens für meine erste Abfrage unüberlegt "&&" übernommen.

Das kann ich hier aber nicht anwenden, denn wie ich festgestellt habe, fragt "&&" die Bounding-Box ab und liefert zu viele Ergebnisse, wenn ich z.B. Place-Nodes abfrage. Ich habe nun ST_INTERSECT genommen, damit klappt es prima.

Ich glaube, langsam verstehe ich das und es beginnt Spaß zu machen.

Christian

Gratuliere!

brogo wrote:

Noch zum Verständnis: Was soll "geom" in der ersten Zeile? Ohne dieses funktioniert die Abfrage auch. "www" am Ende ist dann ein Alias, oder? Ich kann ihn durch alles unbelegte ersetzen.

das ist der "Name" des Ergebnisses. auch ein alias.

lass geom mal weg und schau dir die Überschrift der Ausgabe an. Da sollte dann was anderes stehen.
border, grenzpolygon oder area wäre eventuell aussagekräftiger.
Manchmal kannst du die weglassen.

Gruss
walter

Hallo!

Noch mal ich.

Nun stehe ich vor der Aufgabe, herauszufinden, in welchem Postleitzahlenbereich eine Straße liegt. Dafür wollte ich zunächst eine VIEW mit den Bereichen erstellen, die den Linestring und die PLZ enthält.

Über eine Abfrage wie diese bekomme ja schon mal den Linestring für einen Bereich.

select␣ST_Multi(ST_BuildArea(ST_Union(linestring)))␣geom
from␣(SELECT␣w.linestring
FROM␣relations␣r
JOIN␣relation_members␣rm␣on␣r.id␣=␣rm.relation_id
JOIN␣ways␣w␣on␣rm.member_id␣=␣w.id
WHERE␣r.tags::hstore␣->␣'postal_code'␣=␣'23684'
)␣www
;

Ich würde ja ganz gerne das für alle erstellen, also mit "WHERE r.tags::hstore ? 'postal_code'". Irgendwie müßte ich das Ergebnis ja gruppieren, so daß VOR dem Zusammenfügen immer nur ein PLZ-Bereich ausgewähtl wird. Weiteres Problem ist, daß ich so nicht an den PLZ-Tag rankomme, da ich im SELECT ja nur den Linestring aufnehmen kann.

Oder denke ich schon wieder in die falsche Richtung?

Christian

So, mit einigen Umwegen habe ich das hinbekommen.

Ich habe zwei VIEWS erstellt. Die erste enthält alle Linestrings und die PLZ

CREATE␣OR␣REPLACE␣VIEW␣plz␣AS
SELECT␣w.linestring,␣r.tags::hstore␣->␣'postal_code'␣AS␣plz_nr
FROM␣relations␣r
JOIN␣relation_members␣rm␣ON␣r.id␣=␣rm.relation_id
JOIN␣ways␣w␣ON␣rm.member_id␣=␣w.id
WHERE␣r.tags::hstore␣?␣'postal_code't
;

Mit der zweiten habe ich die Linestrings dann zusammengeführt.

CREATE␣OR␣REPLACE␣VIEW␣plz_areas␣AS
SELECT␣ST_Multi(ST_BuildArea(ST_Union(linestring)))␣plz_geom,␣plz_nr
FROM␣plz
GROUP␣BY␣plz_nr
;

Dann erhalte ich schließlich mit der folgenden Abfrage alle Straßen aus der Gemeinde mit der Relations-ID 382443, zuzüglich der Felder im Raster (habe ich vorher selbst erstellt und in die Tabelle grid importiert) und die dazugehörige Postleitzahl:

SELECT␣DISTINCT␣w.tags::hstore␣->␣'name'␣as␣strassenname,␣w.tags::hstore
->␣'highway'␣as␣typ,␣g.tags::hstore␣->␣'name'␣as␣feld,␣pa.plz_nr
FROM␣ways␣␣w,␣grid␣g,␣plz_areas␣pa
WHERE␣(w.linestring)␣&&␣g.linestring␣AND␣(w.tags::hstore␣?␣'highway'␣)
AND␣(w.tags::hstore␣?␣'name'␣)
AND␣ST_Intersects(
(select␣ST_Multi(ST_BuildArea(ST_Union(linestring)))␣geom
from␣(SELECT␣w.linestring
FROM␣relations␣r
JOIN␣relation_members␣rm␣on␣r.id␣=␣rm.relation_id
JOIN␣ways␣w␣on␣rm.member_id␣=␣w.id
WHERE␣r.id␣=␣382443
ORDER␣BY␣rm.sequence_id
)␣www␣),w.linestring)
AND␣ST_Intersects
(pa.plz_geom,w.linestring)
order␣by␣strassenname,␣feld,␣pa.plz.nr
;

Das ist doch schon eine gute Basis, um ein Straßenverzeichnis zu erstellen. :-)

Christian