PostgreSQL +martin将多张表输出成一个 MVT

引言正常创建表单会造成一个source中只包含一个layers不易维护。前提保证已有martin、PostgreSQL环境并且pgSQL中已正常创建多表。一、确认几何字段在pgAdmin中1、选中数据库要合并的数据库2、点击顶部Tools3、打开Qurey Tool4、执行语句SELECT f_table_schema, f_table_name, f_geometry_column, type, srid FROM geometry_columns WHERE f_table_name IN (test_lines, test_polygons);正常看到结果为表名几何字段类型SRID表1geomLINESTRING4326表2geomPOLYGON4326下面暂时假设两张表的几何字段都叫geom。如果查询结果不是geom需要把后面 SQL 里的geom替换成实际字段名。二、创建空间索引执行语句CREATE INDEX IF NOT EXISTS test_lines_geom_idx ON public.test_lines USING GIST (geom); CREATE INDEX IF NOT EXISTS test_polygons_geom_idx ON public.test_polygons USING GIST (geom); ANALYZE public.test_lines; ANALYZE public.test_polygons;空间索引能避免每次请求瓦片都全表扫描。三、创建组合瓦片函数执行以下完整SQLCREATE OR REPLACE FUNCTION public.base_map( z integer, x integer, y integer ) RETURNS bytea LANGUAGE sql STABLE PARALLEL SAFE AS $$ WITH tile_bounds AS ( SELECT ST_TileEnvelope(z, x, y) AS geom_3857 ), line_layer AS ( SELECT COALESCE( ST_AsMVT( line_features, test_lines, 4096, geom ), \x::bytea ) AS mvt FROM ( SELECT ST_AsMVTGeom( ST_Transform(t.geom, 3857), b.geom_3857, 4096, 64, true ) AS geom FROM public.test_lines AS t CROSS JOIN tile_bounds AS b WHERE t.geom ST_Transform(b.geom_3857, 4326) ) AS line_features WHERE geom IS NOT NULL ), polygon_layer AS ( SELECT COALESCE( ST_AsMVT( polygon_features, test_polygons, 4096, geom ), \x::bytea ) AS mvt FROM ( SELECT ST_AsMVTGeom( ST_Transform(t.geom, 3857), b.geom_3857, 4096, 64, true ) AS geom FROM public.test_polygons AS t CROSS JOIN tile_bounds AS b WHERE t.geom ST_Transform(b.geom_3857, 4326) ) AS polygon_features WHERE geom IS NOT NULL ) SELECT line_layer.mvt || polygon_layer.mvt FROM line_layer CROSS JOIN polygon_layer; $$;四、添加TileJSON图层说明继续执行语句COMMENT ON FUNCTION public.base_map(integer, integer, integer) IS { name: base_map, description: Lines and polygons composite vector tiles, vector_layers: [ { id: test_lines, description: Line features, fields: {} }, { id: test_polygons, description: Polygon features, fields: {} } ] };这会告诉客户端base_map中包含两个source-layer。五、检查函数执行语句SELECT routine_schema, routine_name, data_type FROM information_schema.routines WHERE routine_name base_map;应该能看到routine_schemanameroutine_namenamedata_typecharacter varyingpublicbase_mapbytea六、重启martin1、访问默认配置http://localhost:3000/catalog2、访问主要http://localhost:3000/base_map正常应返回{ name: base_map, tiles: [ http://localhost:3000/base_map/{z}/{x}/{y} ], vector_layers: [ { id: test_lines }, { id: test_polygons } ] }七、配置前端source仅供参考{ version: 8, name: MyLocalStyle, metadata: { maputnik:renderer: mlgljs }, sources: { base_map: { type: vector, url: http://localhost:3000/base_map } }, layers: [ { id: background, type: background, paint: { background-color: #f8f8f8 } }, { id: test_polygons_fill, type: fill, source: base_map, source-layer: test_polygons, paint: { fill-color: rgba(255, 255, 13, 1), fill-opacity: 0.1 } }, { id: test_polygons_outline, type: line, source: base_map, source-layer: test_polygons, paint: { line-width: 2, line-color: #4aa3ff } }, { id: test_lines_layer, type: line, source: base_map, source-layer: test_lines, paint: { line-width: 3, line-color: #ff0000 } } ], id: oyb34yv }八、如有新建表如何添加到集合新建表后base_map不会自动包含它。需要修改public.base_map()函数把新表生成的 MVT 拼进去然后更新函数注释并重启 Martin。假设新表叫test_points几何字段是geom坐标系是 EPSG:4326。1、确认新表信息在gisdb → Query Tool执行SELECT f_table_name, f_geometry_column, type, srid FROM geometry_columns WHERE f_table_name test_points;确认几何字段为geom类型为POINT或MULTIPOINTSRID 为43262、创建空间索引CREATE INDEX IF NOT EXISTS test_points_geom_idx ON public.test_points USING GIST (geom); ANALYZE public.test_points;3、更新base_map函数用下面的完整 SQL覆盖原来的函数CREATE OR REPLACE FUNCTION public.base_map( z integer, x integer, y integer ) RETURNS bytea LANGUAGE sql STABLE PARALLEL SAFE AS $$ WITH tile_bounds AS ( SELECT ST_TileEnvelope(z, x, y) AS geom_3857 ), line_layer AS ( SELECT COALESCE( ST_AsMVT( line_features, test_lines, 4096, geom ), \x::bytea ) AS mvt FROM ( SELECT ST_AsMVTGeom( ST_Transform(t.geom, 3857), b.geom_3857, 4096, 64, true ) AS geom FROM public.test_lines AS t CROSS JOIN tile_bounds AS b WHERE t.geom ST_Transform(b.geom_3857, 4326) ) AS line_features WHERE geom IS NOT NULL ), polygon_layer AS ( SELECT COALESCE( ST_AsMVT( polygon_features, test_polygons, 4096, geom ), \x::bytea ) AS mvt FROM ( SELECT ST_AsMVTGeom( ST_Transform(t.geom, 3857), b.geom_3857, 4096, 64, true ) AS geom FROM public.test_polygons AS t CROSS JOIN tile_bounds AS b WHERE t.geom ST_Transform(b.geom_3857, 4326) ) AS polygon_features WHERE geom IS NOT NULL ), point_layer AS ( SELECT COALESCE( ST_AsMVT( point_features, test_points, 4096, geom ), \x::bytea ) AS mvt FROM ( SELECT ST_AsMVTGeom( ST_Transform(t.geom, 3857), b.geom_3857, 4096, 64, true ) AS geom FROM public.test_points AS t CROSS JOIN tile_bounds AS b WHERE t.geom ST_Transform(b.geom_3857, 4326) ) AS point_features WHERE geom IS NOT NULL ) SELECT line_layer.mvt || polygon_layer.mvt || point_layer.mvt FROM line_layer CROSS JOIN polygon_layer CROSS JOIN point_layer; $$;新增内容主要是point_layer AS (...)以及最后增加的 || point_layer.mvt4、更新图层说明COMMENT ON FUNCTION public.base_map(integer, integer, integer) IS { name: base_map, description: Composite vector tiles, vector_layers: [ { id: test_lines, description: Line features, fields: {} }, { id: test_polygons, description: Polygon features, fields: {} }, { id: test_points, description: Point features, fields: {} } ] };5、测试函数SELECT octet_length(public.base_map(0, 0, 0)) AS tile_size;不报错就说明 SQL 基本正确。6、重启martin前端更新配置文件注以上就是完整创建组合矢量瓦片函数的方法如有建议请大胆提出