How to design graph data model for journey planner in public transport?

[Martin_Pogadl]

Hello guys, I am totally newbie in graph databases, I saw several tutorials and I asked couple of days ago for help with cypher query. Then I realized that my data model is not optimal, and I should design a new one by myself for my needs. Right now I am using PostgreSQL for storing data, I came out from GTFS standard, and customized it a little bit. I added platform_number, runday and distance from last stop to stop_times table.

Here is my ERD:

Why I need a Graph database?

1. I want to search for indirect connections between 2 stops with a transfer node.
   a) shortest path based on time (fastest)
   b) shortest path based on a distance between stations

2. In SQL database I am able to find only a direct connection of one trip. No transfers, and performance is a little bit slower in bigger amount of data.

I found some work of Bruggen where he loaded data into neo4j (link), but to write optimal query over this model for getting outputs is complicated for me. So I think to write a translator for my data if I found an optimal graph data model with your help.

Model created by Bruggen, which I tried:

Output wanted from Graph Data model:
a) Find the shortest path between 2 stations which don't have a direct connection by train.
b) The Shortest path is possible to count by travel time and distance between these stations.
c) The Query will return me Station A (from) Station T (transfer) Station B (to) and trip_id of connection between these stations.

Thanks a lot for your help.
~Martin

[roberto1]

Hi I think you are in the right way, but the problem is that you are only thinking in the model that you have, remember that some of the beauties of neo4j is that you can travers the graph knowing the starting point and the ending point if there is a path neo4j is going to show you that.

may be this video could help you https://www.youtube.com/watch?v=eAbPgyouAE4&t=2s