0
$\begingroup$

I am developing a project that displays a live rendering of bus, when the user clicks on a bus, i would like a linestring representing the bus route to appear/highlight.

I am currently attempting to store Linestring data that plots the route a bus may take on a given trip, however, i am very split on the best way to store this data. This data must be stored in a relational database so i can link the bus route to the bus, but the volume of data is making me question the best storage methodology.

currently, I have JSON data of the following format: fig 1:

[
    {
        "from": "StopA",
        "to": "StopB",
        "polyline": [
            [
                x,
                y
            ],
            [
                x,
                y
            ]
        ]
    },
    {
        "from": "StopB",
        "to": "StopC",
        "polyline": [
            [
                x,
                y
            ],
            [
                x,
                y
            ]
        ]
    }
}]

When writing this to SQLite, I have considered two formats, neither of which seem optimal.

one of which is a new row for each stop for each route. each row is given a unique identifier. example:

|route_id|from_stop|to_stop|polyline|
|Route1_1| Stop_A | Stop_B | (Line) |
|Route1_2| Stop_B | Stop_C | (Line) |

However, there may be hundreds of stops in a full route, and there may be several hundred routes, making this method quite exhaustive.

the second method is storing the entire json in its own column. example:

|route_id|route|
| Route_1| (what is shown in fig 1)|

while this is simpler, it feels poorly structured, and additional parsing may be necessary, especially if the bus route is notably large.

Considering this, would anyone be able to give any guidance on best/better methodologies for this problem. Thanks.

$\endgroup$

0

Browse other questions tagged or ask your own question.