At the OK Lab Berlin we decided to have a focus on a particular dataset in the year 2016. The dataset: A list of all trees that are managed by the city¹. This list contains roughly 300,000 trees and is available at the official geodata portal the FIS-Broker².

The data is published as a Web Feature Service and we made it our first goal to turn the data into a nice geojson API. The results can be found here.

Now the question was: What do we actually want to do with this? For a long time we had a lot of ideas but did not actually do anything. At some point someone proposed creating a video that shows all the trees by plantation year. I thought this was quite a nice idea so set out to create a video that shows just this. In the following I will describe how I did it by using mapnik and some glorious shell scripts³.

The result looks as follows:

Downloading the data

The first step was to download the data.

for i in {1880..2016}
do
  echo $i
  curl "https://trees.codefor.de/api/trees/?format=json&page_size=1000000&year_of_planting=$i" -o data/$i.json;
done

Here we make one API request for each year between 1880 and 2016. The request filters all the data by plantation year and asks for a json response.

Rendering PNGs with mapnik and nik4

Creating the styles

The default solution for rendering geospatial data into images is mapnik.

Mapnik requires the user to pass a stylesheet. This stylesheet contains the path to the data along with instructions for how to render it. Since we want to render 136 different datasets (one for each year) I wrote a small template where the PATH and the YEAR strings will be replaced before rendering the images.

The sytlesheets look something like this:

<Map srs="+init=epsg:3857" background-color="#ffffff">
  <Style name="trees" filter-mode="first">
    <Rule>
      <MarkersSymbolizer width="5" fill="#009900" stroke="#881133" allow-overlap="true" ignore-placement="true" />
    </Rule>
  </Style>
  <Layer name="trees" srs="+init=epsg:4326">
      <StyleName>trees</StyleName>
      <Datasource>
        <Parameter name="file">PATH/data/YEAR.json</Parameter>
         <Parameter name="type">geojson</Parameter>
      </Datasource>
  </Layer>
</Map>

There is not a lot going on in this template. We supply a Datasource and instructions for how to style those datapoints. Note that the PATH and YEAR strings will be replaced with their respective content when rendering so that a line might look something like this: <Parameter name="file">/Users/knut/projects/baumvideo/data/1993.json</Parameter>

We also supply some Rules in the Style tag that describe that we would like to have green dots as markers for our trees and allow those dots to overlap.

In the video you can also see the boroughs of Berlin which I left out in the extract here but they are handled in a very similar manner. You can check out the full template on github.

There is one thing that is not so easy to understand: The srs attributes in Map and Layer. In this context srs stands for Source Reference System. We need those attributes because of our favorite topic: Map Projections 🌍! As you might know the earth is not only not flat but also not exactly spherical. Whenever we want to project the earth into something that is 2-dimensional we need to convert the data into a 2D reference system. This is known as projecting the data. The problem with projections: They are always wrong in some way. A projection for example cannot be true to area and local shape. If this sounds interesting to you you should read the Wikipedia article.

In order to circumvent these problems it is generally a good Idea to use a projection that covers an area that is only as big as necessary (because smaller areas are flatter so that the distortion is not as bad). In Berlin traditionally the reference system used to be called Soldner although this has been replaced more and more by the ETRS89 reference system (created in 1989) that has been defined to make geodata more consistent within the European Union. The ETRS89 system has multiple zones and the zone that is applicable to Berlin is number 33.

As you can see it can easily become a little overwhelming to keep track of all of those different systems. In order to bring some structure into this sytems the European Petroleum Survey Group invented a system where each reference is assigned a so called EPSG number. Those numbers and the defintions for their underlying projections can be serached nicely at epsg.io.

Since the data that we got from the city in the beginning was published in the EU it was referenced in the ETRS89 / UTM zone 33N system. Because our primary goal was to make this data usable as geojson we converted it from ETRS89 to the WGS84 system as defined in the GeoJSON Specification.

In our template we now specify that our source data (this is the geojson) in Datasource is referenced in WGS84 (EPSG:4326) and our target images should be rendered in the old Soldner system (EPSG:3068). This is specified on the Map. We could also have used the new ETRS/UTM sytem but I thought it would be nice to use the old format here. If we were to render tiles to be used in web-maps we would have used EPSG:3857.

Using mapnik to render the images

Now that we have our style template defined we can go on and actually render one image for each year.

import os
import mapnik

filepath = os.path.dirname(os.path.abspath(__file__))

with open("template.xml") as template_file:
    template = template_file.read()

for year in range(1880, 2017):
    m = mapnik.Map(1800, 1200)
    instance = template.replace("YEAR", str(year)).replace("PATH", filepath)
    mapnik.load_map_from_string(m, instance)
    m.zoom_all()
    mapnik.render_to_file(m, "single/{}.png".format(year))
    print("Rendered {}".format(year))

We load our template and iterate through all the years for which we have data. Then we tell Mapnik to create a new Map with dimensions 1800x1200px, replace the PATH and YEAR placeholders, zoom to the data and render out images.

After the script is run we have one file for each year in the rendered folder.

Adding years to the images

The images that we created now are nice but when making them into a video I realized that it was quite easy to lose the overview about which year was generated. That is why I added an additional step which uses imagemagicks’s conert to add the respective year to the top left. The code required looks like this:

for i in {1880..2016}
do
  convert -pointsize 20 -draw "text 30,50 \"${i}\" " rendered/$i.png rendered/$i.png
done

Rendering the video with ffmpeg

The final step was combining all the images into one video. I used ffmpeg to do this.

ffmpeg -framerate 2 -start_number 1880 -i './rendered/%04d.png' -r 30  -pix_fmt yuv420p trees.mp4

And there we have our video! 🚀

^{1. The data is actually split into two parts. The street trees and the park trees↩}

^{2. See here for a slippy map view of the complete data. ↩}

^{3. The complete source code can be found in my github repository baumvideo ↩}