PolarDB开源数据库进阶课16 接入PostGIS全功能及应用举例-阿里云开发者社区

背景

穷鬼玩PolarDB RAC一写多读集群系列已经写了几篇:

本篇文章介绍一下如何在PolarDB数据库中接入PostGIS插件全功能? 实验环境依赖《在Docker容器中用loop设备模拟共享存储》 , 如果没有环境, 请自行参考以上文章搭建环境.

还需要参考文档:

https://postgis.net/docs/manual-3.5/postgis_installation.html#PGInstall

PostGIS的依赖非常多, 请补充好体力.

PostGIS的部署

b站视频链接

Youtube视频链接

安装插件时, 需要在PolarDB集群的所有机器上都进行安装, 顺序建议先PolarDB Standby, 然后是所有的RO节点, 然后是RW节点.

创建插件create extension xxx;则仅需在RW节点执行.

配置插件参数postgresql.conf 里面的 xxx.xxx=xxx也需要在PolarDB集群的所有机器上都进行安装, 顺序建议先PolarDB Standby, 然后是所有的RO节点, 然后是RW节点.

下面仅演示安装插件.

1、进入容器pb1

docker exec -ti pb1 bash

2、换源

sudo sed -i 's|http://ports.ubuntu.com|http://mirrors.aliyun.com|g' /etc/apt/sources.list  
sudo apt update

3、sqlite

cd /data  
wget https://www.sqlite.org/2024/sqlite-autoconf-3470200.tar.gz  
tar -zxvf sqlite-autoconf-3470200.tar.gz  
cd sqlite-autoconf-3470200  
./configure --prefix=/usr/local    
make -j 4   
sudo make install    
  
sudo vi /etc/ld.so.conf    
# add    
/usr/local/lib    
  
sudo ldconfig

4、依赖包

sudo apt-get install -y doxygen valgrind libxml2-dev libtiff-dev libcurl4 libcurl4-openssl-dev swig4.0 libprotobuf-c-dev libprotobuf-c1 libprotobuf-dev libprotobuf23 protobuf-c-compiler protobuf-compiler libboost-all-dev libmpfr-dev libgmp-dev libopenscenegraph-dev xsltproc libpcre3 libpcre3-dev

5、json-c

cd /data  
git clone --depth 1 -b json-c-0.18-20240915 https://github.com/json-c/json-c.git  
cd json-c/  
mkdir build  
cd build  
cmake ..  
make -j 4  
sudo make install

6、proj

cd /data  
wget https://download.osgeo.org/proj/proj-9.5.1.tar.gz  
tar -zxvf proj-9.5.1.tar.gz  
cd proj-9.5.1  
mkdir build  
cd build  
cmake -DBUILD_TESTING=OFF -DENABLE_IPO=ON ..  
cmake --build .  
sudo cmake --build . --target install

7、GEOS

cd /data  
wget https://download.osgeo.org/geos/geos-3.13.0.tar.bz2  
tar -jxvf geos-3.13.0.tar.bz2  
cd geos-3.13.0   
mkdir build    
cd build    
cmake -DCMAKE_BUILD_TYPE=Release -DCMAKE_INSTALL_PREFIX=/usr/local ..    
make -j 4    
sudo make install    
sudo ldconfig

8、GDAL

# https://gdal.org/en/stable/development/building_from_source.html#building-from-source  
cd /data  
wget https://github.com/OSGeo/gdal/releases/download/v3.10.0/gdal-3.10.0.tar.gz   
tar -zxvf gdal-3.10.0.tar.gz   
cd gdal-3.10.0  
mkdir build    
cd build    
cmake -DCMAKE_BUILD_TYPE=Release ..    
make -j 4    
sudo make install

9、CGAL

cd /data  
git clone --depth 1 -b v5.6.2 https://github.com/CGAL/cgal   
cd cgal  
mkdir build    
cd build    
cmake -DCMAKE_BUILD_TYPE=Release ..    
make -j 4    
sudo make install    
sudo ldconfig

10、SFCGAL

cd /data  
wget https://gitlab.com/SFCGAL/SFCGAL/-/archive/v2.0.0/SFCGAL-v2.0.0.tar.gz  
tar -zxvf SFCGAL-v2.0.0.tar.gz  
cd SFCGAL-v2.0.0  
mkdir build  
cd build  
cmake -DCMAKE_BUILD_TYPE=Release ..  
make -j 4  
sudo make install  
sudo ldconfig

11、PostGIS

cd /data  
wget https://download.osgeo.org/postgis/source/postgis-3.5.2.tar.gz  
tar -zxvf postgis-3.5.2.tar.gz  
cd postgis-3.5.2  
  
export LD_LIBRARY_PATH=/usr/local/lib:$LD_LIBRARY_PATH  
  
./configure --with-raster --with-sfcgal=/usr/local/bin/sfcgal-config --with-gdalconfig=/usr/local/bin/gdal-config --with-geosconfig=/usr/local/bin/geos-config

顺利的话, 会看到如下信息, 支持所有Extensions

PostGIS is now configured for aarch64-unknown-linux-gnu  
  
 -------------- Compiler Info -------------   
  C compiler:           gcc -std=gnu11 -I/usr/local/include -fno-math-errno -fno-signed-zeros -Wall -O2  
  C++ compiler (Wagyu): gcc -std=c++11 -x c++   
  C++ compiler (FlatGeobuf): gcc -std=c++11 -x c++   
  CPPFLAGS:              -I/usr/local/include -I/usr/local/include -I/usr/include/aarch64-linux-gnu  -I/usr/include/libxml2 -I/usr/local/include -I/usr/local/include -I/usr/local/include/json-c  -DNDEBUG   
  LDFLAGS:              -L/usr/local/lib -lm  
  SQL preprocessor:     /usr/bin/cpp -traditional-cpp -w -P -Upixel -Ubool  
  Archiver:             gcc-ar rs  
  
 -------------- Additional Info -------------   
  Interrupt Tests:   ENABLED  
  
 -------------- Dependencies --------------   
  GEOS config:          /usr/local/bin/geos-config  
  GEOS version:         3.13.0 (31300)  
  GDAL config:          /usr/local/bin/gdal-config  
  GDAL version:         3.10.0  
  SFCGAL config:        /usr/local/bin/sfcgal-config  
  SFCGAL version:       2.0.0  
  PostgreSQL config:    /home/postgres/tmp_polardb_pg_15_base/bin/pg_config  
  PostgreSQL version:   PostgreSQL 15.10 (PolarDB 15.10.2.0 build d4f5477d debug) on aarch64-linux-gnu  
  PROJ version:         9.5.1 (90501)  
  Libxml2 config:       /usr/bin/xml2-config  
  Libxml2 version:      2.9.13  
  JSON-C support:       yes  
  protobuf support:     yes  
  protobuf-c version:   1003003  
  PCRE support:         Version 2  
  Perl:                 /usr/bin/perl  
  
 --------------- Extensions ---------------   
  PostgreSQL EXTENSION support:       enabled  
  PostGIS Raster:                     enabled  
  PostGIS Topology:                   enabled  
  SFCGAL support:                     enabled  
  Address Standardizer support:       enabled  
  
 -------- Documentation Generation --------   
  xsltproc:             /usr/bin/xsltproc  
  docbook xsl base:       
  dblatex:                
  convert:

安装PostGIS

make -j 4   
sudo make install

完成后, 进入PolarDB数据库, 可以看到可以安装哪些PostGIS相关的插件:

$ psql  
psql (PostgreSQL 15.10 (PolarDB 15.10.2.0 build d4f5477d debug) on aarch64-linux-gnu)  
Type "help" for help.  
  
postgres=#   
postgres=# SELECT name, default_version,installed_version  
  FROM pg_available_extensions WHERE name LIKE 'postgis%' or name LIKE 'address%';  
             name             | default_version | installed_version   
------------------------------+-----------------+-------------------  
 postgis                      | 3.5.2           |   
 address_standardizer         | 3.5.2           |   
 address_standardizer_data_us | 3.5.2           |   
 postgis_sfcgal               | 3.5.2           |   
 postgis_tiger_geocoder       | 3.5.2           |   
 postgis_topology             | 3.5.2           |   
 postgis_raster               | 3.5.2           |   
(7 rows)

先写到这, 有了插件, 做实验就方便多了. 如果你对GIS应用感兴趣, 可以在我的github (https://github.com/digoal/blog) 搜索gis相关文章, 我列举了几篇:

参考

《穷鬼玩PolarDB RAC一写多读集群系列 | 在Docker容器中用loop设备模拟共享存储》

《穷鬼玩PolarDB RAC一写多读集群系列 | 如何搭建PolarDB容灾(Standby)节点》

《穷鬼玩PolarDB RAC一写多读集群系列 | 共享存储在线扩容》

《穷鬼玩PolarDB RAC一写多读集群系列 | 计算节点 Switchover》

《穷鬼玩PolarDB RAC一写多读集群系列 | 在线备份》

《穷鬼玩PolarDB RAC一写多读集群系列 | 在线归档》

《穷鬼玩PolarDB RAC一写多读集群系列 | 实时归档》

《穷鬼玩PolarDB RAC一写多读集群系列 | 时间点恢复(PITR)》

《穷鬼玩PolarDB RAC一写多读集群系列 | 读写分离》

《穷鬼玩PolarDB RAC一写多读集群系列 | 主机全毁, 只剩共享存储的PolarDB还有救吗?》

《穷鬼玩PolarDB RAC一写多读集群系列 | 激活容灾(Standby)节点》

《穷鬼玩PolarDB RAC一写多读集群系列 | 将“共享存储实例”转换为“本地存储实例”》

《穷鬼玩PolarDB RAC一写多读集群系列 | 将“本地存储实例”转换为“共享存储实例”》

《穷鬼玩PolarDB RAC一写多读集群系列 | 升级vector插件》

《穷鬼玩PolarDB RAC一写多读集群系列 | 使用图数据库插件AGE》

《穷鬼玩PolarDB RAC一写多读集群系列 | 接入私有化大模型服务》

https://postgis.net/docs/manual-3.5/postgis_installation.html#PGInstall

https://postgis.net/docs/manual-3.5/reference.html#PostGIS_GUC

https://postgis.net/docs/manual-3.5/postgis_administration.html#raster_configuration

背景

PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的价值产出, 将数据变成生产力.

本文将介绍PolarDB 开源版使用PostGIS 以及泰森多边形解决 "零售、配送、综合体、教培、连锁店等经营"|"通信行业基站建设功率和指向" 的地理最优解问题

测试环境为macOS+docker, PolarDB部署请参考下文:

《如何用 PolarDB 证明巴菲特的投资理念 - 包括PolarDB简单部署》

业务介绍

与地理位置、距离相关的业务:

配送
零售业务O2O
线下教培
综合体
基站
连锁店

1、以KFC为例, 全国有很多家KFC连锁店, 每个店应该辐射哪些小区商圈?

开了新店之后, 与之相邻的老店辐射商圈应该怎么调整?

KFC需要根据辐射小区商圈来预定销量、配置食材、配置多大的门店、多少营业员?

2、配送业务, 根据网点分布, 如何合理化每个网点负责的片区, 使得配送效率最高, 成本最低?

每一个写字楼有且只有一种选择到某个网点的距离最近.

3、基站建设, 每个基站应该对每个方向的功率调多大, 才能整体最优的解决网络质量和覆盖率问题.

以上其实都在回答一个问题:

在有限的资源情况下, 如何整体最优的解决地理位置上的业务覆盖问题.

简化为数学问题, 就是:

以基站、零售店、配送站、连锁店等为离散点, 划分泰森多边形, 每个离散点负责一个多边形, 在这个多边形内的点距离多边形内的离散点最近. 因此离散点只需要负责好这个多边形即可, 这样获得的就是地理位置上的最优解.

例子

《在PostgreSQL中生成和查看泰森多边形 - Voronoi diagram - 最强大脑题目》

《使用 PolarDB 开源版部署 PostGIS 支撑时空轨迹|地理信息|路由等业务》

接下来的内容将以上面这篇文章为例进行讲解:

以PolarDB 和postgis 为例

create extension postgis;

创建生成随机离散点的函数

参数1,2：经度取值范围    
参数3,4：维度取值范围    
参数5：生成多少个离散点    
    
create or replace function gen_rand_multipoint(numeric, numeric, numeric, numeric, int) returns geometry as $$    
declare    
  res text;    
begin    
  res := 'MULTIPOINT (';    
  for i in 1..$5 loop    
    res := res||$1+random()*($2-$1)||' '||$3+random()*($4-$3)||',';    
  end loop;    
  res := rtrim(res,',');    
  res := res||')';    
  return res::geometry;    
end;    
$$ language plpgsql strict;

举例

digoal=# select st_astext(gen_rand_multipoint(120,121,70,71,10));    
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
st_astext | MULTIPOINT(120.942558704875 70.0857633580454,120.821791284718 70.5374327567406,120.60653472133 70.7641549357213,120.966732177418 70.1589297447354,120.494935501367 70.5906278314069,120.999914915301 70.6718445569277,120.941853619181 70.6390802050009,120.022797878832 70.7509728162549,120.612626708578 70.7740727034397,120.310972961597 70.0668104588985,120.741411157884 70.2521727122366,120.031697474886 70.8502694196068,120.77547144331 70.7255614278838,120.18382552173 70.0531326876953,120.045804018155 70.279703093227,120.709843394347 70.9883627230301,120.365466451272 70.5316346790642,120.525795479771 70.9720011726022,120.295789614785 70.4925276571885,120.130930917338 70.7907251161523,120.083155489061 70.1308458326384,120.462569673546 70.0250091082416,120.769926037639 70.4853675523773,120.775981924497 70.3825527466834,120.259440256283 70.0869548860937,120.449363205582 70.0008514141664,120.33912759833 70.4810606804676,120.851120833773 70.1145990416408,120.206622108817 70.0349463559687,120.167731729802 70.2524261269718,120.314649449196 70.8775751241483,120.240788850002 70.6801159004681,120.409209803678 70.7665843297727,120.65211707307 70.7049994184636,120.259111986961 70.7830479904078,120.495724535082 70.3422674760222,120.913893823046 70.9582942086272,120.367276584264 70.6838198606856,120.443661761004 70.1432585087605,120.066372607369 70.7031020172872,120.230213394854 70.5157358134165,120.703953431919 70.5693409931846,120.996796493884 70.5550742656924,120.683940035291 70.2034186027013,120.590020621661 70.8516717650928,120.455844729673 70.9046700708568,120.729246889241 70.6966335796751,120.584785971325 70.1384566929191,120.463217909448 70.2369030443951,120.843456111848 70.7223298968747,120.019951034803 70.3391806469299,120.064597372897 70.9338448578492,120.297474855557 70.4318739576265,120.617664719 70.7411366165616,120.575132466387 70.6840373263694,120.444238634314 70.8053458617069,120.199773139786 70.1481920662336,120.374686854891 70.1965696341358,120.703266331926 70.0586268901825,120.399988236837 70.2932869540527,120.910298655275 70.8558329669759,120.19795702491 70.639545544982,120.552466546651 70.7827429967001,120.778002237901 70.0156844565645,120.019646041095 70.6214583497494,120.738014353439 70.0395970763639,120.960638996679 70.8026117263362,120.973441934213 70.2581138522364,120.234485683963 70.5911066532135,120.999250469264 70.8096181508154)

使用PostGIS生成随机离散点的泰森多边形

Synopsis    
geometry ST_VoronoiPolygons( g1 geometry , tolerance float8 , extend_to geometry );

用法如下

select st_astext(ST_VoronoiPolygons(x)) from gen_rand_multipoint(120,121,70,71,10) x;

使用PostGIS生成随机离散点的泰森多边形的边

Synopsis    
geometry ST_VoronoiLines( g1 geometry , tolerance float8 , extend_to geometry );

用法如下

select st_astext(ST_VoronoiLines(x)) from gen_rand_multipoint(120,121,70,71,10) x;

使用pgadmin，观察泰森多边形，离散点

1、建表，存储泰森多边形

create table tb (id serial, mp geometry, vp geometry, vl geometry, mp_vl geometry);

2、写入一些泰森多边形数据

insert into tb (mp,vp,vl,mp_vl)     
select x, ST_VoronoiPolygons(x), ST_VoronoiLines(x), st_union(x,ST_VoronoiLines(x)) from gen_rand_multipoint(120,121,70,71,10) x;    
    
insert into tb (mp,vp,vl,mp_vl)     
select x, ST_VoronoiPolygons(x), ST_VoronoiLines(x), st_union(x,ST_VoronoiLines(x)) from gen_rand_multipoint(120,121,70,71,100) x;    
    
insert into tb (mp,vp,vl,mp_vl)     
select x, ST_VoronoiPolygons(x), ST_VoronoiLines(x), st_union(x,ST_VoronoiLines(x)) from gen_rand_multipoint(120,121,70,71,1000) x;    
    
insert into tb (mp,vp,vl,mp_vl)     
select x, ST_VoronoiPolygons(x), ST_VoronoiLines(x), st_union(x,ST_VoronoiLines(x)) from gen_rand_multipoint(120,125,70,75,100) x;

部署postgis参考:

《穷鬼玩PolarDB RAC一写多读集群系列 | 接入PostGIS插件全功能》

使用pgadmin4 容器

https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

docker pull dpage/pgadmin4

docker run -p 9090:80 \
    -e 'PGADMIN_DEFAULT_EMAIL=user@domain.com' \
    -e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret' \
    --name pgadmin \
    -d dpage/pgadmin4

宿主机访问pgadmin:

http://127.0.0.1:9090/

pgadmin访问其他容器内的数据库时, 使用其他容器的地址, 例如 172.17.0.2:5432 .

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 容器名

3、使用pgadmin显示泰森多边形

几何数据

离散点对应的泰森多边形的边（multiline对象）

离散点对应的泰森多边形(multipolygon对象)（bound默认为一个BOX，包住所有离散点）

离散点以及对应的泰森多边形的边

放大后的离散点以及对应的泰森多边形的边

4、将得到的泰森多边形multigeometry解析出来，每个多边形一条记录存储，为下一篇文档四色猜想做准备。

《PostgreSQL中的四色猜想(Four color theorem) - 最强大脑题目》

创建测试表，并写入1000个泰森多边形。

create table tc (id serial, poy geometry);    
    
insert into tc (poy) select ST_GeometryN(x,i) from     
  (select generate_series(1,ST_NumGeometries(x)) i, x     
    from ST_VoronoiPolygons(gen_rand_multipoint(120,121,70,71,1000)) x    
  ) t;

例子

digoal=# select st_astext(poy) from tc;    
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
st_astext | POLYGON((120.0068907121 70.0827667811785,120.017136860027 70.1185103197699,120.024032512908 70.1163465285133,120.032180593165 70.1011610062082,120.033703815237 70.0870002751683,120.012202949892 70.0813630370411,120.0068907121    
 70.0827667811785))    
-[ RECORD 2 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
st_astext | POLYGON((119.001530315262 70.1396284378468,119.001530315262 70.2071267817079,119.757509890494 70.2155274483723,119.788978615847 70.2125452904718,119.9209381714 70.1687296375826,120.017136860027 70.1185103197699,120.0068907121    
 70.0827667811785,119.952343492619 70.0692099186264,119.001530315262 70.1396284378468))    
-[ RECORD 3 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
st_astext | POLYGON((119.781778114247 69.0017473017801,119.001530315262 69.0017473017801,119.001530315262 70.1396284378468,119.952343492619 70.0692099186264,120.008027433811 70.0545962635351,120.013949018811 70.0504765592871,120.03048513    
9271 70.0273653734516,120.039308399546 69.9936444173583,119.781778114247 69.0017473017801))    
-[ RECORD 4 ]--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    
st_astext | POLYGON((120.080290328964 69.0017473017801,119.781778114247 69.0017473017801,120.039308399546 69.9936444173583,120.057510105642 70.0145600250777,120.122918984211 70.0308393983058,120.127907151293 70.0314308457492,120.13710363    
4016 70.0261242124239,120.153895004164 69.9966006333282,120.080290328964 69.0017473017801))    
..............

5、输入任意一个泰森多边形ID搜索与之相邻的泰森多边形。

select st_collect(tc.poy)   
from tc,   
  (select * from tc where id=80) t   
where st_intersects(tc.poy, t.poy)   
and GeometryType(ST_Intersection(tc.poy, t.poy)) <> 'POINT'   
;

只有一个点相邻也会认为相邻，所以需要使用GeometryType过滤.

digoal=# select st_intersects(poyx, poyy), GeometryType(ST_Intersection(poyx, poyy)) from   
(values(  
  ST_MakePolygon(ST_GeomFromText('LINESTRING(1 2, 2 2, 2 3, 1 2)'))  
,  
  ST_MakePolygon(ST_GeomFromText('LINESTRING(2 2, 3 2, 3 3, 2 2)'))  
)) as t (poyx, poyy)  
;  
 st_intersects | geometrytype   
---------------+--------------  
 t             | POINT  
(1 row)