View Wandrer.earth Your Points in SQL

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.

On Wandrer.earth you can download an all-points excel file. Excel is not the way I want to work with data, so I wrote a converter to SQL which I can view in a datasette

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:

SELECT full_name, name, points, updated FROM champions ORDER BY points DESC

For me this looks like this:

img1

And the second question is: Which regions are easiest to bring to 25%. The query:

SELECT pure_name, full_name, completed_in_km, percentage,
       distance_to_25_in_km, distance_to_50_in_km, distance_to_75_in_km
FROM points WHERE percentage < 25 ORDER BY distance_to_25_in_km

The result for my data:

img2

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.