Most of my open data work is with government entities to help change their processes, routines and perceptions to ensure steps towards open by design. I almost never really work with open data itself during those activities. So I decided to accept the challenge we ourselves issued with the launch of the Frisian Open Data Platform.

The challenge was to “find out what the planting year was of the monumental tree that is nearest the street light with the provincial ID number 696502”. Finding that out needed to be done by using data from the Frisian Open Data Platform.

Figuring out which data to use was easy. There is a provincial data set that contains the position and ID’s of all street lights for roads where the province is responsible (other roads can be the responsibility of a municipality, or the national government). There is another data set of the city of Leeuwarden that contains all trees of interest within the city limits. If the street light with the right ID is within city limits, it should be possible to answer the question with the tree data set of Leeuwarden.

So what I did was first look in the provincial data set for the right ID. I copied the coordinates that data set gives for that ID into Google Maps, to see where it is on the map, and it turned out to indeed be within Leeuwarden city limits. So the Leeuwarden tree list contains the answer I’m looking for.

Then I started up Q-GIS, which is an open source geo-data viewer (and in fact, a very capable open source GIS *editor* too, as Peter says in the comments). It is possible to connect a CKAN data portal, such as the Frisian platform is, to Q-GIS. Under the menu-option Plugins in Q-GIS one can install a CKAN plugin, which gives you a CKAN logo button in Q-GIS. Pressing that prompts a dialog in which you can specify the right address for the CKAN server you want to use. This was specified on the Frisian platform as https://ckan.dataplatform.nl/api/3/. I also needed to add a default folder that can be used to keep necessary files.

Now I could search within all the Frisian open data platform data sets right within Q-GIS, using that plugin. I first loaded a map of the Netherlands (the TOP10NL map, which is the most detailed map the Dutch Cadastre provides, as a zipfile of 2GB). I used the PDOK Dutch open geoportal for this, for which I had already previously installed the PDOK plugin, in similar ways as the CKAN plugin). Then I added the Provincial street light list, and the Leeuwarden tree list as layers on the map. I then scrolled the map to the location I had previously checked out in Google Maps.

In the screenshot below you see green dots on the red road. Those are provincial street lights. The rightmost green dot is the one we’re looking for. A bit further to the right you see a row of purple dots. Those are the trees, and one of these is nearest our green dot. Now, I visually judged which purple dot is the nearest, although you could calculate it from the coordinates in the data. Also there is some room for error, as most of the trees in that row were planted at the same time as it turns out. By clicking on one of the dots in Q-GIS you can see the data fields and labels attached to it, and that gave me the year of planting.

The map of Leeuwarden, with the street lights as green dots on the red road in the middle, and the monumental trees as purple dots.

At the top you see the depicted map layers (Dutch map top10nl, trees in ‘bomen’, street lights in ‘provfriesland’), below that, when you highlight a specific purple dot, under identification results (‘identificatieresultaten’), PLANTJAAR is the field with the year of planting.