AnalyticDB for PostgreSQL 空间数据分析实战

简介: 数字经济时代,数据是其关键的生产资料,而空间信息作为一重要属性集和模型特征集在业界形成广泛共识。政府层面,美国911之后,通信运营商为政府相关部门(如公安、交通、应急指挥等)提供手机定位信息受法律保护;社会部分行业,尤其涉及GIS、交通、物流、吃住行游、自动驾驶等,无不与空间信息强相关。由此,空间数据的存储、空间查询与分析等特性成为数据库的标配。本文主要介绍如何利用AnalyticDB for PostgreSQL对空间数据进行管理和分析应用。

数字经济时代,数据是其关键的生产资料,而空间信息作为一重要属性集和模型特征集在业界形成广泛共识。政府层面,美国911之后,通信运营商为政府相关部门(如公安、交通、应急指挥等)提供手机定位信息受法律保护;社会部分行业,尤其涉及GIS、交通、物流、吃住行游、自动驾驶等,无不与空间信息强相关。由此,空间数据的存储、空间查询与分析等特性成为数据库的标配,比如NOSQL的Redis/MongoDB、RDBMS的MySQL/SQLServer/Oracle等都有相应模块对其提供支持,PostgreSQL内核支持Geometric几何类型,提供点、线、面、矩形、圆等几何的存储、几何变换、空间关系判定(相交、包含、相等等)功能,模块功能相对单一,缺失坐标系转换特性且用法不太优雅(不符合OGC规范),PostgreSQL开源界为弥补内核Geometric特性缺陷,衍生出PostGIS扩展模块予以完善。
AnalyticDB PG版同样支持空间数据存储、简单/复杂空间查询、空间分析等功能。有所区别的是,公有云产品默认包含PostGIS扩展模块包,但生产实例不默认装载该扩展;专有云产品不包含PostGIS扩展模块包,但为用户提供PostGIS模块整合到专有云AnalyticDB PG版的解决方案。下面介绍如何利用AnalyticDB PG版对空间数据进行管理和应用?

通用操作

1)客户端连接实例
可参考连接实例

2)初次装载PostGIS扩展模块

-- 创建扩展
create extension postgis;

-- 查看版本
select postgis_version();
select postgis_full_version();

3)空间数据写入数据库表
首先创建带Geometry字段的表,SQL参考:

create table testg ( id int, geom geometry ) 
distributed by (id);

该SQL表示插入的空间数据不区分几何类型,几何类型包括Point / MultiPoint / Linestring / MultiLinestring / Polygon / MultiPolygon等。
如果在创建表时已知Geometry类型和SRID(有关SRID可参考 SRID),也可以参考如下SQL创建表:

create table test ( id int, geom geometry(point, 4326) ) 
distributed by (id);

Geometry类型指定Point类型,SRID为4326,SRID不指定默认为0。
写入SQL参考:

-- without srid
insert into testg values (1, ST_GeomFromText('point(116 39)'));

-- with srid
insert into test values (1, ST_GeomFromText('point(116 39)', 4326));

JDBC Java程序参考:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class PGJDBC {
    public static void main(String args[]) {
        Connection conn = null;
        Statement stmt = null;
        try{
            Class.forName("org.postgresql.Driver");
            //conn = DriverManager.getConnection("jdbc:postgresql://<host>:3432/<database>","<user>", "<password>");
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            
            String sql = "INSERT INTO test VALUES (1001, ST_GeomFromText('point(116 39)', 4326) )";
            stmt.executeUpdate(sql);
       
            stmt.close();
            conn.commit();
            conn.close();
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + " : " + e.getMessage());
            System.exit(0);
        }
        System.out.println("insert successfully");
    }
}

如果是OSM格式数据,不用提前创建表,可以借助osm2pgsql工具导入,参考 openstreetmap数据导入
如果是SHP格式数据,不用提前创建表,可以借助shp2pgsql工具导入,参考 shp数据导入,也可以借助一些GIS客户端如ArcGIS Desktop等导入。

4)空间索引管理
创建空间索引SQL参考:

create index idx_test_geom on test using gist(geom);

idx_test_geom为自定义索引名,test为表名,geom为Geometry列名。
查看表有哪些索引SQL参考:

select * from pg_stat_user_indexes 
where relname='test';

查看索引大小SQL参考:

select pg_indexes_size('idx_test_geom');

索引重建SQL参考:

reindex index idx_test_geom;

删除索引SQL参考:

drop index idx_test_geom;

5)典型空间查询SQL
• BBOX范围查询

-- without srid
select st_astext(geom) from testg
where ST_Contains(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), geom);

-- with srid
select st_astext(geom) from test 
where ST_Contains(ST_SetSRID(ST_MakeBox2D(ST_Point(116, 39),ST_Point(117, 40)), 4326), geom);

ST_MakeBox2D算子生成一个Envelope。
• 几何缓冲范围查询

-- without srid
select st_astext(geom) from testg
where ST_DWithin(ST_GeomFromText('POINT(116 39)'), geom, 0.01);

-- with srid
select st_astext(geom) from test 
where ST_DWithin(ST_GeomFromText('POINT(116 39)', 4326), geom, 0.01);

ST_DWithin用法参考:ST_DWithin
• 多边形相交判定(在内部或在边界上)

-- without srid
select st_astext(geom) from testg
where ST_Intersects(ST_GeomFromText('POLYGON((116 39, 116.1 39, 116.1 39.1, 116 39.1, 116 39))'), geom);

-- with srid
select st_astext(geom) from test 
where ST_Intersects(ST_GeomFromText('POLYGON((116 39, 116.1 39, 116.1 39.1, 116 39.1, 116 39))', 4326), geom);

ST_*算子对大小写不敏感,更多用法可参考 PostGIS官方资料

注意:AnalyticDB PG 6.0不完全兼容PostGIS功能集,例如不支持 create extension postgis_topology,不推荐用Geography类型创建表(非要用,SRID默认为0或4326)。

典型案例

电子围栏场景
某客运监控服务运营商,通过安装在客车上的GPS定位终端收集定位数据,常见的业务有偏航报警、常去的服务区频次、驶入特定区域提醒(例如易发事故地段、积水结冰地段)等,这类业务是比较典型的电子围栏应用场景。
以驶入特定区域提醒业务为例,特定区域不会频繁变更且数据量偏少,可以一次采集定期更新,考虑区域表采用复制表,SQL参考:

CREATE TABLE ky_region (
  rid       serial,
  name      varchar(256),
  geom      geometry)
DISTRIBUTED REPLICATED;

插入Polygon / MultiPolygon类型的特定区域数据后,进行统计数据收集(Analyze 表名)并构建GIST索引。
判定驶入区域,可以分为两种情况:一种完全在区域内,一种是到达边界就要提醒。两种情况用到的空间算子有所区别,SQL参考:

-- 完全在区划内
select rid, name from ky_region
where ST_Contains(geom, ST_GeomFromText('POINT(116 39)'));

-- 考虑边界情况
select rid, name from ky_region
where ST_Intersects(geom, ST_GeomFromText('POINT(116 39)'));

SQL解释:输入变化的经纬度,查询区域表geom字段包含或相交与输入点的记录,如果为0条记录表示未驶入任何区域,如果为1条记录表示驶入某个区域,如果大于1条记录表示驶入多个区域(说明区域表有空间重叠的区域,需要从业务上验证空间重叠的合理性)。

智慧交通场景
某智慧交通场景,数据库包含线型轨迹表和其他业务表,一业务功能为查找历史轨迹表中曾经驶入过某一区域的轨迹ID,相关轨迹表结构:

create table vhc_trace_d (
 stat_date        text, 
 trace_id         text, 
 vhc_id           text, 
 rid_wkt          geometry) 
Distributed by (vhc_id) partition by LIST(stat_date)
(
 PARTITION p20191008 VALUES('20191008'),
 PARTITION p20191009 VALUES('20191009'),
 ......
);

轨迹按照天创建Partition表,每天导入数据后做统计数据收集,并对Partition表创建GIST空间索引。
业务SQL参考:

SELECT trace_id FROM vhc_trace_d
WHERE ST_Intersects(
  ST_GeomFromText('Polygon((118.732461  29.207363,118.732366  29.207198,118.732511  29.205951,118.732296  29.205644,
                  118.73226  29.205469,118.732350  29.20470,118.731708  29.203399,118.731701  29.202401, 118.754689 29.213488,
                  118.750827 29.21316,118.750272 29.213337,118.749677 29.213257,118.748699 29.213388,118.747715 29.213206,
                  118.746580 29.213831,118.74639 29.213872,118.744989 29.213858,118.743442 29.213795,118.74174 29.213002,
                  118.735633 29.208167,118.734422 29.207699,118.733045 29.207450,118.732803 29.207342,118.732461  29.207363))'), rid_wkt);

亿级轨迹表做空间查询RT在80ms内,完全满足业务对性能需求。

商业客流分析
某互联网生活服务运营商,基于AnalyticDB PG版做店铺客流量分析,数据库有两张业务表:User签到表和Shop店铺区域表,表结构参考:

-- user
create table user_label (
  ghash7        int, 
  uid           int, 
  workday_geo   geometry, 
  weekend_geo   geometry) 
distributed by (ghash7);

-- shop
create table user_shop (
  ghash7        int, 
  sid           int, 
  shop_poly     geometry) 
distributed by (ghash7);

业务表比较巧的设计是用Geohash或ZOrder编码等方式将地理空间几何降维作为分布键,而不用构建空间索引。
客流统计的SQL参考:

SELECT COUNT(1)
FROM (
    SELECT DISTINCT T0.uid FROM user_label T0 JOIN user_shop T1 
    ON T1.ghash7 = T0.ghash7
    WHERE T1.sid IN (1,2,3)
    AND (ST_Intersects(T0.workday_geo, T1.shop_poly) 
         OR ST_Intersects(T0.weekend_geo, T1.shop_poly))
) c;

与开源方案对标

开源领域,比较典型的能够支撑空间大数据管理与应用的方案有HBase+GeoMesa和Elasticsearch,我们简单做一下对标介绍。
截屏2020-03-10下午12.39.32.png

应用常见问题

1)对表Geometry字段创建了空间索引,空间查询为什么不走空间索引?
具体问题具体分析。通过Explain查看SQL执行计划,如果走的是SeqScan,可以尝试:

set enable_seqscan = off;

--或者调低random_page_cost
set random_page_cost = 10;

2)表数据量很大,为什么对表Geometry字段创建空间索引会失败?
这种情况是存在的,一方面是内存不够触发,另一方面创建索引需要足够耐心。PSQL客户端连接数据库,检查 maintenance_work_mem 参数配置项,根据实例规格可适当调整参数配置,SQL参考:

-- 参看参数配置
show maintenance_work_mem;

-- 修改参数配置
set maintenance_work_mem = '1GB';

另外如果是简单查询场景可以考虑Partition表结合空间索引方式,如果是复杂分析场景,建议考虑典型案例中的商业客流分析案例。

相关实践学习
数据库实验室挑战任务-初级任务
本场景介绍如何开通属于你的免费云数据库,在RDS-MySQL中完成对学生成绩的详情查询,执行指定类型SQL。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
目录
相关文章
|
21小时前
|
数据采集 大数据
大数据实战项目之电商数仓(二)
大数据实战项目之电商数仓(二)
|
21小时前
|
消息中间件 分布式计算 Hadoop
大数据实战项目之电商数仓(一)
大数据实战项目之电商数仓(一)
|
1天前
|
JSON 数据挖掘 API
数据分析实战丨基于pygal与requests分析GitHub最受欢迎的Python库
数据分析实战丨基于pygal与requests分析GitHub最受欢迎的Python库
10 2
|
2天前
|
运维 Cloud Native 关系型数据库
云原生数据仓库AnalyticDB产品使用合集之PostgreSQL版是否直接支持实时物化视图
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
3天前
|
存储 分布式计算 关系型数据库
实时数仓 Hologres产品使用合集之是否提供相应的功能接口和指令,可以将数据从OSS存储同步到Hologres中进行分析
实时数仓Hologres的基本概念和特点:1.一站式实时数仓引擎:Hologres集成了数据仓库、在线分析处理(OLAP)和在线服务(Serving)能力于一体,适合实时数据分析和决策支持场景。2.兼容PostgreSQL协议:Hologres支持标准SQL(兼容PostgreSQL协议和语法),使得迁移和集成变得简单。3.海量数据处理能力:能够处理PB级数据的多维分析和即席查询,支持高并发低延迟查询。4.实时性:支持数据的实时写入、实时更新和实时分析,满足对数据新鲜度要求高的业务场景。5.与大数据生态集成:与MaxCompute、Flink、DataWorks等阿里云产品深度融合,提供离在线
|
7天前
|
存储 缓存 测试技术
现代化实时数仓 SelectDB 再次登顶 ClickBench 全球数据库分析性能排行榜!
近日,在 ClickHouse 发起的分析型数据库性能测试排行榜 ClickBench(https://benchmark.clickhouse.com/)中,现代化实时数仓 SelectDB 时隔两年后再次登顶,在全部近百款数据库和数十种机型中,性能表现位居总榜第一!
43 1
|
13天前
|
SQL 测试技术 OLAP
现代化实时数仓 SelectDB 再次登顶 ClickBench 全球数据库分析性能排行榜!
现代化实时数仓 SelectDB 在时隔两年后再次完成登顶,在全部近百款数据库和数十种机型中,性能位居总榜第一!
现代化实时数仓 SelectDB 再次登顶 ClickBench 全球数据库分析性能排行榜!
|
14天前
|
数据采集 数据挖掘 API
Ownips+Coze海外社媒数据分析实战指南
本文介绍了在社交媒体数据采集和分析中的实践方法,特别强调了使用IPS代理的重要性。社交媒体如Twitter、Facebook等提供了丰富的数据资源。文章推荐了Ownips,这是一家提供高质量静态ISP代理服务的公司,用户可以选择需要的地区IP,并通过API或账号定向访问配置使用。 文章通过一个Twitter趋势数据采集的案例,展示了如何利用Ownips代理IP和Python的requests库以及BeautifulSoup解析HTML来获取和解析数据。此外,文章还提到了如何在Coze平台上创建数据采集插件和Twitter Bot,以实现自动化和智能化的数据分析。
Ownips+Coze海外社媒数据分析实战指南
|
15天前
|
存储 安全 数据挖掘
性能30%↑|阿里云AnalyticDB*AMD EPYC,数据分析步入Next Level
第4代 AMD EPYC加持,云原生数仓AnalyticDB分析轻松提速。
性能30%↑|阿里云AnalyticDB*AMD EPYC,数据分析步入Next Level
|
15天前
|
数据采集 数据挖掘 数据处理
Python数据分析实战:使用Pandas处理Excel文件
Python数据分析实战:使用Pandas处理Excel文件
93 0

相关产品

  • 云数据库 RDS PostgreSQL 版