Wandrer.earth helps me cycle more by tracking all the roads/streets/ways I already cycled on. The goal is to use every street possible in a region. Private ways and forbidden roads are already removed. Walking is tracked too, but not that important for me.
The code for the converter is on github: http://github.com/mfa/wandrer-earth-to-sqlite.
A few remarks about the code:
Earth (so the overall value) is removed
The list of continents is needed because they are toplevel, the same as suburbs
Administrative levels are different in every country, so numbers are used
The fieldnames are transfered to be more sql-like
Monthly champion and points are two different tables
The bonus points rows are dropped for now
I wanted to answer two questions with this new dataset:
Where did I get the most bonus points. The query for this in SQL looks like this:
For me this looks like this:
And the second question is: Which regions are easiest to bring to 25%. The query:
The result for my data:
The second row looks suspicious, but this area seems to be very small.
The last row is Tokyo. I will probably not visit Tokyo in the near future to cycle there again.
So maybe filtering on a region is a good idea, and this is easy with SQL now.
Just add a
WHERE level_2 == "Germany" to limit to Germany.
This solves my current need for planning new routes and gives an easier way to see the monthly champions I achieved.