Canton of Bern Address Import

From OpenStreetMap Wiki
Jump to navigation Jump to search

Goals

Import all addresses (~400'000) of the canton of Bern, Switzerland by community import. Primary data Gebäudeadressen des Kantons Bern (GEBADR) provided by Amt für Geoinformation des Kantons Bern.


Schedule

June 2016: Blog entry at SOSM.ch about the GEBADR list.

October 2016: Start of writing scripts to process data, exchange with local community via talk-ch mailing list.

November 2016: Creation of wiki.osm page and contact with talk-import mailing list. Upload of scripts to GitHub and creating a video tutorial. First import test. Release.

December 2016: First users start with import. Preparation of Tasking Manager.

January 2017: Tasking Manager is ready. Data can now be directly downloaded from Tasking Manager.

February 2017: More users have joined. Blog entry at SOSM and new video tutorial for mapping by Tasking Manager.

May 2017: A background layer containing the building outlines is now available.

Actual status

The import by community members has started in December 2016 and is ongoing.

Check progress on regio-osm.de (communities) or on qa.poole.ch (single addresses).


Import Data

Background

The canton of Bern provides a detailed list of every address in the canton of Bern (GEBADR).

Data is listed in wiki.osm as a potential datasource.

The permission to use GEBADR is given.

Please cite as: "Gebäudeadressen des Kantons Bern © Amt für Geoinformation des Kantons Bern"

Update

The permission to use MOPUBE, another database from the canton of Bern, is given. A background layer with building outlines was created from these data: https://tile.osm.ch/bbe/{zoom}/{x}/{y}.png

Please cite as: "Amtliche Vermessung vereinfacht © Amt für Geoinformation des Kantons Bern"

Import Type

The addresses will be imported manually by the community in JOSM.

Data Preparation

Data Reduction & Simplification

The GEBADR list contains information which is irrelevant for OSM and therefore sorted out. Kept information are postcode, place, streetname, housenumber, building type, and altitude.

Tagging Plans

The following OSM keys are used:

GEBADR key OSM key Comments
addr:country=* "CH"
PLZ
addr:postcode=*
ORT
addr:city=*
LOKALISAT addr:street=* When editing manually before importing, you will find three empty columns for possible entries: addr:place (if it suits better than addr:street), addr:street:fr and addr:place:fr for streets and places that have names in German and French.
GEBNR addr:housenumber=*
NUTZUNG building=* The entries for NUTZUNG are categorised in OSM tags key:building.
HOEHE ele=* The HOEHE entry is removed in the 2019 GEBADR data set. Therefore, the key:ele is not generated anymore.

Data Transformation

First, save the addresses as a .csv file. For example with

ogr2ogr -t_srs EPSG:4326 -f csv adressen.csv GEBADR/LV95/data/GEBADR_GADR.shp -lco GEOMETRY=AS_XYZ

ogr2ogr is part of Gdal which can be obtained here: https://trac.osgeo.org/gdal/wiki/DownloadingGdalBinaries

Data are then edited with two Python 3 scripts:


- Script 1 (before manual editing) creates three consecutive files:

GEBADR_WGS84.csv - Reduces GEBADR list and adds WGS84 coordinates (calculated from Swiss coordinates LV95).

GEBADR_OSMstyle.csv - Headers are changed to OSM keys and NUTZUNG entries are categorised into key:building.

GEBADR_OSMstyle_edited.csv - Entries without housenumbers ("nn") and/or NUTZUNG "unterirdisches gebaeude" (underground building) are sorted out.


The GEBADR_OSMstyle_edited.csv file is used in the tasking manager (see below). Before a tasking manager was available, the file was opened in a spreadsheet and edited manually. The addresses for import are selected and checked. The selection is saved as GEBADR_selection.csv.


- Script 2 (after manual editing) creates a new file GEBADR_OSMimport.csv which has quotation marks added to all non-coordinate entries in the GEBADR_selection.csv.

The created GEBADR_OSMimport.csv file can be easily imported to JOSM by the plug-in OpenData.

The building nodes are merged to building polygons by using the plug-in Conflation.

Tasking Manager

The GEBADR_OSMstyle_edited.csv file was converted to a GeoJSON file:

ogr2ogr GEBADR_OSMstyle_edited.geojson -f geojson GEBADR_OSMstyle_edited.vrt

With the .vrt file containing

<OGRVRTDataSource>
    <OGRVRTLayer name="GEBADR_OSMstyle_edited">
        <SrcDataSource>../GEBADR_OSMstyle_edited.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="longitude" y="latitude"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

GeoJSON files for each political commmunity containing the addresses were created with:

ogr2ogr -clipsrc boundary_NNN.geojson -f geojson NNN.geojson GEBADR_OSMstyle_edited.geojson

Where NNN is the 3 digit BFS number. boundary_NNN.geojson is the boundary of the political community extracted from OSM with overpass.

Additionally, the resulting geojson files were also converted to .osm files, and are now hosted on osm.ch.

The tasking manager was set up with a GeoJSON file creating task tiles defined by community boundaries. The BFS number (an identifier for political communities in Switzerland) in the GeoJSON file was used to create a community-specific link in the "Per Task Instructions" to the address .osm files.

Changeset Tags

The changeset tags should contain the following information:

Comment: "City: Street1 or Place1, Street2/Place2, ..."; e.g. "Bern: Ostring, Papiermühlestrasse, Pappelweg"

Source: "Gebäudeadressen des Kantons Bern © Amt für Geoinformation des Kantons Bern"

Description: "https://wiki.openstreetmap.org/wiki/Canton_of_Bern_Address_Import"

Data Transformation Results

To get the newest version, prepare actual data individually (see description above). Edited data are also hosted online and updated from time to time (GitHub and tasking manager).


Data Merge Workflow

Team Approach

The script development and import organisation is done by User:YBJazzer, the tasking manager is set up by User:Datendelphin, import will be done by local community members.

Scripts and supporting tools

You can find the scripts on GitHub and a video tutorial on YouTube. For the new workflow using a tasking manager, another video tutorial was created.

The progress of address coverage in the canton of Bern can be checked on the tasking manager or Regio-OSM.

References

Primary data source: Gebäudeadressen des Kantons Bern © Amt für Geoinformation des Kantons Bern

In Script 1, the LV95 to WGS84 transformation is partly based on a script by Aaron Schmocker. The Script is available on GitHub. Copyright (c) 2014 Federal Office of Topography swisstopo, Wabern, CH and Aaron Schmocker.

New Workflow with Tasking Manager

Instructions (English)

The tasking manager now enables and easy workflow with the GEBADR addresses. A detailed instruction can be found in the tasking manager, a video tutorial is available here. Consider especially the requirements for an area to be marked as "Done". As well, the changeset should/must (especially the source!) contain the following:

Comment: "City: Street1, Street2, Street3" or similar

Source: "Gebäudeadressen des Kantons Bern © Amt für Geoinformation des Kantons Bern"

Description: "https://wiki.openstreetmap.org/wiki/Canton_of_Bern_Address_Import"

Instructions (German)

Der Tasking Manager ermöglicht jetzt ein einfaches Arbeiten mit den GEBADR Adressen. Eine detaillierte Anleitung findet sich im Tasking Manager, ein Videotutorial ist hier verfügbar. Beachte insbesondere, wann ein Gebiet als "Done" markiert wird. Wichtig ist das Changeset, welches folgendermassen lauten soll/muss (insbesondere die Quellenangabe!):

Kommentar/comment: "Ort: Strasse1, Strasse2, Strasse3" oder ähnlich

Quelle/source: "Gebäudeadressen des Kantons Bern © Amt für Geoinformation des Kantons Bern"

Beschreibung/description: "https://wiki.openstreetmap.org/wiki/Canton_of_Bern_Address_Import"

Quality Assurance

Quality assurance is ensured by using a tasking manager, detailed instructions can be found there. One person transfers address data of a task area to building outlines, draws missing buildings, and splits buildings when necessary. The task is then marked as "Done". A second person will check the task. If the task is fine, the status is changed to "Validated".

Detailed/old Workflow

Step by step instructions (English)

This instruction describes the edit of GEBADR data with Python 3 and Microsoft Excel spreadsheet. MS Excel will save .csv files with ";" as delimiter in German versions. The import plug-in OpenData will also use ";" as delimiter.

You need: Python 3 (to run the scripts with IDLE), a text editor (e.g. Notepad++) and a spreadsheet (e.g. Microsoft Excel).

Information for LibreOffice and OpenOffice users
If you save the GEBADR_GADR.dbf file in your spreadsheet as .csv, two minor changes have to be made:
- Change the delimiter in Script 1 from ";" to ",".
- The entries of the first line of a .dbf file (which are the headers) look a bit special when opened in LibreOffice or OpenOffice spreadsheet.
They look like this: "LOKALISAT,C,60","GEBNR,C,12","BFSNR,N,4,0"
Change them manually to look like this: "LOKALISAT","GEBNR","BFSNR" or LOKALISAT,GEBNR,BFSNR

Workflow
1. Download and unzip GEBADR.zip file.

2. Open "...GEBADR/GEBADR/LV95/data/GEBADR_GADR.dbf" in a spreadsheet.

3. Save as ".../GEBADR_GADR.csv" (MS-DOS) - This is important for correct display of umlauts and French letters. Check in a text editor whether umlauts are displayed correctly.

4. Adjust Script 1: set input (".../GEBADR_GADR.csv") and output paths. Run the script. It will create three files as described above.

Now some manual editing follows:

5. Import ".../GEBADR_OSMstyle_edited.csv" to a spreadsheet (in Microsoft Excel: import as text, separate with ";", make sure each column is imported as text, not standard). Check if umlauts and coordinates are correctly displayed.

6. Filter the addresses you want to import (with Pivot filter), check whether key:place should be used instead of key:street, and add street:fr (resp. place:fr) if necessary. Save file as ".../GEBADR_selection.csv" (Trennzeichen-getrennt). Check in a text editor if file contains your selection.

7. Adjust Script 2, set input path ".../GEBADR_selection.csv" and output path. This script will add quotation marks to all non-coordinate entries, which is the format needed for import.

8. Drag and drop ".../GEBADR_OSMimport.csv" into JOSM. The plug-in OpenData must be installed.

9. Check whether nodes fit to buildings on aerial imagery. Preferrably, merge building nodes to building polygons using the plug-in Conflation. Otherwise, copy address nodes to the data layer using Ctrl+Shift+M. See more about tagging on If you feel not sure about an address localisation, don't import it.

Previously mapped exact address nodes that represent entrances (e.g. Länggasse, Bern) can remain unchanged.

Addresses without buildings: Draw the building outline according to aerial imagery and merge address on building outline.

Multiple addresses in one building: Divide the building with new outlines and merge addresses on building part outlines. If you cannot do that, transfer building type to building polygon and delete key:building in address nodes.

10. Upload your data and cite source in changeset:

Comment: "City: Street1, Street2, Street3"

Source: "Gebäudeadressen des Kantons Bern © Amt für Geoinformation des Kantons Bern"

Description: "https://wiki.openstreetmap.org/wiki/Canton_of_Bern_Address_Import"

Step by step instructions (German)

Dies Anleitung beschreibt die Bearbeitung der GEBADR Daten mit Python 3 und dem Tabellenkalkulationsprogramm Microsoft Excel. MS Excel speichert Tabellen im .csv Format mit ";" als Delimiter. Das Import Plug-in OpenData benötigt ebenfalls ";" als Delimiter.

Benötigt werden: Python 3 (um die Scripts mit IDLE laufen zu lassen), ein Texteditor (z.B. Notepad++) und ein Tabellenkalkulationsprogramm (z.B. Microsoft Excel).

Information für LibreOffice und OpenOffice Benutzer
Wenn du das GEBADR_GADR.dbf File im Tabellenkalkulationsprogramm als .csv speicherst, müssen zwei kleine Änderungen gemacht werden:
- Ändere den Delimiter in Script 1 von ";" zu ",".
- Die Einträge der ersten Zeile eines .dbf Files (was den Kategorietiteln entspricht) sehen etwas speziell aus im LibreOffice oder OpenOffice Tabellenkalkulationsprogramm.
Sie sehen folgendermassen aus: "LOKALISAT,C,60","GEBNR,C,12","BFSNR,N,4,0"
Ändere dies manuell zu: "LOKALISAT","GEBNR","BFSNR" oder LOKALISAT,GEBNR,BFSNR

Workflow
1. Runterladen und entzippen der GEBADR.zip Datei.

2. Öffne "...GEBADR/GEBADR/LV95/data/GEBADR_GADR.dbf" im Tabellenkalkulationsprogramm.

3. Speichere als ".../GEBADR_GADR.csv" (MS-DOS) - Dies ist notwendig für die korrekte Darstellung von Umlauten und französischen Buchstaben, überprüfe im Texteditor die korrekte Schreibweise.

4. Passe in Script 1 den input-Pfad ( ".../GEBADR_GADR.csv") und die output-Pfade an. Lasse das Script laufen. Es werden drei Dateien erstellt, welche weiter oben beschrieben sind.

Jetzt folgt etwas manuelle Bearbeitung:

5. Importiere ".../GEBADR_OSMstyle_edited.csv" ins Tabellenkalkulationsprogramm (in MS Excel: importiere als Text, trenne mit ";", und stelle sicher dass sämtliche Spalten als Text importiert werden). Überprüfe die korrekte Darstellung der Einträge.

6. Filtere die Adressen, welche du importieren möchtest (mit dem Pivot-Filter), überprüfe ob key:place besser passt als key:street und korrigiere gegebenenfalls. Falls vorhanden, schreibe die französischen Strassennamen in die Spalte street:fr. Speichere die Datei als ".../GEBADR_selection.csv" (Trennzeichen-getrennt). Überprüfe im Texteditor, ob deine Auswahl wie gewünscht gezeigt wird.

7. Passe in Script 2 den input-Pfad ( ".../GEBADR_selection.csv") und den ouput-Pfad an. Das Script schreibt Anführungszeichen zu allen nicht-Koordinateneinträgen. Dies ist die richtige Darstellung für den Import.

8. Klicke und ziehe die Datei ".../GEBADR_OSMimport.csv" in JOSM. Das Plug-in OpenData muss installiert sein.

9. Überprüfe ob die Gebäude-/Adresspunkte mit den Luftbildern übereinstimmen. Übertrage die Gebäude-/Adresspunkte auf die Gebäudeumrisse mittels Conflation Plug-in. Sofern kein Gebäudeumriss vorhanden ist, kopiere den Gebäude-/Adresspunkt mittels Ctrl+Shift+M in die Datenebene. Falls du dir über eine Adresse unsicher bist, importiere sie im Zweifelsfall nicht.

Bereits exakt auf Eingänge gemappte Adresspunkte (z.B. Länggasse, Bern) können so belassen werden. Adressen ohne Gebäudeumriss: Zeichne den Gebäudeumriss entsprechend den Luftbildern und merge die Adresse auf den Gebäudeumriss. Mehrere Adressen in einem Gebäude: Unterteile das Gebäude und merge die Adressen mit den Umrissen der Gebäudeteile. Falls dies nicht möglich ist, übertrage den Gebäudetyp auf den Gebäudeumriss und entferne ihn von den Addresspunkten.

10. Lade die Änderungen hoch und schreibe im Changeset:

Kommentar/comment: "Ort: Strasse1, Strasse2, Strasse3"

Quelle/source: "Gebäudeadressen des Kantons Bern © Amt für Geoinformation des Kantons Bern"

Beschreibung/description: "https://wiki.openstreetmap.org/wiki/Canton_of_Bern_Address_Import"