Vector tiles with PostGIS, PHP and OpenLayers

This is an example about how to create vector tiles with PostGIS 3.3 (and PostgreSQL 15.3), serve them with PHP 8.1 and use them in OpenLayers 7.5. In contrast to the few tutorials available on the internet, it not only uses the latest PostGIS version 3.3, which further simplified the generation of vector tiles by introducing the new function ST_TileEnvelope, but also shows the use case, when you want to create the tiles in a projection other than the default Spherical Mercator coordinate system (EPSG:3857).

Create the tiles with a SQL query

WITH
    /* Prepare your data, in my case:
            - ST_Union: aggregate multiple polygons into one per id
            - ST_Transform: transform geometries to the desired target projection srid:21781
            - ST_ForcePolygonCCW: make sure they are rotated counter clock wise
     */
    mydata AS (
        SELECT ST_ForcePolygonCCW(
            ST_Transform(ST_Union(t.geom), 21781)
        ) geom, someColumn
        FROM someTable t
        GROUP by t.someColumn
    ),

    /* Create the tile envelope with ST_TileEnvelope for use in the function ST_AsMVTGeom below.
     * If you want to use your own projection (EPSG:21781), create the bounds with ST_MakeEnvelope.
     * Remember, to use bounds which are preferably square instead of rectangular.
     */
    bounds AS (
        SELECT ST_TileEnvelope(:z, :x, :y, ST_MakeEnvelope(485072, -52106, 837120, 299942, 21781)) AS geom
    ),

    /*
     * Transform the geometry with ST_AsMVTGeom into the coordinate space of a Mapbox Vector Tile (MVT)
     * clipping it to tile bounds if necessary.
     * Note: for the sake of this example we set the buffer to zero, to show the clipped boundaries
     */
    mvtgeom AS (
        SELECT ST_AsMVTGeom(mydata.geom, bounds.geom, 4096, 0) AS geom,
            otherColumn
        FROM mydata, bounds
        WHERE ST_Intersects(mydata.geom, bounds.geom)
    )
/*
 * Create the binary MVT format with ST_AsMVT. All row columns other than the geometry are encoded as feature attributes.
 */
SELECT ST_AsMVT(t.*, 'myLayerName') mvt
FROM mvtgeom t

If you don’t need to use a projection other than Web Mercator, then the above SQL query simplifies to:

WITH mydata AS (
        SELECT ST_ForcePolygonCCW(
            ST_Transform(ST_Union(t.geom), 3857)
        ) geom, prodreg otherColumn
        FROM someTable t
        GROUP by t.otherColumn
    ),
    bounds AS (
        SELECT ST_TileEnvelope(:z, :x, :y) AS geom
    ),
    mvtgeom AS (
        SELECT ST_AsMVTGeom(mydata.geom, bounds.geom) AS geom,
               otherColumn
        FROM mydata, bounds
        WHERE ST_Intersects(data.geom, bounds.geom)
    )
SELECT ST_AsMVT(t.*, 'myLayerName') mvt
FROM mvtgeom t

Serve the tiles with PHP

Now, to access the PostgreSQL database, we use the database abstraction layer PHP Data Objects (PDO). The database driver specific to PostgreSQL has be installed separately, on (Ubuntu) Linux with:

$ apt install php8.1-pgsql

For this example, we create the resource /my-webroot/service.php

// service.php accessed as /myWebroot/service.php/{z}/{x}/{y}.pbf

// Grab the tile indices from the path:
[$z, $x, $y] = explode('/', $_SERVER['PATH_INFO'])
$y = substr($y, 0, -4);

// Query the tiles with the above SQL:
$sql = "WITH data ... FROM mvtgeom t";
$db = connect();
$stmt = $db->prepare($sql);

// Bind the placeholders to the query:
$stmt->execute(['x' => $x, 'y' => $y, 'z' => $z]);
$row = $stmt->fetch();

// Set the correct content type of the response header:
header('Content-Type: application/vnd.mapbox-vector-tile; utf-8');

// Output the requested tile
echo stream_get_contents($row['mvt']);

It’s that simple !

Have the browser cache your tiles

To speed things up, query the database to create the tiles only, when you actually have to send a response body, i.e. wrap the database query in a function or method and only call it when the Etag has changed:

// Create an Etag header to let the browser cache the tiles (optional):
// Note: The identifier needs to be wrapped in double quotes.
$etag = md5($tileDateLastUpdate)
$etagHeader = isset($_SERVER['HTTP_IF_NONE_MATCH']) ? trim($_SERVER['HTTP_IF_NONE_MATCH']) : false;
header('Etag: "'.$etag.'"');
// There is no need for the browser to send a request for this tile again until the next day (the response is fresh for 60*60*24 seconds):
header('Cache-Control: max-age=86400');

// If we have a matching Etag, we know that the content was not modified:
if ($etagHeader === $etag) {    
    // Do not query the database here, since we don't send a body at all.
    http_response_code(304);
} else {
    // Only connect to your database and query it here, since this takes longer:
    echo myTileService.get($x, $y, $z);
}

Use the tiles as a vector layer in OpenLayers

OpenLayers also uses Spherical Mercator (EPSG:3857) as the default. Since we are using the Swiss CH1903/LV03 coordinate system in this example (EPSG:21781), we have to explicitly set the projection on the view and the tile source:

import MVT from './node_modules/ol/format/MVT.js';
import VectorTileLayer from './node_modules/ol/layer/VectorTile.js';
import VectorTileSource from './node_modules/ol/source/VectorTile.js';
import View from './node_modules/ol/View.js';
import Map from './node_modules/ol/Map.js';
import proj4 from './node_modules/proj4';
import {register} from './node_modules/ol/proj/proj4.js';

const proj = {
  /**
   * projection definition
   * CH1903 / LV03 -- Swiss CH1903 / LV03
   * @see https://epsg.io/21781
   * @type {String}
   */
  definition: '+proj=somerc +lat_0=46.9524055555556 +lon_0=7.43958333333333 +k_0=1 +x_0=600000 +y_0=200000 +ellps=bessel +towgs84=674.374,15.056,405.346,0,0,0,0 +units=m +no_defs +type=crs',

  name: 'EPSG:21781',
  extent: [485071.58, 74261.72, 837119.8, 299941.79],
  center: [660013.54, 185171.98],
};

proj4.defs(proj.name, proj.definition);
register(proj4);

const map = new Map({
  target: 'map-container',
  view: new View({
    projection: proj.name,
    center: proj.center,
    extent: proj.extent,
    showFullExtent: true,
    zoom: 1,
  }),
  layers: [
    new VectorTileLayer({
      source: new VectorTileSource({
        projection: proj.name,
        extent: proj.extent,
        format: new MVT(),
        url: 'https://result-maps.test/service.php/tile/{z}/{x}/{y}.pbf',
        maxZoom: 14,
        tileSize: 256,
      })
    })
  ]
});

If we wanted to stick to Spherical Mercator and display the vector tiles over OpenStreetMap, the JavaScript code would simplify to:

import MVT from './node_modules/ol/format/MVT.js';
import VectorTileLayer from './node_modules/ol/layer/VectorTile.js';
import VectorTileSource from './node_modules/ol/source/VectorTile.js';
import View from './node_modules/ol/View.js';
import Map from './node_modules/ol/Map.js';
import TileLayer from './node_modules/ol/layer/Tile.js';
import {OSM} from './node_modules/ol/source.js';

const map = new Map({
  target: 'map-container',
  view: new View({
    center: [915602.8072104596, 5911929.4687797595],
    extent: [663464, 5751550, 1167741, 6075303],
    showFullExtent: true,
    zoom: 3,
  }),
  layers: [
    new TileLayer({source: new OSM()}),
    new VectorTileLayer({
      source: new VectorTileSource({
        format: new MVT(),
        url: 'https://result-maps.test/service.php/tile/{z}/{x}/{y}.pbf',
        maxZoom: 14,
      })
    })
  ]
});

Style your vector tiles

...
import Style from './node_modules/ol/style/Style.js';
import Fill from './node_modules/ol/style/Fill.js';
import Stroke from './node_modules/ol/style/Stroke.js';

new VectorTileLayer({
      source: new VectorTileSource(...),

      // additionally, style the tiles with a white stroke and an orange, semitransparent fill
      style: new Style({
        stroke: new Stroke({
          color: '#fff',
          width: 2
        }),
        fill: new Fill({
          color: 'rgba(255, 153, 0, 0.5)'
        })
      })
    })

We can also use the feature properties to color our polygons, in my case we use the property regionid. Instead of passing a style instance to the style property, we use a function:

    new VectorTileLayer({
      source: new VectorTileSource(...),
      // create a fill color from the feature property region id
      style: function(feature) {
        let color;
        const prop = feature.getProperties();
        switch (prop.regionid) {
          case 1:
            color = 'rgba(213,223,229, 0.6)';
            break;
          case 2:
            color = 'rgba(201,177,189, 0.6)';
            break;
          case 3:
            color = 'rgba(180,149,148, 0.6)';
            break;
          case 4:
            color = 'rgba(127,145,114, 0.6)';
            break;
          case 5:
            color = 'rgba(86,117,104, 0.6)';
            break;
        }
        style.getFill().setColor(color);

        return style;
      },
    })

Worth reading

• Crunchy Data: Serving Dynamic Vector Tiles from PostGIS
• OpenLayers: The VectorTile Layer
• Maps and stuff: Buffer around Vectortiles
• Mozilla Developer Network: HTTP Caching
• PHP: (The only proper) PDO tutorial