Hi.
For learning purpose i would like to make a small database with a small graph of locations, roads and public transport
information.
Then calculate the fastest or cheapest way between two points.
If we think of a minimal network, as below.
A ---5-- B ---10---- C
\ /
\---------5-----/
To travel from A to C can be done in two ways, either via B at a total cost of 15, or directly at a cost of 5.
Let's now say that there are tow buses, one travelling A-B-C and one travelling A-C.
Lets say the departure schedule from A is:
bus A-B-C:
leaves A: 10:00, arrives C 10:15.
leaves A: 11:00, arrives C 11:15.
bus A-C :
leaves A 10:15, arrives C 10:20.
leaves A 11:05, arrives C 11:10.
To get started somewhere i have a few questions about how to make the data model, etc.
1.
What is a good practice, having the graph represent
a) the roads and bus stops,
b) the graph to represent the bus routes and stops, or
c) having the graph to represent every single bus trip (for each departure)?
b) feels quite natural as the network gets smaller, but how do i then represent each and every tip made on the route?
Eg. the bus departures on the route once per hour.
2.
What are the minimum information i need about the graph?
Nodes (stops), edges (travel way), neighbour list of some sort, and some sort of cost to ride on a edge.
Anything else. Direction information?
3.
Is it possible to write recursive SQL in PostgreSQL without using procedural language? How, are there examples?
4.
How do i handle waiting time from the start point, or arrival time to destination?
As an example i don't want the result to be only trips with A-C (as this is cheepest path),
instead i want both options because sometimes A-B-C will bring me to the destination earlier
then A-C, and sometimes it is good to wait for A-C even though there is A-B-C departuring earlier.
if time is 09:55 -> take A-B-C. (obvious).
if time is 10:01 -> take A-C. (obvious).
if time is 10:55 -> take A-C. (not obvious).
5.
Last question, are theree any books covering topics like this?
Thank you.
karl