## Ready to serve Our data has now been reclassified, merged, simplified and subdivided and is ready to use. The result is a table full of geometries for different zoomlevels. Since pg_tileserv would by default simply serve *all* geometries, regardless of zoomlevel, we have to find a way to deal with that. Luckily, pg_tileserv has a feature that is both powerful and simple called 'function layers'. When you create functions in your database that follow a certain layout, they will automatically be picked up by pg_tileserv as a layer and show up in your layers overview. You can write these functions in every language that is available to postgres, so I started with a 'simple' SQL function: CREATE FUNCTION basiskaart.getmvt(z int,x int, y int) RETURNS bytea LANGUAGE 'sql' AS $BODY$ WITH bounds AS (SELECT ST_TileEnvelope(z, x, y) AS geom), mvtgeom AS ( SELECT ST_AsMVTGeom(ST_Transform(t.geom,3857), bounds.geom) AS "geom", "color","zmin","zmax","relh" FROM basiskaart.polygons_zlevels t, bounds WHERE ST_Intersects(t.geom, ST_Transform(bounds.geom,28992)) AND z BETWEEN t.zmin AND t.zmax ) SELECT ST_AsMVT(mvtgeom, 'basiskaart.getmvt') FROM mvtgeom; $BODY$; Despite the relative simplicity of the functions it turned out to be rather slow though. This is likely due to the fact that the planner has difficulty in incorporating the indexes in the plan. Where writing the function in 'plpgsql' it turns out to run quicker. CREATE OR REPLACE FUNCTION basiskaart.getmvt(z int,x int, y int) RETURNS bytea LANGUAGE 'plpgsql' AS $BODY$ DECLARE result bytea; BEGIN EXECUTE ' WITH bounds AS (SELECT ST_TileEnvelope($1,$2,$3) AS geom), mvtgeom AS ( SELECT ST_AsMVTGeom(ST_Transform(t.geom,3857), bounds.geom) AS "geom", "color","class","zmin","zmax","relh" FROM basiskaart.polygons_zlevels t, bounds WHERE t.geom && ST_Transform(bounds.geom,28992) AND $1 > t.zmin AND $1 <= t.zmax ) SELECT ST_AsMVT(mvtgeom, ''basiskaart.getmvt'') FROM mvtgeom; ' INTO result USING z,x, y; RETURN result; END $BODY$; Another reason that that the plpgsql function is faster could be that it gets cached which is cheaper the next time you run it. In the end there is a small disadvantage of more boilerplate in plpgsql but it saves you a ton of headache. As a bonus tip: we had problems with the ST_Intersects function crashing on some *very* invalid geometries. This was solved by only using the bounding box intersection (*&&*). On top of solving this problem, it may have also sped up the query a little since there is no need to check for a real intersection since ST_AsMVTGeom will ignore any overshoots.

Purpose-built for displays of data