【磐维数据库】支持postgis插件以提供空间数据支持

背景说明

 江西移动公司下属智慧网格系统，积极响应数据库国产化工作，将业务数据库从MySQL迁移到磐维数据库上。

 该业务特点是利用PostGIS功能处理和分析全省网格化管理所需的地理信息，作为全国第一个需要使用PostGIS插件上线磐维的业务系统，其挑战和难度非常大。

 在集团运维和研发的大力支持下，现场驻场同事和业务开发同事一起奋战打磨，最终成功上线磐维数据库。

 在此简单回顾安装配置PostGIS和数据迁移的过程。

一、磐维部署postgis扩展

功能描述

PostGIS是在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力的开源GIS数据库，它在PostgreSQL的基础上增加了表达地理信息的空间数据类型和操作这些类型的函数。

PanWeiDB V2.0-S2.0.2版本可使用PostGIS的如下模块：

v  支持矢量分析的postgis模块。

v  支持计算几何的sfcgal模块。

v  支持栅格分析的raster模块。

v  支持拓扑分析的topology模块。

v  支持扩展组件如下：

• Fuzzystrmatch扩展，是一个判断字符串之间相似性和距离的一个插件，主要是提供Soundex、Levenshtein、Metaphone和Double Metaphone四个功能，开源PostgreSQL的contrib中包含此扩展。PostGIS中的postgis_tiger_geocoder依赖此扩展。
• postgis_tiger_geocoder扩展，TIGER指的是拓扑集成地理编码和参考，这个扩展提供了TIGER数据的地理编码支持。

【须知】注意此扩展启用前，需要先启用fuzzystrmatch（字符串模糊查询）插件，以及可选的address_standardizer、address_standardizer_data_us。

• address_standardizer扩展， TIGER数据地址规则化，是基于PAGC标准的地名标准化的扩展。
• address_standardizer_data_us扩展，TIGER地址规则化示例数据集，包含了基于PAGC标准的地名标准化的美国样例数据。

注意事项

• PanWeiDB创建函数不支持添加WINDOW关键字，所以不支持函数ST_ClusterDBSCAN和ST_ClusterKMeans。
• PanWeiDB不支持BRIN 索引，所以不支持geog_brin_inclusion_add_value和geom2d_brin_inclusion_add_value等相关函数。
• PanWeiDB不支持WITH ORDINALITY功能，所以topology.ValidateTopology提供的功能与PostGIS 3.2版本不一致。
• 不支持PostGIS 3.2版本中的ST_AsGeoJson函数。

 安装PostGIS插件示例

版本信息

数据库：PanWeiDB_V2.0-S2.0.2_B01-install-bclinux_euler21.10-x86_64-no_mot.tar

插件：postgis-bclinux-euler21.10_x86-64.tar.gz

1、从移动集团发布安装包种，获取PostGIS插件安装包。

2、使用root用户解压PanWeiDB专用PostGIS插件安装包，将解压得到postgis文件夹下的内容复制到GAUSSHOME目录中（$GAUSSHOME为数据库的安装路径）。

chown -R omm:dbgrp postgis/*
chmod -R 777 postgis/*
cp -r postgis/* $GAUSSHOME
chown -R omm:dbgrp $GAUSSHOME

3、yum安装相关依赖（如果不能使用yum可下载对应版本的安装包进行安装）。

yum install -y gmp gmp-devel mpfr mpfr-devel boost boost-devel  

4、启动数据库并设置guc参数behavior_compat_options（PGDATA为数据库的实例路径，omm为数据库安装用户）。

su - omm
gs_guc reload -D $PGDATA -c " behavior_compat_options = 'bind_procedure_searchpath, display_leading_zero' "
gs_ctl restart

5、重启并连接数据库（可能需要指定路径或修改连接端口等）。

gsql -r -d panweidb

6、创建插件。

create  extension  postgis;
create  extension  postgis_sfcgal;
create  extension  postgis_raster;
create  extension  postgis_topology;

7、查询测试。

SELECT Box3D(ST_GeomFromEWKT('LINESTRING(1 2 3, 3 4 5, 5 6 5)'));

结果返回如下：

Box3d
---------
BOX3D(1 2 3,5 6 5)
(1 row)

 创建测试表验证

 DBeaver图形化工具访问

图形化工具正常不显示数据图形，需要手动设置：

右键数据 ------> 显示/格式 ------> 设置列"st_astext"的格式 ------> geometry

 插入实际测试数据

图形可任意缩放，放大后可以看到更为细致的图形信息：

二、DTP工具迁移geometry数据

源端MySQL参数检查

 表数据量大小检查

 源端MySQL填充数据

 表数据量大小

正向全量迁移及全量数据校验

DTP迁移工具适配geometry数据类型的对象：对象兼容性、数据迁移和数据校验全部通过。

正向增量数据同步

 源端MySQL插入增量数据后，日志显示正确：

DTP工具增量同步界面无错误

增量数据校验报告

 再新增1条数据，manual激活增量校验功能。

# 一个点和多边形
INSERT INTO `t_geo_test` (ID,NAME,SHAPE) VALUES (13, 'Collection', geomfromtext('GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0,1 1,0 1, 0 0)))'));

可以看到增量日志输出：校验速率、校验条数、校验耗时

 DTP图形化界面显示的增量数据校验信息，条数一致，数据校验通过。

 1、经过确认，增量数据内容校验不通过是因为增量检验规则未修正，磐维对geometry数据中较长数据做科学计数，MySQL则是全长度显示，因此未通过增量数据校验内容。

 2、应用侧确认，数据一致无误。

结论

源端数据库版本：Server version: 5.7.20-log MySQL Community Server (GPL)

目标端磐维版本：PanWeiDB_V2.0-dtp3.0.0_B01 built at 20240806

　　　　　　　　gsql ((PanWeiDB_V2.0-S2.0.2_B01) compiled at 2024-03-29 20:29:01 commit 2b900fc )

                                        List of installed extensions
       Name       | Version |   Schema   |                            Description                            
------------------+---------+------------+-------------------------------------------------------------------
 postgis          | 3.2.0   | public     | PostGIS geometry and geography spatial types and functions
 postgis_raster   | 3.2.0   | public     | PostGIS raster types and functions
 postgis_sfcgal   | 3.2.0   | public     | PostGIS SFCGAL functions
 postgis_topology | 3.2.0   | topology   | PostGIS topology spatial types and functions

测试数据类型：

点	线	多边形	中间有个洞的多边形	一个点和多边形	多面边形
POINT	LINESTRING	POLYGON	PolygonWithHole	Collection（GEOMETRYCOLLECTION）	MULTIPOLYGON

磐维2.0数据库已适配PostGIS扩展，支持已编译好的安装包进行最简化配置。

PostGIS的多种空间数据（geometry数据类型）：支持对象评估、全量迁移、全量校验、增量同步、增量校验功能。