超融合时序数据库YMatrixDB与PostGIS案例

目录

什么是PostGIS

PostGIS的特点

PostGIS 基础知识

OGC的WKB和WKT格式

插入数据实例

EWKT、EWKB和Canonical格式

插入数据实例

SQL-MM格式

常几何类型和函数

常用操作符

常用操作函数

OGC标准函数

管理函数

几何对象关系函数

几何对象处理函数

几何对象存取函数

类型转换函数

PostGIS 系统表查看

spatial_ref_sys表

geometry_columns表

PostGIS 两个重要的坐标体系

YMatrixDB 安装PostGIS

PostGIS 安装

在YMatrixDB上安装postgis扩展

YMatrixDB的PostGIS使用案例

计算两点之间的距离

范围内的点查找

弯曲的几何实体案例

YMatrixDB的PostGIS车联网数据案例

车联网数据下载

表创建

数据加载

数据处理

出租车数据分析

出租车行程统计

费率分布

机场行程分析

附近出租车

---

YMatrix适用于各种规模设备的数据融合与物联网时序应用场景，本案例以具体的案例来说明YMatrix在PostGIS中的数据加载、处理和分析的能力以及时空数据的具体使用方法，首先我们先了解下PostGIS，然后再分享几个PostGIS在YMatrixDB的案例。

什么是PostGIS

PostGIS是一个空间数据库，空间数据库像存储和操作数据库中其他任何对象一样去存储和操作空间对象。空间数据库将空间数据和对象关系数据库（Object Relational database）完全集成在一起。实现从以GIS为中心向以数据库为中心的转变。PostGIS 实现了点、线、面、多点、多线、多面等的SQL实现参考。

PostGIS的特点

1. PostGIS 具有强大的功能，具有以下的特点
2. PostGIS支持空间数据类型，包括点（POINT）、线（LINESTRING）、面（POLYGON）、多点 （MULTIPOINT）、多线（MULTILINESTRING）、多面（MULTIPOLYGON）和几何集合 （GEOMETRYCOLLECTION）等。
3. 支持对象表达方法，比如WKT和WKB。
4. 提供简单的空间分析函数，同时也提供其他一些具有复杂分析功能的函数。
5. 支持所有的数据存取和构造方法，如GeomFromText()、AsBinary()，以及GeometryN()等。
6. 对于元数据的支持，如GEOMETRY_COLUMNS和SPATIAL_REF_SYS。同时也支持AddGeometryColumn和DropGeometryColumn函数等。
7. 能对矢量数据和栅格数据做处理，能通过 SQL 调用栅格、矢量数据的投影函数。
8. 能通过多种工具导入多种标准的栅格数据，同时能通过 SQL 语句将栅格渲染至各种格式GeoTiff、PNG、JPG、NetCDF 等。
9. 能通过 SQL 调用 KML、GML、GeoJSON、GeoHash、WKT 等标准文本类型的矢量数据的函数。
10. 矢量或栅格操作函数，包括按区域伸缩栅格像元值、局域统计、按矢量图形裁剪栅格、矢量化栅格等。

PostGIS 基础知识

OGC的WKB和WKT格式

OGC定义了两种描述几何对象的格式，分别是WKB（Well-Known Binary）和WKT（Well-Known Text）格式。

插入数据实例

EWKT、EWKB和Canonical格式

EWKT和EWKB相比OGC WKT和WKB格式主要的扩展有3DZ、3DM、4D坐标和内嵌空间参考支持。

插入数据实例

INSERT INTO table (SHAPE,NAME) VALUES (GeomFromText('POINT(116.39 39.9)', 4326), '北京');

SQL-MM格式

SQL-MM格式定义了一些插值曲线，这些插值曲线和EWKT有点类似，也支持3DZ、3DM、4D坐标，但是不支持嵌入空间参考。

常几何类型和函数

操作实例

--  点
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))

常用操作符

常用操作函数

OGC标准函数

管理函数

几何对象关系函数

几何对象处理函数

几何对象存取函数

类型转换函数

PostGIS 系统表查看

spatial_ref_sys表

在基于PostGIS模板创建的数据库的public模式下，有一个spatial_ref_sys表，它存放的是OGC规范的空间参考。

geometry_columns表

1、geometry_columns表存放了当前数据库中所有几何字段的信息，比如我当前的库里面有两个空间表，在geometry_columns表中就可以找到这两个空间表中几何字段的定义 2、其中f_table_schema字段表示的是空间表所在的模式，f_table_name字段表示的是空间表的表名，f_geometry_column字段表示的是该空间表中几何字段的名称，srid字段表示的是该空间表的空间参考。

taix=# select * from geometry_columns;
 f_table_catalog | f_table_schema | f_table_name  | f_geometry_column | coord_dimension | srid | type
-----------------+----------------+---------------+-------------------+-----------------+------+-------
 taix            | public         | trip          | pickup_geom       |               2 | 2163 | POINT
 taix            | public         | trip          | dropoff_geom      |               2 | 2163 | POINT
 taix            | public         | trip_1_prt_1  | pickup_geom       |               2 | 2163 | POINT
 taix            | public         | trip_1_prt_1  | dropoff_geom      |               2 | 2163 | POINT

PostGIS 两个重要的坐标体系

1. 4326 \ GCS_WGS_1984 \ Geographic Coordinate System(GCSS)地理坐标系, World Geodetic System(WGS)世界大地测量系统
2. 26986 \ 美国马萨诸塞州地方坐标系（区域坐标系）\ 投影坐标, 平面坐标

YMatrixDB 安装PostGIS

YMatrixDB的安装可以参考

https://ymatrix.cn/doc/5.0/install/mx5_cluster/mx5_cluster

PostGIS 安装

使用以下连接下载postgis安装包及相关的依赖。

链接: https://pan.baidu.com/s/1D_awBTLzOqZV5--cxE8_oA 提取码: gjj7

如果是集群安装的数据库需要在每台节点安装postgis。

---- 以下操作需要使用root用户执行
 
-- 解压postgis安装包
# unzip postgis-install.zip
 
-- 创建postgis的repo
# cd postgis-install
# ls
county.tgz  create_repo.sh  mxdb-postgis-2.5-1.el7.x86_64.rpm  pkg-postgis
 
# sh create_repo.sh
Create postgis repo successfully!
 
-- 查看postgis的repo
# yum repolist
repo id                                  repo name                                   status                         0
postgis                                  postgis 
 
 
-- 安装postgis
# yum install  --disablerepo="*" --enablerepo=postgis -y mxdb-postgis-2.5-1.el7.x86_64.rpm

在YMatrixDB上安装postgis扩展

使用mxadmin用户登录到数据库并创建postgis扩展，postgis适用于当前session数据库，如果其他的数据库使用，请切换到其他数据库中再次创建即可。

postgres=# create extension postgis;
CREATE EXTENSION
postgres=# \dx
                                         List of installed extensions
      Name       | Version |   Schema   |                             Description
-----------------+---------+------------+---------------------------------------------------------------------
 gp_exttable_fdw | 1.0     | pg_catalog | External Table Foreign Data Wrapper for Greenplum
 plpgsql         | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis         | 2.5.5   | public     | PostGIS geometry, geography, and raster spatial types and functions
(3 rows)

YMatrixDB的PostGIS使用案例

首先我们先熟悉一下PostGIS的常用的案例，然后再使用真实的北京市内所有的酒店信息和宾馆信息做统计。

计算两点之间的距离

-- 两个点之间的距离,距离单位是m
select ST_Distance(ST_GeographyFromText('SRID=4326;POINT(114.017299 22.537126)'),
ST_GeographyFromText('SRID=4326;POINT(114.025919 22.534866)'));
 
-- 两点之间的斜度数
SELECT ST_Distance(ST_GeomFromText('POINT(114.017299 22.537126)',4326),
ST_GeomFromText('POINT(114.025919 22.534866)', 4326));

范围内的点查找

-- 查看两点的距离是否有1000m，单位米m，返回t是在范围内，否则不在
SELECT ST_DWithin(
ST_GeographyFromText('SRID=4326;POINT(114.017299 22.537126)'),
ST_GeographyFromText('SRID=4326;POINT(114.025919 22.534866)'),1000);
 
-- 查看两点直接的斜度，是否在制定的斜度内，返回t是在范围内，f不在斜度内
SELECT ST_DWithin(ST_GeomFromText('POINT(114.017299 22.537126)',4326),
ST_GeomFromText('POINT(114.025919 22.534866)', 4326),0.00811134108875483);
 
-- 查找给定经纬度5km以内的点
-- geom_point::geography，单位变成米, 否则默认距离单位是度。
SELECT
    uuid,
    longitude,
    latitude,
    ST_DistanceSphere (
        geom_point,
    ST_GeomFromText('POINT(121.248642 31.380415)', 4326 )) distance 
FROM
    s_poi_gaode 
WHERE
    ST_DWithin ( geom_point :: geography, ST_GeomFromText ( 'POINT(121.248642 31.380415)', 4326 ) :: geography, 5000 ) IS TRUE 
    order by distance desc
    LIMIT 30;
 
-- 查看给定坐标的最近的10个点
SELECT * FROM s_poi_gaode_gps ORDER BY geom_point <-> ST_GeomFromText ( 'POINT(121.248642 31.380415)', 4326 )  LIMIT 10;

弯曲的几何实体案例

-- 创建表
create table global_points (
id int,
name varchar(64),
location geography(point,4326)
) Distributed by(id);
 
-- 插入数据
insert into global_points (id,name, location) values (1,'town', 'srid=4326;point(-110 30)');
insert into global_points (id,name, location) values (2,'forest', 'srid=4326;point(-109 29)');
insert into global_points (id,name, location) values (3,'london', 'srid=4326;point(0 49)');
 
-- 创建索引
create index global_points_gix on global_points using gist ( location );
 
-- 查看数据
postgis=# select * from global_points;
 id |  name  |                      location
----+--------+----------------------------------------------------
  2 | forest | 0101000020E61000000000000000405BC00000000000003D40
  3 | london | 0101000020E610000000000000000000000000000000804840
  1 | town   | 0101000020E61000000000000000805BC00000000000003E40
(3 rows)
 
-- 查询给位置1000公里之内的城镇
select name from global_points where st_dwithin(location, 'srid=4326;point(-110 29)'::
geography, 1000000);
 
-- 计算从西雅图(-122.33 47.606)飞往伦敦(0.0 51.5)的距离
select st_distance('linestring(-122.33 47.606, 0.0 51.5)'::geography, 'point(-21.96
64.15)'::geography);
 
-- 计算点线之间的距离
select st_distance('linestring(-122.33 47.606, 0.0 51.5)'::geometry, 'point(-21.96 64.15)
'::geometry);

我们可以使用地图查看西雅图(-122.33 47.606)和伦敦(0.0 51.5)的坐标点

https://lbsyun.baidu.com/jsdemo/demo/yLngLatLocation.htm

YMatrixDB的PostGIS车联网数据案例

车联网数据下载

某城市拥有超过800万人口和20万辆出租车的行程信息，该行程信息乘客的上下车时间、上下车地点、乘车人数、车费和支付的方式等。接下来使用该数据分析出租车的出车情况和交通的拥堵情况，根据道路的拥堵情况合理的分配出车调度，便于乘客方便打车提高乘客的生活体验。

数据下载：

https://pan.baidu.com/share/init?surl=JJv6ADN5vHOlem7smTrEDw  (提取码1x4u)

表创建

接下来创建付费方式、费率和行程时序表。

----   付费方式表
create table if not exists payment_types (
    payment_type int,
    description text
) DISTRIBUTED BY(payment_type);
 
insert into payment_types values
(1, '信用卡'),
(2, '现金'),
(3, '免付费'),
(4, '有争议'),
(5, '未知'),
(6, '无效行程');
 
---- 费率表
create table if not exists rate_codes (
    rate_code   int,
    description text
) DISTRIBUTED BY(rate_code);
 
insert into rate_codes values
(1, '标准费率'),
(2, '1号机场'),
(3, '2号机场'),
(4, '特殊区域'),
(5, '协商价'),
(6, '团体');
 
---- 行程时序表
create extension matrixts ;
create table if not exists trip (
  vendor_id text,
  pickup_datetime timestamp without time zone,
  dropoff_datetime timestamp without time zone,
  passenger_count int,
  trip_distance numeric,
  pickup_longitude numeric,
  pickup_latitude numeric,
  rate_code_id int,
  store_and_fwd_flag text,
  dropoff_longitude numeric,
  dropoff_latitude numeric,
  payment_type int,
  fare_amount numeric,
  extra numeric,
  mta_tax numeric,
  tip_amount numeric,
  tolls_amount numeric,
  improvement_surcharge numeric,
  total_amount numeric,
  trip_duration numeric generated always as (EXTRACT(EPOCH FROM (dropoff_datetime - pickup_datetime)::INTERVAL)/60) STORED,
  is_valid boolean,
  pickup_geom geometry(POINT,2163),
  dropoff_geom geometry(POINT,2163)
)
USING mars2 with (compresstype=zstd, compresslevel=5)
DISTRIBUTED BY (vendor_id)
PARTITION BY RANGE (pickup_datetime)
( START (date '2016-01-01') INCLUSIVE
   END (date '2016-02-01') EXCLUSIVE
   EVERY (INTERVAL '1 day')
 );
 
CREATE INDEX trip_index ON trip USING mars2_btree(c1, daq_time) WITH (uniquemode=true);
 
pickup_datetime : 上车时间点
dropoff_datetime : 下车时间点 
pickup_longitude :  上车地点的经度值
pickup_latitude :  上车地点的纬度值
dropoff_longitude : 下车地点的经度值
dropoff_longitude : 下车地点的纬度值
passenger_count : 表示乘客数量
trip_distance :  旅程的距离（单位为英里）
total_amount :  乘车费用
trip_duration : 乘车的时长（单位为分钟)
pickup_geom/dropoff_geom : 位置区域信息

数据加载

mxgate的详细使用可以参考： https://ymatrix.cn/doc/latest/tools/mxgate.md

-- 使用以下命令把数据加载到表中
tail -n +2 yellow_tripdata_2016-01.csv | mxgate --source stdin --db-database mxdb --db-master-host master --db-master-port 5432 --db-user mxadmin --time-format raw --target trip --parallel 256  --delimiter ','  --exclude-columns trip_duration,is_valid,pickup_geom,dropoff_geom

数据处理

在时序的场景中，因为各种复杂的原因，数据有时会包含一些明显错误或者无效的数据。借助YMatrix提供的丰富SQL能力，可以快速检测并清除这些无效错误数据。 一种错误情况是下车时间早于或者等于上车时间，我们把is_valid字段设置成false表示该数据无效。

insert into trip(vendor_id,pickup_datetime,is_valid) 
select vendor_id,pickup_datetime,'false' as is_valid 
from  trip 
where dropoff_datetime <= pickup_datetime 
order by 1,2;
 
vacuum trip;

还有一种情况是汽车的平均速度大于每小时300英里，尤其在旅程或者时间较长时，这种情况明显不合理，我们也把is_valid设置成false表示该数据无效。

insert into trip(vendor_id,pickup_datetime,is_valid) 
select vendor_id,pickup_datetime,'false' as is_valid 
from  trip 
where trip_distance > trip_duration* (300/60) and trip_distance > 100 
order by 1,2;
 
vacuum trip;

出租车数据分析

出租车行程统计

YMatrix提供了time_bucket函数，支持按照任意时间区间的分段计算。需要在数据库上安装Matrixts Extension来初始化时序组件:

 CREATE EXTENSION matrixts;

接下来我们就可以通过下面的SQL语句统计出每天有多少行程：

select time_bucket('24 hours', pickup_datetime) as day, count(*) 
from trip 
where is_valid is null
group by day
order by day;

如果想要了解2016年1月2号一天中每个小时，分别有多少人乘车，可以用下面的SQL：

SELECT time_bucket('1 hour', pickup_datetime) as hour, sum(passenger_count)
FROM trip 
where is_valid is null
and pickup_datetime >= '2016-01-02 00:00:00' and pickup_datetime < '2016-01-03 00:00:00'
GROUP BY hour
ORDER BY hour;

接下来我们统计0-10、10-50、50-100、100-200、200英里以上不同行程距离区段的总行程信息

select distance_range, count(*) as num_of_trips  
from
(
    select
    case
        when trip_distance <= 10 then 10
        when trip_distance > 10 AND trip_distance <= 50 then 50
        when trip_distance > 50 AND trip_distance <= 100 then 100 
        when trip_distance > 100 AND trip_distance <= 200 then 200
        when trip_distance > 200 then 500 
        end as distance_range
    from trip where is_valid is null
) as temp
group by distance_range;

执行后可以看到这样的输出：

 distance_range | num_of_trips
----------------+--------------
             10 |      3883839
             50 |       239067
            100 |          168
            200 |           24
            500 |            1

费率分布

YMatrix 时序数据库支持多表关联和高级窗口函数，例如RANK(),ROW_NUMBER(),DENSE_RANK() 。以下使用高级窗口函数统计出费率每个类型的个数。

SELECT rates.description, 
       COUNT(vendor_id) AS num_trips,
       RANK () OVER (ORDER BY COUNT(vendor_id) DESC) AS trip_rank 
FROM trip
JOIN rate_codes rates ON trip.rate_code_id = rates.rate_code
WHERE is_valid is null AND pickup_datetime < '2016-02-01'
GROUP BY rates.description
ORDER BY LOWER(rates.description);

执行后可以看到这样的输出。

 description | num_trips | trip_rank
-------------+-----------+-----------
 1号机场     |     92551 |         2
 2号机场     |      7304 |         4
 协商价      |     14010 |         3
 团体        |       37 |          6
 标准费率    |   4007265 |         1
 特殊区域    |      1886 |         5

我们可以统计出去1号机场和2号机场机场的次数，平均行程时间（下车时间点 - 上车时间点）,平均票价，最小和最大和平均路程(单位为英里)以及平均乘客人数。根据这些信息可以合理的安排出租车的运行情况，减少车的拥堵情况。

select rates.description,
   count(vendor_id) as num_trips,
   avg(dropoff_datetime - pickup_datetime) as avg_trip_duration,
   min(trip_distance) as min_distance,
   max(trip_distance) as max_distance,
   avg(passenger_count) as avg_passengers
from trip
join rate_codes rates on trip.rate_code_id = rates.rate_code
where is_valid is null  and trip.rate_code_id in (2,3) and pickup_datetime < '2016-02-01'
group by rates.description
order by rates.description;

执行后可以看到这样的输出：

 description | num_trips | avg_trip_duration | min_distance | max_distance |   avg_passengers
-------------+-----------+-------------------+--------------+--------------+--------------------
 1号机场     |     92274 | 00:44:30.722349   |         0.00 |       213.60 | 1.7191408197325357
 2号机场     |      7335 | 00:34:10.621541   |         0.00 |        77.40 | 1.7346966598500341

机场行程分析

我们使用YMatrix的time_bucket函数分析最近一段内出租车去1号机场和2号机场的情况。实时观测出租车的运行情况，使用软件实时的同步给出租车司机，并协助出租车规划最优路线。

select time_bucket('5 minute', pickup_datetime) as datetime,rates.description,count(*)  
from trip
join rate_codes rates on trip.rate_code_id = rates.rate_code
where is_valid is null  and  trip.rate_code_id in (2,3) and pickup_datetime > '2016-01-02 08:00' and pickup_datetime < '2016-01-02 10:00'
group by datetime,rates.description
order by datetime desc;

执行后可以看到这样的输出：

      datetime       | description | count
---------------------+-------------+-------
 2016-01-02 09:55:00 | 1号机场     |    16
 2016-01-02 09:55:00 | 2号机场     |     1
 2016-01-02 09:50:00 | 1号机场     |     8
 2016-01-02 09:50:00 | 2号机场     |     3
 2016-01-02 09:45:00 | 1号机场     |     8
 2016-01-02 09:40:00 | 1号机场     |    10
 2016-01-02 09:40:00 | 2号机场     |     3
 2016-01-02 09:35:00 | 1号机场     |    10
 2016-01-02 09:35:00 | 2号机场     |     2
 2016-01-02 09:30:00 | 1号机场     |    15
 2016-01-02 09:30:00 | 2号机场     |     2
 2016-01-02 09:25:00 | 1号机场     |    15

附近出租车

YMatrix支持空间范围数据查询，需要将纬度和经度点转换为几何坐标，来根据该经纬度的位置统计出租车的数量。根据(lat,long) (40.7589,-73.9851)位置信息获取400米范围内每隔30分钟的出租车的数量。

insert into trip(vendor_id,pickup_datetime,pickup_geom) 
select vendor_id,pickup_datetime,st_transform(st_setsrid(st_makepoint(pickup_longitude,pickup_latitude),4326),2163) as pickup_geom 
from  trip  order by 1,2;
 
insert into trip(vendor_id,pickup_datetime,dropoff_geom) 
select vendor_id,pickup_datetime,st_transform(st_setsrid(st_makepoint(dropoff_longitude,dropoff_latitude),4326),2163) as dropoff_geom 
from  trip  order by 1,2;
 
vacuum trip; 
 
select time_bucket('30 minutes', pickup_datetime) as thirty_min, count(*) as near_times_sq
from  trip where is_valid is null  and 
 st_distance(pickup_geom, st_transform(st_setsrid(st_makepoint(-73.9851,40.7589),4326),2163)) < 400
and pickup_datetime < '2016-01-01 14:00'
group by thirty_min 
order by thirty_min;

执行后可以看到这样的输出。

     thirty_min      | near_times_sq
---------------------+---------------
 2016-01-01 00:00:00 |            21
 2016-01-01 00:30:00 |            34
 2016-01-01 01:00:00 |            24
 2016-01-01 01:30:00 |            23
 2016-01-01 02:00:00 |            20
 2016-01-01 02:30:00 |            33
 2016-01-01 03:00:00 |            51
 2016-01-01 03:30:00 |            57
 2016-01-01 04:00:00 |            88