tilestrata-postgresql

A TileStrata plugin for serving the result of a custom SQL query as a tile when you need more control of the query than tilestrata-postgis-geojson-tiles or tilestrata-postgismvt.

Configuration

• sql (function, required): method that returns a PostGIS query that will be executed. Be sure to protect against sql injection if doing dynamic filtering based on the request query string. The query can contain the following tokens:
  • {bbox} (box2d): the tile bounding box
  • {buffered_bbox} (box2d): the buffered tile bounding box (uses config.buffer)
  • {bbox_3857} (box2d): the tile bounding box in Web Mercator Projection (EPSG:3857)
  • {buffered_bbox_3857} (box2d): the buffered tile bounding box (uses config.buffer) in Web Mercator Projection (EPSG:3857)
  • {z} (int) : the tile zoom level
  • {y} (int) : the tile y coordinate
  • {x} (int) : the tile x coordinate
• pgConfig (object, required): postgres connection options
  • {host} (string)
  • {password} (string)
  • {user} (string)
  • {port} (string)
  • {database} (string)
• contentType (string, required): result MIME type (eg. application/x-protobuf)
• formatResults (function, required): method that returns a string or buffer when given a list of rows

Example

var tilestrataPostgreSQL = require('tilestrata-postgresql');
 
var layer = server.layer('vector-tiles', {minZoom: 5, maxZoom: 14});
 
// .../vector-tiles/tile.pbf
layer
  .route('tile.pbf')
    .use(headers({
      'Access-Control-Allow-Origin': '*'
    }))
    .use(tilestrataPostgreSQL({
      contentType: 'application/x-protobuf',
      sql: function(server, req) {
        return "SELECT ST_AsMVT(q, 'layername') AS tile FROM ( SELECT id, name, ST_AsMVTGeom(ST_Transform(geometry, 3857), {bbox_3857}, 4096, 256, true) FROM tablename WHERE ST_Intersects(geom, {bbox}) ) q";
      },
      formatResults: function(rows) {
          return rows[0] && rows[0].tile
      },
      pgConfig: {
        username: 'postgres',
        password: 'password',
        host: 'localhost',
        port: '5432',
        database: 'postgres'
      }
    }));