PostgreSQL+Postgis MapboxVectorTile应用

最新推荐文章于 2023-01-11 17:02:36 发布

上古石

最新推荐文章于 2023-01-11 17:02:36 发布

阅读量2.2k

点赞数 2

分类专栏：海量数据展示文章标签： postgresql postgis

本文链接：https://blog.csdn.net/gery1990/article/details/110471047

版权

海量数据展示专栏收录该内容

2 篇文章 0 订阅

订阅专栏

PostgreSQL+Postgis实时矢量数据加载

背景
软件环境
实现过程
结果展示
相关链接

背景

该实验基于Postgres+Postgis搭建实时矢量瓦片服务。
矢量瓦片的优势

可以支持高分辨率屏幕显示
地图渲染在前端，可以支持前端随意更改配图方案，解决传统栅格瓦片动态样式上的问题
要素查询可以在前端进行
保持了矢量数据的优势，同时采用切片方式在传输效率上有一定提升

实时矢量瓦片

不在线下用工具预先切片，采用即时浏览传输矢量瓦片;
采用实时矢量切片可以做到数据编辑功能，同时解决数据时效性问题

使用到的相关函数

ST_AsMvtGeo 将Geom转化为MVT的geom
ST_AsMVT 将geom转换为MVT数据
ST_TileEnvelope 根据行列号获取Envelop （该函数需要Postgis3.0才有，使用postgis-vt-util代替）

软件环境

PostgreSQL11
Postgis2.5
java
(前端渲染) leaflet1.7、mapbox

实现过程

模拟数据

使用sql语句创建表，并在给定坐标范围内模拟点数据

CREATE TABLE data_1000000 AS SELECT
( ST_Dump ( ST_GeneratePoints ( kl.geom, 200000 ) ) ).geom AS geom,
md5( ( random ( ) * random ( ) ) :: text ) AS id,
random ( ) * 1000 AS val 
FROM ( 
  SELECT ST_SetSRID ( ST_MakeBox2D ( ST_Point ( 113.213316,22.954094 ), ST_Point ( 113.62805,23.368228 ) ), 4326 
) geom ) kl;

sql解读：
创建表名：data_1000000
坐标范围：左下角坐标(113.213316,22.954094)、右下角坐标(113.62805,23.368228)，坐标系SRID为4326
模拟数据量：100万
表字段：geom（空间字段）、id（md5格式的唯一编码）、val（点的权重值0-1000）

部署扩展函数

由于需要用到ST_TileEnvelope函数，该函数需要postgis3.0才支持，所以使用扩展的函数库实现(postgis-vt-util下载)
执行postgis-vt-util.sql添加相关函数到数据库

这个库由mapbox提供，生成符合mapbox矢量切片格式的数据

后端接口

后端需要实现前端请求切片范围的矢量切片生成，传入的参数可能是行列号或者经纬度范围，最终返回byte的矢量切片数据

处理逻辑

graph LR
start("前端请求") --> isEnvelop("是否传入经纬度范围")
isEnvelop --no--> tileBBox("根据切片z,x,y转换经纬度范围")
isEnvelop --yes--> makeEnvelop("生成几何范围")
tileBBox --> makeEnvelop
makeEnvelop --> asMVTGeom("将几何图形转为mapbox矢量瓦片坐标")
asMVTGeom --> asMVT("返回mapbox矢量切片的byte数组")

点线面在转换byte数组时需要分开处理

代码编写

//根据切片xyz获取矢量切片
public byte[] getMVT(String tableName, int x, int y, int z, int srid) {
    byte[] res;
    try {
        String sqlStr = "SELECT " +
                "ST_AsMVT( vt, 'points', 256, 'geo' ) FROM ( " +
                "SELECT ST_SetSRID ( ST_Point ( ST_X ( A.geo ), ST_Y ( A.geo )), " + srid + " ) geo FROM (" +
                "SELECT ST_AsMVTGeom ( w.geom, Box2D ( TileBBox ( " + z + "," + x + "," + y + ", " + srid + " )), 256, 0, TRUE ) AS geo  " +
                "FROM " + tableName + " w  WHERE TileBBox ( " + z + "," + x + "," + y + ", " + srid + " ) && geom  ) A  " +
                "GROUP BY ST_X ( A.geo ), ST_Y ( A.geo ) ) AS vt";

        res = jdbcTemplate.queryForObject(sqlStr,byte[].class);
    } catch (Exception e) {
        throw e;
    }
    return res;
}

//根据空间范围获取矢量切片
public byte[] getMVTByEnvelop(String tableName, double lon_min, double lat_min, double lon_max, double lat_max, int srid) {
    byte[] res;
    try {
        String sqlStr = "SELECT " +
                "ST_AsMVT( vt, 'points', 256, 'geo' ) FROM ( " +
                "SELECT ST_SetSRID ( ST_Point ( ST_X ( A.geo ), ST_Y ( A.geo )), " + srid + " ) geo FROM (" +
                "SELECT ST_AsMVTGeom ( w.geom,ST_MakeEnvelope ( " + lon_min + "," + lat_min + "," + lon_max + "," + lat_max + ", " + srid + " ), 256, 0, TRUE ) AS geo  " +
                "FROM " + tableName + " w  WHERE ST_MakeEnvelope ( " + lon_min + "," + lat_min + "," + lon_max + "," + lat_max + ", " + srid + " ) && geom  ) A  " +
                "GROUP BY ST_X ( A.geo ), ST_Y ( A.geo ) ) AS vt";

        res = jdbcTemplate.queryForObject(sqlStr, byte[].class);
    } catch (Exception e) {
        throw e;
    }
    return res;
}

前端代码编写

mapboxgl加载方法

<!DOCTYPE html>
<html>
<head>
    <title>Mapbox加载MVT</title>
    <meta charset='utf-8'>
    <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no">
    <script src="https://api.mapbox.com/mapbox-gl-js/v1.10.0/mapbox-gl.js"></script>
    <link href="https://api.mapbox.com/mapbox-gl-js/v1.10.0/mapbox-gl.css" rel="stylesheet" />
    <style>
        body {
            margin: 0;
            padding: 0;
        }
        html,
        body,
        #map {
            height: 100%;
        }
        .layerPanel {
            position: absolute;
            top: 10px;
            right: 20px;
            background: #ffffff;
            padding: 5px 2px;
        }
    </style>
</head>

<body>
    <div id='map'></div>
    <script>
        let center = [113.33616, 23.0963];
		//获取地图样式
        let style = getStyle();
        //创建3857坐标系地图
        var map = window.map = new mapboxgl.Map({
            container: 'map',
            // epsg: 'EPSG:3857',
            center : center,
            zoom:12,
            hash: true,
            style: style
        });
        // map.showTileBoundaries = true;
        map.on('load', function () {
            //添加天地图底图
            addTDTLayers(map);
            map.addLayer({
                "type": "circle",
                "source": "test",
                "paint":{
					"circle-color": "blue",
					"circle-radius":4,
                },
                "source-layer": "points",
                "maxzoom": 17,
                "id": "1"
            })
        });

        function getStyle() {
            let style = {
                "version": 8,
                "name": "Basic-my",
                "metadata": {
                    "mapbox:autocomposite": true,
                    "mapbox:type": "template"
                },
                "center": center,
                "zoom": 3,
                "bearing": 0,
                "pitch": 0,
                "sources": {
                    "test": {
                        "type": "vector",
                        "tiles": ["{后端服务}/getMVT?tableName=data_1000000&x={x}&y={y}&z={z}"]
                    }
                },
                "layers": []
            };
            return style;
        }
        //添加3857坐标系天地图
        function addTDTLayers(map) {
            //添加3857天地图矢量source
            var source_vec = {
                "type": "raster",
                "tiles": [
                    "http://t0.tianditu.com/DataServer?T=vec_w&x={x}&y={y}&l={z}&tk=7b06bd204544a8631beccd5fd56ad8c1",
                ],

                "tileSize": 256
            }
            if (!map.getSource("TDT_VEC")) {
                map.addSource("TDT_VEC", source_vec);
            }

            //添加3857天地图矢量图层
            var Layer_vec = {
                "id": "tdtvec",
                "type": "raster",
                "source": "TDT_VEC",
                "minzoom": 0,
                "maxzoom": 17
            }
            if (!map.getLayer("tdtvec")) {
                map.addLayer(Layer_vec);
            }
        }
    </script>
</body>
</html>

Leaflet加载方式

<!DOCTYPE html>
<html>
<head>
	<title>VectorGrid.Protobuf example</title>
	<meta charset="utf-8" />
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<link rel="stylesheet" href="src/leaflet.css" />
</head>
<body style='margin:0'>
	<div id="map" style="width: 100vw; height: 100vh; background: white"></div>
	<script type="text/javascript"  src="src/leaflet-src.js"></script>
	<script type="text/javascript"  src="src/Leaflet.VectorGrid.js"></script>
	<script>
		var map = L.map('map',{
			renderer: L.canvas
		});			
		map.setView({ lat: 23.0963, lng: 113.33616 }, 14);
		//加载底图
		L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
			attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
		}).addTo(map);
	
		var vectorTileStyling = {
			points:{
				weight: 2,
				color: 'red',
				opacity: 1,
				fillColor: 'yellow',
				fill: true,
				radius: 6,
				fillOpacity: 0.7
			}
		}
		
		var mapboxUrl = "http://localhost:8081/xxx/getMVT?tableName=data_1000000&x={x}&y={y}&z={z}";

		var mapboxVectorTileOptions = {
			rendererFactory: L.canvas.tile,
			vectorTileLayerStyles: vectorTileStyling
		};

		var mapboxPbfLayer = L.vectorGrid.protobuf(mapboxUrl, mapboxVectorTileOptions);
		map.addLayer(mapboxPbfLayer)
	</script>
</body>
</html>