a function to generate an entire MVT tile from OpenMapTiles

getmvt.sql

\set zoom 10
\set x 0
\set y 0

SELECT STRING_AGG(mvtl, '') AS mvt FROM (
SELECT IsEmpty, count(*) OVER () AS LayerCount, mvtl FROM (
  SELECT (sum(IsEmpty::int) != 0) AS IsEmpty, ST_AsMVT(mvtl2, 'water', 4096, 'mvtgeometry') as mvtl FROM (SELECT CASE :zoom <= 8 WHEN TRUE THEN FALSE ELSE ST_WITHIN(ST_GeomFromText('POLYGON((0 4096,0 0,4096 0,4096 4096,0 4096))', 3857), mvtgeometry) END AS IsEmpty, mvtgeometry, class, intermittent FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 4, true) AS mvtgeometry, class, intermittent FROM (SELECT geometry, class, intermittent FROM layer_water(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'waterway', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, name, name_en, name_de, class, brunnel, intermittent, tags, NULLIF(tags->'name:ar', '') AS "name:ar", NULLIF(tags->'name:az', '') AS "name:az", NULLIF(tags->'name:be', '') AS "name:be", NULLIF(tags->'name:bg', '') AS "name:bg", NULLIF(tags->'name:br', '') AS "name:br", NULLIF(tags->'name:bs', '') AS "name:bs", NULLIF(tags->'name:ca', '') AS "name:ca", NULLIF(tags->'name:co', '') AS "name:co", NULLIF(tags->'name:cs', '') AS "name:cs", NULLIF(tags->'name:cy', '') AS "name:cy", NULLIF(tags->'name:da', '') AS "name:da", NULLIF(tags->'name:de', '') AS "name:de", NULLIF(tags->'name:el', '') AS "name:el", NULLIF(tags->'name:en', '') AS "name:en", NULLIF(tags->'name:eo', '') AS "name:eo", NULLIF(tags->'name:es', '') AS "name:es", NULLIF(tags->'name:et', '') AS "name:et", NULLIF(tags->'name:eu', '') AS "name:eu", NULLIF(tags->'name:fi', '') AS "name:fi", NULLIF(tags->'name:fr', '') AS "name:fr", NULLIF(tags->'name:fy', '') AS "name:fy", NULLIF(tags->'name:ga', '') AS "name:ga", NULLIF(tags->'name:gd', '') AS "name:gd", NULLIF(tags->'name:he', '') AS "name:he", NULLIF(tags->'name:hr', '') AS "name:hr", NULLIF(tags->'name:hu', '') AS "name:hu", NULLIF(tags->'name:hy', '') AS "name:hy", NULLIF(tags->'name:id', '') AS "name:id", NULLIF(tags->'name:is', '') AS "name:is", NULLIF(tags->'name:it', '') AS "name:it", NULLIF(tags->'name:ja', '') AS "name:ja", NULLIF(tags->'name:ja_kana', '') AS "name:ja_kana", NULLIF(tags->'name:ja_rm', '') AS "name:ja_rm", NULLIF(tags->'name:ka', '') AS "name:ka", NULLIF(tags->'name:kk', '') AS "name:kk", NULLIF(tags->'name:kn', '') AS "name:kn", NULLIF(tags->'name:ko', '') AS "name:ko", NULLIF(tags->'name:ko_rm', '') AS "name:ko_rm", NULLIF(tags->'name:la', '') AS "name:la", NULLIF(tags->'name:lb', '') AS "name:lb", NULLIF(tags->'name:lt', '') AS "name:lt", NULLIF(tags->'name:lv', '') AS "name:lv", NULLIF(tags->'name:mk', '') AS "name:mk", NULLIF(tags->'name:mt', '') AS "name:mt", NULLIF(tags->'name:ml', '') AS "name:ml", NULLIF(tags->'name:nl', '') AS "name:nl", NULLIF(tags->'name:no', '') AS "name:no", NULLIF(tags->'name:oc', '') AS "name:oc", NULLIF(tags->'name:pl', '') AS "name:pl", NULLIF(tags->'name:pt', '') AS "name:pt", NULLIF(tags->'name:rm', '') AS "name:rm", NULLIF(tags->'name:ro', '') AS "name:ro", NULLIF(tags->'name:ru', '') AS "name:ru", NULLIF(tags->'name:sk', '') AS "name:sk", NULLIF(tags->'name:sl', '') AS "name:sl", NULLIF(tags->'name:sq', '') AS "name:sq", NULLIF(tags->'name:sr', '') AS "name:sr", NULLIF(tags->'name:sr-Latn', '') AS "name:sr-Latn", NULLIF(tags->'name:sv', '') AS "name:sv", NULLIF(tags->'name:th', '') AS "name:th", NULLIF(tags->'name:tr', '') AS "name:tr", NULLIF(tags->'name:uk', '') AS "name:uk", NULLIF(tags->'name:zh', '') AS "name:zh", NULLIF(tags->'name_int', '') AS "name_int", NULLIF(tags->'name:latin', '') AS "name:latin", NULLIF(tags->'name:nonlatin', '') AS "name:nonlatin" FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 4, true) AS mvtgeometry, name, name_en, name_de, class, brunnel, intermittent, tags FROM (SELECT geometry, name, name_en, name_de, tags, class, brunnel, intermittent FROM layer_waterway(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'landcover', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, class, subclass FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 4, true) AS mvtgeometry, class, subclass FROM (SELECT geometry, class, subclass FROM layer_landcover(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'landuse', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, class FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 4, true) AS mvtgeometry, class FROM (SELECT geometry, class FROM layer_landuse(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'mountain_peak', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, name, name_en, name_de, class, ele, ele_ft, rank, osm_id, tags, NULLIF(tags->'name:ar', '') AS "name:ar", NULLIF(tags->'name:az', '') AS "name:az", NULLIF(tags->'name:be', '') AS "name:be", NULLIF(tags->'name:bg', '') AS "name:bg", NULLIF(tags->'name:br', '') AS "name:br", NULLIF(tags->'name:bs', '') AS "name:bs", NULLIF(tags->'name:ca', '') AS "name:ca", NULLIF(tags->'name:co', '') AS "name:co", NULLIF(tags->'name:cs', '') AS "name:cs", NULLIF(tags->'name:cy', '') AS "name:cy", NULLIF(tags->'name:da', '') AS "name:da", NULLIF(tags->'name:de', '') AS "name:de", NULLIF(tags->'name:el', '') AS "name:el", NULLIF(tags->'name:en', '') AS "name:en", NULLIF(tags->'name:eo', '') AS "name:eo", NULLIF(tags->'name:es', '') AS "name:es", NULLIF(tags->'name:et', '') AS "name:et", NULLIF(tags->'name:eu', '') AS "name:eu", NULLIF(tags->'name:fi', '') AS "name:fi", NULLIF(tags->'name:fr', '') AS "name:fr", NULLIF(tags->'name:fy', '') AS "name:fy", NULLIF(tags->'name:ga', '') AS "name:ga", NULLIF(tags->'name:gd', '') AS "name:gd", NULLIF(tags->'name:he', '') AS "name:he", NULLIF(tags->'name:hr', '') AS "name:hr", NULLIF(tags->'name:hu', '') AS "name:hu", NULLIF(tags->'name:hy', '') AS "name:hy", NULLIF(tags->'name:id', '') AS "name:id", NULLIF(tags->'name:is', '') AS "name:is", NULLIF(tags->'name:it', '') AS "name:it", NULLIF(tags->'name:ja', '') AS "name:ja", NULLIF(tags->'name:ja_kana', '') AS "name:ja_kana", NULLIF(tags->'name:ja_rm', '') AS "name:ja_rm", NULLIF(tags->'name:ka', '') AS "name:ka", NULLIF(tags->'name:kk', '') AS "name:kk", NULLIF(tags->'name:kn', '') AS "name:kn", NULLIF(tags->'name:ko', '') AS "name:ko", NULLIF(tags->'name:ko_rm', '') AS "name:ko_rm", NULLIF(tags->'name:la', '') AS "name:la", NULLIF(tags->'name:lb', '') AS "name:lb", NULLIF(tags->'name:lt', '') AS "name:lt", NULLIF(tags->'name:lv', '') AS "name:lv", NULLIF(tags->'name:mk', '') AS "name:mk", NULLIF(tags->'name:mt', '') AS "name:mt", NULLIF(tags->'name:ml', '') AS "name:ml", NULLIF(tags->'name:nl', '') AS "name:nl", NULLIF(tags->'name:no', '') AS "name:no", NULLIF(tags->'name:oc', '') AS "name:oc", NULLIF(tags->'name:pl', '') AS "name:pl", NULLIF(tags->'name:pt', '') AS "name:pt", NULLIF(tags->'name:rm', '') AS "name:rm", NULLIF(tags->'name:ro', '') AS "name:ro", NULLIF(tags->'name:ru', '') AS "name:ru", NULLIF(tags->'name:sk', '') AS "name:sk", NULLIF(tags->'name:sl', '') AS "name:sl", NULLIF(tags->'name:sq', '') AS "name:sq", NULLIF(tags->'name:sr', '') AS "name:sr", NULLIF(tags->'name:sr-Latn', '') AS "name:sr-Latn", NULLIF(tags->'name:sv', '') AS "name:sv", NULLIF(tags->'name:th', '') AS "name:th", NULLIF(tags->'name:tr', '') AS "name:tr", NULLIF(tags->'name:uk', '') AS "name:uk", NULLIF(tags->'name:zh', '') AS "name:zh", NULLIF(tags->'name_int', '') AS "name_int", NULLIF(tags->'name:latin', '') AS "name:latin", NULLIF(tags->'name:nonlatin', '') AS "name:nonlatin" FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 64, true) AS mvtgeometry, name, name_en, name_de, class, ele, ele_ft, rank, osm_id, tags FROM (SELECT osm_id, geometry, name, name_en, name_de, tags, class, ele, ele_ft, rank FROM layer_mountain_peak(TileBBox(:zoom, :x, :y), :zoom, 256)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'park', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, class, name, name_en, name_de, rank, tags, NULLIF(tags->'name:ar', '') AS "name:ar", NULLIF(tags->'name:az', '') AS "name:az", NULLIF(tags->'name:be', '') AS "name:be", NULLIF(tags->'name:bg', '') AS "name:bg", NULLIF(tags->'name:br', '') AS "name:br", NULLIF(tags->'name:bs', '') AS "name:bs", NULLIF(tags->'name:ca', '') AS "name:ca", NULLIF(tags->'name:co', '') AS "name:co", NULLIF(tags->'name:cs', '') AS "name:cs", NULLIF(tags->'name:cy', '') AS "name:cy", NULLIF(tags->'name:da', '') AS "name:da", NULLIF(tags->'name:de', '') AS "name:de", NULLIF(tags->'name:el', '') AS "name:el", NULLIF(tags->'name:en', '') AS "name:en", NULLIF(tags->'name:eo', '') AS "name:eo", NULLIF(tags->'name:es', '') AS "name:es", NULLIF(tags->'name:et', '') AS "name:et", NULLIF(tags->'name:eu', '') AS "name:eu", NULLIF(tags->'name:fi', '') AS "name:fi", NULLIF(tags->'name:fr', '') AS "name:fr", NULLIF(tags->'name:fy', '') AS "name:fy", NULLIF(tags->'name:ga', '') AS "name:ga", NULLIF(tags->'name:gd', '') AS "name:gd", NULLIF(tags->'name:he', '') AS "name:he", NULLIF(tags->'name:hr', '') AS "name:hr", NULLIF(tags->'name:hu', '') AS "name:hu", NULLIF(tags->'name:hy', '') AS "name:hy", NULLIF(tags->'name:id', '') AS "name:id", NULLIF(tags->'name:is', '') AS "name:is", NULLIF(tags->'name:it', '') AS "name:it", NULLIF(tags->'name:ja', '') AS "name:ja", NULLIF(tags->'name:ja_kana', '') AS "name:ja_kana", NULLIF(tags->'name:ja_rm', '') AS "name:ja_rm", NULLIF(tags->'name:ka', '') AS "name:ka", NULLIF(tags->'name:kk', '') AS "name:kk", NULLIF(tags->'name:kn', '') AS "name:kn", NULLIF(tags->'name:ko', '') AS "name:ko", NULLIF(tags->'name:ko_rm', '') AS "name:ko_rm", NULLIF(tags->'name:la', '') AS "name:la", NULLIF(tags->'name:lb', '') AS "name:lb", NULLIF(tags->'name:lt', '') AS "name:lt", NULLIF(tags->'name:lv', '') AS "name:lv", NULLIF(tags->'name:mk', '') AS "name:mk", NULLIF(tags->'name:mt', '') AS "name:mt", NULLIF(tags->'name:ml', '') AS "name:ml", NULLIF(tags->'name:nl', '') AS "name:nl", NULLIF(tags->'name:no', '') AS "name:no", NULLIF(tags->'name:oc', '') AS "name:oc", NULLIF(tags->'name:pl', '') AS "name:pl", NULLIF(tags->'name:pt', '') AS "name:pt", NULLIF(tags->'name:rm', '') AS "name:rm", NULLIF(tags->'name:ro', '') AS "name:ro", NULLIF(tags->'name:ru', '') AS "name:ru", NULLIF(tags->'name:sk', '') AS "name:sk", NULLIF(tags->'name:sl', '') AS "name:sl", NULLIF(tags->'name:sq', '') AS "name:sq", NULLIF(tags->'name:sr', '') AS "name:sr", NULLIF(tags->'name:sr-Latn', '') AS "name:sr-Latn", NULLIF(tags->'name:sv', '') AS "name:sv", NULLIF(tags->'name:th', '') AS "name:th", NULLIF(tags->'name:tr', '') AS "name:tr", NULLIF(tags->'name:uk', '') AS "name:uk", NULLIF(tags->'name:zh', '') AS "name:zh", NULLIF(tags->'name_int', '') AS "name_int", NULLIF(tags->'name:latin', '') AS "name:latin", NULLIF(tags->'name:nonlatin', '') AS "name:nonlatin" FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 4, true) AS mvtgeometry, class, name, name_en, name_de, rank, tags FROM (SELECT geometry, class, name, name_en, name_de, tags, rank FROM layer_park(TileBBox(:zoom, :x, :y), :zoom, 256)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'boundary', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, admin_level, disputed, maritime FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 4, true) AS mvtgeometry, admin_level, disputed, maritime FROM (SELECT geometry, admin_level, disputed, maritime FROM layer_boundary(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'aeroway', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, ref, class FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 4, true) AS mvtgeometry, ref, class FROM (SELECT geometry, ref, class FROM layer_aeroway(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'transportation', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, class, subclass, brunnel, oneway, ramp, service, layer, level, indoor, surface FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 4, true) AS mvtgeometry, class, subclass, brunnel, oneway, ramp, service, layer, level, indoor, surface FROM (SELECT geometry, class, subclass, oneway, ramp, brunnel, service, layer, level, indoor, surface FROM layer_transportation(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'building', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, render_height, render_min_height, colour, hide_3d FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 4, true) AS mvtgeometry, render_height, render_min_height, colour, hide_3d FROM (SELECT geometry, render_height, render_min_height, colour, hide_3d FROM layer_building(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'water_name', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, name, name_en, name_de, class, intermittent, osm_id, tags, NULLIF(tags->'name:ar', '') AS "name:ar", NULLIF(tags->'name:az', '') AS "name:az", NULLIF(tags->'name:be', '') AS "name:be", NULLIF(tags->'name:bg', '') AS "name:bg", NULLIF(tags->'name:br', '') AS "name:br", NULLIF(tags->'name:bs', '') AS "name:bs", NULLIF(tags->'name:ca', '') AS "name:ca", NULLIF(tags->'name:co', '') AS "name:co", NULLIF(tags->'name:cs', '') AS "name:cs", NULLIF(tags->'name:cy', '') AS "name:cy", NULLIF(tags->'name:da', '') AS "name:da", NULLIF(tags->'name:de', '') AS "name:de", NULLIF(tags->'name:el', '') AS "name:el", NULLIF(tags->'name:en', '') AS "name:en", NULLIF(tags->'name:eo', '') AS "name:eo", NULLIF(tags->'name:es', '') AS "name:es", NULLIF(tags->'name:et', '') AS "name:et", NULLIF(tags->'name:eu', '') AS "name:eu", NULLIF(tags->'name:fi', '') AS "name:fi", NULLIF(tags->'name:fr', '') AS "name:fr", NULLIF(tags->'name:fy', '') AS "name:fy", NULLIF(tags->'name:ga', '') AS "name:ga", NULLIF(tags->'name:gd', '') AS "name:gd", NULLIF(tags->'name:he', '') AS "name:he", NULLIF(tags->'name:hr', '') AS "name:hr", NULLIF(tags->'name:hu', '') AS "name:hu", NULLIF(tags->'name:hy', '') AS "name:hy", NULLIF(tags->'name:id', '') AS "name:id", NULLIF(tags->'name:is', '') AS "name:is", NULLIF(tags->'name:it', '') AS "name:it", NULLIF(tags->'name:ja', '') AS "name:ja", NULLIF(tags->'name:ja_kana', '') AS "name:ja_kana", NULLIF(tags->'name:ja_rm', '') AS "name:ja_rm", NULLIF(tags->'name:ka', '') AS "name:ka", NULLIF(tags->'name:kk', '') AS "name:kk", NULLIF(tags->'name:kn', '') AS "name:kn", NULLIF(tags->'name:ko', '') AS "name:ko", NULLIF(tags->'name:ko_rm', '') AS "name:ko_rm", NULLIF(tags->'name:la', '') AS "name:la", NULLIF(tags->'name:lb', '') AS "name:lb", NULLIF(tags->'name:lt', '') AS "name:lt", NULLIF(tags->'name:lv', '') AS "name:lv", NULLIF(tags->'name:mk', '') AS "name:mk", NULLIF(tags->'name:mt', '') AS "name:mt", NULLIF(tags->'name:ml', '') AS "name:ml", NULLIF(tags->'name:nl', '') AS "name:nl", NULLIF(tags->'name:no', '') AS "name:no", NULLIF(tags->'name:oc', '') AS "name:oc", NULLIF(tags->'name:pl', '') AS "name:pl", NULLIF(tags->'name:pt', '') AS "name:pt", NULLIF(tags->'name:rm', '') AS "name:rm", NULLIF(tags->'name:ro', '') AS "name:ro", NULLIF(tags->'name:ru', '') AS "name:ru", NULLIF(tags->'name:sk', '') AS "name:sk", NULLIF(tags->'name:sl', '') AS "name:sl", NULLIF(tags->'name:sq', '') AS "name:sq", NULLIF(tags->'name:sr', '') AS "name:sr", NULLIF(tags->'name:sr-Latn', '') AS "name:sr-Latn", NULLIF(tags->'name:sv', '') AS "name:sv", NULLIF(tags->'name:th', '') AS "name:th", NULLIF(tags->'name:tr', '') AS "name:tr", NULLIF(tags->'name:uk', '') AS "name:uk", NULLIF(tags->'name:zh', '') AS "name:zh", NULLIF(tags->'name_int', '') AS "name_int", NULLIF(tags->'name:latin', '') AS "name:latin", NULLIF(tags->'name:nonlatin', '') AS "name:nonlatin" FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 256, true) AS mvtgeometry, name, name_en, name_de, class, intermittent, osm_id, tags FROM (SELECT osm_id, geometry, name, name_en, name_de, tags, class, intermittent FROM layer_water_name(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'transportation_name', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, name, name_en, name_de, ref, ref_length, network, class, subclass, level, layer, indoor, tags, NULLIF(tags->'name:ar', '') AS "name:ar", NULLIF(tags->'name:az', '') AS "name:az", NULLIF(tags->'name:be', '') AS "name:be", NULLIF(tags->'name:bg', '') AS "name:bg", NULLIF(tags->'name:br', '') AS "name:br", NULLIF(tags->'name:bs', '') AS "name:bs", NULLIF(tags->'name:ca', '') AS "name:ca", NULLIF(tags->'name:co', '') AS "name:co", NULLIF(tags->'name:cs', '') AS "name:cs", NULLIF(tags->'name:cy', '') AS "name:cy", NULLIF(tags->'name:da', '') AS "name:da", NULLIF(tags->'name:de', '') AS "name:de", NULLIF(tags->'name:el', '') AS "name:el", NULLIF(tags->'name:en', '') AS "name:en", NULLIF(tags->'name:eo', '') AS "name:eo", NULLIF(tags->'name:es', '') AS "name:es", NULLIF(tags->'name:et', '') AS "name:et", NULLIF(tags->'name:eu', '') AS "name:eu", NULLIF(tags->'name:fi', '') AS "name:fi", NULLIF(tags->'name:fr', '') AS "name:fr", NULLIF(tags->'name:fy', '') AS "name:fy", NULLIF(tags->'name:ga', '') AS "name:ga", NULLIF(tags->'name:gd', '') AS "name:gd", NULLIF(tags->'name:he', '') AS "name:he", NULLIF(tags->'name:hr', '') AS "name:hr", NULLIF(tags->'name:hu', '') AS "name:hu", NULLIF(tags->'name:hy', '') AS "name:hy", NULLIF(tags->'name:id', '') AS "name:id", NULLIF(tags->'name:is', '') AS "name:is", NULLIF(tags->'name:it', '') AS "name:it", NULLIF(tags->'name:ja', '') AS "name:ja", NULLIF(tags->'name:ja_kana', '') AS "name:ja_kana", NULLIF(tags->'name:ja_rm', '') AS "name:ja_rm", NULLIF(tags->'name:ka', '') AS "name:ka", NULLIF(tags->'name:kk', '') AS "name:kk", NULLIF(tags->'name:kn', '') AS "name:kn", NULLIF(tags->'name:ko', '') AS "name:ko", NULLIF(tags->'name:ko_rm', '') AS "name:ko_rm", NULLIF(tags->'name:la', '') AS "name:la", NULLIF(tags->'name:lb', '') AS "name:lb", NULLIF(tags->'name:lt', '') AS "name:lt", NULLIF(tags->'name:lv', '') AS "name:lv", NULLIF(tags->'name:mk', '') AS "name:mk", NULLIF(tags->'name:mt', '') AS "name:mt", NULLIF(tags->'name:ml', '') AS "name:ml", NULLIF(tags->'name:nl', '') AS "name:nl", NULLIF(tags->'name:no', '') AS "name:no", NULLIF(tags->'name:oc', '') AS "name:oc", NULLIF(tags->'name:pl', '') AS "name:pl", NULLIF(tags->'name:pt', '') AS "name:pt", NULLIF(tags->'name:rm', '') AS "name:rm", NULLIF(tags->'name:ro', '') AS "name:ro", NULLIF(tags->'name:ru', '') AS "name:ru", NULLIF(tags->'name:sk', '') AS "name:sk", NULLIF(tags->'name:sl', '') AS "name:sl", NULLIF(tags->'name:sq', '') AS "name:sq", NULLIF(tags->'name:sr', '') AS "name:sr", NULLIF(tags->'name:sr-Latn', '') AS "name:sr-Latn", NULLIF(tags->'name:sv', '') AS "name:sv", NULLIF(tags->'name:th', '') AS "name:th", NULLIF(tags->'name:tr', '') AS "name:tr", NULLIF(tags->'name:uk', '') AS "name:uk", NULLIF(tags->'name:zh', '') AS "name:zh", NULLIF(tags->'name_int', '') AS "name_int", NULLIF(tags->'name:latin', '') AS "name:latin", NULLIF(tags->'name:nonlatin', '') AS "name:nonlatin" FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 8, true) AS mvtgeometry, name, name_en, name_de, ref, ref_length, network, class, subclass, level, layer, indoor, tags FROM (SELECT geometry, name, name_en, name_de, tags, ref, ref_length, network::text, class::text, subclass, layer, level, indoor FROM layer_transportation_name(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'place', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, name, name_en, name_de, capital, class, iso_a2, rank, osm_id, tags, NULLIF(tags->'name:ar', '') AS "name:ar", NULLIF(tags->'name:az', '') AS "name:az", NULLIF(tags->'name:be', '') AS "name:be", NULLIF(tags->'name:bg', '') AS "name:bg", NULLIF(tags->'name:br', '') AS "name:br", NULLIF(tags->'name:bs', '') AS "name:bs", NULLIF(tags->'name:ca', '') AS "name:ca", NULLIF(tags->'name:co', '') AS "name:co", NULLIF(tags->'name:cs', '') AS "name:cs", NULLIF(tags->'name:cy', '') AS "name:cy", NULLIF(tags->'name:da', '') AS "name:da", NULLIF(tags->'name:de', '') AS "name:de", NULLIF(tags->'name:el', '') AS "name:el", NULLIF(tags->'name:en', '') AS "name:en", NULLIF(tags->'name:eo', '') AS "name:eo", NULLIF(tags->'name:es', '') AS "name:es", NULLIF(tags->'name:et', '') AS "name:et", NULLIF(tags->'name:eu', '') AS "name:eu", NULLIF(tags->'name:fi', '') AS "name:fi", NULLIF(tags->'name:fr', '') AS "name:fr", NULLIF(tags->'name:fy', '') AS "name:fy", NULLIF(tags->'name:ga', '') AS "name:ga", NULLIF(tags->'name:gd', '') AS "name:gd", NULLIF(tags->'name:he', '') AS "name:he", NULLIF(tags->'name:hr', '') AS "name:hr", NULLIF(tags->'name:hu', '') AS "name:hu", NULLIF(tags->'name:hy', '') AS "name:hy", NULLIF(tags->'name:id', '') AS "name:id", NULLIF(tags->'name:is', '') AS "name:is", NULLIF(tags->'name:it', '') AS "name:it", NULLIF(tags->'name:ja', '') AS "name:ja", NULLIF(tags->'name:ja_kana', '') AS "name:ja_kana", NULLIF(tags->'name:ja_rm', '') AS "name:ja_rm", NULLIF(tags->'name:ka', '') AS "name:ka", NULLIF(tags->'name:kk', '') AS "name:kk", NULLIF(tags->'name:kn', '') AS "name:kn", NULLIF(tags->'name:ko', '') AS "name:ko", NULLIF(tags->'name:ko_rm', '') AS "name:ko_rm", NULLIF(tags->'name:la', '') AS "name:la", NULLIF(tags->'name:lb', '') AS "name:lb", NULLIF(tags->'name:lt', '') AS "name:lt", NULLIF(tags->'name:lv', '') AS "name:lv", NULLIF(tags->'name:mk', '') AS "name:mk", NULLIF(tags->'name:mt', '') AS "name:mt", NULLIF(tags->'name:ml', '') AS "name:ml", NULLIF(tags->'name:nl', '') AS "name:nl", NULLIF(tags->'name:no', '') AS "name:no", NULLIF(tags->'name:oc', '') AS "name:oc", NULLIF(tags->'name:pl', '') AS "name:pl", NULLIF(tags->'name:pt', '') AS "name:pt", NULLIF(tags->'name:rm', '') AS "name:rm", NULLIF(tags->'name:ro', '') AS "name:ro", NULLIF(tags->'name:ru', '') AS "name:ru", NULLIF(tags->'name:sk', '') AS "name:sk", NULLIF(tags->'name:sl', '') AS "name:sl", NULLIF(tags->'name:sq', '') AS "name:sq", NULLIF(tags->'name:sr', '') AS "name:sr", NULLIF(tags->'name:sr-Latn', '') AS "name:sr-Latn", NULLIF(tags->'name:sv', '') AS "name:sv", NULLIF(tags->'name:th', '') AS "name:th", NULLIF(tags->'name:tr', '') AS "name:tr", NULLIF(tags->'name:uk', '') AS "name:uk", NULLIF(tags->'name:zh', '') AS "name:zh", NULLIF(tags->'name_int', '') AS "name_int", NULLIF(tags->'name:latin', '') AS "name:latin", NULLIF(tags->'name:nonlatin', '') AS "name:nonlatin" FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 256, true) AS mvtgeometry, name, name_en, name_de, capital, class, iso_a2, rank, osm_id, tags FROM (SELECT osm_id, geometry, name, name_en, name_de, tags, class, rank, capital, iso_a2 FROM layer_place(TileBBox(:zoom, :x, :y), :zoom, 256)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'housenumber', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, housenumber FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 8, true) AS mvtgeometry, housenumber FROM (SELECT geometry, housenumber FROM layer_housenumber(TileBBox(:zoom, :x, :y), :zoom)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'poi', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, name, name_en, name_de, class, subclass, rank, agg_stop, level, layer, indoor, osm_id, tags, NULLIF(tags->'name:ar', '') AS "name:ar", NULLIF(tags->'name:az', '') AS "name:az", NULLIF(tags->'name:be', '') AS "name:be", NULLIF(tags->'name:bg', '') AS "name:bg", NULLIF(tags->'name:br', '') AS "name:br", NULLIF(tags->'name:bs', '') AS "name:bs", NULLIF(tags->'name:ca', '') AS "name:ca", NULLIF(tags->'name:co', '') AS "name:co", NULLIF(tags->'name:cs', '') AS "name:cs", NULLIF(tags->'name:cy', '') AS "name:cy", NULLIF(tags->'name:da', '') AS "name:da", NULLIF(tags->'name:de', '') AS "name:de", NULLIF(tags->'name:el', '') AS "name:el", NULLIF(tags->'name:en', '') AS "name:en", NULLIF(tags->'name:eo', '') AS "name:eo", NULLIF(tags->'name:es', '') AS "name:es", NULLIF(tags->'name:et', '') AS "name:et", NULLIF(tags->'name:eu', '') AS "name:eu", NULLIF(tags->'name:fi', '') AS "name:fi", NULLIF(tags->'name:fr', '') AS "name:fr", NULLIF(tags->'name:fy', '') AS "name:fy", NULLIF(tags->'name:ga', '') AS "name:ga", NULLIF(tags->'name:gd', '') AS "name:gd", NULLIF(tags->'name:he', '') AS "name:he", NULLIF(tags->'name:hr', '') AS "name:hr", NULLIF(tags->'name:hu', '') AS "name:hu", NULLIF(tags->'name:hy', '') AS "name:hy", NULLIF(tags->'name:id', '') AS "name:id", NULLIF(tags->'name:is', '') AS "name:is", NULLIF(tags->'name:it', '') AS "name:it", NULLIF(tags->'name:ja', '') AS "name:ja", NULLIF(tags->'name:ja_kana', '') AS "name:ja_kana", NULLIF(tags->'name:ja_rm', '') AS "name:ja_rm", NULLIF(tags->'name:ka', '') AS "name:ka", NULLIF(tags->'name:kk', '') AS "name:kk", NULLIF(tags->'name:kn', '') AS "name:kn", NULLIF(tags->'name:ko', '') AS "name:ko", NULLIF(tags->'name:ko_rm', '') AS "name:ko_rm", NULLIF(tags->'name:la', '') AS "name:la", NULLIF(tags->'name:lb', '') AS "name:lb", NULLIF(tags->'name:lt', '') AS "name:lt", NULLIF(tags->'name:lv', '') AS "name:lv", NULLIF(tags->'name:mk', '') AS "name:mk", NULLIF(tags->'name:mt', '') AS "name:mt", NULLIF(tags->'name:ml', '') AS "name:ml", NULLIF(tags->'name:nl', '') AS "name:nl", NULLIF(tags->'name:no', '') AS "name:no", NULLIF(tags->'name:oc', '') AS "name:oc", NULLIF(tags->'name:pl', '') AS "name:pl", NULLIF(tags->'name:pt', '') AS "name:pt", NULLIF(tags->'name:rm', '') AS "name:rm", NULLIF(tags->'name:ro', '') AS "name:ro", NULLIF(tags->'name:ru', '') AS "name:ru", NULLIF(tags->'name:sk', '') AS "name:sk", NULLIF(tags->'name:sl', '') AS "name:sl", NULLIF(tags->'name:sq', '') AS "name:sq", NULLIF(tags->'name:sr', '') AS "name:sr", NULLIF(tags->'name:sr-Latn', '') AS "name:sr-Latn", NULLIF(tags->'name:sv', '') AS "name:sv", NULLIF(tags->'name:th', '') AS "name:th", NULLIF(tags->'name:tr', '') AS "name:tr", NULLIF(tags->'name:uk', '') AS "name:uk", NULLIF(tags->'name:zh', '') AS "name:zh", NULLIF(tags->'name_int', '') AS "name_int", NULLIF(tags->'name:latin', '') AS "name:latin", NULLIF(tags->'name:nonlatin', '') AS "name:nonlatin" FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 64, true) AS mvtgeometry, name, name_en, name_de, class, subclass, rank, agg_stop, level, layer, indoor, osm_id, tags FROM (SELECT osm_id, geometry, name, name_en, name_de, tags, class, subclass, agg_stop, layer, level, indoor, rank FROM layer_poi(TileBBox(:zoom, :x, :y), :zoom, 256)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
    UNION ALL
  SELECT FALSE AS IsEmpty, ST_AsMVT(mvtl2, 'aerodrome_label', 4096, 'mvtgeometry') as mvtl FROM (SELECT mvtgeometry, name, name_en, name_de, class, iata, icao, ele, ele_ft, osm_id, tags, NULLIF(tags->'name:ar', '') AS "name:ar", NULLIF(tags->'name:az', '') AS "name:az", NULLIF(tags->'name:be', '') AS "name:be", NULLIF(tags->'name:bg', '') AS "name:bg", NULLIF(tags->'name:br', '') AS "name:br", NULLIF(tags->'name:bs', '') AS "name:bs", NULLIF(tags->'name:ca', '') AS "name:ca", NULLIF(tags->'name:co', '') AS "name:co", NULLIF(tags->'name:cs', '') AS "name:cs", NULLIF(tags->'name:cy', '') AS "name:cy", NULLIF(tags->'name:da', '') AS "name:da", NULLIF(tags->'name:de', '') AS "name:de", NULLIF(tags->'name:el', '') AS "name:el", NULLIF(tags->'name:en', '') AS "name:en", NULLIF(tags->'name:eo', '') AS "name:eo", NULLIF(tags->'name:es', '') AS "name:es", NULLIF(tags->'name:et', '') AS "name:et", NULLIF(tags->'name:eu', '') AS "name:eu", NULLIF(tags->'name:fi', '') AS "name:fi", NULLIF(tags->'name:fr', '') AS "name:fr", NULLIF(tags->'name:fy', '') AS "name:fy", NULLIF(tags->'name:ga', '') AS "name:ga", NULLIF(tags->'name:gd', '') AS "name:gd", NULLIF(tags->'name:he', '') AS "name:he", NULLIF(tags->'name:hr', '') AS "name:hr", NULLIF(tags->'name:hu', '') AS "name:hu", NULLIF(tags->'name:hy', '') AS "name:hy", NULLIF(tags->'name:id', '') AS "name:id", NULLIF(tags->'name:is', '') AS "name:is", NULLIF(tags->'name:it', '') AS "name:it", NULLIF(tags->'name:ja', '') AS "name:ja", NULLIF(tags->'name:ja_kana', '') AS "name:ja_kana", NULLIF(tags->'name:ja_rm', '') AS "name:ja_rm", NULLIF(tags->'name:ka', '') AS "name:ka", NULLIF(tags->'name:kk', '') AS "name:kk", NULLIF(tags->'name:kn', '') AS "name:kn", NULLIF(tags->'name:ko', '') AS "name:ko", NULLIF(tags->'name:ko_rm', '') AS "name:ko_rm", NULLIF(tags->'name:la', '') AS "name:la", NULLIF(tags->'name:lb', '') AS "name:lb", NULLIF(tags->'name:lt', '') AS "name:lt", NULLIF(tags->'name:lv', '') AS "name:lv", NULLIF(tags->'name:mk', '') AS "name:mk", NULLIF(tags->'name:mt', '') AS "name:mt", NULLIF(tags->'name:ml', '') AS "name:ml", NULLIF(tags->'name:nl', '') AS "name:nl", NULLIF(tags->'name:no', '') AS "name:no", NULLIF(tags->'name:oc', '') AS "name:oc", NULLIF(tags->'name:pl', '') AS "name:pl", NULLIF(tags->'name:pt', '') AS "name:pt", NULLIF(tags->'name:rm', '') AS "name:rm", NULLIF(tags->'name:ro', '') AS "name:ro", NULLIF(tags->'name:ru', '') AS "name:ru", NULLIF(tags->'name:sk', '') AS "name:sk", NULLIF(tags->'name:sl', '') AS "name:sl", NULLIF(tags->'name:sq', '') AS "name:sq", NULLIF(tags->'name:sr', '') AS "name:sr", NULLIF(tags->'name:sr-Latn', '') AS "name:sr-Latn", NULLIF(tags->'name:sv', '') AS "name:sv", NULLIF(tags->'name:th', '') AS "name:th", NULLIF(tags->'name:tr', '') AS "name:tr", NULLIF(tags->'name:uk', '') AS "name:uk", NULLIF(tags->'name:zh', '') AS "name:zh", NULLIF(tags->'name_int', '') AS "name_int", NULLIF(tags->'name:latin', '') AS "name:latin", NULLIF(tags->'name:nonlatin', '') AS "name:nonlatin" FROM (SELECT ST_AsMVTGeom(geometry, TileBBox(:zoom, :x, :y), 4096, 64, true) AS mvtgeometry, name, name_en, name_de, class, iata, icao, ele, ele_ft, osm_id, tags FROM (SELECT osm_id, geometry, name, name_en, name_de, tags, class, iata, icao, ele, ele_ft FROM layer_aerodrome_label (TileBBox(:zoom, :x, :y), :zoom, 256)) AS t) AS mvtl1 WHERE ST_IsValid(mvtgeometry)) AS mvtl2 HAVING COUNT(*) > 0
) AS all_layers
) AS counter_layers
HAVING BOOL_AND(NOT IsEmpty OR LayerCount <> 1)