TileMill project and SQL script to generate map tiles as seen on http://citysdk.waag.org/buildings/

README.md

Buildings in the Netherlands by year of construction

Map can be seen on http://citysdk.waag.org/buildings/. The map shows all 9,866,539 buildings in the Netherlands, shaded according to year of construction. Data from BAG, via CitySDK. Map made with TileMill by Bert Spaan, Waag Society, inspired by BKLYNR.

This README file explains how to get the data, create the map and export high-res PNG and PDF files.

Download and import BAG data

First, install PostgreSQL and PostGIS. To download and import the latest BAG data into a local database, you can use the data, tools and tutorials provided by NLExtract. The scripts in this gist expect a database named bag.

Create buildings table

To create a map with buildings by year of construction (or area and function), execute the SQL from buildings.sql in this gist.

Create TileMill project and map tiles

Install TileMill, create a new project and replace the contents of the newly created project directory (usually in ~/Documents/MapBox/project), with the two files style.mss and project.mml from this gist. The TileMill project connects with PostgreSQL using user postgres and password postgres. Edit project.mml to change user and password if needed.

High-res exports

TileMill lets you export high-res images from the command line: http://gis.stackexchange.com/questions/52401/how-to-automate-export-in-tilemill

buildings.sql

-- Aggregate mode function, to compute modal area and function
-- From: http://wiki.postgresql.org/wiki/Aggregate_Mode
CREATE OR REPLACE FUNCTION _final_mode(anyarray)
  RETURNS anyelement AS
$BODY$
    SELECT a
    FROM unnest($1) a
    GROUP BY 1 
    ORDER BY COUNT(1) DESC, 1
    LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE mode(anyelement) (
  SFUNC=array_append, --Function to call for each row. Just builds the array
  STYPE=anyarray,
  FINALFUNC=_final_mode, --Function to call after everything has been added to array
  INITCOND='{}' --Initialize an empty array when starting
);

CREATE SCHEMA tilemill;

CREATE TABLE tilemill.pand AS
SELECT 
  p.identificatie::bigint, bouwjaar::int, 
  ST_Transform(p.geovlak, 4326) AS geom,
  round(mode(oppervlakteverblijfsobject)) AS oppervlakte,
  mode(vg.gebruiksdoelverblijfsobject::text) AS gebruiksdoel
FROM verblijfsobjectactueelbestaand v
JOIN verblijfsobjectpandactueel vp 
  ON vp.identificatie = v.identificatie
JOIN pandactueelbestaand p 
  ON vp.gerelateerdpand = p.identificatie
JOIN verblijfsobjectgebruiksdoelactueel vg 
  ON v.identificatie = vg.identificatie
GROUP BY 
  p.identificatie, bouwjaar, p.geovlak;

CREATE INDEX pand_geom_idx
  ON tilemill.pand
  USING gist (geom);

project.mml

{
  "bounds": [
    3.197,
    50.6947,
    7.3718,
    53.7357
  ],
  "center": [
    4.9544,
    52.2855,
    14
  ],
  "format": "png",
  "interactivity": false,
  "minzoom": 8,
  "maxzoom": 16,
  "srs": "+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over",
  "Stylesheet": [
    "style.mss"
  ],
  "Layer": [
    {
      "extent": [
        3.35892515241211,
        50.7508812472414,
        7.22599197370906,
        53.4973814976261
      ],
      "Datasource": {
        "type": "postgis",
        "table": "(SELECT identificatie, bouwjaar, geom FROM tilemill.pand) AS p",
        "key_field": "",
        "geometry_field": "",
        "extent_cache": "auto",
        "extent": "3.35892515241211,50.7508812472414,7.22599197370906,53.4973814976261",
        "host": "localhost",
        "dbname": "bag",
        "user": "postgres",
        "password": "postgres",
        "id": "pand",
        "project": "bag",
        "srs": "+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs"
      },
      "id": "pand",
      "class": "",
      "srs-name": "WGS84",
      "srs": "+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs",
      "advanced": {},
      "name": "pand"
    }
  ],
  "scale": 1,
  "metatile": 2,
  "name": "BAG - Bouwjaar",
  "description": "",
  "attribution": ""
}

style.mss

Map {
  background-color: #000;
}

#pand {
  [bouwjaar<1800] {
    polygon-fill: #A50026;
  }
  [bouwjaar>=1800][bouwjaar<1850] {
    polygon-fill: #D73027;
  }
  [bouwjaar>=1850][bouwjaar<1900] {
    polygon-fill: #F46D43;
  }
  [bouwjaar>=1900][bouwjaar<1930] {
    polygon-fill: #FDAE61;
  }
  [bouwjaar>=1930][bouwjaar<1945] {
    polygon-fill: #FEE090;
  }
  [bouwjaar>=1945][bouwjaar<1960] {
    polygon-fill: #FFFFBF;
  }
  [bouwjaar>=1960][bouwjaar<1975] {
    polygon-fill: #E0F3F8;
  }
  [bouwjaar>=1975][bouwjaar<1985] {
     polygon-fill: #ABD9E9;
  }
  [bouwjaar>=1985][bouwjaar<1995] {
    polygon-fill: #74ADD1;
  }
  [bouwjaar>=1995][bouwjaar<2005] {
    polygon-fill: #4575B4;
  }
  [bouwjaar>=2005] {
    polygon-fill: #313695;
  } 
  
}