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 …

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

Auf jeden Fall brauchst du** postgresq 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

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

Ist auch vorhanden.

Und genau bei diesem Punkt brauche ich Hilfe.

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

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

Christian

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…

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. :slight_smile:

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

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

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. :slight_smile:

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.

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

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

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

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.

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!

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. :slight_smile:

Christian