梧州网站设计理念,成都网站开发培训,聊城做网站的公司策划,微盟微商城官网1. 背景
1.1. OceanBase Mysql gis空间表达式的应用场景及能力
在OceanBase 4.1版本中#xff0c;mysql模式下支持了gis数据类型以及部分空间对象相关的表达式#xff0c;随着客户使用空间数据的需求日益增长#xff0c;需要快速地补齐空间数据存储和计算分析的能力#…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.2Mysql gis空间表达式的特性支持
OceanBase 4.2.2版本下新增的兼容mysql gis空间表达式如下
表达式名称功能介绍boolean ST_Crosses(geometry g1, geometry g2)判断两个Geometry g1和g2在空间上是否有交叉即几何体内部有部分点相同但不完全相同boolean ST_Overlaps(geometry g1, geometry g2)判断两个几何体是否空间上相交且具有相同的维度但并不完全互相包含。geometry ST_Difference(geometry g1, geometry g2)返回geometry g1中不和geometry g2相交的部分等价于g1 - ST_Intersection(g1, g2)geometry ST_Union(geometry g1, geometry g2)返回geometry g1和geometry g2的并集geometry ST_Length(geometry g1, unit string)计算LineString类型或MultiLinestring类型的长度该长度和线段所在空间坐标系有关MultiLinestring类型的长度等于其包含的LineString长度之和unit参数指定了长度显示单位geometry ST_SymDifference(geometry geomA, geometry geomB)返回geometry A和Geometry B不重叠的部分等价于ST_Difference(ST_Union(g1, g2), ST_Intersection(g1, g2))。ST_AsGeoJSON(g [, max_dec_digits [, options]])将geometry转换为对应的json格式包含两个可选参数max_dec_digits和flag。ST_Centroid(geometry A)计算几何体的质心点该质心点不保证在几何体上
此外新增ob mysql特有gis空间表达式如下:
表达式名称功能介绍geometry _ST_PointOnSurface(geometry g1)返回一个保证位于几何体内部的点和ST_Centroid不同ST_Centroid返回的中心点不一定在几何体上geometry _ST_MakeEnvelope(float xmin, float ymin, float xmax, float ymax, integer sridunknown);该函数通过输入左下角坐标与右上角坐标构造一个矩形;geometry _ST_ClipByBox2D(geometry geom, box2d box);通过Box快速剪裁几何体不会检查输入几何体的合法性也不保证输出的几何体是合法的。该函数第二个参数虽然是Box但实际上可以输入任意Geometry会隐式转换为Boxtext _ST_GeometryType(geometry g1);返回Geometry的类型boolean _ST_IsCollection(geometry g1);判断输入的Geometry是否为集合类型integer _ST_NumInteriorRings(geometry a_polygon);返回Polygon的内环数如果Geometry非Polygon类型则返回NULLboolean _ST_Touches(geometry A, geometry B)判断两个Geometry A和B边界是否至少有一个共同点且内部不相交。注意若两个Geometry均为Point类型直接返回false因为Point没有边界boolean ST_Equals(geometry A, geometry B);判断两个Geometry A和B是否在空间结构上相等即包含相同的点集但不考虑点集顺序是否一致bytea _ST_AsMVT(table_name.*, text name, integer extent, text geom_name, text feature_id_name);将一组行聚合返回一个使用Google Protocol Buffers编码格式二进制的mapbox矢量瓦片blob类型第一个参数是必须的后面的参数可选geometry _ST_MakeValid(geometry input);尝试将输入的无效的多边形修复为一个有效的多边形geometry ST_AsMVTGeom(geometry geom, // 输入空间对象 box2d bounds, // MVT空间坐标范围 integer extent4096, // 矢量切片的范围4096个单位 integer buffer256, // 缓存区大小 boolean clip_geomtrue);将一个输入的空间对象转换到bounds定义的map vector tile空间坐标系下超出bounds的部分且位于buffer指定的缓冲区以内的部分根据clip_geom参数的配置来决定是否裁剪map vector tile空间坐标系起始点位于左上角屏幕参考系转换后输出的满足mapbox vector tile 规范的有效geometry常作为st_asmvt的入参。
支持3维gis数据存储gis表达式适配3维数据分类如下
输入WKT/WKB格式数据构造空间对象的表达式支持输入3维数据构造空间对象将存储的空间数据转换成指定格式WKT/WKB/EWKB/EWKT输出的表达式支持查询3维空间数据不支持输入3D数据
类型表达式功能适配内容构造空间对象st_geomfromtext输入wkt格式创建几何值如果是3d geo跳过检查经纬度范围的逻辑封装geo_to_wkb增加3d-wkb格式转成swkbst_geometryfromtext输入wkt格式创建几何值_st_geomfromewkt输入ewkt格式创建几何值同上_st_geogfromtext输入wkt格式创建几何值默认geography坐标系同上_st_geographyfromtext输入wkt格式创建几何值默认geography坐标系st_geomfromwkb输入wkb格式创建几何值增加校验3d wkb格式是否合法st_geometryfromwkb输入wkb格式创建几何值_st_geomfromewkb输入ewkb格式创建几何值3d-ewkb和iso 3d-wkb的type表示的方式不一样需要做转换同样需要校验输入的wkb是否合法格式转换st_astext/st_aswkt输出wkt格式读取swkb转换成geo对象然后转换成3d-wkt格式。st_aswkb/st_asbinary输出wkb格式swkb转wkb_st_asewkb输出ewkb格式ewkb转wkb_st_asewkt输出ewkt格式wkt前面需要加上sridxxx空间计算表达式st_area计算空间对象面积将3维空间数据转为2维处理st_intersects计算两个空间对象是否相交st_transform将一个空间对象的坐标转换到指定的空间坐标系下_st_covers计算空间对象A是否包含空间对象Bst_buffer计算一个距离小于或等于给定距离的所有点的POLYGON/MULTIPOLYGONst_distance计算两个空间对象间距离_st_dwithin计算空间对象A是否在一定距离内被包含于空间对象Bst_distance_sphere返回球体上两个点和/或多点之间的最小球面距离st_contains计算空间对象A是否包含空间对象Bst_within计算空间对象A是否包含于空间对象BST_Crosses判断两个Geometry g1和g2在空间上是否有交叉即几何体内部有部分点相同但不完全相同ST_Overlaps判断两个几何体是否空间上相交且具有相同的维度但并不完全互相包含。_ST_Touches判断两个Geometry A和B边界是否至少有一个共同点且内部不相交。注意若两个Geometry均为Point类型直接返回false因为Point没有边界ST_Equals判断两个Geometry A和B是否在空间结构上相等即包含相同的点集但不考虑点集顺序是否一致
以下表达式支持空间索引
表达式名称功能介绍boolean ST_Crosses(geometry g1, geometry g2)判断两个Geometry g1和g2在空间上是否有交叉即几何体内部有部分点相同但不完全相同boolean ST_Overlaps(geometry g1, geometry g2)判断两个几何体是否空间上相交且具有相同的维度但并不完全互相包含。boolean _ST_Touches(geometry A, geometry B)判断两个Geometry A和B边界是否至少有一个共同点且内部不相交。注意若两个Geometry均为Point类型直接返回false因为Point没有边界boolean ST_Equals(geometry A, geometry B);判断两个Geometry A和B是否在空间结构上相等即包含相同的点集但不考虑点集顺序是否一致
2. 使用操作
2.1. ST_Crosses(g1,g2)
支持空间索引判断两个Geometry g1和g2在空间上是否有交叉即几何体内部有部分点相同但不完全相同。
OceanBase(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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 |
| Clarkes chain | 20.1166195164 |
| Clarkes foot | 0.3047972654 |
| Clarkes link | 0.201166195164 |
| Clarkes 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(rootoceanbase)SET ls ST_GeomFromText(LineString(1 1,2 2,3 3), 4326);
Query OK, 0 rows affected (0.001 sec)OceanBase(rootoceanbase)SELECT ST_Length(ls, foot);
------------------------
| ST_Length(ls, foot) |
------------------------
| 1029205.9131247795 |
------------------------
1 row in set (0.001 sec)OceanBase(rootoceanbase)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(rootoceanbase)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 sridunknown);-- 构造矩形
OceanBase(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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是否为集合类型包括
GEOMETRYCOLLECTIONMULTI{POINT,POLYGON,LINESTRING,CURVE,SURFACE}COMPOUNDCURVEOB暂不支持报错处理
OceanBase(rootoceanbase) 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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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聚合的一组行表名.*namelayer的命名默认为defaultextent瓦片的范围默认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(rootoceanbase)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(rootoceanbase)SET g1 ST_GeomFromText(MULTIPOINT(5 0,15 10,15 25));
Query OK, 0 rows affected (0.001 sec)OceanBase(rootoceanbase)SET g2 ST_GeomFromText(MULTIPOINT(1 1,15 10,15 25));
Query OK, 0 rows affected (0.001 sec)OceanBase(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)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(rootoceanbase)create table geo_3d(g geometry);
Query OK, 0 rows affected (0.302 sec)# 写入3维的点
OceanBase(rootoceanbase)insert into geo_3d values(ST_GeomFromText(POINT Z (0 0 0)));
Query OK, 1 row affected (0.025 sec)#查询3维数据
OceanBase(rootoceanbase)select st_aswkt(g) from geo_3d;
-----------------
| st_aswkt(g) |
-----------------
| POINT Z (0 0 0) |
-----------------
1 row in set (0.007 sec)# 对3维空间对象做空间计算时会先转换为2维;
# 如下例所示point0 0 0和point(0 0 1)在3维空间下是不相交的转换到2维就是相交的
OceanBase(rootoceanbase)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(rootoceanbase)select st_x(g,1) from geo_3d;
ERROR 3037 (22023): Invalid GIS data provided to function st_x.# 用子类型(point/linestring等)定义gis字段不支持插入3维数据
OceanBase(rootoceanbase)create table t(geo point);
Query OK, 0 rows affected (0.511 sec)
# 可以写入2维数据
OceanBase(rootoceanbase)insert into t values(ST_GeomFromText(POINT(0 0)));
Query OK, 1 row affected (0.004 sec)
# 无法写入3维数据
OceanBase(rootoceanbase)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仍然有一定差距将在后续的版本迭代中快速补齐相关功能。
