0

I'd like to find the public transportation lines (bus, tramways) that operate on a particular road.

  • Do you know any tool doing that?
  • If not, do you know any algorithm doing that?

The only generic way I found is to replicate what "query feature" (arrow) in OSM does: on a specific lat/lng, draw a circle and find everything in (nodes, ways, etc).

1

I see you marked your post with the "postgis" and "osm2pgsql" tags, so I am assuming you are using these tools.

If so, and you run a system with the latest v1.5.1 of osm2pgsql supporting the new "flex output" option of osm2pgsql, then there is the work of Paul Norman on the draft version of the flex Lua style file of 'openstreetmap-carto'.

That style contains a new table definition ("planet_osm_route") that likely exactly contains the information you need:

  • Note: the "planet_osm_route" table is a non-spatial table
  • It contains multiple records for each route, with a column "member_id" that refers back to the OSM ID of e.g. "planet_osm_line" line, and can be used to join it with that. This will allow you to visualize selected routes spatially.
  • You will need to re-import your data using osm2pgsql and the flex option of osm2pgsql to get access to this new table (and a few more, e.g. a new "planet_osm_transport_line" table).
  • Also note that I currently have one issue open with the "planet_osm_transport_line" table, where some road line elements are missing (https://github.com/gravitystorm/openstreetmap-carto/pull/4492). Note: the "planet_osm_line" table isn't affected by this issue.

enter image description here

By the way, there are probably two more edits to the flex Lua style file of 'openstreetmap-carto' you will likely want to make:

  • By default, the style will split very long line elements in 100k "Web Mercator meters" max. This leads to duplicate OSM IDs in the 'planet_osm_line', 'planet_osm_roads' and 'planet_osm_transport_line' tables, and may be undesirable based on your use case. Note that the default splitting takes place because otherwise there may be negative consequences for rendering speed of extremely long lines (however, I think this issue is limited in real life). If you want to prevent the splitting, remove the 'split_at = 100000' clauses as visible in the image below:

enter image description here

  • Removing splitting requires another thing though: if you remove splitting, osm2pgsql may create a mixture of "Simple Features" "LineString" and "MultiLineString" geometries. The latter cannot be stored in the default "LineString" type column generated by the style. To solve this, replace the 'linestring' in the image below by the key word 'geometry' to create a generic "Geometry" type PostGIS column that can store both SF LineString and MultiLineString geometries.

enter image description here

3
  • @roland, I added some extra information to my original post that you may like to read.
    – Marco_B
    Nov 19 '21 at 16:48
  • Thanks for your help. I created the DB again using the flex option. In planet_osm_route, I want to link route as "road" and route as "tram". My objective is to "find tram that belong to road XYZ". Is it actually possible? I see no way to map member_id to osm_id
    – roland
    Nov 19 '21 at 19:40
  • How do you mean you cannot "map member_id to osm_id". As I wrote, one thing you may wish to do is to remove the 'split_at' option to ensure you have unique osm_id's in 'planet_osm_line'. I have had no problem joining these tables. I will post an example with screenshot in another answer below.
    – Marco_B
    Nov 19 '21 at 20:26
0

See the images below showing the join between the two osm2pgsql generated tables that shows all road elements from 'planet_osm_line' belonging to route with osm_id=18 in the 'planet_osm_route' table:

enter image description here

enter image description here

4
  • Thanks. It's working if the transportation lines (tram, bus) are on the road itself (e.g. openstreetmap.org/way/27861247). The query runs fine and returns the 4 relations. A different case (openstreetmap.org/way/14374075) : the transportation lines are not on the road but between the one-way roads. In this case, relations refers to a bag of entries but no transportation lines. Any idea on how to solve this one?
    – roland
    Nov 20 '21 at 12:38
  • 1
    No direct solution. Of course, you could buffer the routes and try to catch "the most probable" road(s) along which the route section runs by selecting the longest intersections between the route buffers and roads, but it won't be as elegant and precise as the result from the JOIN I showed. You will likely need to put a minimum length > buffer width (e.g. buffer width 10 meters, minimum road length 50 meters) to weed out crud of intersecting roads.
    – Marco_B
    Nov 20 '21 at 17:23
  • 1
    Instead of the suggested creation of true buffers, which is a rather expensive operation, using something like PostGIS's 'ST_DWithin' function to find nearby roads, may be more appropriate: postgis.net/docs/ST_DWithin.html
    – Marco_B
    Nov 20 '21 at 18:06
  • indeed, this is a similar approach I had in mind for special cases. Thanks for your help. Cheers!
    – roland
    Nov 21 '21 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.