数据库内核月报 － 2015 / 07

最近上线了一个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 的性能对比:

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

在功能上，和MYSQL对比，PostGIS具有下列优势：

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