Best way to find bus/transport routes from point A to B

General talk
1

Hi, I’m making a web app that uses the OSM database. I want to find buses that take you from point A to B.
The way I currently look for buses is this:


            SELECT pid, ref, name,
            st_asgeojson(st_transform(way, 4326)) as geojson
            FROM planet_osm_line
            WHERE route='bus'
            AND st_dwithin(way, st_transform(st_geomfromewkt(%(p1)s), 3857), %(r)s)
            AND st_dwithin(way, st_transform(st_geomfromewkt(%(p2)s), 3857), %(r)s)
            ORDER BY (st_distance(way, st_transform(st_geomfromewkt(%(p1)s), 3857)) +
            st_distance(way, st_transform(st_geomfromewkt(%(p2)s), 3857))) DESC, pid

where p1 and p2 are the points A and B, and r is the radius of tolerance to walk to the bus stop.
I have some problems with this:

  1. some bus routes are “splitted” into several lines, for example if the bus route makes a circle, the line breaks into two lines and a polygon. This makes me unable to find those routes in my search since the sub-routes may go through either A or B but not both.

  2. I don’t realize how to filter those routes that go from B to A, instead from A to B.

today I realized that osm2pgsql does not import relations by default, so I might me missing that, but I’m pretty lost on how to efficiently find the routes that go from A to B in that direction, and without splitting the route into several lines.

What am I missing here? What would the best way to approach this be?
Thanks!

pd: I’m pretty new to sql dbs but I’m trying my best here.