Advanced SQL editing

When establishing tiles serving from PostGIS, MapTiler Server tries to free you from manual writing SQL queries in most common cases (see Vector tiles from PostgreSQL). But sometimes is the simple editor not enough and you need to use the full power of SQL.

You can either modify generated queries with the advanced SQL editor:

advancedMode.png

or write SQL query them from scratch during a new layer creation:

createSql.png

Query structure

MapTiler Server PostGIS integration is based on the concept of layers. Each layer is represented with a standalone SQL query returning geometry in tile coordinate space - usually a result of calling the ST_AsMVTGeom function. MapTiler Server expects this geometry to be named ‘mvtgeometry’, so it can be passed to ST_AsMVT function to produce binary MapBox Vector Tile. If you select other columns than ‘mvtgeometry’, they will be passed to ST_AsMVT as well and encoded as feature attributes (useful for map styling).

In the next step, the results of all layers queries are concatenated into one tile and compressed with gzip (only in case your’s database has gzip extension enabled, otherwise does this compression MapTiler Server in tile serving).

Layer query is applied only if tile for zoom level in interval <layer’s minzoom, layer’s maxzoom) is requested. You can (and should) limit your query according to the requested tile’s the x/y/z coordinates - placeholders {x} {y} {z} in your query will be replaced with actual values.

Handling many geometries

In case you are querying tables containing large numbers of geometries, you will probably hit performance issues. It makes even no sense to put all these geometries in tiles on low zoom levels. You can use two approaches here:

  1. generalization
  2. feature count reduction
    • e.g. filtering the largest polygons

With this, you can create views (materialized for performance boost) and use them as layers for low zoom levels. The original full table should be queried on higher zoom levels.

Vector tiles from PostgreSQL
Prepare your Postgres for MapTiler Server
How to import spatial data to PostGIS for MT Server