Brandweer opent data - Deel 1: van tabel tot kaart

31 okt 2013 Edward Mac Gillavry

Onlangs maakten we een visualisatie van alle uitrukken van de Brandweer Amsterdam-Amstelland vanaf januari 2006 tot september 2010. De gegevensbron achter de visualisatie was vrijgegeven als CSV-bestand. GeoJSON krijgt steeds meer aandacht als een bestandsformaat om geografische gegevens binnen web-applicaties te gebruiken. Dit experiment legt uit hoe de gegevens hebben omgezet naar dit formaat.

Voorbereidingen

Aangezien Brandweer Amsterdam-Amstelland de gegevens al in 2011 hadden vrijgegegven in het kader van de Apps for Amsterdam-wedstrijd, zijn we natuurlijk niet de eersten die de gegevens onder handen hebben genomen. Indertijd hebben de mensen van Hack de Overheid al een eerste poging gedaan om het originele CSV-bestand te begrijpen en zij bieden nu een aangepaste versie ter download op hun eigen website aan.

Incident Id,Nr Incident,Dtg Start Incident,Dtg Einde Incident,Brw Melding Cl,Brw Melding Cl1,Brw Melding Cl2,Naam Locatie1,Postcode4pp,Plaats Naam,Gemeente Naam,T X Coord Loc,T Y Coord Loc
622.659.209,1,01-01-2006 00:00,01-01-2006 00:25,Brand,Buitenbrand              brand,Overig                  buitbr,SINT WILLIBRORDUSDWARSSTRAAT,,AMSTERDAM,AMSTERDAM,122.094,485.317
622.660.251,2,01-01-2006 00:03,01-01-2006 00:25,Brand,Binnenbrand              brand,.Wonen                   binbr,BERNARD LODERSTRAAT,1063,AMSTERDAM,AMSTERDAM,116.315,488.262
...
641.256.091,11.519,15-09-2010 09:54,15-09-2010 11:14,Dienstverlening,Algemeen                dienst,Ass. Politie          Algemeen,BOOMSTRAAT,1015,AMSTERDAM,AMSTERDAM,120.725,488.028
641.255.950,11.521,15-09-2010 10:39,15-09-2010 11:13,Dienstverlening,Burger                  Dienst,Buitensluiting          Burger,VAN DER HOOPLAAN,1185,AMSTELVEEN,AMSTELVEEN,118.681,478.956

Nadat we het ZIP-bestand hadden uitgepakt en het CSV-bestand hadden geopend, leek het ons interessant om de volgende kolommen in de tabel te gebruiken voor onze visualisatie:

  • Incident Id (unieke code voor een uitruk)
  • Brw Melding Cl (type uitruk)
  • Dtg Start Incident (starttijd)
  • T X Coord Loc (X-coördinaat)
  • T Y Coord Loc (Y-coördinaat)

Aan de slag dan maar? Niet helemaal! Zo viel het ons op, dat de meeste kolomnamen spaties bevatten. Bovendien zijn de waarden in sommige kolommen genoteerd volgens de Nederlandse taalinstellingen:

  1. Alle numerieke waarden bevatten een scheidingsteken voor duizendtallen (een punt in het Nederlandse taalgebied)
  2. De tijdnotatie is dd-mm-yyyy hh:mm
  3. De coördinaten zijn in het Nederlandse referentiesysteem: het Rijksdriehoekstelsel (RD)

Dit is natuurlijk erg makkelijk voor ons, maar de meeste software is niet in staat om deze notaties meteen te begrijpen. Aan de slag, dus!

Notaties veranderen met sed en OGR

Gelukkig is de punt in de gegevens alleen als scheidingsteken van duizendtallen. Omdat ze geen andere betekenis hebben kunnen we ze zonder pardon uit het CSV-bestand verwijderen. In plaats van alle gegevens regel voor regel aan te passen, gebruiken we het tekstverwerkingsprogrammaatje sed om alle punten in een keer te verwijderen:

sed 's/\.//g' BrwAAincidenten_reshaped.csv > calloutscleaned.csv

Zo is ons eerste punt met betrekking tot de Nederlandse taalinstellingen weggewerkt. Kijk eens aan: geen punten meer!

Incident Id,Nr Incident,Dtg Start Incident,Dtg Einde Incident,Brw Melding Cl,Brw Melding Cl1,Brw Melding Cl2,Naam Locatie1,Postcode4pp,Plaats Naam,Gemeente Naam,T X Coord Loc,T Y Coord Loc
622659.209,1,01-01-2006 00:00,01-01-2006 00:25,Brand,Buitenbrand              brand,Overig                  buitbr,SINT WILLIBRORDUSDWARSSTRAAT,,AMSTERDAM,AMSTERDAM,122094,485317
622660.251,2,01-01-2006 00:03,01-01-2006 00:25,Brand,Binnenbrand              brand,.Wonen                   binbr,BERNARD LODERSTRAAT,1063,AMSTERDAM,AMSTERDAM,116.315,488.262
...
641256091,11519,15-09-2010 09:54,15-09-2010 11:14,Dienstverlening,Algemeen                dienst,Ass. Politie          Algemeen,BOOMSTRAAT,1015,AMSTERDAM,AMSTERDAM,120725,488028
641255950,11521,15-09-2010 10:39,15-09-2010 11:13,Dienstverlening,Burger                  Dienst,Buitensluiting          Burger,VAN DER HOOPLAAN,1185,AMSTELVEEN,AMSTELVEEN,118681,478956

Het aanpassen van de tijdnotatie moet niet alleen voor iedere regel gebeuren, maar ook in specifieke kolommen. Bovendien willen we de coördinaten herprojecteren vanuit het Nederlandse referentiesysteem naar WGS 84, het standaard referentiesysteem van GeoJSON. Deze uitgangspunten leiden tot het scenario, waar we de kracht van OGR Simple Feature Library dat deel uitmaakt van GDAL, the Geospatial Data Abstraction Library ten volle kunnen toepassen:

  1. OGR leest en schrijft niet-geografische tabellen in CSV-bestanden
  2. OGR vertaalt de kolommen met X- en Y-coördinaten uit een CSV-bestand naar geografische punten met behulp van de VRT driver
  3. OGR voert selecties uit die zijn meegegeven met de -sql optie
  4. OGR herprojecteert tussen referentiesystemen, die worden meegegeven met de -s_srs en -t_srs opties

Eerst maken we calloutscleaned.vrt, een VRT-bestand om een geografische laag te maken uit een platte tabel met coördinaten in verschillende kolommen. We gebruiken het VRT-bestand ook om de informatie over het Nederlandse referentiesysteem aan de coördinaten in het CSV-bestand te koppelen:

<OGRVRTDataSource>
    <OGRVRTLayer name="calloutscleaned">
        <SrcDataSource>calloutscleaned.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <GeometryField encoding="PointFromColumns" x="T X Coord Loc" y="T Y Coord Loc"/>
        <LayerSRS>+proj=sterea +lat_0=52.15616055555555 +lon_0=5.38763888888889 +k=0.9999079 +x_0=155000 +y_0=463000 +ellps=bessel +units=m +towgs84=565.2369,50.0087,465.658,-0.406857330322398,0.350732676542563,-1.8703473836068,4.0812 +no_defs</LayerSRS>
    </OGRVRTLayer>
</OGRVRTDataSource>

Het VRT-bestand bevat bovendien informatie over de laagnaam (<OGRVRTLayer>) en de naam van het CSV-bestand (<SrcDataSource>). De tag <GeometryType> geeft aan, dat de gegevens betrekking hebben op puntlocaties. De <GeometryField> tags vertellen OGR welke kolommen in het CSV-bestand de coördinaten bevatten. Ten slotte bevat de tag <LayerSRS> een zogenaamde Well-Known Text (WKT)-tekstregel met alle parameters die het Rijksdriehoekstelsel beschrijven.

Nu we het VRT-bestand calloutscleaned.vrt hebben, kunnen we de andere punten die betrekking hebben op de Nederlandse taalinstellingen in een keer aanpakken. Hier is het eenregelige commando dat we hebben gebruikt om met het VRT-bestand een GeoJSON-bestand te maken. Maak je geen zorgen, we zullen in het volgende stukje dit eenregelige commando onder de loep nemen!

ogr2ogr -f GeoJSON -t_srs EPSG:4326 -sql "SELECT 'Incident Id' AS cid, 'Brw Melding Cl' AS class, CONCAT(SUBSTR('Dtg Start Incident',7,4), '-', SUBSTR('Dtg Start Incident',4,2),'-',SUBSTR('Dtg Start Incident',1,2),SUBSTR('Dtg Start Incident',11,6)) AS starttime FROM calloutscleaned WHERE ('T X Coord Loc' != '') AND (CAST('T X Coord Loc' AS integer) > 0) AND (CAST('T X Coord Loc' AS integer) < 300000) AND (CAST('T Y Coord Loc' AS integer) > 289000) AND (CAST('T Y Coord Loc' AS integer) < 629000)" callouts.geojson calloutscleaned.vrt -lco COORDINATE_PRECISION=6 -skipfailures
-f
Zo weet OGR dat een GeoJSON-bestand moet worden gemaakt.
-t_srs
Dit vertelt OGR om de coördinaten te herprojecteren naar WGS 84. Met deze EPSG-codes worden gebruikt om referentiesystemen aan te duiden. De code 4326 duidt het referentiesysteem WGS 84 aan.
-sql
Dit is de SQL selectie waarmee OGR vertelt hoe de notatie van de verschillende kolommen wordt aangepast. We hernoemen de kolommen die spaties bevatten met AS en veranderen de tijdnotatie naar de yyyy-mm-dd hh:mm-notatie (SQL 92 standaard voor het datatype TIMESTAMP). Ten slotte controleren we of de coördinaten überhaupt binnen het geldingsgebied van het Rijksdriehoekstelsel vallen. Hierdoor blijven er nog 62415 van de 63016 uitrukken over.
-lco
Deze optie is specifiek voor het bestandsformaat GeoJSON en vertelt OGR hoe veel decimalen van de coördinaten moeten worden weggeschreven naar het uitvoerbestand. Deze optie verkleint het uitvoerbestand met een extra extra megabyte vergeleken met de standaard hoeveelheid decimalen.

GeoJSON op GitHub

Het GeoJSON-bestand callouts.geojson bevat nu de kolommen die we eerder hebben aangemerkt in een meer software-vriendelijke notatie:

{
    "type": "FeatureCollection",
    "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
    "features": [
        { "type": "Feature", "properties": { "cid": "622659209", "class": "Brand", "starttime": "2006-01-01 00:00" }, "geometry": { "type": "Point", "coordinates": [ 4.904198, 52.354767 ] } },
        { "type": "Feature", "properties": { "cid": "622660251", "class": "Brand", "starttime": "2006-01-01 00:03" }, "geometry": { "type": "Point", "coordinates": [ 4.819037, 52.380858 ] } },
        ...
        { "type": "Feature", "properties": { "cid": "641256091", "class": "Dienstverlening", "starttime": "2010-09-15 09:54" }, "geometry": { "type": "Point", "coordinates": [ 4.883828, 52.379048 ] } }
        { "type": "Feature", "properties": { "cid": "641255950", "class": "Dienstverlening", "starttime": "2010-09-15 10:39" }, "geometry": { "type": "Point", "coordinates": [ 4.854789, 52.297384 ] } },
    ]
}

Het GeoJSON-bestand is ook beschikbaar op GitHub. Een mooie bijkomstigheid is, dat GitHub tegenwoordig een GeoJSON-bestand dat is toegevoegd automatisch tot een interactief kaartje maakt. Bovendien groepeert GitHub de locaties die dicht bij elkaar liggen tot proportionele cirkelsymbolen.

Omdat het bestand met alle uitrukken de bestandgrootte van 10Mb overschreidt, werkt deze functionaliteit van Github niet. Daarom hebben we een paar extra GeoJSON-bestanden gemaakt met de uitrukken per jaar. De -sql optie voor het ogr2ogr-commando heben we daarom uitgebreid met een extra WHERE voorwaarde, die het jaar selecteert. Nu hebben we 5 GeoJSON-bestanden die allemaal ruim onder de maximale bestandsgrootte van 10Mb blijven, zodat deze automatisch als een interactief kaartje op GitHub worden getoond!

Denk de volgende keer iets langer na als je gegevens onder een open licentie vrijgeeft. Gebruik algemeen bekende eenheden (zoals bijvoorbeeld lengte- en breedtegraden voor locaties) en gebruik standaardnotaties voor de verschillende types gegevens (bijvoorbeeld tijdsregistratie). Zo zorg je ervoor, dat ontwikkelaars sneller op weg zijn geholpen en meer tijd hebben om mooie toepassen te bouwen.