So I did update of the OSM data that we get as CSV export from WhiteBikes database. I had to take care of few troubles - export is not exactly CSV, data order differs, but at least they are consistent. I think it can’t be automated and changes should be reviewed, I did it by hand as I want to see which data modified. There are around 50 bike stands in the city.

So, lets see what did I have to do:

Prepare list of stands from database, in a format similar to previously retrieved data

• download new data from whitebikes at * censored *. In case that website is saved as HTML and not as pure text try to use view source + select all - copy - paste into file.
• It is in format of CSV (spreadsheet) that can be loaded into office if needed.
• But it is in incorrect format, so it has to be adapted beforehand.
• Replace all commas by dot. Replace all semicolons by comma.
• After this change it will be CSV (Comma-separated-values) and it can be used in excel.

• Though, there may be still some issues - semicolons are sometimes used also in text, not only as separators.

• import into office spreadsheet editor (excel, calc) and make it nice and aligned by station number:
• add new first column, fill it with numbers 1-100
• set other columns for sorting, sort them by number (they may be reordered from database export)
• align them with the numbers in first column, fill in empty lines for numbers that are not present

• export into CSV again (see sample file export-2018-09-26-ordered-with-gaps.csv)

• now, you can compare old and new csv with your favourite compare tool (Meld, WinDiff) and see it nicely aligned, see the differences that happened.

You may have to modify files to match some general differences (for example http to image changed to https, and such), (as example see sql-2017-03-26.csv and sql-2017-03-26-ordered-with-gaps-look-like-new.csv)

note - CSV column names are: “standId,standName,standDescription,standPhoto,serviceTag,placeName,longitude,latitude”. Important column is ‘serviceTag’, if that is 1 then it is not a public sharing stand, it is a service one, and it should be either not imported, or marked as disabled:amenity.

Prepare these data into OSM file to see the data in JOSM

Install python to run https://raw.githubusercontent.com/OSMBrasil/csv2osm/master/csv2osm.py for conversion of csv file into OSM file. If it won’t work for you, then you can use your favourite editor which can do search-and-replace with regexp (Notepad++ on windows, Kate on linux)

1. Take file (see export-2018-09-26-ordered-with-gaps.csv) and remove empty lines, and those with coords “,0,0” (see export-2018-09-26-ordered-with-gaps-to-osm-cleaned-0-0.csv)

2. Do a replace of:

^([^,]+),([^,]+),([^,]+),([^,]),([^,]),([^,]+),([^,]+),([^,]+),(.*)$

into:

<node id='-\1' action='modify' lat='\9' lon='\8'><tag k='name' v='\3' /><tag k='description' v='\4' /><tag k='number' v='\2' /><tag k='amenity' v='bar' /></node>

This will convert CSV lines into nodes with lat, lon, description, name. It is made into bar to have big visible icon.

Add these two lines before first line:

<?xml version=’1.0’ encoding=’UTF-8’?> <osm version=’0.6’ generator=’JOSM’>

Add this line after last line:

</osm>

Then save it with extension .osm (see export-2018-09-26-ordered-with-gaps-to-osm.osm) and you can load it into JOSM.

Prepare existnig stands in JOSM

Run JOSM, open the dialog for data download, and switch to tab “Download from overpass API” (in my JOSM 13756)

Fill in this query to get WhiteBike stands: > node > [operator=WhiteBikes] > ({{bbox}}); > out;

Now lets do the change

You have:

• layer with existing stands data
• you open layer with stands as they are in whitebike database (export-2018-09-26-ordered-with-gaps-to-osm.osm)
• you have open a comparison of old data (export-2018-09-26-ordered-with-gaps-to-osm.osm and sql-2017-03-26-ordered-with-gaps-look-like-new.csv)
• additional data: satelite imagery (from bing), mapillary data

You do:

• go along the list in comparison tool, see if there is any difference
• if there is then either delete the stand (ZRUSENY means deleted), or modify it.
• do not insert stands with 6th column value ‘1’, that means it is service stand and it won’t show on WhiteBike maps. Only ‘0’ is ok.
• If stands are not at the same place in existing data and in database, then look into export-stands-20170613-popisky.csv where is described that many stands are incorrectly placed in WhiteBikes map (sometimes up to 50 meters away from position in description or on photo).
• mark stand types according rules on wiki osm.wiki/Sk:bicycle_parking_Cyklokoalicia_import
• if stand is temporarily disabled, then mark it as disused:amenity=bicycle_rental and keep other properties, for easy update back to working state

Notes - helper, for copying attributes:

• amenity=bicycle_rental
• bicycle_parking=stands
• name=NOVEMESTO
• network=WhiteBikes Bratislava
• operator=WhiteBikes
• ref:whitebikes=108 NOVEMESTO
• ref:whitebikes_image=http://whitebikes.info/stands/NOVEMESTO.jpg
• ref=108
• website=http://www.whitebikes.info

Parking stands:

• amenity=bicycle_parking
• bicycle_parking=stands
• ref:cyklokoalicia=108 NOVEMESTO

Changeset is 62997154.