Zusammenhängende Autobahnknoten aus einer OSM Datenbank extrahieren

Hallo zusammen,

und zwar habe ich eine PostgreSQL Datenbank mit der gesamten Topologie des deutschen Autobahn-/Strassennetzes. Es handelt sich dabei um den Inhalt der Datei “germany.highway.osm.bz” von cloudmade.com, welchen ich mittels Osmosis in die DB geladen habe. Nun möchte ich mir alle Knoten von Autobahnausfahrten (motorway junctions) bestimmter Autobahnen ausgeben lassen, wobei die Reihenfolge der Ausfahrtkoordinaten beibehalten werden soll. Zwei Koordinatenpunkte (jeweils bestehend aus Längen-/Breitengrad) sollen quasi eine Autobahnteilstrecke ergeben für die ich dann die Distanz berechnen kann. Mir ist nicht ganz klar wie die Daten in der DB hinterlegt und miteinander verknüpft sind, so daß ich nicht genau weiß wie die SQL Anfrage aussehen soll. Versucht habe ich folgende Query, bin mir aber nicht sicher ob das stimmt und wie ich die Ausgabe interpretieren soll.

SELECT * FROM nodes n,ways w,way_nodes wn,relations r, relation_members rm
WHERE r.id = rm.relation_id AND rm.member_id = w.id AND n.id = wn.node_id AND w.id = wn.way_id
AND w.tags::hstore → ‘highway’ = ‘motorway’ AND w.tags::hstore → ‘ref’ = ‘A 1’
ORDER BY rm.sequence_id, wn.way_id, wn.sequence_id;

Dabei gehe ich davon aus das die Spalte “sequence_id” die Reihenfolge der Knoten eines Wegs ist. Liege ich richtig in der Annahme? Dann wäre noch die Frage was genau ein Way ist, d.h. wieviele Ways hat beispielsweise die Autobahn A4 und wie lasse ich mir diese dann mit geordneter Sequenznummer ausgeben? Eine weitere Unklarheit besteht in der Codierung der Geokoordinaten. Mit welcher Funktion wandle ich den Geometry-Datentyp, welcher in hexadezimaler Form vorliegt, in Dezimalgrad um?
Hoffe ich habe mich einigermaßen verständlich ausgedrückt. Bin für jede Hilfe dankbar.

Gruß
Mario

Also das ist die A4: http://www.openstreetmap.org/browse/relation/48147
Und die besteht bei mir aus über 2000 Wegstückchen, den Rest kann ich Dir nicht sagen weil ich das Datenbank-Schema von osm2pgsql verwende. Ich habs nicht weiter probiert, aber ich würde die Stückchen von PostGIS zusammensetzen lassen:
http://postgis.refractions.net/docs/ch08.html#PostGIS_Aggregate_Functions
Und dann hängts davon ab was Du damit machen willst. Die Koordinaten kannste Dir dann mit transform() oder askml() aus der Datenbank rausziehen. Mit irgendwelche Sequence-IDs hab ich mich so direkt nie beschäftigen müssen.

Leider NEIN! Die sequence_id ist die Reihenfolge, in der die Members innerhalb der Relation angeortnet sind. Das hat nicht mit deren physikalischen Anordnung/Verknüpfung zu tun.

Ways haben member_type = ‘W’ in der DB. Die musst du schon selber “zusammenpfriemeln”.
Das ist einfach, wenn alle ways einen gemeinsamen Weg von Anfang bis Ende bilden; bei Verzweigungen (Y) geht das eben nicht.

Ich mache sowas mit SQL-Abfragen geschrieben in PL/pgsql http://www.postgresql.org/files/documentation/books/pghandbuch/html/plpgsql.html
da hat man alle Möglichkeiten von progresql verbunden mit postgis plus eine einfache Programmiersprache - was will man mehr.

kann ich nichts zu sagen.

Gruss
Walter

Das ist der Preis dafür, dass du eine DB nach dem Simple/Snapshot-Schema aufgesetzt hast. (genau wie ich)

Die Mehrzahl der Kollegen aus dem “Renderer-Umfeld” benutzen osm2pgsql, da dieses Programm einem die “Pfriemelarbeit” abnimmt.
Die haben dafür dann aber andere Probleme, die man mit einer osmosis-basierenden DB mit “links” erledigt hat.
Es gibt sogar 1-2 Kollegen, die haben beides - je nach Anwendung

Die Geometrien kann man so sehen, hier z. B. als Text

select osm_id, st_astext(way) from planet_osm_roads where ref='A 4';

Ich hab mal grad probiert, aus irgendwelchen Gründen klappt das st_linemerge nicht, da kommt immer eine Multiline raus, vermutlich weil ich in meiner Datenbank die Reihenfolge der Stückchen nicht abgreifen kann, da müsste wohl vorsortieren.

das ist aber das osm2pgsql-Schema - das passt nicht zu Marios Fragestellung/Umfeld.

Aber ich nehme an, dass es daran liegt, dass die Ways der A4 eben kein einzelner Linestring werden können, da es sich um mehrere Strecken handelt. Das fängt schon bei mehrspurigen Strassen an. Da hat osm2pgsql auch keine Chance.
“Schnapp” dir eine ganz einfache unverzweigte einspurige Strasse und dein Beispiel könnte klappen. Aber nur da.

Gruss
walter

Hallo und danke euch beiden erst einmal für die Antworten.

Welche Möglichkeiten hätte ich denn um mir die korrekte physikalische Anordnung der Autobahnen anzeigen zu lassen? Man müßte sich das doch irgendwie vorsortiert aus der Datenbank ziehen können!? Ich habe mittlerweile herausgefunden das man mit der funktion bbox() die geometry daten in dezimalgrad transformieren kann. Wenn ich das auf auf die “linestring” - Spalte der ways-Tabelle anwende, erhalte ich jeweils Koordinaten - Päarchen. Beispielsweise gehören die Koordinaten (7.1293957,50.9502245), (7.1273371,50.9500677) zu einer Ausfahrt mit dem Namen Bensberg der Autobahn A4. Ein linestring stellt somit bereits eine Teilstrecke dar. Demnach müssten doch alle Ways zusammengenommen die gesamte Autobahnstrecke bilden oder liege ich da falsch? Wenn dem so ist dann hat man bestimmt auch daran gedacht die Teilstrecken sortiert zu speichern.Somit wäre einer Sortierung der Members innerhalb der Relation auch eine sortierte Ausgabe aller Teilstrecken. Andernfalls verstehe ich den Sinn der sequence_id nicht.

Inwiefern kann mir osm2pgsql bei meinem Problem weiterhelfen? Wo sind die Unterschiede zu dem snapshot-Schema.

Gruß
Mario

Das ist richtig.

Nein, die sind absolut unsortiert. Man kann sie natürlich im Osm-Editor JOSM sortieren, aber das bringt nichts:

a) die “Sortierung” ist nicht permanent, sie kann und wird sich jederzeit ändern.
b) Es gibt keine Sortierung von Wegen einer Strecke, die sich verzweigt (Y). Mal dir das mal auf ein Stück Papier und schau dir das ganz genau an.
Ein Y wird im allerbesten Fall aus 2 Strecken bestehen, die in sich verbunden sind, aber es sind IMMER zwei Stecken. Das geht topologisch nicht anders.

Der Sinn ist mir auch nicht ersichtlich.

Der macht vieles bereits beim Import nach SQL - Aber auch osm2pgsql kann aus einer Straße, die sich gabelt, kein komplettes Stück machen; es werden immer mehrere Teilstücke sein. Bei dem Y sind das 2 oder 3, so genau kenn ich osm2pgsql nicht. Probier es hal aus oder frag die Leute, die osm2pgsql verwenden.
Spätestens dann, wenn die Stecke noch in verschiedene Blöcke mit unterschiedlichen Eigenschaften aufgeteilt sind (z.B. maxspeed) ist eh Schluss mit Lustig.
Dann musst du die Stücke selber zusammenbasteln.

gruss
Walter

p.s. ich gehe bei der ganzen Diskussion davon aus, dass du wirklich komplette Strecken brauchst. Beim Rendern oder beim Rouen braucht man die aber garnicht.
Was willst du denn eigentlich machen?

Hallo Walter, Mario

Der Sinn der Sequence-ID ist es, die Member einer Relation in der Reihenfolge auszugeben, in der sie nach der letzten Änderung waren. Das hat bei Autobahnen, die ja meist aus getrennten Fahrbahnen und damit getrennten Wegen in OSM bestehen solange keine Bedeutung, wie die Autobahn-Relationen nicht richungsmäßig getrennt sind.

ÖPNV-Routen werden zunehmend als Routen je Richtung erfasst (zumindest in Deutschland). Da hat man dann einen durchgehenden Weg je Richtung. Und damit macht a) eine Sortierung Sinn und b) ist es wichtig, die Sortierung innerhalb der Relation beizubehalten. Wege kann man in diesem Fall im Relationen-Editor von JOSM sortieren lassen. Haltestellen muss man jedoch weiterhin per Hand in die gewünschte Reihenfolge bringen.

Aber Walter hat Recht, wenn er sagt, dass die Reihenfolge bei Ergänzungen nicht zwingend beachtet wird. Es ist eine Frage der intelligente Programmierung in den Editoren, ob es normalerweise klappt oder nur zufällig, wenn man z.B. einen Weg auftrennen muss. (Werden Member per Hand ergänzt, ist natürlich das Wissen und die Intelligenz des Mappers gefragt.)

PS: Nicht jeder Relationstyp hat überhaupt eine ‘natürliche’ Sortierung.
Bei denen ohne ist das Beibehalten der Member-Reihenfolge vor allem Komfort.

Edbert (EvanE)