![\"\"](\"https:\/\/neogeo.fr\/wp-content\/uploads\/2024\/07\/grilles.png\")
<\/figure>\n<\/div>\nMost tile systems start with a single tile covering the entire world, called “zoom level 0,” and each subsequent zoom level increases the number of tiles by a factor of 4, doubling both vertically and horizontally.<\/p>\n\n
Tile coordinates<\/h3>\n\n
Each tile in a mosaicked map is referenced by its zoom level, horizontal position, and vertical position. The commonly used addressing scheme is XYZ. <\/p>\n\n
Here is an example at zoom level 2:<\/p>\n
![\"\"](\"https:\/\/neogeo.fr\/wp-content\/uploads\/2024\/07\/grilles-2.png\")
<\/figure>\n<\/div>\nWe will retrieve the tile using the following XYZ addressing:<\/p>\n\n
http://server\/{z}\/{x}\/{y}.format<\/code><\/pre>\n\nFor example, you can view the image tile of Australia at the following address; the same principle applies to vector tiles:https:\/\/tile.openstreetmap.org\/2\/3\/2.png<\/a><\/p>\n\nHow a vector tile works<\/h3>\n\n
Vector tiles look like raster tiles, but instead of an image, they contain data describing the elements that make them up using vector paths. Instead of sets of pixels, there are sets of point coordinates, to which several attribute pieces of information can be associated. <\/p>\n\n
Like their raster cousins, vector tiles can be cached, both server-side and browser-side, allowing users to navigate huge maps quickly, even on lightweight devices.<\/p>\n\n
How is it used?<\/h2>\n\nGenerating vector tiles on the fly from PostGIS<\/h3>\n\n
There are different building blocks for publishing data on the fly from PostGIS. Here is our selection:<\/p>\n\n
\n- pg_tileserv => https:\/\/github.com\/CrunchyData\/pg_tileserv<\/a>: a tile server coded in Go and produced by Crunchy Data. We prefer to use the dockerized version in our applications => https:\/\/hub.docker.com\/r\/pramsey\/pg_tileserv<\/a> <\/li>\n\n\n\n
- Martin => https:\/\/github.com\/maplibre\/martin<\/a>: very similar to pg_tileserv, coded in Rust and produced by MapLibre.<\/li>\n\n\n\n
- GeoServer: also allows the generation of vector tiles<\/li>\n<\/ul>\n\n
For the purposes of this blog, we will focus on using pg_tileserv.<\/p>\n\n
pg_tileserv<\/h3>\n\n
By default, pg_tileserv allows you to generate vector tiles from the geographic tables in the database.<\/p>\n
\n![\"\"](\"https:\/\/neogeo.fr\/wp-content\/uploads\/2024\/07\/pg_tileserv.png\")
<\/figure>\n<\/div>\nThe real benefit of using a tool like pg_tileserv lies in generating tiles based on more refined analysis.<\/p>\n\n
Here is an example of a query that can be used to display map data. This query will simplify the geometry based on the zoom threshold and filter the data based on the operator and technology. <\/p>\n\n
CREATE OR REPLACE FUNCTION generate_couvertures_tiles(\n z integer,\n x integer,\n y integer,\n in_operateur bigint,\n in_techno character varying)\n RETURNS bytea\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\nAS $BODY$\n\n#variable_conflict use_variable\nbegin\n return (\n WITH\n bounds AS (\n SELECT ST_TileEnvelope(z, x, y) AS geom,\n (CASE\n when z >= 12 then 0\n when z = 11 then 0\n when z = 10 then 10\n when z = 9 then 100\n when z = 8 then 300\n when z = 7 then 900\n when z = 6 then 1500\n when z <= 5 then 3000\n ELSE 1 END\n ) as simplify_tolerance\n ),\n\n mvtgeom AS (\n SELECT fid, operateur, date, techno, usage, niveau, dept, filename,\n ST_AsMVTGeom(\n ST_Simplify(t.geom,simplify_tolerance), bounds.geom) AS geom\n\n FROM couverture_theorique t, bounds\n WHERE ST_Intersects(t.geom, bounds.geom )\n and operateur = in_operateur\n and techno = in_techno\n )\n SELECT ST_AsMVT(mvtgeom)\n FROM mvtgeom\n );\nend;\n$BODY$;<\/code><\/pre>\n\nTo call this function, we will use a URL formatted as follows:<\/p>\n\n
https://hostname\/tileserv\/public.generate_couvertures_tiles\/{Z}\/{X}\/{Y}.pbf?in_operateur=28020&in_techno=3G<\/code><\/pre>\n\nNow you’re ready to let your imagination run wild to retrieve the data you want and meet the needs of your map.<\/p>\n\n
We can think of creating clusters, thematic analysis, layer aggregation, or whatever else comes to mind.<\/p>\n\n
What about performance?<\/h2>\n\n
All this newfound freedom will lead us into increasingly complex queries, and therefore degraded performance. If each tile takes 10 seconds to generate, the user experience will be poor. <\/p>\n\n
To address this issue, let’s remember that the vector tile is based on a fixed grid. It is therefore possible to generate a cache (caching). Great! But how do we do that? <\/p>\n\n
Here we go:<\/p>\n\n
1 – Create a tile cache table<\/p>\n\n
CREATE TABLE IF NOT EXISTS tiles_cache\n(\n z integer NOT NULL,\n x integer NOT NULL,\n y integer NOT NULL,\n operateur bigint NOT NULL,\n techno character varying COLLATE pg_catalog."default" NOT NULL,\n mvt bytea NOT NULL,\n CONSTRAINT tiles_pkey PRIMARY KEY (z, x, y, operateur, techno)\n)<\/code><\/pre>\n\n2 – Generate the cache<\/p>\n\n
To generate the cache, we first retrieve the grids on which we have data. The maximum zoom threshold (max_zoom) can be defined in the following function. <\/p>\n\n
CREATE OR REPLACE FUNCTION gettilesintersectinglayer(\n liste_operateur bigint,\n liste_techno character varying)\n RETURNS TABLE(x integer, y integer, z integer)\n LANGUAGE 'plpgsql'\n COST 100\n VOLATILE PARALLEL UNSAFE\n ROWS 1000\n\nAS $BODY$\nDECLARE\n tile_bounds public.GEOMETRY;\n max_zoom INTEGER := 7;\nBEGIN\n FOR current_zoom IN 1..max_zoom LOOP\n FOR _x IN 0..(2 ^ current_zoom - 1)\n LOOP\n FOR _y IN 0..(2 ^ current_zoom - 1)\n LOOP\n tile_bounds := ST_TileEnvelope(current_zoom, _x, _y);\n IF EXISTS (\n SELECT 1 FROM couverture_theorique\n WHERE ST_Intersects(geom, tile_bounds)\n AND operateur = liste_operateur\n AND techno = liste_techno\n )\n THEN\n RAISE NOTICE 'Traitement %', current_zoom || ', ' || _x || ', ' || _y;\n z := current_zoom;\n x := _x;\n y := _y;\n RETURN NEXT;\n END IF;\n END LOOP;\n END LOOP;\n END LOOP;\nEND;\n$BODY$;<\/code><\/pre>\n\nUsing this grid table, we will generate all the tiles and insert them into the cache table.<\/p>\n\n
with oper as (\n SELECT distinct operateur from couverture_theorique\n),\ntechno as (\n SELECT distinct techno from couverture_theorique\n)\ninsert into tiles_cache_couverture(z, x, y, operateur, techno, mvt)\nselect tile.z, tile.x, tile.y, oper.operateur, techno.techno,\ngenerate_couvertures_tiles(tile.z, tile.x, tile.y, oper.operateur, techno.techno)\nfrom techno, oper, GetTilesIntersectingLayer(operateur, techno) as tile;<\/code><\/pre>\n\n 3 – Mixed cache\/query rendering<\/p>\n\n
Once the tiles have been inserted into the cache table, when we want to retrieve a tile, we need to route the lookup so that the function either retrieves the tile from the cache table or generates it on the fly.<\/p>\n\n
CREATE FUNCTION couvertures(z integer, x integer, y integer, liste_operateur integer[], liste_techno character varying[]) RETURNS bytea\n LANGUAGE plpgsql\n AS $$\n\n#variable_conflict use_variable\nbegin\n if (z <= 7 and array_length(liste_operateur,1) = 1) then\n return (\n SELECT mvt\n from tiles_cache_couverture\n Where tiles_cache_couverture.x=x\n AND tiles_cache_couverture.y=y\n AND tiles_cache_couverture.z=z\n and operateur = any(liste_operateur)\n and techno = any(liste_techno)\n );\n else\n return (\n WITH\n bounds AS (\n SELECTST_TileEnvelope(z, x, y) AS geom,\n (CASE\n when z >= 12 then 0\n when z = 11 then 0\n when z = 10 then 10\n when z = 9 then 100\n when z = 8 then 300\n when z = 7 then 900\n when z = 6 then 1500\n when z <= 5 then 3000\n ELSE 1 END\n ) as simplify_tolerance\n ),\n\n mvtgeom AS (\n SELECT fid, operateur, date, techno, usage, niveau, dept, filename,\n public.ST_AsMVTGeom(\n ST_Simplify(t.geom,simplify_tolerance), bounds.geom) AS geom\n\n FROM couverture_theorique t, bounds\n WHEREST_Intersects(t.geom, bounds.geom )\n and operateur = any(liste_operateur)\n and techno = any(liste_techno)\n )\n SELECTST_AsMVT(mvtgeom)\n FROM mvtgeom\n );\n end if;\nend;\n$$;<\/code><\/pre>\n\nNow, by calling the “coverages” layer in pg_tileserv, at the smallest zoom levels (below 8), which are the most demanding in terms of computing geometric simplification, we will use the tile cache. However, when we get closer, we will use on-the-fly tile generation since performance is good. <\/p>\n\n
For the more advanced users, here’s a little bonus: an example of a cluster layer generated on the database side. The cluster adapts to the zoom threshold, clustering at the departmental level, then the municipal level, then as a natural cluster (St_ClusterDBSCAN) with dynamic spacing for each zoom threshold, and finally displaying by individual object when very close. We could have imagined a honeycomb-style cluster, which I find more effective, since the issue with PostGIS clustering is that it is calculated within the extent of each tile. This means it splits clusters arbitrarily when there is high density between two tiles. <\/p>\n\n
CREATE FUNCTION cluster_filtres(z integer, x integer, y integer, filtres text[]) RETURNS bytea\n LANGUAGE plpgsql\n AS $_$\n\n#variable_conflict use_variable\nDECLARE\n query text;\n result bytea;\nbegin\n --MISE EN PLACE DES FILTRES\n\n if ( z < 9) then\n --Vue par departement\n query := '\n WITH\n bounds AS (\n SELECT ST_TileEnvelope($1, $2, $3) AS geom\n ),\n item_fitler AS (\n --On r\u00e9cup\u00e8re nos donn\u00e9es filtr\u00e9es\n SELECT distinct t.id\n FROM table t\n WHERE ' || filtres || '\n ),\n tot_dept AS (\n select code_departement, count(1) as tot_item\n from departement d\n INNER JOIN table t ON ST_Intersects(d.geom, t.geom )\n INNER JOIN item_fitler ON item_fitler.id = t.id\n group by code_departement\n ),\n mvtgeom AS (\n SELECT tot_dept.code_departement, tot_dept.tot_item,\n ST_AsMVTGeom(ST_PointOnSurface(tot_dept.geom), bounds.geom) AS geom\n FROM tot_dept\n INNER JOIN bounds ON ST_Intersects(tot_dept.geom, bounds.geom )\n WHERE tot_item is not null\n )\n SELECT ST_AsMVT(mvtgeom)\n FROM mvtgeom\n ';\n --RAISE NOTICE 'Calling query (%)', query;\n EXECUTE query INTO result USING z, x, y, filtres;\n return result;\n \n elsif ( z <= 10) then\n --Vue par commune, On ajoute un buffer pour r\u00e9cup\u00e9rer les items autour de la tuile sans devoir le faire sur la France enti\u00e8re\n query := '\n WITH\n bounds AS (\n SELECT ST_TileEnvelope($1, $2, $3) AS geom\n ),\n item_fitler AS (\n --On r\u00e9cup\u00e8re nos donn\u00e9es filtr\u00e9es\n SELECT distinct t.id\n FROM table t\n INNER JOIN bounds ON ST_Intersects(t.geom, ST_Buffer(bounds.geom, 10000) )\n WHERE ' || filtres || '\n ),\n tot_com AS (\n select insee_com, count(1) as tot_item\n from commune c\n INNER JOIN table t ON ST_Intersects(c.geom, t.geom )\n INNER JOIN item_fitler ON item_fitler.id = t.id\n group by insee_com\n having count(1) > 0\n ),\n mvtgeom AS (\n SELECT tot_com.insee_com, tot_com.tot_item,\n ST_AsMVTGeom(ST_PointOnSurface(tot_com.geom), bounds.geom) AS geom\n FROM tot_com\n INNER JOIN bounds ON ST_Intersects(tot_com.geom, bounds.geom )\n WHERE tot_item is not null\n )\n SELECT ST_AsMVT(mvtgeom)\n FROM mvtgeom\n ';\n --RAISE NOTICE 'Calling query (%)', query;\n EXECUTE query INTO result USING z, x, y, filtres;\n return result;\n \n elsif ( z <= 15) then\n --Vue par cluster\n query := '\n WITH\n bounds AS (\n SELECT ST_TileEnvelope($1, $2, $3) AS geom\n ),\n item_fitler AS (\n --On r\u00e9cup\u00e8re nos donn\u00e9es filtr\u00e9es\n SELECT distinct t.id\n FROM table t\n INNER JOIN bounds ON ST_Intersects(t.geom, bounds.geom )\n WHERE ' || filtres || '\n ),\n clustered_points AS (\n SELECT ST_ClusterDBSCAN(t.geom, eps :=\n (CASE\n when $1 = 11 then 500\n when $1 = 12 then 385\n when $1 = 13 then 280\n when $1 = 14 then 150\n when $1 = 15 then 75\n ELSE 1 END\n ) , minpoints := 1) over() AS cid,\n t.fid, t.geom\n FROM table t\n INNER JOIN item_fitler s ON s.id = t.id\n group by t.id, t.geom\n ),\n mvtgeom AS (\n SELECT cid, array_agg(id) as ids,\n count(1) as tot_item,\n ST_AsMVTGeom(ST_PointOnSurface(ST_Collect(c.geom)), bounds.geom) AS geom\n FROM clustered_points c, bounds\n WHERE ST_Intersects(c.geom, bounds.geom )\n group by cid, bounds.geom\n )\n SELECT public.ST_AsMVT(mvtgeom)\n FROM mvtgeom\n ';\n RAISE NOTICE 'Calling query (%)', query;\n EXECUTE query INTO result USING z, x, y, filtres;\n return result;\n else\n --vue par objet\n query := '\n WITH\n bounds AS (\n SELECT ST_TileEnvelope($1, $2, $3) AS geom\n ),\n item_fitler AS (\n --On r\u00e9cup\u00e8re nos donn\u00e9es filtr\u00e9es\n SELECT distinct t.id\n FROM table t\n INNER JOIN bounds ON ST_Intersects(t.geom, bounds.geom )\n WHERE ' || filtres || '\n ),\n mvtgeom as (\n SELECT ST_AsMVTGeom(t.geom, bounds.geom) AS geom, t.id\n FROM item_fitler t , bounds\n WHERE ST_Intersects(t.geom, bounds.geom )\n )\n SELECT ST_AsMVT(mvtgeom)\n FROM mvtgeom\n ';\n EXECUTE query INTO result USING z, x, y, filtres;\n return result;\n end if;\nend;\n$_$;<\/code><\/pre>\n\nSo, what are the benefits?<\/h2>\n\nBasemap<\/h3>\n\n
When it comes to choosing basemaps, there are many advantages.<\/p>\n\n
You can fairly easily customize a basemap by modifying the display settings you want to use for each element. To do this, you can rely on style files like those provided by Etalab: https:\/\/openmaptiles.geo.data.gouv.fr\/<\/a>. <\/p>\n\nIf you host the style files yourself, you can modify them to choose the style of the elements. Here’s what the configuration of an element from a vector tile looks like: <\/p>\n\n
{\n "id": "landuse-commercial",\n "type": "fill",\n "source": "openmaptiles",\n "source-layer": "landuse",\n "filter": [\n "all",\n ["==", "$type", "Polygon"],\n ["==", "class", "commercial"]\n ],\n "layout": {\n "visibility": "visible"\n },\n "paint": {\n "fill-color": "hsla(0, 60%, 87%, 0.23)"\n }\n },<\/code><\/pre>\n\nFor example, it is possible to extrude buildings if you want a 3D rendering<\/p>\n
\n![\"\"](\"https:\/\/neogeo.fr\/wp-content\/uploads\/2024\/07\/3D.png\")
<\/figure>\n<\/div>\nVector display also allows labels\/icons to always be displayed in the correct reading direction.<\/p>\n\n
Choosing the layer display order<\/h3>\n\n
It’s also worth noting that it is possible to change the display order of layers from the basemap and business layers, which is not possible with WMTS-type basemaps. This allows municipality labels to stand out on top of a business layer. <\/p>\n
\n![\"\"](\"https:\/\/neogeo.fr\/wp-content\/uploads\/2024\/07\/couche.png\")
<\/figure>\n<\/div>\nVector layer<\/h3>\n\n
Since the layer is vector-based, it is also possible to retrieve its attributes.<\/p>\n\n
Style editing tool<\/h3>\n\n
There are several tools available for modifying the style of vector data. However, I recommend Maputnik<\/em>, which is very comprehensive and accessible => https:\/\/maplibre.org\/maputnik\/#0.77\/0\/0<\/a> <\/p>\n\nConclusion<\/h2>\n\n
In conclusion, vector tiles represent a significant advance in the dissemination and display of spatial data, enabling the creation of advanced mapping applications. However, they still need to be integrated into robust tools and high-performance methodologies in order to meet the growing need for precision and responsiveness. Vector tile technology is essential for the future of digital mapping, offering a balance between performance, flexibility, and interactivity. <\/p>\n\n
Future directions?<\/h2>\n\n
Authentication and roles through pg_tileserv. In a future blog post, we could explore how to combine token-based authentication, the pg_tileserv service, and PostgreSQL security through Row Level Security. This would allow permissions to be managed at the object level, natively within PostgreSQL. <\/p>\n\n
\n\n
![\"\"](\"https:\/\/tile.openstreetmap.org\/2\/3\/2.png\")
<\/figure>\n<\/div>\n