Exploring unmapped City Bike stations in Oslo

These notes are primarily written for my future self (to prevent having to figure out things I have already figured out once).

Oslo has a great City Bike system. There are over 200 stations where one can pick up or return a bike (map), for the yearly fee of a few lunches.

I would like to see if some of these stations are missing from OpenStreetMap. I will be looking for the amenity=bicycle_rental tag. I get an OSM extract from GeoFabrik:

1
curl https://download.geofabrik.de/europe/norway-latest.osm.pbf -o norway-latest.osm.pbf

Using this gis.SE answer, I know I a modified version of osmconf.ini would help me query the amenity class. I create bysykkel_osm.ini:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
#
# Configuration file for OSM import
#

# put here the name of keys, or key=value, for ways that are assumed to be polygons if they are closed
# see http://wiki.openstreetmap.org/wiki/Map_Features
closed_ways_are_polygons=aeroway,amenity,boundary,building,craft,geological,historic,landuse,leisure,military,natural,office,place,shop,sport,tourism,highway=platform,public_transport=platform

# Uncomment to avoid laundering of keys ( ':' turned into '_' )
#attribute_name_laundering=no

# Some tags, set on ways and when building multipolygons, multilinestrings or other_relations,
# are normally filtered out early, independent of the 'ignore' configuration below.
# Uncomment to disable early filtering. The 'ignore' lines below remain active.
#report_all_tags=yes

# uncomment to report all nodes, including the ones without any (significant) tag
#report_all_nodes=yes

# uncomment to report all ways, including the ones without any (significant) tag
#report_all_ways=yes

# uncomment to specify the the format for the all_tags/other_tags field should be JSON
# instead of the default HSTORE formatting.
# Valid values for tags_format are "hstore" and "json"
#tags_format=json

[points]
# common attributes
osm_id=yes
osm_version=no
osm_timestamp=no
osm_uid=no
osm_user=no
osm_changeset=no

# keys to report as OGR fields
attributes=name,barrier,highway,ref,address,is_in,place,man_made,amenity
# keys that, alone, are not significant enough to report a node as a OGR point
unsignificant=created_by,converted_by,source,time,ele,attribution
# keys that should NOT be reported in the "other_tags" field
ignore=created_by,converted_by,source,time,ele,note,todo,openGeoDB:,fixme,FIXME
# uncomment to avoid creation of "other_tags" field
#other_tags=no
# uncomment to create "all_tags" field. "all_tags" and "other_tags" are exclusive
#all_tags=yes

[lines]
# common attributes
osm_id=yes
osm_version=no
osm_timestamp=no
osm_uid=no
osm_user=no
osm_changeset=no

# keys to report as OGR fields
attributes=name,highway,waterway,aerialway,barrier,man_made,railway,amenity

# type of attribute 'foo' can be changed with something like
#foo_type=Integer/Real/String/DateTime

# keys that should NOT be reported in the "other_tags" field
ignore=created_by,converted_by,source,time,ele,note,todo,openGeoDB:,fixme,FIXME
# uncomment to avoid creation of "other_tags" field
#other_tags=no
# uncomment to create "all_tags" field. "all_tags" and "other_tags" are exclusive
#all_tags=yes

#computed_attributes must appear before the keywords _type and _sql
computed_attributes=z_order
z_order_type=Integer
# Formula based on https://github.com/openstreetmap/osm2pgsql/blob/master/style.lua#L13
# [foo] is substituted by value of tag foo. When substitution is not wished, the [ character can be escaped with \[ in literals
# Note for GDAL developers: if we change the below formula, make sure to edit ogrosmlayer.cpp since it has a hardcoded optimization for this very precise formula
z_order_sql="SELECT (CASE [highway] WHEN 'minor' THEN 3 WHEN 'road' THEN 3 WHEN 'unclassified' THEN 3 WHEN 'residential' THEN 3 WHEN 'tertiary_link' THEN 4 WHEN 'tertiary' THEN 4 WHEN 'secondary_link' THEN 6 WHEN 'secondary' THEN 6 WHEN 'primary_link' THEN 7 WHEN 'primary' THEN 7 WHEN 'trunk_link' THEN 8 WHEN 'trunk' THEN 8 WHEN 'motorway_link' THEN 9 WHEN 'motorway' THEN 9 ELSE 0 END) + (CASE WHEN [bridge] IN ('yes', 'true', '1') THEN 10 ELSE 0 END) + (CASE WHEN [tunnel] IN ('yes', 'true', '1') THEN -10 ELSE 0 END) + (CASE WHEN [railway] IS NOT NULL THEN 5 ELSE 0 END) + (CASE WHEN [layer] IS NOT NULL THEN 10 * CAST([layer] AS INTEGER) ELSE 0 END)"

[multipolygons]
# common attributes
# note: for multipolygons, osm_id=yes instantiates a osm_id field for the id of relations
# and a osm_way_id field for the id of closed ways. Both fields are exclusively set.
osm_id=yes
osm_version=no
osm_timestamp=no
osm_uid=no
osm_user=no
osm_changeset=no

# keys to report as OGR fields
attributes=name,type,aeroway,admin_level,barrier,boundary,building,craft,geological,historic,land_area,landuse,leisure,man_made,military,natural,office,place,shop,sport,tourism,amenity
# keys that should NOT be reported in the "other_tags" field
ignore=area,created_by,converted_by,source,time,ele,note,todo,openGeoDB:,fixme,FIXME
# uncomment to avoid creation of "other_tags" field
#other_tags=no
# uncomment to create "all_tags" field. "all_tags" and "other_tags" are exclusive
#all_tags=yes

[multilinestrings]
# common attributes
osm_id=yes
osm_version=no
osm_timestamp=no
osm_uid=no
osm_user=no
osm_changeset=no

# keys to report as OGR fields
attributes=name,type,amenity
# keys that should NOT be reported in the "other_tags" field
ignore=area,created_by,converted_by,source,time,ele,note,todo,openGeoDB:,fixme,FIXME
# uncomment to avoid creation of "other_tags" field
#other_tags=no
# uncomment to create "all_tags" field. "all_tags" and "other_tags" are exclusive
#all_tags=yes

[other_relations]
# common attributes
osm_id=yes
osm_version=no
osm_timestamp=no
osm_uid=no
osm_user=no
osm_changeset=no

# keys to report as OGR fields
attributes=name,type,amenity
# keys that should NOT be reported in the "other_tags" field
ignore=area,created_by,converted_by,source,time,ele,note,todo,openGeoDB:,fixme,FIXME
# uncomment to avoid creation of "other_tags" field
#other_tags=no
# uncomment to create "all_tags" field. "all_tags" and "other_tags" are exclusive
#all_tags=yes

Since now I am only interested in the bike stations in Oslo, I will also need an Oslo boundary. Using this great answer, I do:

1
2
3
curl -s "https://polygons.openstreetmap.fr/get_geojson.py?id=406091&params=0" | \
 jq -c '{"type": "FeatureCollection", "name": "main", "features": [.geometries[] | {"type":"Feature","properties":{"name": "Oslo"},"geometry": .}]}' \
 >| oslo_borders.geojson

Having oslo_borders.geojson, and norway-latest.osm.pbf, we can use a shell script to obtain all amenity=bicycle_rental places within Oslo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#!/bin/bash

set -o errexit
set -o nounset

rm -rf bysykkel_osm
mkdir bysykkel_osm

for each in $(ogrinfo norway-latest.osm.pbf | tail -n +3 | awk '{print $2}'); do

    rm -f bysykkel_osm/bysykkel_osm_${each}.geojson
    
    ogr2ogr -f GeoJSON \
            -dialect sqlite \
            -sql "SELECT ST_Centroid(osm.geometry) FROM ${each} AS osm, 'oslo_borders.geojson'.main AS oslo WHERE amenity=='bicycle_rental' AND ST_INTERSECTS(oslo.geometry,osm.geometry)" \
            bysykkel_osm/bysykkel_osm_${each}.geojson norway-latest.osm.pbf \
            -nln main \
            --config OSM_CONFIG_FILE bysykkel_osm.ini

done

Running this script produces:

1
2
3
4
5
bysykkel_osm_lines.geojson
bysykkel_osm_multipolygons.geojson
bysykkel_osm_points.geojson
bysykkel_osm_multilinestrings.geojson
bysykkel_osm_other_relations.geojson

Let’s unite these files to a single GeoJSON. Keeping in mind to use the -single command option, we can get a GeoJSON in the metric, locally accurate EPSG:25832 system via:

1
python3 /opt/homebrew/Cellar/gdal/3.6.4_4/bin/ogrmerge.py -f GeoJSON -overwrite_ds -single -t_srs EPSG:25832 -o bysykkel_osm.geojson bysykkel_osm/bysykkel_osm_*.geojson -nln main

Luckily, Oslo Bysykkel provides historical data about the usage of their service, which include station coordinates. I download the 2023 April one:

1
curl https://data.urbansharing.com/oslobysykkel.no/trips/v1/2023/04.json -o oslo_bysykkel_2023_04.json

To extract station coordinates (in EPSG:25832, so I can use the result nicely with bysykkel_osm.geojson produced above), I run the following Python script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import pandas as pd
import geopandas as gpd
import shapely.geometry

df = pd.read_json("oslo_bysykkel_2023_04.json")
df = df.drop_duplicates(subset=['start_station_name'],keep='first')
df = df[['start_station_name','start_station_description','start_station_latitude','start_station_longitude']]

df = df.assign(geometry = df.apply(lambda row: shapely.geometry.Point(row['start_station_longitude'],row['start_station_latitude']),axis=1))

df = df[['start_station_name','start_station_description','geometry']]
df = df.rename(columns={'start_station_name':'station_name','start_station_description':'station_description'})

bysykkel_data = gpd.GeoDataFrame(df).set_crs(4326)
bysykkel_data.to_crs(25832).to_file("oslo_bysykkel_2023_04.geojson")

To get all official bike stations not within 25m of any OSM amenity=bicycle_rental location, I do:

1
2
3
4
5
6
7
8
ogr2ogr -f GeoJSON \
         -t_srs EPSG:4326 \
         -dialect sqlite \
         -sql "SELECT bysykkel.* FROM 'oslo_bysykkel_2023_04.geojson'.oslo_bysykkel_2023_04 AS bysykkel \
               WHERE NOT EXISTS (SELECT 1 FROM 'bysykkel_osm.geojson'.main AS osm WHERE ST_DISTANCE(bysykkel.geometry, osm.geometry) <= 25)" \
         oslo_bysykkel_not_on_osm.geojson oslo_bysykkel_2023_04.geojson \
         -nln main \
         -lco COORDINATE_PRECISION=8

Producing oslo_bysykkel_2023_04.geojson as a result. This file includes 139 features (I know via jq '.features | length' oslo_bysykkel_not_on_osm.geojson). This many potential bike statioins can be added to OpenStreetMap!

In the OSM iD editor, after Fn-U, there is the Custom Map Data button. Click on the 3 dots next to it. Upload oslo_bysykkel_not_on_osm.geojson. Get beautiful map with dots on it, indicating potentially unmapped bike stations:

Zoom to one of the dots, indeed discover an unmapped bike station:

It seems I have a bunch of stations to map. But first, I’ll ask Bysykkel if they are ok with this. They probably are, as their data is published under NLOD, and there are many other sources in OSM originally distributed under NLOD. One can explore similar mapping opportunities in Bergen and Trondheim too.