TileStache POC

.Tuto.md

Dataviz

## Intro

You've got a GIS dataset, you want to serve a map to visualize it ?

What we need

• A GIS dataset. Here's one

• A postGIS database to store it.

• A map server, in order to serve a visible map. We'll use Mapnik

• A tile server, in order to send the geographic data. We'll use Tilestache.

Infos

• I'm running Ubuntu 14.04 64bit.

Part 1 : The DataBase.

• Let's install postgreSQL : tutorial here

  • PostgreSQL is a database.

• Let's install postGIS : tutorial here

  • Postgis is a plugin for PostgreSQL that handle geolocalised data

• Let's install ogr2ogr : tutorial here

  • ogr2ogr allows to convert geoJSON, topoJSON, and much more to postGIS

• Create a PostGIS data base

• Install the legacy.sql as done here

  • Some of TileStache SQL requests are outdated

• Add your dataset to the database : here

## Part 2 : TileStache

• Install TileStache : Modified version here, branch zoomInVecTile

• Install the gdal library : sudo apt-get install python-gdal

• Edit the tilestache.cfg file.

  • Add a new layer connected to your database : (in layers)

"NewLayerName": { "allowed origin": "*", "provider": { "name": "vector", "driver": "PostGIS", "parameters": { "dbname": "database Name", "host": "Where the db is hosted (localhost for example)", "user": "the user to access the database (postgres by default)", "password": "its password", "query": "A SQL query returning at least coordinates under the name geometry" } } } ```

• Install Mapnik 2.1 : see this issue

• Install shapely : sudo apt-get install python-shapely

• Run the default TileStache server : tilestache/scripts/tilestache-server.py (python2)

• Go on the URL localhost:8080/NewLayerName/{zoom}/{X}/{Y}.geojson

Part 3 : Hexagons

• clone the cartoDB-postgresql repo : here warning : copyright LICENSE

• copy the scripts-available in your project folder

• Add the CDB_Hexagon.sql to your database : psql -d myDataBaseName -a -f CDB_Hexagon.sql

• Add the CDB_XYZ.sql to your database : psql -d myDataBaseName -a -f CDB_XYZ.sql

• Make a sql query that makes hexagons. Look at the tilestache.cfg and hexagon_request.pgsql

## Part 3.5 : Bonus : Frontend

• Install node.js

• Clone this repository

• Run npm install

• Run node index.js -l trees

• Go on your browser, on this url : localhost:3000

## Part 4 : Speedups

Enable caching.

• Example : caching on disk. Add this to the tilestache.cfg :

"cache":
  {
    "name": "Disk",
    "path": "./cache/",
    "umask": "0022",
    "dirs": "portable",
    "gzip": ["xml", "json"]
  },

Get rid of werkzeug.

• Install Gunicorn, for example : pip install gunicorn

• Run TileStache with gunicorn "TileStache:WSGITileServer('../tilestache.cfg')" instead of the script tilestache-server.py

• For the frontend, use node index.js -p 8000 -l trees

Part 5 : Arbitrary aggregates

To do this, we'll use plpython to create arbitrary aggregate functions easily in postgreSQL

• Install plpython : sudo apt-get install postgresql-plpython-9.6

hexagon_request.pgsql

WITH hexagons AS(
    SELECT
        st_setsrid(the_geom, 900913) AS the_geom
    FROM(
        SELECT CDB_HexagonGrid(
            ST_Expand(
                !bbox!,
                greatest(CDB_XYZ_Resolution(!zoom!)) * 50
            ),
            greatest(CDB_XYZ_Resolution(!zoom!)) * 25
        ) AS the_geom
    ) foo
),

trees AS(
SELECT ST_Transform(ST_GeomFromWKB(wkb_geometry, 4326), 900913) AS the_geom FROM environmental_streettrees
    WHERE ST_Expand(
        !bbox!,
        greatest(CDB_XYZ_Resolution(!zoom!)) * 50
    ) && ST_Transform(ST_GeomFromWKB(wkb_geometry, 4326), 900913)
)

SELECT
    hex.the_geom AS __geometry__,
    count(t.the_geom) AS density 
FROM -- Count how many trees there are per hexagon
    hexagons hex,
    trees t
WHERE
    hex.the_geom && t.the_geom
    and st_x(st_centroid(hex.the_geom)) < st_xmax(!bbox!)
    and st_y(st_centroid(hex.the_geom)) < st_ymax(!bbox!)
    and st_x(st_centroid(hex.the_geom)) >= st_xmin(!bbox!)
    and st_y(st_centroid(hex.the_geom)) >= st_ymin(!bbox!)

GROUP BY hex.the_geom

tilestache.cfg

{
  "cache":
  {
    "name": "Test",
    "path": "/tmp/stache",
    "umask": "0000"
  },
  "layers": 
  {
    "osm":
    {
        "provider": {"name": "proxy", "provider": "OPENSTREETMAP"},
        "png options": {"palette": "http://tilestache.org/example-palette-openstreetmap-mapnik.act"}
    },
    "example":
    {
        "provider": {"name": "mapnik", "mapfile": "TileStache/examples/style.xml"},
        "projection": "spherical mercator"
    },
    "trees": {
        "allowed origin": "*",
        "provider": {
            "class": "TileStache.Goodies.VecTiles:Provider",
            "kwargs":
            {
              "dbinfo":
              {
                "host": "localhost",
                "user": "postgres_user",
                "password": "postgres_password",
                "database": "database_name"
              },
              "queries":
              [
                "hexagons_request.pgsql"
              ],
              "clip": false
            }
        }
    }
  }
}