如何基于PolarDB-PG处理空间数据
1. 创建资源
开始实验之前，您需要先创建实验相关资源。
- 在实验室页面，单击创建资源。
- （可选）在实验室页面左侧导航栏中，单击云产品资源列表，可查看本次实验资源相关信息（例如IP地址、子用户信息等）。
说明：资源创建过程需要3～5分钟（视资源不同开通时间有所差异，ACK等资源开通时间较长）。完成实验资源的创建后，您可以在云产品资源列表查看已创建的资源信息，例如：子用户名称、子用户密码、AK ID、AK Secret、资源中的项目名称等。
实验环境一旦开始创建则进入计时阶段，建议学员先基本了解实验具体的步骤、目的，真正开始做实验时再进行创建。
资源创建成功，可在左侧的资源卡片中查看相关资源信息以及RAM子账号信息
2. 创建PolarDB for PostgreSQL
首先创建PolarDB for PostgreSQL实例，这里我们利用Docker创建单机版实例。本步骤执行时间较长，请耐心等待。
- 设置yum镜像源并通过yum安装docker
yum install -y yum-utils yum-config-manager \ --add-repo \ https://download.docker.com/linux/centos/docker-ce.repo yum install docker-ce docker-ce-cli containerd.io docker-compose-plugin -y
- 启动docker并拉取polardb-pg的单机版镜像
systemctl start docker docker pull polardb/polardb_pg_local_instance:single
- 基于docker镜像启动一个docker容器，容器中自带一个polardb的实例，我们后续的操作都在容器中进行
docker run \ -it --cap-add=SYS_PTRACE --privileged=true \ --name polardb_pg_single \ polardb/polardb_pg_local_instance:single \ bash
3. 下载和安装PostGIS
- 本步骤以及之后的操作都在docker容器中执行，请确定命令行开头为 postgres@xxxxxx, 如果是root@xxx，请重新执行第一步的最后一条命令进入docker
docker run \ -it --cap-add=SYS_PTRACE --privileged=true \ --name polardb_pg_single \ polardb/polardb_pg_local_instance:single \ bash
- 下载PostGIS源码，这里我们下载3.1.8版本源码并解压。
wget https://labfileapp.oss-cn-hangzhou.aliyuncs.com/database/postgis-3.1.8.tar.gz tar -xzvf postgis-3.1.8.tar.gz
- 安装PostGIS编译所需的依赖库。
sudo apt install -y libgeos-dev libproj-dev libgdal-dev libxml2-dev libjson-c-dev libprotoc-dev libprotobuf-dev libprotobuf-c-dev protobuf-compiler protobuf-c-compiler
- 编译和安装PostGIS。
cd postgis-3.1.8 ./configure make -j4 make install
4. 在数据库中安装插件和存储空间数据
- 通过psql工具连接数据库。
psql
- 安装PostGIS插件。
CREATE EXTENSION postgis;
- 插入一份道路数据。
CREATE TABLE roads ( road_id SERIAL PRIMARY KEY, road_name VARCHAR(64), road_geom geometry(LINESTRING,3005) ); BEGIN; INSERT INTO roads (road_id, road_geom, road_name) VALUES (1,'SRID=3005;LINESTRING(191232 243118,191108 243242)','Jeff Rd'); INSERT INTO roads (road_id, road_geom, road_name) VALUES (2,'SRID=3005;LINESTRING(189141 244158,189265 244817)','Geordie Rd'); INSERT INTO roads (road_id, road_geom, road_name) VALUES (3,'SRID=3005;LINESTRING(192783 228138,192612 229814)','Paul St'); INSERT INTO roads (road_id, road_geom, road_name) VALUES (4,'SRID=3005;LINESTRING(189412 252431,189631 259122)','Graeme Ave'); INSERT INTO roads (road_id, road_geom, road_name) VALUES (5,'SRID=3005;LINESTRING(190131 224148,190871 228134)','Phil Tce'); INSERT INTO roads (road_id, road_geom, road_name) VALUES (6,'SRID=3005;LINESTRING(198231 263418,198213 268322)','Dave Cres'); COMMIT;
5. 执行空间查询
- 查询全部数据。
SELECT road_id, ST_AsText(road_geom) AS geom, road_name FROM roads;
- 创建索引，加速相交查询。
CREATE INDEX on roads USING gist(road_geom);
- 查询和一个矩形框相交的数据。
SELECT ST_AsText(road_geom) AS geom FROM roads WHERE road_geom && ST_MakeEnvelope(191232, 243117,191232, 243119);
- 查询道路的总长度。
SELECT SUM(ST_Length(road_geom)) FROM roads;
6. 执行空间分析
PostGIS支持多种空间分析操作，这里以使用DBSCAN进行密度聚类为例。
- 创建测试数据集。
CREATE TABLE cluster_pnts (id int, geom geometry); INSERT INTO cluster_pnts VALUES (0, NULL), (1, 'POINT (0 0)'), (2, 'POINT (0 1)'), (3, 'POINT (-0.5 0.5)'), (4, 'POINT (1 0)'), (5, 'POINT (1 1)'), (6, 'POINT (1.0 0.5)'), (7, 'POINT EMPTY'), (8, NULL), (9, 'POINT (0 0)'), (10, 'POINT (0 1)'), (11, 'POINT (-0.5 0.5)'), (12, 'POINT (1 0)'), (13, 'POLYGON((80 80,80 90,90 90,90 80,80 80))');
- 执行DBSCAN聚类，其中0.7代表邻域的半径，2代表识别为聚类中心所需的点密度。输出结果的第一列为点的序号，第二列为聚类所属的类的序号。
select id, ST_ClusterDBSCAN(geom, 0.7,2) over () from cluster_pnts;
实验链接：https://developer.aliyun.com/adc/scenario/59c74f426f644ad295f4d6e3e10c68d8
