GeoJSON from Relations

I've been working on a couple of small projects and both need overlays on maps. Using the lovely LeafletJS library overlays as points, lines and polygons are easy if your overlay comes as GeoJSON. I often store the data in a database and generate the GeoJSON on the fly to download to the overlay using Ajax. The issue sometimes can be where to get the data from to create the overlay and how then to store it in the database in the best way to create the dynamic download.

I have written some bespoke scripts for each situation so far so I thought I'd see how easy it would be be to write something a bit more general. My first thought was how to make a GeoJSON file that describes a route from a relation. Often route relations are in OSM data but they don't render on the Standard render. These routes might be commuter or tourist cycle routes, long-distance walks, tourist walks in a city, bus routes and so on. A series of overlays would then allow these to be selectively displayed.

To create a simple GeoJSON overlay for a route I could create a LineString feature. That needs a series of points with latitude and longitude for each point in the correct order to draw the whole route. These come from the nodes in the ways in the relation. The nodes in each way can be listed in either direction along the way, depending on how it was drawn. The nodes that make the join between the ways will appear in the two joining ways.

Another way would be to create a MultiLineString feature. This could be made of each way that makes up the relation, so each point in each LineString is simply a node along the corresponding way. The duplicate nodes that join two ways just make the two LineStrings join up. The direction each LineString is drawn does not matter to the way it looks. Using these for simple overlays would also allow only some of the LineStrings to be used for a limited area. This is simpler, but it might not be what is sometimes needed. One big disadvantage is that if the overlay is drawn with a transparent line, where two lines overlap at the joining node the doubled-up more opaque colour will be obvious.

The ideal route relation would only have ways in it. If there are nodes in the relation they can easily be ignored, but there is the case of super relations, where a long or complex route relation might be made of other, smaller relations. I'm going to ignore that for now. The ideal route relation would have a series of ways listed in the relation so that each follows the other correctly. This is where the whole process breaks down most often. Ways in relations are often out of sequence. Often one ways leads directly into the end of another, but sometimes that is not so. A tee junction in OSM is entirely normal, but when a route takes the tee, the way may not be broken at the junction so a spur continues past the junction. These really ought to be fixed, but in reality not all have been. In city centres with a variety of bus routes and cycles routes the streets can get broken into many small sections in a few places and just occasionally someone who doesn't spot or understand the relations may try to 'mend' the ways either breaking the relations or leaving these spurs as they go. There may also be gaps in the route. Then there are roundabouts. They are closed so there is no way to join one end of that to the end of another way. Some routes have one-way sections too.

I need to work out the best way to deal with these anomalies. I'm hopeful the result will be useful. Then I need to work out the best way to store GeoJSON in databases such as MySQL and SQLite to then be useful.