From OpenStreetMap Wiki
Jump to: navigation, search


This page is about importing Alberi Monumentali (historical trees) dataset published by Ministero delle politiche agricole alimentari, forestali e del turismo (Mipaaft) for regione Friuli Venezia Giulia (RAFVG).

Results of a nationwide survey is splitted in regional Excel files. In this page we consider regional excel file downloadable at Mipaaf page and named "Friuli Venezia Giulia 9.8.2018".

Dataset shall be adapted in order to generate OSM files suitable to be imported in planet.osm. It shall not be a blind import: source data shall be checked by mappers thru an audit support map.

The import is being discussed on the national OSM mailing list. The import will be the result of consensus there.


This import aims to have a complete and updated set of trees denoted as "natural monument" for the italian territory. National survey took in consideration historic and ecological values and dimensions. Since Mipaaft published a "first update" on 2018-08-09 (defined herein), import procedure shall define a specific reference tag for future Mipaaft releases. This import has been set on a regional size, as a pilot for other italian regions (admin_level=4).


First import will be performed after community audit on shared json preview files. Audit progress will be trackable in project page. Import limited size should take 10-30 days to be accomplished.

Import Data


Source dataset contains <200 punctual objects which quality is good and in locations provided by "Regione FVG" source, spatially accurate. During a pre-audit, few minior spatial errors has been detected (<20m).


  • Language: ita
  • Date: 24/04/2018
  • Subject: RAFVG - Direzione generale - Servizio paesaggio e biodiversità
  • Role: originator
  • Web site:
  • Classification: unclassified
  • Other constraints: public


Record format and tagging plan

Mipaaft dataset table structure will be taken as-it-is from Excel table, adapted and pruned thru OpenRefine.

Mipaaf alberi Monumentali - record format
Field Name Description:it Description:en Example tagged as
1 ID SCHEDA codice univoco scheda albero unique tree code 01/D014/GO/06 ref:mipaaft
2 PROVINCIA nome provincia province name Gorizia
3 COMUNE Nome Comune Municipality name Cormons
4 LOCALITA' località locality Plessiva
5 LATITUDINE SU GIS latitudine latitude 46°14'53,82
6 LONGITUDINE SU GIS longitudine longitude 12°33'20,42
7 ALTITUDINE (m s.l.m.) altitudine metri su livello medio del mare elevation above mean sea level 1200 ele
8 CONTESTO URBANO si/no urbano (si/no) urban (yes/no) si/no
9 NOME SCIENTIFICO nome scientifico scientific name Quercus ilex L. species
10 NOME VOLGARE nome volgare common name Leccio species:it
11 CIRCONFERENZA circonferenza (cm) circumference (cm) 340 circumference
12 ALTEZZA (m) altezza in metri height in meters 24 height
13 CRITERI DI MONUMENTALITA' candidato per candidate for its età, forma, valore ecologico denotation=natural_monument
14 PROPOSTA NOTEVOLE INTERESSE PUBBLICO di pubblico interesse public interest si protected=yes
15 RIFERIMENTO LEGISLATIVO Riferimento legislativo Legislative reference D. M. 5450/19/12/2017

Few records, reporting tree row or cluster, will be managed by fixme tag.

Leaf details

Leaf detail tags (life_cycle and leaf_type) could be added cross-referencing wiki specific paragraph. To accomplish such integration, wiki table should be converted in csv format thru wikitable2csv online tool and shuold be stored as an Openrefine table with minor adjusts.

Alternatively, since the above list is incomplete, leaf detail import could be done with the following procedure:

  • generating overpass-turbo europeantrees list in wide european area
  • converting species to genus and minor adjusts thru OpenRefine operations
  • sorting and duplicate removal on genus
    • sort -u europeantrees.tsv europeantrees-uniq.tsv
  • cross-referencing result table with dataset thru OpenRefine

Import Type

The dataset will be imported on a regional base (OSM admin_level=4). Prior to upload, each osm preview file will be published and linked in the italian wiki page to be manually checked by local teams.

Data Preparation

The data is presented as xlsx Excel file in a collection of punctual elements, one for each tree. Datum is not defined (WGS84 supposely).


Prior to OSM JSON conversion, some issues require refining operations, documented herein. A summary of actions performed thru OpenRefine:

  • loading xlsx:
    • Ignore first 1 line at beginning of file
    • Parse next 1 line as column headers
    • Discard initial 1 row of data
  • general geo coordinates validation
    • leading & trailing spaces trimming
    • spaces between values
    • two single quotes instead of double quotes
    • typos
  • some column renaming (for readability)
  • dimensional fields string to number conversion
  • latitude & longitude DMS to DD conversion
  • denotation=natural_monument based on "Criteri Monumentalità" field
  • protection?yes based on "Dichiarazione di interesse pubblico" field


Conflator input requires json format. Dataset conversion to json is performed thru OpenRefine template documented herein.

Further validation on output json files can be performed thru jsonlint (npm -g install jsonlint).

Up to 2.8 version, Openrefine doesn't manage null values; workaround to remove lines containing nulls:

pi@raspberrypi:~/OSM sed -i -e '/ : null/d' <Openrefine-output-file>.json


Conflation is performed by OSM Conflator. Objects tagged ad natural=tree will be extracted from OSM in a bounding box defined by source dataset. Existing OpenStreetMap data within a range is merged and tags will be added/replaced accordingly to conflator parameter file

Conflator output example

pi@raspberrypi:~/OSM conflate -i AlberiMonumentali-FVG-csv.json  -o AM.osm -c previewAM.json
12:02:28 Read 139 items from the dataset
12:02:40 Downloaded 2457 objects from OSM
12:02:42 Matched 14 points
12:02:42 Adding 125 unmatched dataset points
12:02:42 Deleted 0 and retagged 0 unmatched objects from OSM

Dedicated upload account

The account attilaimport will be used to upload community revised .osm files.

Changeset Tags

Changeset will be tagged with:

Team Approach

Import will be managed by the following OSM users:

  • Cascafico


Step by step operations:

  1. dataset download
  2. OpenrRefine operations
  3. OpenRefine json export
  4. run conflator
  5. audit map announcement & publication
  6. wait for community validation
  7. conflation re-run
  8. Upload changeset(s) in OSM

In case of import problems, changeset involved will be reverted using proper reverter


changeset objects notes


In case some problems will be detected after upload:


  • TBD


  • TBD