进阶课 16 启用PostGIS全功能
digoal约 13 分钟PolarDB 进阶课程16 启用PostGIS全功能
穷鬼玩PolarDB RAC一写多读集群系列已经写了几篇:
《在Docker容器中用loop设备模拟共享存储》 《如何搭建PolarDB容灾(Standby)节点》 《共享存储在线扩容》 《计算节点 Switchover》 《在线备份》 《在线归档》 《实时归档》 《时间点恢复(PITR)》 《读写分离》 《主机全毁, 只剩共享存储的PolarDB还有救吗?》 《激活容灾(Standby)节点》 《将“共享存储实例”转换为“本地存储实例”》 《将“本地存储实例”转换为“共享存储实例”》 《升级vector插件》 《使用图数据库插件AGE》 《接入私有化大模型服务》
本篇文章介绍一下如何在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"forhelp. 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相关文章, 我列举了几篇:
《沉浸式学习PostgreSQL|PolarDB 18: 通过GIS轨迹相似伴随|时态分析|轨迹驻点识别等技术对拐卖、诱骗场景进行侦查》 《PolarDB 开源版 使用PostGIS 数据寻龙点穴（空间聚集分析）- 大数据与GIS分析解决线下店铺选址问题》 《PolarDB 开源版 使用PostGIS 以及泰森多边形 解决 "零售、配送、综合体、教培、连锁店等经营"|"通信行业基站建设功率和指向" 的地理最优解问题》 《一起学PolarDB - 第20期 - 为什么GIS时空查询即耗CPU又费IO?》 《DB吐槽大会,第50期 - PG GiST距离排序操作符和过滤无法同时使用索引》 《PostGIS空间索引(GiST、BRIN、R-Tree)选择、优化 - 2》
参考
《穷鬼玩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
例子
《在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 '[email protected]' \
-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)
参考
https://en.wikipedia.org/wiki/Voronoi_diagram
https://baike.baidu.com/item/%E6%B3%B0%E6%A3%AE%E5%A4%9A%E8%BE%B9%E5%BD%A2
https://gis.stackexchange.com/questions/114764/how-to-use-st-delaunaytriangles-to-construct-a-voronoi-diagram
https://gis.stackexchange.com/questions/172198/constructing-voronoi-diagram-in-postgis
http://postgis.net/docs/manual-2.5/ST_VoronoiLines.html
http://postgis.net/docs/manual-2.5/ST_VoronoiPolygons.html
http://postgis.net/docs/manual-2.5/reference.html
https://stackoverflow.com/questions/21719941/postgis-convert-multipolygon-to-single-polygon
https://baike.baidu.com/tashuo/browse/content?id=d967b9032e228a4e4a39827e&fr=qingtian&lemmaId=3428661
《PostgreSQL中的四色猜想(Four color theorem) - 最强大脑题目》
《在PostgreSQL中生成和查看泰森多边形 - Voronoi diagram - 最强大脑题目》
《使用 PolarDB 开源版 部署 PostGIS 支撑时空轨迹|地理信息|路由等业务》