# Fun with osm-tag-csv-history

### Post boxes

I was curious how many post boxes I have added into OSM.

First get uid from any OSM changeset, mine is uid="8244053"

Then get historical OSM data these can be downloaded from [Geofabrik](https://osm-internal.download.geofabrik.de/europe.html) select the country and download **internal.osh.pbf** - OSM login is required to download - e.g. full Czech republic history is 1.4GB.

Now install [osm-tag-csv-history](https://github.com/rory/osm-tag-csv-history).

Running the tool we can export all the Czech post box history into csv file

osm-tag-csv-history -i ./czech-republic-internal.osh.pbf -o - --no-header | grep '^amenity,post_box,' > post_box_history_CZ.csv

it took some good amount of time, thus I suggest really first export before doing other commands

real	10m31.343s
user	10m31.306s
sys	0m20.009s

can be combined with osmium to cut certain area I have it described it here https://hackmd.io/XfrY334rS7CV0tnPzx8Wvw we got some 18k+ records

cat ./post_box_history_CZ.csv | wc -l
18288

it looks like

amenity,post_box,,n170269221,1,,2007-12-15T06:57:05Z,Martin Vidner,17606,311219
amenity,post_box,,n171205346,1,,2007-12-15T17:06:29Z,Martin Vidner,17606,317365
...
amenity,post_box,,n8592644467,1,,2021-04-04T15:26:05Z,mermar,55844,102276618
amenity,post_box,,n8592644468,1,,2021-04-04T15:26:05Z,mermar,55844,102276618

we're interested in 9th column which is UID, let's use awk defining "," as field separator (there are specialized csv manipulation tools/libraries for serious stuff)

cat ./post_box_history_CZ.csv | awk -F "," '$9 == "8244053" {print}

amenity,post_box,,n8443161516,1,,2021-02-21T17:29:56Z,mahdi1234,8244053,99691022
amenity,post_box,,n8443189217,1,,2021-02-21T17:29:56Z,mahdi1234,8244053,99691022

now we need to filter 5th column to get first revision of post box only

cat ./post_box_history_CZ.csv | awk -F "," '$9 == "8244053" {print}' | awk -F "," '$5 == "1" {print}' | wc -l
128

et voila! the answer is 128 post boxes so far :)

### Tracks length

So I though how about to measure how much hiking tracks have I added?

First prepare highway=path and highway=track files the same way as above for post box (again excluding all but rev1 since we're not interested in segments where e.g. path was changed to track or vice versa) and combine into one file, we have then combined_path_and_track.csv

...
highway,path,,w914791705,1,,2021-03-08T18:26:11Z,mahdi1234,8244053,100655886
highway,track,,w591385742,1,,2018-05-27T16:09:31Z,mahdi1234,8244053,59316817
...

Now we need to extract 4th column which is way ID and extract those ways from country pbf using osmium

cat ./combined_path_and_track.csv | awk -F "," '{print $4}' | osmium getid -i - ./czech-republic-internal.osh.pbf -o paths_and_tracks.osm.pbf -r

and convert it to osm format, which can be read by JOSM

osmium cat ./paths_and_tracks.osm.pbf -o paths_and_tracks.osm

Fire up JOSM, enable measure plugin https://wiki.openstreetmap.org/wiki/JOSM/Plugins/measurement and open osm file.

Use the measure tool and CTRL+A to selected everything - it seems I created some 140km of new tracks so far.


Knowing all this I checked how much speed limits (no filtering on revisions, any revision of way this time, since we filter on added speedmax) I made.


### Steps count

One of my very first obsessions when started with OSM was counting the steps, so let's prepare steps file.

step_count,15,,w123065836,4,3,2020-10-17T12:05:01Z,mahdi1234,8244053,92626082
step_count,5,,w125157271,4,3,2018-10-13T17:16:50Z,mahdi1234,8244053,63490392
step_count,17,,w125691240,3,2,2018-07-28T16:59:40Z,mahdi1234,8244053,61147223

we're interested in 2nd column which is steps figure, so let's quick & dirty sum it using awk again :)

cat ./steps.csv |  awk -F "," '{print $2}' | awk '{ sum += $1 } END { print sum }'
2472

2472 steps and that's it for today, cheers ...