学习
实践
活动
专区
工具
TVP
写文章
专栏首页Greenplum超融合时序数据库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)格式。

几何要素

WKT格式

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

插入数据实例

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

EWKT、EWKB和Canonical格式

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

几何类型

格式

3D点

POINT(0 0 0)

内嵌空间参考的点

SRID=32632;POINT(0 0)

带M值的点

POINTM(0 0 0)

带M值的3D点

POINT(0 0 0 0)

内嵌空间参考的带M值的多点

SRID=4326;MULTIPOINTM(0 0 0,1 2 1)

插入数据实例

SQL INSERT INTO table (SHAPE, NAME) VALUES(GeomFromEWKT('SRID=4326;POINTM(116.39 39.9 10)'), '北京')

SQL-MM格式

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

几何类型

格式

插值圆弧

CIRCULARSTRING(0 0, 1 1, 1 0)

插值复合曲线

COMPOUNDCURVE(CIRCULARSTRING(0 0, 1 1, 1 0),(1 0, 0 1))

曲线多边形

CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, 3 1, 1 1))

多曲线

MULTICURVE((0 0, 5 5),CIRCULARSTRING(4 0, 4 4, 8 4))

多曲面

MULTISURFACE(CURVEPOLYGON(CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0),(1 1, 3 3, 3 1, 1 1)),((10 10, 14 12, 11 10, 10 10),(11 11, 11.5 11, 11 11.5, 11 11)))

常几何类型和函数

名字

存储空间

描述

表现形式

point

16字节

平面上的点

(x,y)

line

32字节

直线

{A,B,C}

lseg

32字节

线段

((x1,y1),(x2,y2))

box

32字节

矩形

((x1,y1),(x2,y2))

path

16+16n字节

闭合路径

((x1,y1),…)

path

16+16n字节

开放路径

[(x1,y1),…]

polygon

40+16n字节

多边形

((x1,y1),…)

circle

24字节

<(x,y),r>

操作实例

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

常用操作符

操作符

描述

示例

结果

+

平移

select box '((0,0),(1,1))' + point '(2.0,0)';

(3,1),(2,0)

平移

select box '((0,0),(1,1))' – point '(2.0,0)';

(-1,1),(-2,0)

*

伸缩/旋转

select box '((0,0),(1,1))' * point '(2.0,0)';

(2,2),(0,0)

/

伸缩/旋转

select box '((0,0),(2,2))' / point '(2.0,0)';

(1,1),(0,0)

#

交点或者交面

select box'((1,-1),(-1,1))' # box'((1,1),(-1,-1))';

(1,1),(-1,-1)

#

path或polygon的顶点数

select #path'((1,1),(2,2),(2,1))';

3

@-@

长度或周长

select @-@ path'((1,1),(2,2),(2,1))';

3.414213562

@@

中心

select @@ circle'<(0,0),1>';

(0,0)

##

第一个操作数和第二个操作数的最近点

select point '(0,0)' ## lseg '((2,0),(0,2))';

(1,1)

<->

间距

select circle '<(0,0),1>' <-> circle '<(5,0),1>';

3

&&

是否有重叠

select box '((0,0),(1,1))' && box '((0,0),(2,2))';

t

<<

是否严格在左

select circle '((0,0),1)' << circle '((5,0),1)';

t

>>

是否严格在右

select circle '((0,0),1)' >> circle '((5,0),1)';

f

&<

是否没有延伸到右边

select box '((0,0),(1,1))' &< box '((0,0),(2,2))';

t

&>

是否没有延伸到左边

select box '((0,0),(3,3))' &> box '((0,0),(2,2))';

t

<<|

是否严格在下

select box '((0,0),(3,3))' <<| box '((3,4),(5,5))';

t

|>>

是否严格在上

select box '((3,4),(5,5))' |>> box '((0,0),(3,3))';

t

&<|

是否没有延伸到上面

select box '((0,0),(1,1))' &<| box '((0,0),(2,2))';

t

|&>

是否没有延伸到下面

select box '((0,0),(3,3))' |&> box '((0,0),(2,2))';

t

<^

是否低于(允许接触)

select box '((0,0),(3,3))' <^ box '((3,3),(4,4))';

t

>^

是否高于(允许接触)

select box '((0,0),(3,3))' >^ box '((3,3),(4,4))';

f

?#

是否相交

select lseg '((-1,0),(1,0))' ?# box '((-2,-2),(2,2))';

t

?-

是否水平对齐

select ?- lseg '((-1,1),(1,1))';

t

?-

两边图形是否水平对齐

select point '(1,0)' ?- point '(0,0)';

t

?|

是否竖直对齐

select ?| lseg '((-1,0),(1,0))';

f

?|

两边图形是否竖直对齐

select point '(0,1)' ?| point '(0,0)';

t

?-|

是否垂直

select lseg '((0,0),(0,1))' ?-| lseg '((0,0),(1,0))';

t

?||

是否平行

select lseg '((-1,0),(1,0))' ?|| lseg '((-1,2),(1,2))';

t

@>

是否包含

select circle '((0,0),2)' @> point '(1,1)';

t

<@

是否包含于或在图形上

select point '(1,1)' <@ circle '((0,0),2)';

t

~=

是否相同

select polygon '((0,0),(1,1))' ~= polygon '((1,1),(0,0))';

t

常用操作函数

函数

返回值

描述

示例

结果

area(object)

double precision

面积

select area(circle'((0,0),1)');

3.141592654

center(object)

point

中心

select center(box'(0,0),(1,1)');

(0.5,0.5)

diameter(circle)

double precision

圆周长

select diameter(circle '((0,0),2.0)');

4

height(box)

double precision

矩形竖直高度

select height(box '((0,0),(1,1))');

1

isclosed(path)

boolean

是否为闭合路径

select isclosed(path '((0,0),(1,1),(2,0))');

t

isopen(path)

boolean

是否为开放路径

select isopen(path '[(0,0),(1,1),(2,0)]');

t

length(object)

double precision

长度

select length(path '((-1,0),(1,0))');

4

npoints(path)

int

path中的顶点数

select npoints(path '[(0,0),(1,1),(2,0)]');

3

npoints(polygon)

int

多边形的顶点数

select npoints(polygon '((1,1),(0,0))');

2

pclose(path)

path

将开放path转换为闭合path

select pclose(path '[(0,0),(1,1),(2,0)]');

((0,0),(1,1),(2,0))

popen(path)

path

将闭合path转换为开放path

select popen(path '((0,0),(1,1),(2,0))');

[(0,0),(1,1),(2,0)]

radius(circle)

double precision

圆半径

select radius(circle '((0,0),2.0)');

2

width(box)

double precision

矩形的水平长度

select width(box '((0,0),(1,1))');

1

OGC标准函数

管理函数

函数

说明

AddGeometryColumn(, , , , , )

添加几何字段

DropGeometryColumn(, , )

删除几何字段

Probe_Geometry_Columns()

检查数据库几何字段并在geometry_columns中归档

ST_SetSRID(geometry, integer)

给几何对象设置空间参考(在通过一个范围做空间查询时常用)

几何对象关系函数

函数

说明

ST_Distance(geometry, geometry)

获取两个几何对象间的距离

ST_DWithin(geometry, geometry, float)

如果两个几何对象间距离在给定值范围内,则返回TRUE

ST_Equals(geometry, geometry)

判断两个几何对象是否相等(比如LINESTRING(0 0, 2 2)和LINESTRING(0 0, 1 1, 2 2)是相同的几何对象)

ST_Disjoint(geometry, geometry)

判断两个几何对象是否分离

ST_Intersects(geometry, geometry)

判断两个几何对象是否相交

ST_Touches(geometry, geometry)

判断两个几何对象的边缘是否接触

ST_Crosses(geometry, geometry)

判断两个几何对象是否互相穿过

ST_Within(geometry A, geometry B)

判断A是否被B包含

ST_Overlaps(geometry, geometry)

判断两个几何对象是否是重叠

ST_Contains(geometry A, geometry B)

判断A是否包含B

ST_Covers(geometry A, geometry B)

判断A是否覆盖 B

ST_CoveredBy(geometry A, geometry B)

判断A是否被B所覆盖

ST_Relate(geometry, geometry, intersectionPatternMatrix)

通过DE-9IM 矩阵判断两个几何对象的关系是否成立

ST_Relate(geometry, geometry)

获得两个几何对象的关系(DE-9IM矩阵)

几何对象处理函数

函数

说明

ST_Centroid(geometry)

获取几何对象的中心

ST_Area(geometry)

面积量测

ST_Length(geometry)

长度量测

ST_PointOnSurface(geometry)

返回曲面上的一个点

ST_Boundary(geometry)

获取边界

ST_Buffer(geometry, double, [integer])

获取缓冲后的几何对象

ST_ConvexHull(geometry)

获取多几何对象的外接对象

ST_Intersection(geometry, geometry)

获取两个几何对象相交的部分

ST_Shift_Longitude(geometry)

将经度小于0的值加360使所有经度值在0-360间

ST_SymDifference(geometry A, geometry B)

获取两个几何对象不相交的部分(A、B可互换)

ST_Difference(geometry A, geometry B)

从A去除和B相交的部分后返回

ST_Union(geometry, geometry)

返回两个几何对象的合并结果

ST_Union(geometry set)

返回一系列几何对象的合并结果

ST_MemUnion(geometry set)

用较少的内存和较长的时间完成合并操作,结果和ST_Union

几何对象存取函数

函数

说明

ST_AsText(geometry)

获取几何对象的WKT描述

ST_AsBinary(geometry)

获取几何对象的WKB描述

ST_SRID(geometry)

获取几何对象的空间参考ID

ST_Dimension(geometry)

获取几何对象的维数

ST_Envelope(geometry)

获取几何对象的边界范围

ST_IsEmpty(geometry)

判断几何对象是否为空

ST_IsSimple(geometry)

判断几何对象是否不包含特殊点(比如自相交)

ST_IsClosed(geometry)

判断几何对象是否闭合

ST_IsRing(geometry)

判断曲线是否闭合并且不包含特殊点

ST_NumGeometries(geometry)

获取多几何对象中的对象个数

ST_GeometryN(geometry,int)

获取多几何对象中第N个对象

ST_NumPoints(geometry)

获取几何对象中的点个数

ST_PointN(geometry,integer)

获取几何对象的第N个点

ST_ExteriorRing(geometry)

获取多边形的外边缘

ST_NumInteriorRings(geometry)

获取多边形内边界个数

ST_NumInteriorRing(geometry)

(同上)

ST_InteriorRingN(geometry,integer)

获取多边形的第N个内边界

ST_EndPoint(geometry)

获取线的终点

ST_StartPoint(geometry)

获取线的起始点

ST_GeometryType(geometry)

获取几何对象的类型

ST_GeometryType(geometry)

类似上,但是不检查M值,即POINTM对象会被判断为point

ST_X(geometry)

获取点的X坐标

ST_Y(geometry)

获取点的Y坐标

ST_Z(geometry)

获取点的Z坐标

ST_M(geometry)

获取点的M值

类型转换函数

函数

返回类型

描述

示例

结果

box(circle)

box

圆形转矩形

select box(circle ‘((0,0),2.0)’);

(1.41421356237309,1.41421356237309),(-1.41421356237309,-1.41421356237309)

box(point)

box

点转空矩形

select box(point ‘(0,0)’);

(0,0),(0,0)

box(point, point)

box

点转矩形

select box(point ‘(0,0)’, point ‘(1,1)’);

(1,1),(0,0)

box(polygon)

box

多边形转矩形

select box(polygon ‘((0,0),(1,1),(2,0))’);

(2,1),(0,0)

bound_box(box, box)

box

将两个矩形转换成一个边界矩形

select bound_box(box ‘((0,0),(1,1))’, box ‘((3,3),(4,4))’);

(4,4),(0,0)

circle(box)

circle

矩形转圆形

select circle(box ‘((0,0),(1,1))’);

<(0.5,0.5),0.707106781186548>

circle(point, double precision)

circle

圆心与半径转圆形

select circle(point ‘(0,0)’, 2.0);

<(0,0),2>

circle(polygon)

circle

多边形转圆形

select circle(polygon ‘((0,0),(1,1),(2,0))’);

<(1,0.333333333333333),0.924950591148529>

line(point, point)

line

点转直线

select line(point ‘(-1,0)’, point ‘(1,0)’);

{0,-1,0}

lseg(box)

lseg

矩形转线段

select lseg(box ‘((-1,0),(1,0))’);

[(1,0),(-1,0)]

lseg(point, point)

lseg

点转线段

select lseg(point ‘(-1,0)’, point ‘(1,0)’);

[(-1,0),(1,0)]

path(polygon)

path

多边形转path

select path(polygon ‘((0,0),(1,1),(2,0))’);

((0,0),(1,1),(2,0))

point(double precision, double precision)

point

select point(23.4, -44.5);

(23.4,-44.5)

point(box)

point

矩形转点

select point(box ‘((-1,0),(1,0))’);

(0,0)

point(circle)

point

圆心

select point(circle ‘((0,0),2.0)’);

(0,0)

point(lseg)

point

线段中心

select point(lseg ‘((-1,0),(1,0))’);

(0,0)

point(polygon)

point

多边形的中心

select point(polygon ‘((0,0),(1,1),(2,0))’);

(1,0.333333333333333)

polygon(box)

polygon

矩形转4点多边形

select polygon(box ‘((0,0),(1,1))’);

((0,0),(0,1),(1,1),(1,0))

polygon(circle)

polygon

圆形转12点多边形

select polygon(circle ‘((0,0),2.0)’);

((-2,0),(-1.73205080756888,1),(-1,1.73205080756888),(-1.22460635382238e-16,2),(1,1.73205080756888),(1.73205080756888,1),(2,2.4492127

0764475e-16),(1.73205080756888,-0.999999999999999),(1,-1.73205080756888),(3.67381906146713e-16,-2),(-0.999999999999999,-1.73205080756

888),(-1.73205080756888,-1))

polygon(npts, circle)

polygon

圆形转npts点多边形

select polygon(12, circle ‘((0,0),2.0)’);

((-2,0),(-1.73205080756888,1),(-1,1.73205080756888),(-1.22460635382238e-16,2),(1,1.73205080756888),(1.73205080756888,1),(2,2.4492127

0764475e-16),(1.73205080756888,-0.999999999999999),(1,-1.73205080756888),(3.67381906146713e-16,-2),(-0.999999999999999,-1.73205080756

888),(-1.73205080756888,-1))

polygon(path)

polygon

将path转多边形

select polygon(path ‘((0,0),(1,1),(2,0))’);

((0,0),(1,1),(2,0))

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 : 位置区域信息

数据加载

-- 使用以下命令把数据加载到表中 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

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

数据处理

在时序的场景中,因为各种复杂的原因,数据有时会包含一些明显错误或者无效的数据。借助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

原创声明,本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

登录 后参与评论
0 条评论

相关文章

  • 分久必合?数据库进入“超”融合时代 | 爱分析调研

    上世纪60年代,网状和层状数据库揭开了数据库系统发展的帷幕;1970年,来自IBM实验室的Edgar F. Codd发表了《大型共享数据库数据的关系模型》论文,...

    爱分析ifenxi
  • 将数据库性能提升100倍?大数据时代中,一位数据库老兵的创新之路

    在无数数据库行业的老将新兵中,我们注意到一批力图解决大数据语境下,数据库使用和运维难题的“引路人”。今天,InfoQ 的专访对象 --- 姚延栋,正是这批大数据...

    深度学习与Python
  • Techo Hub—工业互联网的技术探索与实践(西安站2021.06.01)

    5月29日下午,以《工业互联网的技术探索与实践》为主题的2021 腾讯云Techo Hub技术巡回活动第二站在西安老钢厂设计创意产业园盛大开启。陕西高端装备与智...

    Techo Hub团队
  • 时序数据库破局开放探讨

    近几年IoT、IIoT、AIoT和智慧城市快速发展,时序/时空数据库成为数据架构技术栈的标配。根据国际知名网站DB-Engines数据,时序数据库在过去24个月...

    用户6543014
  • 腾讯云数据库产品介绍

    腾讯云上有许多种数据库产品,本文简单介绍每种产品的介绍,特性,应用场景等,帮助各位根据业务需要选择最适合的数据库。

    scarlett学习手册
  • MatrixDB是Hive的25.8倍是Impala+Kudu的8.8倍

    过去大家提到大数据就会联想到Hadoop,而Hadoop是从2003-2004年开始,Google公布了GFS\ MapReduce\BigTable 三篇论文...

    小徐
  • 数据3分钟丨GitHub开放全世界最大安全咨询数据库;DataBench-T正式开源;甲骨文283亿美元收购Cerner

    数据 3 分钟 由 ACDU (中国 DBA 联盟) 与墨天轮联合出品的全新视频节目上线啦~三分钟带你来了解数据行业动态,节目内容主要包含数据行业最新的产品发布...

    数据和云
  • 深圳大学梁臻博士提出EEGFuseNet高维脑电图混合无监督深度特征表征与融合模型及其在情绪识别中的应用

    近期,深圳大学医学部生物医学工程学院梁臻博士(张治国教授团队MIND LAB核心成员)提出一种实用的基于无监督学习的卷积循环混合生成对抗网络,用以实现有效的脑电...

    脑机接口社区
  • 2022爱分析・数据库厂商全景报告 | 爱分析报告

    在国内数字化转型以及信创建设持续推进的大背景下,众多厂商入局国内数据库市场,为企业提供了面向多种应用场景的数据库,以及相关的生态工具或服务。国内数据库市场因此迎...

    爱分析ifenxi
  • 开源开放 | 一个融合多元关系和事件表示的金融领域本体模型FTHO(CCKS2021)

    在此开放资源中,面对金融领域多元关系表示的困境和时序事件表示需求,我们以OWL语义为基础,结合金融领域专业知识,融合超图概念和事件5W(When,where,W...

    一点人工一点智能
  • 战略升级!腾讯云数据库五大新品重磅发布

    点击▲关注 腾讯云数据库 8月28日,腾讯云数据库在京正式启动战略升级发布会。 未来,腾讯云数据库将聚焦云原生、自治、超融合三大战略方向,并且面向全球用户同步发...

    腾讯云数据库 TencentDB
  • 战略升级!腾讯云数据库五大新品重磅发布

      8月28日,腾讯云数据库在京正式启动战略升级发布会。 未来,腾讯云数据库将聚焦云原生、自治、超融合三大战略方向,并且面向全球用户同步发布五大战略级新品: ...

    腾讯数据库技术
  • 【每周CV论文推荐】初学视频分类与行为识别有哪些值得阅读的论文?

    欢迎来到《每周CV论文推荐》。在这个专栏里,还是本着有三AI一贯的原则,专注于让大家能够系统性完成学习,所以我们推荐的文章也必定是同一主题的。

    用户1508658
  • 让预训练语言模型读懂数字:超对称技术发布 10 亿参数 BigBang Transformer [乾元]金融大规模预训练语言模型

    4.创新的预训练方法可大幅提高语言模型准确度:Similarity Sampling 和Source Prompt

    AI科技大本营
  • 业内视频超分辨率新标杆,快手&大连理工研究登上CVPR 2022

    转载整理自 快手音视频技术 量子位 | 公众号 QbitAI 现在,视频超分辨率算法又迎来新突破。 一篇最新登上CVPR 2022的论文,从一种新的视角,统一了...

    量子位
  • 跨过数据库大山(发布会归来有感)

    今日有幸受邀参加腾讯云数据库发布会,在其圆桌论坛环节,与几位行业大咖就数据库当下的热点话题,进行了分享。下面将我的一些分享要点,汇集如下。

    用户5548425

扫码关注腾讯云开发者

领取腾讯云代金券