Category Archives: open data

Transparenzportal Hamburg API: Alle Datensätze eines bestimmten Hosts

Let’s take it to the next level: Wir wollen alle auf gehosteten Datensätze, weil da die ganzen schicken Geodaten sind. Wir müssen also einen Query bauen, der uns alle Datensätze gibt, die “^” in der resources.url haben.

Mit kann man komplexe Queries schreiben, sagt . Mit ein bisschen Scrollen stößt man gegebenenfalls auf resource_search und über Google nach “ckan resource_search” auf, dessen Query man dann nimmt und sich damit nach durchhangelt. Voll einfach! … Der Query dauert mehrere Sekunden und scheint ALLE Hits zurückzugeben, super!

Download läuft, so langsam wie eben leider ist:

Insgesamt sind es rund 104 Gigabyte, allerdings inklusive einiger Duplikate. Übrigens stecken auch SHA256-Hashes in den Daten, praktisch zum Überprüfen der Downloads.

Ugly-but-does-the-job URLs rausziehen:
$ cat\ | json_pp | grep '"url"' | grep -Eo 'http.*"' | sed 's#"$##' > urls

Transparenzportal Hamburg API: Bisschen Basics

Grundlegende Links:

Die Links zu den Daten stecken in den Resources der Packages, z.B.:

import urllib.request
import json
url = ""
with urllib.request.urlopen(url) as req:
	response =
	response_dict = json.loads(response.decode('utf-8'))
	assert response_dict['success']
result = response_dict['result']
resources = result['resources']
for resource in resources:

gibt uns

Das ist doch schon mal was.

Fancy Free File Formats For Hamburg’s Open Geodata

While this is about data of the city of Hamburg, Germany, I decided to post in English as GeoPackage Propaganda should be accessible. ;)

I am casually working on converting open geodata released via the Transparenzportal Hamburg to more usable GeoPackages, GeoTIFFs and similar formats with free and open-source tools like GDAL and GMT where possible. This includes things like orthophotos, ALKIS, addresses, DEM, districts etc. You can get a list of most available source data here but there are some datasets “hidden” in other categories as well. The data is usually released in GML or as gridded files (e.g. JPEG or XYZ tiles/files). While this is pretty much perfect as source formats, working with them is cumbersome. My goal is to make this data more accessible for anyone in tools like QGIS.

For now you can find the 20cm orthophotos for 2013-2015 and the 1m DEM in Mind the licenses, see the readme file for a bit of info. More to come, I want to plan the pipeline a bit better first though. There should be a full script & log from source to GeoPackage for each file.

I will also provide mirrors of the source files. If you want to collaborate, please contact me. Apart from Hamburg I will also add free/open datasets for the whole of Germany, things related to (nearby) bathymetry and some global ones. If you want Shapefiles, ECW, MrSID or similar, you can pay me for converting.

Oakland: All license plate reader data (ALPR)

WTF! via

817159 timestamped car plate locations.

oakland scans
oakland selected scans

Pages:” clas


for id in ${ids}
echo “Grabbing ${id}”
wget -a wget.log -x –content-disposition “${id}/rows.csv?accessType=DOWNLOAD”
# screw excel wget -a wget.log -x –content-disposition “${id}/rows.csv?accessType=DOWNLOAD&bom=true”
wget -a wget.log -x –content-disposition “${id}/rows.json?accessType=DOWNLOAD”
wget -a wget.log -x –content-disposition “${id}/rows.pdf?accessType=DOWNLOAD”
wget -a wget.log -x –content-disposition “${id}/rows.rdf?accessType=DOWNLOAD”
wget -a wget.log -x –content-disposition “${id}/rows.rss?accessType=DOWNLOAD”
wget -a wget.log -x –content-disposition “${id}/rows.xls?accessType=DOWNLOAD”
wget -a wget.log -x –content-disposition “${id}/rows.xlsx?accessType=DOWNLOAD”
wget -a wget.log -x –content-disposition “${id}/rows.xml?accessType=DOWNLOAD”
wget -a wget.log -x –content-disposition “${id}/rows.xml?accessType=DOWNLOAD”


Be aware that only the CSV format is guaranteed to have all records. At least that’s what some files say.

$ sed ‘s#,.*##g’ *.csv | sort | uniq | wc -l

sed ‘s#,.*##g’ *.csv | sort | uniq -c | sort -h | tail
grep -h ‘^PLATENUMBER,’ *.csv | sed ‘s#,”(#,#g’ | sed ‘s#)”##’
grep -hEo “\(37.*\)\”” *.csv | sed ‘s#[()” ]*##g’

Happy stalking :(

UTM coordinate grids of Hamburg

The coordinates are wrong, I should have be xmin and ymin to match the official grid. Will update the PDFs soonish, sorry!

The LGV offers their official UTM grid for Hamburg in the Transparenzportal. Since many datasets are indexed by those grid tiles, it can be handy to have a quick references. Queue QGIS!

Load the layers “utm_raster1km any” and “utm_raster2km any” of the GML file. The CRS is EPSG:25832. Set their styles to have no fill.

Label with substr(x_min($geometry),0,4) || '\n' || substr(y_min($geometry),0,5) to truncate the coordinate display to just the interesting bits, the three leading numbers of X and the four leading numbers of Y.

Print them. You now have nice maps of the grid that you can use as reference when browsing through files with names like, LoD1_571_5939_1_HH.xml or dop20c_32576_5953.jpg (ignore the leading 32…).

I added the Stadtteile as background (and Wished QGIS could style by the 4 color theorem).

Click to download PDFs (they should be DIN A4, ask the composer why they are not):

Merge GML files into one SQLite or Spatialite file

For example the buildings in Hamburg, Germany:

for file in *.xml
 if [ -f "${layer}".spatialite ]
  ogr2ogr -f "SQLite" -update -append "${layer}".spatialite "${file}" "${layer}" -dsco SPATIALITE=YES
  ogr2ogr -a_srs EPSG:25832 -f "SQLite" "${layer}".spatialite "${file}" "${layer}" -dsco SPATIALITE=YES

Remove the -dsco SPATIALITE=YES and change the output filename for SQLite. QGIS can work with both.

$ bash AX_Gebaeude

Be aware that Spatialite is much more sensitive to geometric problems. You might get things like

ERROR 1: sqlite3_step() failed:
ax_gebaeude.GEOMETRY violates Geometry constraint [geom-type or SRID not allowed] (19)
ERROR 1: ROLLBACK transaction failed: cannot rollback - no transaction is active
ERROR 1: Unable to write feature 1712 from layer AX_Gebaeude.

ERROR 1: Terminating translation prematurely after failed translation of layer AX_Gebaeude (use -skipfailures to skip errors)

but on the other hand, you get spatial indexing which makes queries or high zoom interaction much quicker.

Be aware that if you try to merge files into a Shapefile and fields are getting truncated, those fields will only be filled with data for the first file you merge. On the later files OGR will try to match the input field names to the merged file’s fieldnames, notice the difference and discard them. If you still want to convert to Shapefiles, check out the -fieldTypeToString IntegerList,StringList options.

A GeoTIFF of the German 2011 census population density

tl;dr: GMT is documented for people who use it since the 80s.

update 2: You can use gmtinfo to calculate the extents, using -I- will make it output the -R parameter! xyz2grd $(gmtinfo -I- *.xyz) ...

update: xyz2grd supports GeoTIFF via its GDAL driver (now?)! For example -Gfile.tif=gd:GTiff. See eg So the way below is overly complicated. I do not know how to apply the advanced settings of GDAL though like compression and prediction etc.


The Statistische Ämter des Bundes und der Länder offer a 100 meter grid of Germany’s population density: (110MB). Datensatzbeschreibung_Bevoelkerung_100m_Gitter.xlsx provides additional information.

Let’s turn that dataset into a GeoTIFF so we can use it in our GIS. We will use free and open-source tools from GMT and GDAL. GDAL loves to interpolate values but our data is discrete/regular. We do not want any kind of interpolation. So xyz2grd from GMT is the best choice for turning the xyz data into a “continuous” GIS format (tell me if not).

Getting to know the data

Inside the zip is a 1.3GB file Zensus_Bevoelkerung_100m-Gitter.csv with about 36 million lines.



Datensatzbeschreibung_Bevoelkerung_100m_Gitter.xlsx says the coordinates are in ETRS89-LAEA Europe – EPSG:3035.

First we need to find out the geographic extends of the data, you could use your favourite cli tools for that, I wrote a quick .vrt file and used ogrinfo on that:

$ cat Zensus_Bevoelkerung_100m-Gitter.csv.vrt

 <OGRVRTLayer name="Zensus_Bevoelkerung_100m-Gitter">
  <GeometryField encoding="PointFromColumns" x="x_mp_100m" y="y_mp_100m" />

$ ogrinfo -al Zensus_Bevoelkerung_100m-Gitter.csv.vrt

INFO: Open of `Zensus_Bevoelkerung_100m-Gitter.csv.vrt’ using driver `VRT’ successful.

Layer name: Zensus_Bevoelkerung_100m-Gitter
Geometry: Point
Feature Count: 35785840
Extent: (4031350.000000, 2684050.000000) – (4672550.000000, 3551450.000000)

Creating a netcdf/grd file from the xyz data with xyz2grd

xyz2grd wants xyz, nothing else. The Gitter_ID_100m column is redundant in any case, you can calculate it yourself from the x and y fields if needed. So first let’s convert it to a “x y z” format with awk. The separator is ;

awk 'FS=";" {print $2" "$3" "$4}' Zensus_Bevoelkerung_100m-Gitter.csv >

Now we can write our xyz2grd commandline.

We have our extends:

We know the spacing is 100 units (meters):

There is one header line:

And of course we know that we want a “classic” netcdf4 chunk size, whatever that means. We knew that right away, not after googling helplessly for an hour and eventually finding the hint on some mailing list. Not knowing this might have lead to QGIS only seeing NaN values for z, R’s ncdf/raster saying “Error in substr(w, 1, 3) : invalid multibyte string at ‘<89>HDF” and GDAL “0ERROR 1: nBlockYSize = 130, only 1 supported when reading bottom-up dataset”.

The resulting commandline:
xyz2grd -Vl -R4031350/4672550/2684050/3551450 -I100 -h1 --IO_NC4_CHUNK_SIZE=c -GZensus_Bevoelkerung_100m-Gitter.cdf

You can inspect the file with grdinfo and gdalinfo now if you want.

Creating a GeoTIFF from the netcdf/grd file

Let’s turn it into a GeoTIFF with gdal_translate. We will need a bunch of commandline parameters.

The spatial reference system is EPSG:3035, so:
-a_srs EPSG:3035

Values of -1 mean “no data”:
-a_nodata -1

TIFF is uncompressed by default, we want good lossless compression:

The resulting commandline:
gdal_translate -co COMPRESS=DEFLATE -a_srs EPSG:3035 -a_nodata -1 Zensus_Bevoelkerung_100m-Gitter.cdf Zensus_Bevoelkerung_100m-Gitter.tif

The resulting file is about 8 Megabytes and should work in any reasonable GIS. Have fun!

TODO: What license is this now?


Feinstaub zum Jahreswechsel 2014/2015

Auf den großartigen Seiten des Hamburger Luftmessnetz kann man schöne Diagramme der verschiedenen Messwerte sehen, zum Beispiel von Feinstaub: PM10 oder PM2,5. Leider kann man nicht auf Diagramme sämtlicher Stationen verlinken, stattdessen muss der Benutzer sie per Hand zusammenstellen. Weil ich die Kurven gerne auf /r/dataisbeautiful verlinken wollte und die neuen Regeln dort etwas eigen sind, habe ich die Diagramme einfach mal mit Gnumeric nachgebaut.

Erstmal die Teilchen mit einem aerodynamischen Durchmesser von weniger als 10 Mikrometer (10 µm), PM10. Hiervon darf 35 mal Im Jahr ein Tagesmittelwert von 50µg/m³ überschritten werden.

Feinstaub in Hamburg, Silvester 2014_2015 PM10

Und dann noch die fieseren PM2,5 (kleiner 2,5µm), diese werden nur an drei Stationen gemessen:

Feinstaub in Hamburg, Silvester 2014_2015 PM2,5

In Berlin gibt es diese Werte leider nur täglich, aber der Feinstaub-Monitor der Berliner Morgenpost ist einen Blick wert (auch wenn er leider 2014 zu Ende gegangen ist?).

English version: This image shows the amount of particulate matter with a diameter of 10 micrometres or less in the days before and during new year 2014/2015 in Hamburg, Germany. This image shows the amount of particulate matter with a diameter of 2.5 micrometres or less.

Auch bundesweit gibt es schöne Werte, leider nur täglich. Das Umweltbundesamt stellt interpolierte Karten bereit, ich hab die letzten vier Tage mal zusammengefasst:
Feinstaub in Deutschland, Silvester 2014_2015

Schade, dass die Farbskala ein fixes Maximum hat, die Werte lagen ja wohl eher über 50µg/m³.

Wie auch immer, frohes neues!

Wunschzettel für das Hamburger Transparenzportal

Lieber Transparenzmensch, ich wünsche mir für das Hamburger Transparenzportal:

  • Dateien sollten sinnvoll benannt werden. mag im originalen Kontext funktionieren, aber ohne Datum, Herkunft oder Readme ist das Archiv für sich nicht sehr offensichtlich. Dass die Dateien darin dann zum Beispiel Poldata_poly.gml heißen, hilft dem Unkundigen nicht wirklich weiter. Mithilfe von Namespaces, zum Beispiel der veröffentlichenden Stelle, dem Datum usw wären lesbare, informative Dateinamen möglich.
  • Jede heruntergeladene Datei sollte auf irgendeine Weise Informationen über ihre Herkunft und Lizenz beinhalten. Bei Archiven wäre dies einfach über eine Textdatei mit demselben Namen möglich. Am besten auch noch gleich die Herkunfts-URL(s) mit rein. Ja, auch CSV-Dateien können mehr als Daten enthalten, einfach ein paar Headerzeilen rein und fertig.
  • Mehr Daten, weniger PDFs bitte. Du könntest doch bestimmt auch viel besser arbeiten, wenn alle Kinder ihre Wunschzettel standardisiert als Textdateien oder schönen strukturierten, freien Formaten einreichen würden oder?
  • Weniger Javascript, mehr Performanz für das Transparenzportal.
  • Mehr Homogenität in den Metadaten. Baugenehmigungen haben schöne beschreibende Titel, öffentliche Beschlüsse dagegen sind “total langweilige Nummern”.
  • Sortierung nach dem Datum der Daten, nicht nur der Veröffentlichung.
  • Eine schöne Einführung in die API, mit vielen Beispielen und Codeschnipseln.
  • Volltextsuche in allen Dokumenten, nicht nur den Metadaten im Portal. Ja, das wäre wirklich schön…
  • Redundanz! Die Downloads sollten redundant (und performant) vorgehalten werden, es nervt etwas, wenn man 10 Gigabyte Luftbilder mit 500 Kilobytes/s herunterladen muss oder wegen Wartungsarbeiten nicht an die Daten kommt.
  • Jeder Eintrag sollte einen direkten Link zum Download haben.
  • Jede Datei sollte mit einem Hash veröffentlicht werden (SHA1 oder SHA256 oder sowas). Änderungen von Daten müssen offensichtlich gemacht und dokumentiert werden. Daten dürfen nicht “heimlich” geändert werden, auch wenn es nur der erneute Upload eines defekten Archivs ist.
  • Und sag mir doch bitte unter welcher Lizenz die Texte und Grafiken im Transparenzportal selbst verfügbar sind?

Wie auch immer, ich danke dir für die vielen Stunden, die ich mit deinen Datengaben dieses Jahr schon spielen konnte. Schenkst du uns nächstes Jahr die Verpflichtung der Anstalten öffentlichen Rechts zur Veröffentlichung? Die drücken sich im Moment noch. Und sag bitte dem Bundestransparenzmenschen, dass die MTS-K-Daten doch nun wirklich einfach freigegeben werden sollten. Das wäre auch für die dortigen Mitarbeiter eine enorme Arbeitsentlastung.