OceanBase V4.2特性解析：MySQL模式下GIS空间表达式的场景及能力解析_ocenbase postgis

1. 背景

1.1. OceanBase Mysql gis空间表达式的应用场景及能力

在OceanBase 4.1版本中，mysql模式下支持了gis数据类型以及部分空间对象相关的表达式，随着客户使用空间数据的需求日益增长，需要快速地补齐空间数据存储和计算分析的能力；

针对mysql的空间表达式的能力，目前补齐了部分gis表达式，包括：

• 空间关系计算表达式：判断空间关系是否成立（ST_Crosses/ST_Overlaps）
• 空间对象计算表达式：根据输入的空间对象计算出新的空间对象（ST_Difference/ST_Union/ST_SymDifference）
• 空间对象测量表达式：测量空间对象长度（ST_Length）
• 空间对象分析表达式：计算空间对象质心点（ST_Centroid）
• 空间对象格式转换表达式：空间对象转换为json类型（ST_AsGeoJSON）

pg作为gis行业使用最广的数据库，提供了部分空间表达式的功能是mysql不具备的，且该部分表达式也被广泛应用，同时pg还支持3维空间对象存储。因此ob在兼容mysql gis的能力基础上也对空间表达式的能力进行了扩展补充，作为ob mysql模式下特有的空间表达式（这部分空间表达式以'_'做为前缀）；包括以下几类：

• • 空间关系计算表达式：判断空间关系是否成立（_ST_Touches/_ST_Equals）
  • 空间对象构造表达式：构造空间对象（_ST_MakeEnvelope/）
  • 空间对象计算表达式：根据输入的空间对象计算出新的空间对象（_ST_ClipByBox2D）
  • 空间对象属性访问表达式：查询空间对象某一属性（_ST_GeometryType/_ST_IsCollection/_ST_NumInteriorRings）
  • 空间对象分析表达式：计算一个保证位于几何体内部的点（_ST_PointOnSurface）
  • 空间对象格式转换表达式：空间对象转换为mapbox vector tile格式（ST_AsMVTGeom/_ST_AsMVT）

ob mysql gis也支持了3维空间对象的存储能力。

1.2  OceanBase v4.2.2，Mysql gis空间表达式的特性支持

OceanBase 4.2.2版本下新增的兼容mysql gis空间表达式如下：

此外，新增ob mysql特有gis空间表达式如下:

支持3维gis数据存储，gis表达式适配3维数据分类如下：

• 输入WKT/WKB格式数据构造空间对象的表达式，支持输入3维数据构造空间对象；
• 将存储的空间数据转换成指定格式WKT/WKB/EWKB/EWKT输出的表达式，支持查询3维空间数据；
• 不支持输入3D数据；

• 以下表达式支持空间索引

2. 使用操作

2.1. ST_Crosses(g1,g2)

支持空间索引，判断两个Geometry g1和g2在空间上是否有交叉，即几何体内部有部分点相同，但不完全相同。

OceanBase(root@oceanbase)>select st_crosses(st_geomfromtext('LINESTRING(1 1, 11 11)'), st_geomfromtext('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'));
+------------------------------------------------------------------------------------------------------------------+
| st_crosses(st_geomfromtext('LINESTRING(1 1, 11 11)'), st_geomfromtext('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) |
+------------------------------------------------------------------------------------------------------------------+
|                                                                                                                1 |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.004 sec)

2.2. ST_Overlaps(g1,g2)

支持空间索引，判断两个几何体是否空间上相交且具有相同的维度，但并不完全互相包含。

OceanBase(root@oceanbase)>SELECT ST_OVERLAPS(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,0 0),(2 2,2 4,4 4,4 2,2 2))'), ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))'));
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_OVERLAPS(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,0 0),(2 2,2 4,4 4,4 2,2 2))'), ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))')) |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                               1 |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)

2.3. ST_Difference(g1,g2)

返回geometry A中不和geometry B相交的部分，等价于A - ST_Intersection(A, B)。

OceanBase(root@oceanbase)>SELECT ST_AsText(ST_Difference(ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)')));
+----------------------------------------------------------------------------------------------------------------------+
| ST_AsText(ST_Difference(ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)'))) |
+----------------------------------------------------------------------------------------------------------------------+
| LINESTRING(50 150,50 200)                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.004 sec)

2.4. ST_Union(g1,g2)

返回geometry A和geometry B的并集

OceanBase(root@oceanbase)>SELECT ST_AsText(ST_Union(ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)')));
+-----------------------------------------------------------------------------------------------------------------+
| ST_AsText(ST_Union(ST_GeomFromText('LINESTRING(50 100, 50 200)'),ST_GeomFromText('LINESTRING(50 50, 50 150)'))) |
+-----------------------------------------------------------------------------------------------------------------+
| MULTILINESTRING((50 100,50 200),(50 50,50 100))                                                                 |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.004 sec)

2.5. ST_Length(ls[,unit])

unit参数指定了长度显示单位，默认为metre（米），常见的有foot、centimetre等。全部单位和其对应转换系数如下，当且仅当srid != 0且单位合法时生效。

+--------------------------------------+---------------------+
| UNIT_NAME                            | CONVERSION_FACTOR   |
+--------------------------------------+---------------------+
| British chain (Benoit 1895 A)        |          20.1167824 |
| British chain (Benoit 1895 B)        |  20.116782494375872 |
| British chain (Sears 1922 truncated) |           20.116756 |
| British chain (Sears 1922)           |  20.116765121552632 |
| British foot (1865)                  | 0.30480083333333335 |
| British foot (1936)                  |        0.3048007491 |
| British foot (Benoit 1895 A)         |  0.3047997333333333 |
| British foot (Benoit 1895 B)         | 0.30479973476327077 |
| British foot (Sears 1922 truncated)  | 0.30479933333333337 |
| British foot (Sears 1922)            |  0.3047994715386762 |
| British link (Benoit 1895 A)         |         0.201167824 |
| British link (Benoit 1895 B)         |  0.2011678249437587 |
| British link (Sears 1922 truncated)  |          0.20116756 |
| British link (Sears 1922)            |  0.2011676512155263 |
| British yard (Benoit 1895 A)         |           0.9143992 |
| British yard (Benoit 1895 B)         |  0.9143992042898124 |
| British yard (Sears 1922 truncated)  |            0.914398 |
| British yard (Sears 1922)            |  0.9143984146160288 |
| centimetre                           |                0.01 |
| chain                                |             20.1168 |
| Clarke's chain                       |       20.1166195164 |
| Clarke's foot                        |        0.3047972654 |
| Clarke's link                        |      0.201166195164 |
| Clarke's yard                        |        0.9143917962 |
| fathom                               |              1.8288 |
| foot                                 |              0.3048 |
| German legal metre                   |        1.0000135965 |
| Gold Coast foot                      |  0.3047997101815088 |
| Indian foot                          | 0.30479951024814694 |
| Indian foot (1937)                   |          0.30479841 |
| Indian foot (1962)                   |           0.3047996 |
| Indian foot (1975)                   |           0.3047995 |
| Indian yard                          |  0.9143985307444408 |
| Indian yard (1937)                   |          0.91439523 |
| Indian yard (1962)                   |           0.9143988 |
| Indian yard (1975)                   |           0.9143985 |
| kilometre                            |                1000 |
| link                                 |            0.201168 |
| metre                                |                   1 |
| millimetre                           |               0.001 |
| nautical mile                        |                1852 |
| Statute mile                         |            1609.344 |
| US survey chain                      |   20.11684023368047 |
| US survey foot                       | 0.30480060960121924 |
| US survey link                       |  0.2011684023368047 |
| US survey mile                       |  1609.3472186944375 |
| yard                                 |              0.9144 |
+--------------------------------------+---------------------+

OceanBase(root@oceanbase)>SET @ls = ST_GeomFromText('LineString(1 1,2 2,3 3)', 4326);
Query OK, 0 rows affected (0.001 sec)
 
OceanBase(root@oceanbase)>SELECT ST_Length(@ls, "foot");
+------------------------+
| ST_Length(@ls, "foot") |
+------------------------+
|     1029205.9131247795 |
+------------------------+
1 row in set (0.001 sec)
 
OceanBase(root@oceanbase)>SELECT ST_Length(@ls);
+-------------------+
| ST_Length(@ls)    |
+-------------------+
| 313701.9623204328 |
+-------------------+
1 row in set (0.004 sec)

2.6. _ST_PointOnSurface

定义：geometry _st_pointonsurface(a geometry)，返回geometry类型为POINT。

返回一个保证位于几何体内部的点，和ST_Centroid不同，ST_Centroid返回的中心点不一定在几何体上。例如下图绿色点为中心点，红色点为ST_PointOnSurface所返回的内部点。

棕色：中心点，绿色：内部点

OceanBase(root@oceanbase)>SELECT ST_AsText(_ST_PointOnSurface(geom)) AS pt_on_surf,
    -> ST_AsText(ST_Centroid(geom)) AS centroid
    -> FROM (SELECT ST_GeomFromText('POLYGON ((0 0, 0 10, 10 10, 10 8, 2 8, 2 2, 10 2, 10 0, 0 0))') AS geom) AS t;
+------------+----------------------------+
| pt_on_surf | centroid                   |
+------------+----------------------------+
| POINT(1 5) | POINT(4.076923076923077 5) |
+------------+----------------------------+
1 row in set (0.001 sec)

2.7. _ST_MakeEnvelope

该函数通过输入左下角坐标与右上角坐标，构造一个矩形，常作为以Box为参数的其他函数的输入

-- 定义
geometry _ST_MakeEnvelope(float xmin, float ymin, float xmax, float ymax, integer srid=unknown);
 
-- 构造矩形
OceanBase(root@oceanbase)>SELECT ST_AsText( _ST_MakeEnvelope(10, 10, 11, 11, 4326) );
+-----------------------------------------------------+
| ST_AsText( _ST_MakeEnvelope(10, 10, 11, 11, 4326) ) |
+-----------------------------------------------------+
| POLYGON((10 10,11 10,11 11,10 11,10 10))            |
+-----------------------------------------------------+
1 row in set (0.004 sec)
 
-- 作为其他函数的输入
-- Rely on implicit cast from geometry to box2d for the second parameter
OceanBase(root@oceanbase)>SELECT ST_ASTEXT(_ST_ClipByBox2D(ST_GEOMFROMTEXT('POLYGON((-2 -2, -2 11, 11 11, 11 -2, -2 -2))'), 
    ->                       _ST_MakeEnvelope(0,0,10,10)));
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_ASTEXT(_ST_ClipByBox2D(ST_GEOMFROMTEXT('POLYGON((-2 -2, -2 11, 11 11, 11 -2, -2 -2))'), 
                      _ST_MakeEnvelope(0,0,10,10))) |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((0 0,0 10,10 10,10 0,0 0))                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)

2.8. _ST_ClipByBox2D

geometry _ST_ClipByBox2D(geometry geom, box2d box);

通过Box快速剪裁几何体，不会检查输入几何体的合法性，也不保证输出的几何体是合法的。

该函数第二个参数虽然是Box，但实际上可以输入任意Geometry，会转换为对应的Box。

OceanBase(root@oceanbase)>SELECT ST_ASTEXT(_ST_ClipByBox2D(ST_GEOMFROMTEXT('POLYGON((-2 -2, -2 11, 11 11, 11 -2, -2 -2))'), 
    ->                       _ST_MakeEnvelope(0,0,10,10)));
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_ASTEXT(_ST_ClipByBox2D(ST_GEOMFROMTEXT('POLYGON((-2 -2, -2 11, 11 11, 11 -2, -2 -2))'), 
                      _ST_MakeEnvelope(0,0,10,10))) |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((0 0,0 10,10 10,10 0,0 0))                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)

2.9. _ST_GeometryType

返回Geometry的SQL-MM类型，格式为ST_[TYPE]

OceanBase(root@oceanbase)>SELECT _ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));
+--------------------------------------------------------------------------------------------------+
| _ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)')) |
+--------------------------------------------------------------------------------------------------+
| ST_LineString                                                                                    |
+--------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

2.10. _ST_IsCollection

判断输入的Geometry是否为集合类型，包括

1. GEOMETRYCOLLECTION
2. MULTI{POINT,POLYGON,LINESTRING,CURVE,SURFACE}
3. COMPOUNDCURVE，OB暂不支持，报错处理

OceanBase(root@oceanbase)> SELECT _ST_IsCollection(st_geomfromtext('LINESTRING(0 0, 1 1)'));
+-----------------------------------------------------------+
| _ST_IsCollection(st_geomfromtext('LINESTRING(0 0, 1 1)')) |
+-----------------------------------------------------------+
|                                                         0 |
+-----------------------------------------------------------+
1 row in set (0.003 sec)

2.11. _ST_NumInteriorRings

返回Polygon的内环数，如果Geometry非Polygon类型，则返回NULL

-- 定义
integer _ST_NumInteriorRings(geometry a_polygon);
 
-- 示例
OceanBase(root@oceanbase)>select _ST_NumInteriorRings(ST_GeomFromText('POLYGON((2 2 1,2 8 5,8 8 7,8 2 4,2 2 1))'));
+-----------------------------------------------------------------------------------+
| _ST_NumInteriorRings(ST_GeomFromText('POLYGON((2 2 1,2 8 5,8 8 7,8 2 4,2 2 1))')) |
+-----------------------------------------------------------------------------------+
|                                                                                 0 |
+-----------------------------------------------------------------------------------+
1 row in set (0.003 sec)

2.12. _ST_Touches

支持空间索引，判断两个Geometry A和B边界是否至少有一个共同点，且内部不相交。注意若两个Geometry均为Point类型，直接返回false，因为Point没有边界。

OceanBase(root@oceanbase)>SELECT _ST_Touches(st_geomfromtext('LINESTRING(0 0, 1 1, 0 2)'),
    ->                    st_geomfromtext('POINT(0 2)'));
+-------------------------------------------------------------------------------------------------------------+
| _ST_Touches(st_geomfromtext('LINESTRING(0 0, 1 1, 0 2)'),
                   st_geomfromtext('POINT(0 2)')) |
+-------------------------------------------------------------------------------------------------------------+
|                                                                                                           1 |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

2.13. ST_Equals

支持空间索引，判断两个Geometry A和B是否在空间结构上相等，即包含相同的点集，但不考虑点集顺序是否一致。

OceanBase(root@oceanbase)>SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'),
    -> ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
+-------------------------------------------------------------------------------------------------------+
| ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'),
ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)')) |
+-------------------------------------------------------------------------------------------------------+
|                                                                                                     1 |
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

2.14. _ST_AsMVT

功能：将表包含gis列的所有行数据聚合返回一个使用Google Protocol Buffers编码格式二进制的mapbox矢量瓦片；

OceanBase(root@oceanbase)>SELECT 'TG1', hex(_ST_AsMVT(q.*, 'test', 4096, 'geom')) FROM (SELECT 1 AS c1, ST_GeomFromText('POINT(25 17)')AS geom) AS q;
+-----+----------------------------------------------------------------------+
| TG1 | hex(_ST_AsMVT(q.*, 'test', 4096, 'geom'))                            |
+-----+----------------------------------------------------------------------+
| TG1 | 1A200A0474657374120B12020000180122030932221A026331220228012880207802 |
+-----+----------------------------------------------------------------------+
1 row in set (0.004 sec)

上例中_ST_AsMVT中生成的结果中编码了字段C1和它的值（1）、字段geom和它的值（'POINT(25 17)'）点的信息；

参数说明：

• row：聚合的一组行，表名.*
• name：layer的命名，默认为"default"
• extent：瓦片的范围（默认4096个单位, 4096 * 4096），直接透传保存在layer中；
• geom_name：通过该参数指定行中geom列，默认取第一个geom列；如果一行中有多个gis列，未被指定的gis列直接当成string进行编码；
• feature_id_name: 通过该参数指定行中的列，将该列中的值作为feature的id；如果不指定，feature id默认为0；

输出的pb编码的二进制数据可读性不高，为提高可测试性，提高脚本来解析：mvt_debug.js

// 执行脚本，将表达式结果作为参数传入
node mvt_debug.js '1A200A0474657374120B12020000180122030932221A026331220228012880207802'
// 解析结果
layer name: [ 'test' ]
feature count: 1
feature VectorTileFeature {
  properties: { c1: 1 },
  extent: 4096,
    type: 1,
    _pbf: {
    buf: Uint8Array(34) [
      26, 32, 10,   4, 116, 101, 115, 116, 18,
      11, 18,  2,   0,   0,  24,   1,  34,  3,
      9, 50, 34,  26,   2,  99,  49,  34,  2,
      40,  1, 40, 128,  32, 120,   2
      ],
      pos: 21,
      type: 2,
      length: 34
  },
  _geometry: 17,
    _keys: [ 'c1' ],
    _values: [ 1 ]
}
id undefined
bbox [ 25, 17, 25, 17 ]
GEO [ [ { x: 25, y: 17 } ] ]

2.15. _ST_MakeValid

将输入的无效多边形修复为有效的多边形，只支持投影坐标系，不支持地理坐标系

OceanBase(root@oceanbase)>select st_astext(_st_makevalid(st_geomfromtext('POLYGON((0 0,1 1,2 2,0 2,1 1,2 0,0 0))')));
+-------------------------------------------------------------------------------------+
| st_astext(_st_makevalid(st_geomfromtext('POLYGON((0 0,1 1,2 2,0 2,1 1,2 0,0 0))'))) |
+-------------------------------------------------------------------------------------+
| MULTIPOLYGON(((1 1,2 2,0 2,1 1)),((1 1,0 0,2 0,1 1)))                               |
+-------------------------------------------------------------------------------------+
1 row in set (0.006 sec)

上例中将自相交的多边形，

转换为2个三角形

2.16. ST_SymDifference(g1,g2)

OceanBase(root@oceanbase)>SET @g1 = ST_GeomFromText('MULTIPOINT(5 0,15 10,15 25)');
Query OK, 0 rows affected (0.001 sec)
 
OceanBase(root@oceanbase)>SET @g2 = ST_GeomFromText('MULTIPOINT(1 1,15 10,15 25)');
Query OK, 0 rows affected (0.001 sec)
 
OceanBase(root@oceanbase)>SELECT ST_AsText(ST_SymDifference(@g1, @g2));
+---------------------------------------+
| ST_AsText(ST_SymDifference(@g1, @g2)) |
+---------------------------------------+
| MULTIPOINT((1 1),(5 0))               |
+---------------------------------------+
1 row in set (0.004 sec)

2.17. ST_AsGeoJSON(g[,max_dec_digits[,options]])

--flag 0(000)到7(111)表现
OceanBase(root@oceanbase)>SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,0);
+---------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,0) |
+---------------------------------------------------------------------+
| {"type": "Point", "coordinates": [12.2, 11.1]}                      |
+---------------------------------------------------------------------+
1 row in set (0.003 sec)
 
OceanBase(root@oceanbase)>SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,1);
+----------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,1)              |
+----------------------------------------------------------------------------------+
| {"bbox": [12.2, 11.1, 12.2, 11.1], "type": "Point", "coordinates": [12.2, 11.1]} |
+----------------------------------------------------------------------------------+
1 row in set (0.001 sec)
 
OceanBase(root@oceanbase)>SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,2);
+--------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,2)                                          |
+--------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "EPSG:4326"}}, "type": "Point", "coordinates": [12.2, 11.1]} |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
 
OceanBase(root@oceanbase)>SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,3);
+------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,3)                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "EPSG:4326"}}, "bbox": [12.2, 11.1, 12.2, 11.1], "type": "Point", "coordinates": [12.2, 11.1]} |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
 
OceanBase(root@oceanbase)>SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,4);
+-------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,4)                                                           |
+-------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "urn:ogc:def:crs:EPSG::4326"}}, "type": "Point", "coordinates": [12.2, 11.1]} |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
 
OceanBase(root@oceanbase)>SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,5);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,5)                                                                                             |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "urn:ogc:def:crs:EPSG::4326"}}, "bbox": [12.2, 11.1, 12.2, 11.1], "type": "Point", "coordinates": [12.2, 11.1]} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
 
OceanBase(root@oceanbase)>SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,6);
+-------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,6)                                                           |
+-------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "urn:ogc:def:crs:EPSG::4326"}}, "type": "Point", "coordinates": [12.2, 11.1]} |
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)
 
OceanBase(root@oceanbase)>SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,7);
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(11.11111 12.22222)', 4326),1,7)                                                                                             |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"crs": {"type": "name", "properties": {"name": "urn:ogc:def:crs:EPSG::4326"}}, "bbox": [12.2, 11.1, 12.2, 11.1], "type": "Point", "coordinates": [12.2, 11.1]} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

2.18. ST_Centroid

OceanBase(root@oceanbase)>SELECT ST_AsText(_ST_PointOnSurface(geom)) AS pt_on_surf,
    -> ST_AsText(ST_Centroid(geom)) AS centroid
    -> FROM (SELECT ST_GeomFromText('POLYGON ((0 0, 0 10, 10 10, 10 8, 2 8, 2 2, 10 2, 10 0, 0 0))') AS geom) AS t;
+------------+----------------------------+
| pt_on_surf | centroid                   |
+------------+----------------------------+
| POINT(1 5) | POINT(4.076923076923077 5) |
+------------+----------------------------+
1 row in set (0.001 sec)

2.19. _ST_AsMVTGeom

OceanBase(root@oceanbase)>SELECT ST_AsText(_ST_AsMVTGeom(ST_GeomFromText('POLYGON ((0 0, 0 -5, 10 0, 10 5, 0 0))'),ST_GeomFromText('POLYGON((0 0,0 4096,4096 4096,4096 0,0 0))'),4096, 0, false));
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsText(_ST_AsMVTGeom(ST_GeomFromText('POLYGON ((0 0, 0 -5, 10 0, 10 5, 0 0))'),ST_GeomFromText('POLYGON((0 0,0 4096,4096 4096,4096 0,0 0))'),4096, 0, false)) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((0 4096,10 4091,10 4096,0 4101,0 4096))                                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
 
OceanBase(root@oceanbase)>SELECT ST_AsText(_ST_AsMVTGeom(ST_GeomFromText('POLYGON ((0 0, 0 -5, 10 0, 10 5, 0 0))'),ST_GeomFromText('POLYGON((0 0,0 4096,4096 4096,4096 0,0 0))'),4096, 0, true));
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_AsText(_ST_AsMVTGeom(ST_GeomFromText('POLYGON ((0 0, 0 -5, 10 0, 10 5, 0 0))'),ST_GeomFromText('POLYGON((0 0,0 4096,4096 4096,4096 0,0 0))'),4096, 0, true)) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((0 4096,10 4091,10 4096,0 4096))                                                                                                                        |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

2.20. 3维空间对象

3维空间对象ddl/dml与2维基本一致，只是3维空间对象无法写入到由子类型定义的字段上

# 创建gis字段
OceanBase(root@oceanbase)>create table geo_3d(g geometry);
Query OK, 0 rows affected (0.302 sec)
 
# 写入3维的点
OceanBase(root@oceanbase)>insert into geo_3d values(ST_GeomFromText('POINT Z (0 0 0)'));
Query OK, 1 row affected (0.025 sec)
 
#查询3维数据
OceanBase(root@oceanbase)>select st_aswkt(g) from geo_3d;
+-----------------+
| st_aswkt(g)     |
+-----------------+
| POINT Z (0 0 0) |
+-----------------+
1 row in set (0.007 sec)
 
# 对3维空间对象做空间计算时，会先转换为2维;
# 如下例所示point（0 0 0）和point(0 0 1)在3维空间下是不相交的，转换到2维就是相交的
OceanBase(root@oceanbase)>select st_intersects(g, st_geomfromtext('POINT Z (0 0 1)')) from geo_3d;
+------------------------------------------------------+
| st_intersects(g, st_geomfromtext('POINT Z (0 0 1)')) |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.005 sec)
 
# 部分表达式不支持3维数据
OceanBase(root@oceanbase)>select st_x(g,1) from geo_3d;
ERROR 3037 (22023): Invalid GIS data provided to function st_x.
 
# 用子类型(point/linestring等)定义gis字段，不支持插入3维数据
OceanBase(root@oceanbase)>create table t(geo point);
Query OK, 0 rows affected (0.511 sec)
# 可以写入2维数据
OceanBase(root@oceanbase)>insert into t values(ST_GeomFromText('POINT(0 0)'));
Query OK, 1 row affected (0.004 sec)
# 无法写入3维数据
OceanBase(root@oceanbase)>insert into t values(ST_GeomFromText('POINT Z (0 0 0)'));
ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field.

3. 总结和展望

如上所述，本次特性为满足用户的需求，补充了部分ob mysql特有的gis空间表达式；同时补齐了部分兼容mysql gis的空间表达式。目前ob mysql gis提供的空间计算和分析的能力与mysql gis仍然有一定差距，将在后续的版本迭代中快速补齐相关功能。