PgSQL · 功能分析 · PostGIS 在 O2O应用中的优势

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 最近上线了一个O2O相关的应用,用到了PostgreSQL和非常著名的插件PostGIS,该应用把PostgreSQL和PostGIS的优势在O2O领域成功的发挥了出来。O2O业务分为线上和线下两部分,线下部分业务和位置距离等密切相关,不可避免的需要保存商户位置信息,进行距离的计算,范围覆盖计算等.

最近上线了一个O2O相关的应用,用到了PostgreSQL和非常著名的插件PostGIS,该应用把PostgreSQL和PostGIS的优势在O2O领域成功的发挥了出来。O2O业务分为线上和线下两部分,线下部分业务和位置距离等密切相关,不可避免的需要保存商户位置信息,进行距离的计算,范围覆盖计算等,这部分业务简称LBS(Location Based Service),即基于地理位置信息服务。使用PostgreSQL和PostGIS实现这些业务,具有天然的优势。

空间数据库扩展

由于空间数据具有空间位置、非结构化、空间关系、分类编码、海量数据等特征,一般的商用数据库管理系统本身难以满足要求,因此很多厂商都在其产品基础上,加入了空间方面的扩展。例如,支持空间扩展的产品有Oracle的Oracle Spatial,IBM的DB2 Spatial Extender,Informix 的Spatial DataBlade等。其优点是空间数据的管理与通用数据库系统融为一体,空间数据按对象存取,可在数据库内核中实现空间操作和处理,扩展SQL比较方便,较易实现数据共享与互操作;其缺点主要表现为,实现难度大,压缩数据比较困难,目前的功能和性能与第一类系统尚存在差距。

开源数据库方面,目前性能最优秀的数据库软件当属PostgreSQL数据库,而构建在其上的空间对象扩展模块PostGIS则使得其成为一个真正的大型空间数据库。

PostgreSQL和PostGIS

1986年,加州大学伯克利分校的 Michael Stonebraker 教授领导了Postgres的项目,它是PostgreSQL的前身。这个项目的成果非常显著,在现代数据库的许多方面都作出了大量的贡献,如在面向对象的数据库、部分索引技术、规则、过程和数据库扩展方面都取得了显著的成果。同时,Stonebraker将PostgreSQL纳入到BSD版权体系中,使得PostgreSQL在各种科研机构和一些公共服务组织得到了广泛的应用。

在PostgreSQL中已经定义了一些基本的集合实体类型,这些类型包括:点(POINT)、线(LINE)、线段(LSEG)、方形(BOX)、多边形(POLYGON)和圆(CIRCLE)等,另外PostgreSQL定义了一系列的函数和操作符来实现几何类型的操作和运算。同时PostgreSQL引入空间数据索引R-tree。

尽管在PostgreSQL提供了上述几项支持空间数据的特性,但其提供的空间特性很难达到GIS的要求,主要表现在:缺乏复杂的空间类型、没有提供空间分析、没有提供投影变换功能。为了使得PostgreSQL更好的提供空间信息服务,PostGIS应运而生。

PostGIS是PostgreSQL的一个扩展,PostGIS提供如下空间信息服务功能:空间对象、空间索引、空间操作函数和空间操作符,同时PostGIS遵循OpenGIS的规范。

O2O应用中空间计算需求

外卖应用短距离配送系统解决的是物流配送过程中最后一公里的问题。国家邮政局市场监管司预计,今年“双十一”期间快递总量将超过数亿件,最高日处理量可能突破7000万件。如此多的包裹堆积在城市物流的“最后一公里”上,让物流公司喘不过气来。商务部商业流通司司长王选庆说:“我国物流业在城际间的干线运输效率已经大大提高。现在真正的难题集中在城市配送的‘最后一公里’,配送难、配送贵的问题越来越突出。” 数据显示,末端配送成本已经占到物流行业总成本的30%以上。大量的社会资源消耗在“最后一公里”上,加重了城市的交通和环境压力,却未能带来配送效率的提高。短距离配送系统定位同城短距离配送业务,物流公司通过该平台获取同城内的物流配送订单,批量配送到距离该地约5公里内的目的地。典型业务模型如下:
1. 业务员查询自己负责范围内的订单信息,需求不规则多边形(自己负责的业务范围)的范围查询;
2. 业务员获取订单配送距离和推荐路线,需求点到点的距离计算、路径计算;
3. 相似路径的多个订单的批量配送,需求位置和大量传统数据符合运算;
4. 实时配送,位置跟踪。大量位置相关信息的存取,需要有较好的性能。

PostgreSQL+PostGIS解决方案

上述需求对应PostgreSQL 和PostGIS涉及到的数据类型和SQL用法有:

  1. 数据类型。几何对象的数据类型如下:

     POINT(0 0) ——点
     LINESTRING(0 0,1 1,1 2) ——线
     POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) ——面
     MULTIPOINT(0 0,1 2) ——多点
     MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4)) ——多线
     MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))) ——多面
     GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4))) ——几何集合
    
  2. 距离计算函数:ST_distance_sphere(point, point)
  3. 范围判定函数。判断A是否被B包含:ST_Within(geometry A, geometry B)
  4. 更多几何运算:

      面积量测 ST_Area(geometry)
      获取两个几何对象相交的部分 ST_Intersection(geometry, geometry)
      判断两个几何对象的边缘是否接触 ST_Touches(geometry, geometry)
      判断两个几何对象是否互相穿过 ST_Crosses(geometry, geometry)
      判断两个几何对象是否是重叠 ST_Overlaps(geometry, geometry)
      判断A是否包含B ST_Contains(geometry A, geometry B)
      判断A是否覆盖 B ST_Covers(geometry A, geometry B)
      判断A是否被B所覆盖 ST_CoveredBy(geometry A, geometry B)
    
  5. 如果需要精确的计算结果,则要做坐标系转换,称为空间投影。例如,以两个空间点的位置,计算两点间的距离。计算结果的单位与你的空间数据的参考系有关。如果使用4326(wgs84)这个坐标系,则是以度为单位的,要想转成米为单位的话还得做一下转换。例如,GEOCS 代表的是地理坐标系,也就是以经纬度表示的坐标系统,例如编号为4326的坐标系统,PROJCS 代表的投影坐标系,它是通过一种算法把球面坐标系转成平面坐标系,以便计算,一般是以米为单位表示,例如编号为26986的坐标系统。在求两点之间的距离时,由于存的数据都是经纬度,因此它参考的是GEOCS,要想得到以米为单位的结果,首先要把它转成PROJCS,可以通过ST_Transform来实现。查看PostGIS手册,这个函数的原型如下:

     geometry ST_Transform(geometry g1, integer srid);
    

    第一个参数是原来的几何对象,第二个参数为要把它转换到这个投影所代表的坐标系下。这时我们只要找一个单位是米的投影坐标系,用下面的方法转换过去就好了:

     SELECT ST_Distance(
            ST_Transform(ST_GeomFromText('POINT(-87.734087560562 43.770129071141)',4326),26986),
            ST_Transform(ST_GeomFromText('POINT(-87.747382933006 43.759234252055)', 4326),26986));
    

    这个查出来的结果即是以米为单位的两点间的距离了。

  6. 空间索引。空间相关的数据支持创建GIST索引,这大大提高了这类数据查询的效率,使用下面的方法可以创建空间索引:

     CREATE INDEX shape_index_area
        ON sp_delivery_area.area
        USING gist(shape);
    

无论是数据类型还是各种几何运算,都和关系数据库PostgreSQL紧密结合,你可以在进行数据库事务操作的同时,进行空间运算,这让业务的开发异常的简单并且效率极高。

与其他方案的对比

这是典型的业务场景和 MongoDB 的性能对比:

PgSQL 和 MongoDB性能对比

基本上PostgreSQL的性能可以秒杀MongoDB。

在功能上,和MYSQL对比,PostGIS具有下列优势:

PgSQL 和 MySQL对比

O2O业务场景中的LBS业务使用PostgreSQL + PostGIS有无法比拟的优势。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
存储 关系型数据库 OLAP
PgSQL · 应用案例 · PostgreSQL OLAP加速技术之向量计算
背景 在主流的OLTP数据库产品中,毫无疑问,PostgreSQL已经具备非常强大的竞争力(性能、功能、稳定性、成熟度、案例、跨行业应用等)。 通过这些文章我们可以了解更细致的情况。 《数据库十八摸 - 致 架构师、开发者》 《数据库界的华山论剑 tpc.org》 《PostgreSQL 前世今生》 在OLAP领域,PostgreSQL社区也是豪情万丈的,比如内核已经实现了基于CPU的多
7487 0
|
存储 Oracle 关系型数据库
【PG云栖周刊】第2期·PostGIS北京3月活动,存储扩展引擎zheap,Oracle兼容性之 - 数据类型
3月17-18 PG象行中国2018-地理信息处理GIS专题(北京站),活动地点:北京师范大学 艺术楼 201教室;PostgreSQL最新存储扩展引擎zheap,计划加入到PG12;PostgreSQL 10.3更新版本发布;云数据库PPAS Oracle兼容性 - 数据类型;PostgreSQL中HOOK的使用,避免误删库的问题。
5145 0
【PG云栖周刊】第2期·PostGIS北京3月活动,存储扩展引擎zheap,Oracle兼容性之 - 数据类型
|
SQL 存储 NoSQL
MSSQL · 特性分析 · 列存储技术做实时分析
摘要 数据分析指导商业行为的价值越来越高,使得用户对数据实时分析的要求变得越来越高。使用传统RDBMS数据分析架构,遇到了前所未有的挑战,高延迟、数据处理流程复杂和成本过高。这篇文章讨论如何利用SQL Server 2016列存储技术做实时数据分析,解决传统分析方法的痛点。 传统RDBMS数据分析 在过去很长一段时间,企业均选择传统的关系型数据库做OLAP和Data Warehouse工作。这一
3622 0
|
关系型数据库 数据库 PostgreSQL
PgSQL · 特性分析 · 浅析PostgreSQL 中的JIT
--- title: PgSQL · 特性分析 · 浅析PostgreSQL 中的JIT author: 卓刀 --- ## 背景 估计很多同学看过之前的月报[PgSQL · 特性分析· JIT 在数据仓库中的应用价值](http://mysql.taobao.org/monthly/2016/11/10/),对JIT(just in time)和LLVM(Low Level Vir
2872 0
|
关系型数据库 PostgreSQL 索引
PgSQL · 应用案例 · PostgreSQL 图像搜索实践
背景 imgsmlr是PostgreSQL的一款支持以图搜图的插件, 支持 1、几种图像特征值数据类型, 2、图像特征值相似算子, 3、图像特征值相似排序索引支持, 4、图像相似排序的索引(通过扩展GiST索引接口实现)支持, 5、png,gif等图像格式特征值提取函数。
2009 0
|
SQL 关系型数据库 PostgreSQL
PgSQL · 应用案例 · 惊天性能!单RDS PostgreSQL实例支撑 2000亿
背景 20亿用户,每个用户1000个标签,基于任意标签组合圈选、透视(业务上的需求是一次最多计算100个标签的组合)。 相当于要处理2000亿记录。 1、实时求标签组合的记录数。(即满足标签组合的用户有多少) 2、用户ID。
2277 0
|
SQL 监控 Go
MSSQL · 应用案例 · 日志表设计优化与实现
摘要 这篇文章从日志表问题引入、日志表的共有特性、日志表的设计需求、设计思路以及设计详细实现的角度,阐述了在SQL Server数据库中如何最优化设计日志表来降低系统资源的占用和提高系统吞吐量。问题引入 在平时与客户服务与交流过程中,我们不止一次的被客人问及这样的场景:我们现在面临如何设计SQL Server日志表方案,如何最优化设计数据库日志记录表。
1533 0
|
存储 缓存 关系型数据库
PgSQL · 代码浅析 · PostgreSQL 可靠性分析
背景 PostgreSQL 可靠性与大多数关系数据库一样,都是通过REDO来保障的。 群里有位童鞋问了一个问题,为什么PostgreSQL的REDO块大小默认是8K的,不是512字节。 这位童鞋提问的理由是,大多数的块设备扇区大小是512字节的,512字节可以保证原子写,而如果REDO的块大于512字节,可能会出现partial write。 那么PostgreSQL的redo(wal) 块
2439 0