AnalyticDB for PostgreSQL 空间数据分析实战

本文涉及的产品
阿里云百炼推荐规格 ADB PostgreSQL,4核16GB 100GB 1个月
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介: 数字经济时代,数据是其关键的生产资料,而空间信息作为一重要属性集和模型特征集在业界形成广泛共识。政府层面,美国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表结合空间索引方式,如果是复杂分析场景,建议考虑典型案例中的商业客流分析案例。

相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
目录
相关文章
|
30天前
|
人工智能 分布式计算 Cloud Native
云原生数据仓库AnalyticDB:深度智能化的数据分析洞察
云原生数据仓库AnalyticDB(ADB)是一款深度智能化的数据分析工具,支持大规模数据处理与实时分析。其架构演进包括存算分离、弹性伸缩及性能优化,提供zero-ETL和APS等数据融合功能。ADB通过多层隔离保障负载安全,托管Spark性能提升7倍,并引入AI预测能力。案例中,易点天下借助ADB优化广告营销业务,实现了30%的任务耗时降低和20%的成本节省,展示了云原生数据库对出海企业的数字化赋能。
|
30天前
|
Cloud Native 关系型数据库 MySQL
无缝集成 MySQL,解锁秒级数据分析性能极限
在数据驱动决策的时代,一款性能卓越的数据分析引擎不仅能提供高效的数据支撑,同时也解决了传统 OLTP 在数据分析时面临的查询性能瓶颈、数据不一致等挑战。本文将介绍通过 AnalyticDB MySQL + DTS 来解决 MySQL 的数据分析性能问题。
|
3月前
|
消息中间件 Java Kafka
实时数仓Kappa架构:从入门到实战
【11月更文挑战第24天】随着大数据技术的不断发展,企业对实时数据处理和分析的需求日益增长。实时数仓(Real-Time Data Warehouse, RTDW)应运而生,其中Kappa架构作为一种简化的数据处理架构,通过统一的流处理框架,解决了传统Lambda架构中批处理和实时处理的复杂性。本文将深入探讨Kappa架构的历史背景、业务场景、功能点、优缺点、解决的问题以及底层原理,并详细介绍如何使用Java语言快速搭建一套实时数仓。
396 4
|
3月前
|
SQL 存储 数据挖掘
快速入门:利用AnalyticDB构建实时数据分析平台
【10月更文挑战第22天】在大数据时代,实时数据分析成为了企业和开发者们关注的焦点。传统的数据仓库和分析工具往往无法满足实时性要求,而AnalyticDB(ADB)作为阿里巴巴推出的一款实时数据仓库服务,凭借其强大的实时处理能力和易用性,成为了众多企业的首选。作为一名数据分析师,我将在本文中分享如何快速入门AnalyticDB,帮助初学者在短时间内掌握使用AnalyticDB进行简单数据分析的能力。
88 2
|
8月前
|
数据采集 大数据
大数据实战项目之电商数仓(二)
大数据实战项目之电商数仓(二)
183 0
|
6月前
|
前端开发 数据挖掘 关系型数据库
基于Python的哔哩哔哩数据分析系统设计实现过程,技术使用flask、MySQL、echarts,前端使用Layui
本文介绍了一个基于Python的哔哩哔哩数据分析系统,该系统使用Flask框架、MySQL数据库、echarts数据可视化技术和Layui前端框架,旨在提取和分析哔哩哔哩用户行为数据,为平台运营和内容生产提供科学依据。
404 9
|
6月前
|
存储 SQL 人工智能
AnalyticDB for MySQL:AI时代实时数据分析的最佳选择
阿里云云原生数据仓库AnalyticDB MySQL(ADB-M)与被OpenAI收购的实时分析数据库Rockset对比,两者在架构设计上有诸多相似点,例如存算分离、实时写入等,但ADB-M在多个方面展现出了更为成熟和先进的特性。ADB-M支持更丰富的弹性能力、强一致实时数据读写、全面的索引类型、高吞吐写入、完备的DML和Online DDL操作、智能的数据生命周期管理。在向量检索与分析上,ADB-M提供更高检索精度。ADB-M设计原理包括分布式表、基于Raft协议的同步层、支持DML和DDL的引擎层、高性能低成本的持久化层,这些共同确保了ADB-M在AI时代作为实时数据仓库的高性能与高性价比
|
6月前
|
存储 数据采集 数据可视化
基于Python flask+MySQL+echart的电影数据分析可视化系统
该博客文章介绍了一个基于Python Flask框架、MySQL数据库和ECharts库构建的电影数据分析可视化系统,系统功能包括猫眼电影数据的爬取、存储、展示以及电影评价词云图的生成。
296 1
|
9月前
|
存储 安全 数据挖掘
性能30%↑|阿里云AnalyticDB*AMD EPYC,数据分析步入Next Level
第4代 AMD EPYC加持,云原生数仓AnalyticDB分析轻松提速。
性能30%↑|阿里云AnalyticDB*AMD EPYC,数据分析步入Next Level
|
8月前
|
存储 SQL Cloud Native
云原生数据仓库AnalyticDB产品使用合集之热数据存储空间在什么地方查看
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
136 4

相关产品

  • 云数据库 RDS PostgreSQL 版