在线咨询
内容搜索

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

发布时间：2025-12-09 20:25:28 浏览次数：4

目录

什么是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的特点

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

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

    插入数据实例

    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)

    插入数据实例

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

    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>

    操作实例

    -- 点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 | POINTtaix | public | trip | dropoff_geom | 2 | 2163 | POINTtaix | public | trip_1_prt_1 | pickup_geom | 2 | 2163 | POINTtaix | public | trip_1_prt_1 | dropoff_geom | 2 | 2163 | POINT

    PostGIS 两个重要的坐标体系

  • 4326 \ GCS_WGS_1984 \ Geographic Coordinate System(GCSS)地理坐标系, World Geodetic System(WGS)世界大地测量系统
  • 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# lscounty.tgz create_repo.sh mxdb-postgis-2.5-1.el7.x86_64.rpm pkg-postgis# sh create_repo.shCreate postgis repo successfully!-- 查看postgis的repo# yum repolistrepo id repo name status 0postgis 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 EXTENSIONpostgres=# \dxList of installed extensionsName | Version | Schema | Description-----------------+---------+------------+---------------------------------------------------------------------gp_exttable_fdw | 1.0 | pg_catalog | External Table Foreign Data Wrapper for Greenplumplpgsql | 1.0 | pg_catalog | PL/pgSQL procedural languagepostgis | 2.5.5 | public | PostGIS geometry, geography, and raster spatial types and functions(3 rows)

    YMatrixDB的PostGIS使用案例

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

    计算两点之间的距离

    -- 两个点之间的距离,距离单位是mselect 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，单位变成米, 否则默认距离单位是度。SELECTuuid,longitude,latitude,ST_DistanceSphere (geom_point,ST_GeomFromText('POINT(121.248642 31.380415)', 4326 )) distance FROMs_poi_gaode WHEREST_DWithin ( geom_point :: geography, ST_GeomFromText ( 'POINT(121.248642 31.380415)', 4326 ) :: geography, 5000 ) IS TRUE order by distance descLIMIT 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 | 0101000020E61000000000000000405BC00000000000003D403 | london | 0101000020E6100000000000000000000000000000008048401 | 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.9664.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') INCLUSIVEEND (date '2016-02-01') EXCLUSIVEEVERY (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 nullgroup by dayorder by day;

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

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

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

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

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

    distance_range | num_of_trips----------------+--------------10 | 388383950 | 239067100 | 168200 | 24500 | 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 tripJOIN rate_codes rates ON trip.rate_code_id = rates.rate_codeWHERE is_valid is null AND pickup_datetime < '2016-02-01'GROUP BY rates.descriptionORDER BY LOWER(rates.description);

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

    description | num_trips | trip_rank-------------+-----------+-----------1号机场 | 92551 | 22号机场 | 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_passengersfrom tripjoin rate_codes rates on trip.rate_code_id = rates.rate_codewhere is_valid is null and trip.rate_code_id in (2,3) and pickup_datetime < '2016-02-01'group by rates.descriptionorder 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.71914081973253572号机场 | 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 tripjoin rate_codes rates on trip.rate_code_id = rates.rate_codewhere 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.descriptionorder by datetime desc;

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

    datetime | description | count---------------------+-------------+-------2016-01-02 09:55:00 | 1号机场 | 162016-01-02 09:55:00 | 2号机场 | 12016-01-02 09:50:00 | 1号机场 | 82016-01-02 09:50:00 | 2号机场 | 32016-01-02 09:45:00 | 1号机场 | 82016-01-02 09:40:00 | 1号机场 | 102016-01-02 09:40:00 | 2号机场 | 32016-01-02 09:35:00 | 1号机场 | 102016-01-02 09:35:00 | 2号机场 | 22016-01-02 09:30:00 | 1号机场 | 152016-01-02 09:30:00 | 2号机场 | 22016-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_sqfrom trip where is_valid is null and st_distance(pickup_geom, st_transform(st_setsrid(st_makepoint(-73.9851,40.7589),4326),2163)) < 400and 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 | 212016-01-01 00:30:00 | 342016-01-01 01:00:00 | 242016-01-01 01:30:00 | 232016-01-01 02:00:00 | 202016-01-01 02:30:00 | 332016-01-01 03:00:00 | 512016-01-01 03:30:00 | 572016-01-01 04:00:00 | 88
    上一篇：万年历（c语言） 下一篇：盈世企业邮箱 如何登陆尚易企业邮箱？
    需要做网站？需要网络推广？欢迎咨询客户经理 13272073477

    免责声明：本站内容（文字信息+图片素材）来源于互联网公开数据整理或转载，仅用于学习参考，如有侵权问题，请及时联系本站删除，我们将在5个工作日内处理。联系邮箱：303555158#qq.com（把#换成@）

    Copyright © 格特瑞商务咨询-贵州做网站公司 版权所有 | 黔ICP备19002813号-1