What you need for pgRouting - at minimum - is a table with edges:
edge_id, from_node, to_node, cost
1, 1, 2, 12.0
2, 2, 3, 57.0
Note that geometries of the edges are not necessary for most algorithms. Only the logical topology has to be created.
If you want to have edge geometries: start from the bus/train stop layer and create one edge for every connection (with snapping to the stops layer enabled). You can chose between assigning from_node and to_node ids manually or using one of the built-in pgRouting functions. For the automated process to work, you have to do a good job while digitizing the geometries.
Depending on how many bus/train lines you are planning to add, it might be advisable to try to automate as much of this process as possible. Is the "sort" attribute included in your bus stop data or did you have to add that manually?
Previous answer:
Since the ends of the two lines you show in the screenshot are very far apart, you would have to set the tolerance in pgr_createTopology really high (http://docs.pgrouting.org/dev/src/common/doc/functions/create_topology.html).
Instead you should pay more attention to digitizing the lines correctly by enabling snapping.
To sort your geo-lookup by distance, you can do
queries = yourmodel.objects.filter(
location__distance_lte=(base_point, D(m=certain_distance))
).distance(base_point).order_by('distance')
Where location
is the fieldname of the first column in your table, base_point
is an instance of django.contrib.gis.geos.Point
Since it is sorted, to get the closest busstop you then just have to get the first element of queries
as list. I mean
closest_busstop = list(queries)[0]
distance_to_closest_busstop = base_point.distance(closest_busstop)
UPDATE
Perhaps an expert will contradict me (and i would be glad), but it will be necessary to "target" the closest busstop from the returned queryset in one way or another. Given that you have a list of base points, something like this should do the job
django.VERSION < 1.10
get_cbusstop = lambda bpoint: list(
yourmodel.objects.filter(
location__distance_lte=(bpoint, D(m=certain_distance))
).distance(bpoint).order_by('distance')
)[0]
django.VERSION > 1.9
from django.contrib.gis.db.models.functions import Distance
get_cbusstops = lambda bpoint: yourmodel.objects.filter(
location__distance_lte=(bpoint, D(m=certain_distance))
).annotate(
distance=Distance('<busstop_shape_fieldname>', bpoint)
)
get_cbusstop = lambda bpoint: sorted(
get_cbusstops(bpoint), key=lambda obj_:obj_.distance
)[0]
For both version
Then, you can declare a list of base points, say base_points_list
, formed over location_1
,...
,location_n
, and do
closest_busstops = [get_cbusstop(bp) for bp in base_points_list]
Best Answer
The first part of the solution is this:
This snaps the busstops to the lines of the road network as you can see in the picture and works quite easy.
Next I will try to split the lines at the locations of the points. After splitting the lines I want to use pgr_createTopology again. After that it should be possible to create a query to find out the nearest nodes to the busstops, which will than be my newly generated nodes at the 'splitpoints'.
I would be grateful if somebody had a hint for me how to split linestring with point features in postgis, since after I looked at similar questions there does not seem to be an easy solution for that at the moment.