Gieß den Kiez Tree data

This is a script to harvest tree data from a Web Feature Service from Berlins Geodata Portal and integrate it to our Gieß-den-Kiez-database.

In the application Gieß-den-Kiez.de, Berlin's street trees are displayed on a map. The data about the trees comes from Berlin's street and green space offices and is made available as open data via Berlin's Geodata portal, the FIS-Broker. The underlying database, the green space information system (GRIS), is continuously maintained by the administration: Trees not yet recorded and newly planted trees are entered and felled trees are deleted. The data set is then updated in the Geodata portal once a year, always in spring. In order to reflect the current status, the data in Gieß den Kiez is therefore also updated once a year when the new tree dataset is published.

We use these Python scripts to automate this. Using the script get_data_from_wfs.py, the data can be downloaded from the FIS-Broker in GeoJSON format and saved locally. Using main.py we connect to our Gieß-den-Kiez database and the data is then compared with the existing tree data of the database using their GML-IDs (also called technical IDs in the FIS-Broker). In this way, deleted and added trees are identified and removed or added from the database. All matching trees are also identified and updated for the columns specified in config.yml.

Inputs

• New tree data in GML or GeoJSON format
• config.yaml that configurates paths, tablesnames, overwritting, mapping of column names and columns to update

database:
  parameter-path: .env
  data-table-name: trees
  replace-table: True

new-data-paths:
  - tree_data/data_files/s_wfs_baumbestand.geojson
  - tree_data/data_files/s_wfs_baumbestand_an.geojson

data-schema:
  mapping:
    art_bot: artbot
    art_dtsch: artdtsch
    gattung_deutsch: gattungdeutsch
    gml_id: gmlid
  merge-on:
    - gmlid
  update:
    - standalter
    - baumhoehe
    - kronedurch
    - stammumfg
    - gmlid
    - lat
    - lng
    - standortnr
    - kennzeich
    - artdtsch
    - artbot
    - gattungdeutsch
    - gattung
    - pflanzjahr

• .env that contains database credentials

PG_SERVER=
PG_PORT=
PG_USER=
PG_PASS=
PG_DB=

Example Usage

1. Step: Download newest tree data from the FIS-Broker. Locally run:

python tree_data/get_data_from_wfs.py

2. Step: Set filename path and current year in the conf.yml

year: 23

new-data-paths:
  - data_files/s_wfs_baumbestand_YYYY-MM-DD.geojson
  - data_files/s_wfs_baumbestand_an_YYYY-MM-DD.geojson

3. Setp: Configure you .env file and provide the credentials of your production database

4. Step: Execute main.py to connect to your production database and finally update the database:

python tree_data/main.py

Updating Caretaker labels

In Gieß den Kiez it is visible which trees are maintained by Berlin's street and green space offices. However, this information is not included in the offical Berlin tree dataset. Instead, Berlin's green space offices provide separate Excel tables containing the trees they water. This information needs to be entered 'manually' into the database table "trees" using SQL commands. The procedure is as follows:

1. Extract only the FIS-Broker-ID'S (gmlids) from the Excel sheet to a csv file
2. Create a new table with this ID's in the database: CREATE TABLE caretaker_ids(id VARCHAR NOT NULL)
3. Import ID’s from CSV-Table into the database table
4. Delete old caretaker labels from the trees table: UPDATE trees SET caretaker = NULL
5. JOIN new caretaker labels to the trees: UPDATE trees t SET caretaker = 'Bezirk XY' FROM caretaker_ids c WHERE t.gmlid = c.id
6. Delete the no longer needed table: DROP TABLE caretaker_ids

Contributors ✨

Thanks goes to these wonderful people (emoji key):

Lisa-Stubert 🔣 💻 📖

Lucas Vogel 📖

Julia Zet 💻 📖

This project follows the all-contributors specification. Contributions of any kind welcome!

Credits

A project by:

Supported by: