YMatrix 文档
关于 YMatrix
标准集群部署
数据写入
数据迁移
数据查询
运维监控
参考指南
- MPP 架构
- 镜像分布策略
- 持续聚集
- 滑动窗口
- Grafana 监控指标解读
- Prometheus 监控指标解读
- 术语表
-
工具指南
- mxaddmirrors
- mxbackup
- mxbench
- mxdeletesystem
- mxgate
- mxinitstandby
- mxmoveseg
- mxpacklogs
- mxrecover
- mxrestore
- mxshift
- mxstart
- mxstate
- mxstop
- gpconfig
- pgvector
-
数据类型
-
存储引擎
-
执行引擎
-
系统配置参数
SQL 参考
- ABORT
- ALTER_DATABASE
- ALTER_EXTENSION
- ALTER_EXTERNAL_TABLE
- ALTER_FOREIGN_DATA_WRAPPER
- ALTER_FOREIGN_TABLE
- ALTER_FUNCTION
- ALTER_INDEX
- ALTER_RESOURCE_GROUP
- ALTER_RESOURCE_QUEUE
- ALTER_ROLE
- ALTER_RULE
- ALTER_SCHEMA
- ALTER_SEQUENCE
- ALTER_SERVER
- ALTER_TABLE
- ALTER_TABLESPACE
- ALTER_TYPE
- ALTER_USER_MAPPING
- ALTER_VIEW
- ANALYZE
- BEGIN
- CHECKPOINT
- COMMIT
- COPY
- CREATE_DATABASE
- CREATE_EXTENSION
- CREATE_EXTERNAL_TABLE
- CREATE_FOREIGN_DATA_WRAPPER
- CREATE_FOREIGN_TABLE
- CREATE_FUNCTION
- CREATE_INDEX
- CREATE_RESOURCE_GROUP
- CREATE_RESOURCE_QUEUE
- CREATE_ROLE
- CREATE_RULE
- CREATE_SCHEMA
- CREATE_SEGMENT_SET
- CREATE_SEQUENCE
- CREATE_SERVER
- CREATE_TABLE
- CREATE_TABLE_AS
- CREATE_TABLESPACE
- CREATE_TYPE
- CREATE_USER_MAPPING
- CREATE_VIEW
- DELETE
- DROP_DATABASE
- DROP_EXTENSION
- DROP_EXTERNAL_TABLE
- DROP_FOREIGN_DATA_WRAPPER
- DROP_FOREIGN_TABLE
- DROP_FUNCTION
- DROP_INDEX
- DROP_RESOURCE_GROUP
- DROP_RESOURCE_QUEUE
- DROP_ROLE
- DROP_RULE
- DROP_SCHEMA
- DROP_SEGMENT_SET
- DROP_SEQUENCE
- DROP_SERVER
- DROP_TABLE
- DROP_TABLESPACE
- DROP_TYPE
- DROP_USER_MAPPING
- DROP_VIEW
- END
- EXPLAIN
- GRANT
- INSERT
- LOAD
- LOCK
- REINDEX
- RELEASE_SAVEPOINT
- RESET
- REVOKE
- ROLLBACK_TO_SAVEPOINT
- ROLLBACK
- SAVEPOINT
- SELECT INTO
- SET ROLE
- SET TRANSACTION
- SET
- SHOW
- START TRANSACTION
- TRUNCATE
- UPDATE
- VACUUM
常见问题(FAQ)
空间数据类型
本文档主要介绍 YMatrix 支持的空间数据类型。
通常,一个数据库会支持字符串(String)、数值(Number)和日期(Date)这些常见数据类型,但空间数据库由于需要表现数据地理特征(Geographic Features),需要添加额外的数据类型:空间数据类型。这些空间数据类型封装了诸如边界(Boundary)和维度(Dimension)等空间结构。
YMatrix 使用 PostGIS 扩展来支持空间数据类型。
注意!
该特性仅在企业版中提供。
1 安装 PostGIS
PostGIS 依赖 geos39,你需要首先在每个节点上安装 RPM 包:
$ sudo yum install https://ftp.postgresql.org/pub/repos/yum/common/redhat/rhel-7-x86_64/geos39-3.9.1-1.rhel7.x86_64.rpm
$ sudo yum install mxdb-postgis-2.5-1.el7.x86_64.rpm
连接数据库并创建扩展:
=# CREATE EXTENSION postgis;
2 使用 PostGIS
2.1 PostGIS 常用空间数据类型
PostGIS 支持的常用数据类型包括:
- POINT:点
- LINESTRING:线段
- POLYGON:多边形
2.1.1 创建表
创建包括空间数据类型的表:
# 点表
=# CREATE TABLE global_points (
id INT,
name VARCHAR(64),
location GEOGRAPHY(POINT)
)
USING MARS3
DISTRIBUTED BY (id)
ORDER BY (id);
# 面表
=# CREATE TABLE global_areas (
id INT,
name VARCHAR(64),
area GEOGRAPHY(POLYGON)
)
USING MARS3
DISTRIBUTED BY (id)
ORDER BY (id);
2.1.2 插入数据
插入点数据:
=# INSERT INTO global_points VALUES(1, 'point1', 'POINT(-110 29)');
=# INSERT INTO global_points VALUES(2, 'point2', 'POINT(-111 30)');
插入面数据:
=# INSERT INTO global_areas VALUES(1, 'area1', 'POLYGON((-100 25, -100 30, -120 30, -120 25, -100 25))');
=# INSERT INTO global_areas VALUES(2, 'area2', 'POLYGON((-90 25, -90 30, -100 30, -100 25, -90 25))');
2.1.3 常用空间计算函数
计算点坐标距离:
=# SELECT name, ST_Distance('POINT(-110 30)'::geography, location) FROM global_points;
name | st_distance
--------+------------------
point1 | 110844.074057415
point2 | 96485.974080553
(2 rows)
计算面积:
=# SELECT id,name,ST_Area(area) FROM global_areas;
id | name | st_area
----+-------+--------------------
1 | area1 | 1098617743576.8588
2 | area2 | 547807626660.82526
(2 rows)
判断点是否在面内:
=# SELECT a.name, p.name FROM global_points AS p, global_areas AS a WHERE ST_Intersects (a.area, p.location);
name | name
-------+--------
area1 | point1
area1 | point2
(2 rows)
计算面的交集:
=# SELECT ST_AsText(ST_Intersection(a1.area, a2.area)) FROM global_areas AS a1, global_areas AS a2 WHERE a1.name = 'area1' AND a2.name = 'area2';
st_astext
---------------------------------------------------------
LINESTRING(-100 24.9999999999992,-100 29.9999999999995)
(1 row)
2.2 创建空间索引
空间数据需要建立空间索引,才能使空间计算更加高效。PostGIS 支持的空间索引包括:
- GIST:通用查询树
- BRIN:块范围索引
- SP-GiST:空间分区通用查询树
为 location
字段建立索引:
=# CREATE INDEX global_points_gix ON global_points USING GIST ( location );
为 area
字段建立索引:
=# CREATE INDEX global_areas_gix ON global_areas USING GIST ( area );