7

I currently designing a table/document database structure and use GeoJson to store points and routes. Currently I choose MongoDb, I will consider PostGIS later.

I want to store route info for public bus. The route contain 2 sub routes going back and forth in circular within the same route number. The route info contain related bus stops and waypoints.

The structure should be based on how I can query back.

So my use case is I want to query and get the whole route info and also the current sub route based on current location.

Which one is suitable to query from db if I structure like:

  1. Use MultiLineString to store the 2 LineStrings consist of bus stops and waypoints. The other info store in different column/property. Problem: How to query or differentiate sub routes.
  2. Separate the sub routes in another table/document or use different column/property in a table/document (multiple geospartial indexes). Problem: Duplicate bus stops and waypoints and getting the whole route info. Probably need multiple db queries.
  3. Use Feature in GeoJson. However I can't imagine how FeatureCollection with multiple geometries can help to query my use case above.
1

I am going to try to substitute some terms because you are using a similar word to describe two distinct ideas. Let me know if this is wrong.


What I propose is going to be closest to you #2 solution:

In your data have a bus route that is made of one or many leg(s). We cannot have a route without at least one leg. The route doesn't need to be a MultiLineFeature or even a spatial primitive, the job of the route is just to hold administrative information about itself and doesn't necessarily know anything about its leg(s).

A leg knows which route (foreign key) it belongs to. If your legs are pre-baked, line features then they can be your smallest needed unit. If your legs are not prebaked then they can hold just information about the leg itself and need not to be a spatial primitive, can refer to waypoint to determine their shape.

Finally, there are waypoints, and only waypoints. Stations become waypoints. Why? There is really no difference between a waypoint and a station other than one gets stopped at, to load and unload passengers, that is information that can be stored in the entry for the waypoint (eg. a field called is_station with a value 1 if the bus needs to stop, 0 if doesn't stop). If waypoints are your smallest unit then you can update routing without having to create new line features, just update your leg/waypoint association table with new waypoints.

I personally would go the way of making waypoints my smallest unit, because it gives the greatest flexibility.

I would also make a many-to-many join collection/table. Because a waypoint/station may also serve more than one leg, and a leg will have to have more than one waypoint. Additionally, provide a field to give waypoints an order, that way the direction of travel can be known.

Schema:

+----------+       +--------+     +-------------+
|          |       |        |     |             |
|  ROUTES  +-1---N-+  LEGS  |     |  WAYPOINTS  |
|          |       |        |     |             |
+----------+       +----+---+     +---------+---+
                        |                   |
                        1                   1
                        |                   |
                        N                   N
                        |                   |
                   +----+-------------------+-----+
                   |   LEG/WAYPOINT ASSOCIATION   |
                   |                              |
                   +------------------------------+

Query:

Many public transit systems only pick people up at stations, so I would use a spatial query to find the nearest waypoint to my location that is is_station=1.

Once my station has been selected, than I perform a query that looks at a leg/waypoint association collection to perform a many-to-many join. This will tell me which legs service this station.

The legs, in turn, will know which routes they serve and therefore you can know everything you need to know about your routes. If a user is looking at your information it is up to them to decide which route they prefer, or for you to ask more information to show the optimal route.

  • Thanks for sharing. I went to the same way, solution #2. Now I got routes, points and waypoints collections. routes store route.stops a sorted array of bus stop Id from points collection. I can then index and maintain unique and query nearby bus stop. waypoints collection store documents with (break into) a single Point location together with routeId so that I can query nearby route. I use Point instead of MultiPoint or LineString to get a single point distance and coordinate during $geoNear aggregate. – CallMeLaNN Aug 19 '16 at 6:29
  • Great! Trying to fill in our huge unanswered backlog where I feel ever remotely qualified to help. – RomaH Aug 19 '16 at 12:56

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.