RainingNight

Oracle数据库经纬度坐标查询优化与结果错误原因分析、SQL中WKT超长文本字符串处理

目录

一、Oracle几何空间数据对象和其他数据库的差异

和MySQL、PostgreSQL等数据库相比，Oracle数据库的地理几何空间更难学习和上手使用，我总结的一点原因是Oracle Spatial文档中阐述了太多的和其他数据库不通用的几何对象构建、查询方法，需要单独深入学习研究Oracle Spatial文档，知识碎片化严重，实现一个相同功能可能有N个功能相近的函数可以给你调用（茴香豆的茴字的N种写法），学习成本太高，还稍不注意就容易踩坑。

WKT（Well Known Text）是通用的几何空间对象的文本表示方法，相比于同样通用的GeoJSON文本，WKT更简单，易于理解也容易生成，重要的是几乎所有支持地理几何空间的数据库都支持WKT文本。

  • MySQL、PostgreSQL中可用ST_GeomFromText('wkt',SRID)来构造几何对象
  • SQLServer中可用geometry::STGeomFromText('wkt',SRID)来构造几何对象
  • Oracle中可用SDO_GEOMETRY('wkt',SRID)来构造几何对象，但此处有坑

SQL语句中手写的单个文本字符串长度，在Oracle中也有限制，测试发现SQLPlus中最长的手写单个文本长度3000左右，SQL Developer中32767左右，超过了长度SQL就没法执行，直接报语法解析错误，这在其他数据库中是闻所未闻的。复杂的一个地理坐标边界图形的WKT可能有上百KB甚至上MB的超长文本，这在Oracle中单纯的使用SQL语句将会是一个很大的挑战。

本地测试所使用的数据库版本：Oracle Database 21c Express Edition Release 21.0.0.0.0
在线测试所使用的Live SQL版本：Live SQL 23.3.1, running Oracle Database 19c EE Extreme Perf - 19.17.0.0.0 （也有坑）
Oracle Spatial参考文档地址：https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl/index.html
开源省市区坐标边界数据（可导入Oracle）：https://github.com/xiangyuecn/AreaCity-JsSpider-StatsGov github可换成gitee

二、Oracle查询一个经纬度坐标是否在边界内部

2.1 查询条件

  • 边界几何图形：POLYGON ((53 20, 52 20, 52 23, 57 23, 57 20, 56 20, 56 22, 53 22, 53 20))，类似一个倒过来的凹字
  • 坐标点：POINT (55 21)，这个点位于凹进去的里面，但不在凹字内部

边界几何图形和坐标点位置

查询这个坐标点是否在图形内部，结果应当是不在内部。

2.2 查询结果错误，似乎是仅做了MBR匹配

编写查询语句，这里直接给出相同一个图形的顺时针和逆时针两种写法：

declare
	-- 定义坐标点
    p SDO_GEOMETRY:=SDO_GEOMETRY('POINT (55 21)',4326);
	-- 定义边界几何图形（顺时针），如果你的WKT是这种，那放到Oracle里面就惨了
    g1 SDO_GEOMETRY:=SDO_GEOMETRY('POLYGON ((53 20, 52 20, 52 23, 57 23, 57 20, 56 20, 56 22, 53 22, 53 20))',4326);
	-- 定义边界几何图形（逆时针），符合Oracle坐标顺序要求
    g2 SDO_GEOMETRY:=SDO_GEOMETRY('POLYGON ((53 20, 53 22, 56 22, 56 20, 57 20, 57 23, 52 23, 52 20, 53 20))',4326);
    v1 clob; v2 clob;
begin
	-- 计算坐标点和2个边界的位置关系
    select SDO_ANYINTERACT(g1,p),SDO_ANYINTERACT(g2,p) into v1,v2 from dual;
    dbms_output.put_line('g1:'||v1 ||'  '|| 'g2:'||v2);
	
	-- 检查2个边界是否有效（ST_IsValid）
	dbms_output.put_line('g1 IsValid:'||SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(g1,0.0000001));
	dbms_output.put_line('g2 IsValid:'||SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(g2,0.0000001));
end;

SQL Developer中执行后输出结果（和Live SQL中测试结果相同）：

g1:TRUE  g2:FALSE
g1 IsValid:13367 [Element <1>] [Ring <1>]
g2 IsValid:TRUE

可以看到顺时针表示的边界WKT g1 居然包含了这个坐标点：g1:TRUE ；并且检查g1的有效性，显示图形是无效的。

尝试将坐标点改成POINT (55 10)，此坐标完全在凹字外面，查询结果就是正确的，因此可以得出：边界g1和坐标p的位置计算只进行了外接矩形（MBR）的匹配，没有进行精确的计算，p坐标点在g1的MBR内部就直接返回了匹配，导致结果错误。

或者去掉SRID（删掉,4326，即SRID=NULL），查询结果也是正确的，这个就很奇葩了，代码正在以奇怪的方式正确运行。。。很有迷惑性。

相同的边界图形WKT 和 坐标点，在MySQL、PostgreSQL、SQLServer中测试均结果正确，也不存在图形无效的问题，仅仅是Oracle上有问题。

2.3 错误原因

这个错误结果困扰了一个多礼拜，SDO_ANYINTERACT和SDO_RELATE的文档中没有找到关于图形坐标方向的说明，一度以为是提供了SRID的问题（只因SRID=NULL时查询结果又是正确的），还好后面在 SDO_ELEM_INFO 和 Recommendations for Loading and Validating Spatial Data文档中得到了确切的答案：

You should specify 3 only if you do not know if the simple polygon is exterior or interior, and you should then upgrade the table or layer to the current format using the SDO_MIGRATE.TO_CURRENT procedure, described in SDO_MIGRATE Package (Upgrading) .
.......
1005: exterior polygon ring (must be specified in counterclockwise order)
2005: interior polygon ring (must be specified in clockwise order)

For any geometries with the wrong orientation or an invalid ETYPE or GTYPE value, use SDO_MIGRATE.TO_CURRENT on these invalid geometries to fix them.

意思就是Oracle里面边界图形的外环必须逆时针的。但SDO_ANYINTERACT查询的时候，对于顺时针的错误图形，Oracle既不报错还返回错误结果，这就很离谱，这点的原因还没翻到文档说明。注：其他几个数据库并不要求环的方向顺序。

2.4 解决办法

根据文档提示，使用 SDO_MIGRATE.TO_CURRENT 方法对SDO_GEOMETRY构造出来的边界几何图形坐标的顺序进行处理，达到方向顺序要求

	-- 在原来的基础上，套一层TO_CURRENT()调用，提供一个dim数组即可
    g1 SDO_GEOMETRY:=SDO_MIGRATE.TO_CURRENT(SDO_GEOMETRY('POLYGON ((53 20, 52 20, 52 23, 57 23, 57 20, 56 20, 56 22, 53 22, 53 20))',4326),SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, 0.0000001),SDO_DIM_ELEMENT('Y', -90, 90, 0.0000001)));

套了一层TO_CURRENT()后，对g1的查询就对了，图形的有效性也是正确的。

三、SQL中WKT超长文本在Oracle中如何编写

3.1 Oracle中执行含超长文本的SQL报错

修改并运行以下代码：

-- 字符串里面放50k的字符串，可浏览器控制台里面执行js得到长文本: new Array(50001).join("a")
select '这里放50000个字符......' from dual;

SQL Developer 中直接报错（Live SQL支持更差）：

ORA-01704: 字符串文字太长
01704. 00000 -  "string literal too long"
*Cause:    The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.
           Longer values may only be entered using bind variables.

好家伙，第一次见限制SQL语句中字符串长度的，翻阅文档找到了 PL/SQL Program Limits，size of a string literal (bytes): 32767，其他数据库从来没有这种问题。Longer values may only be entered using bind variables.提示的这个解决办法目测是针对编程环境下的PreparedStatement，我都写SQL了，SQL文件里面绑个锤子的变量。

开头也说过了复杂地理坐标边界图形的WKT可能有上百KB甚至几MB的超长文本，其他数据库直接简单的通过类似insert tab values('100kb wkt')即可完成插入，现在Oracle由于这个书写字符串长度的限制，势必要额外编写非常规的代码。

3.2 使用CLOB无限拼接得到超长文本

Oracle的CLOB类型能放下4GB的文本，因此可以将超长的WKT文本切分成一小段一小段的文本，拼接进clob变量里面，避免单个字符串过长，即可解决这个问题。

declare txt clob:='';begin
	txt:=txt||'POLYGON(( .... WKT切分的2KB字符串';
	txt:=txt||'.... WKT切分的2KB字符串';
	txt:=txt||'.... WKT切分的2KB字符串';
	-- ...... 剩余的2KB字符串
	
	dbms_output.put_line('txt: '||dbms_lob.getlength(txt));
	
	-- 把超长WKT文本转成几何空间对象，修正坐标方向，插入数据库
	-- INSERT INTO tableName(polygon) VALUES( SDO_MIGRATE.TO_CURRENT(SDO_GEOMETRY(txt,4326),SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, 0.0000001),SDO_DIM_ELEMENT('Y', -90, 90, 0.0000001))) );
end;
/

Oracle不同终端（SQLPlus、SQL Developer）里面SQL能书写的单个字符串长度混乱，3k-32k不等，因此长字符串采取2KB长度分段拼接进行支持，获得最大兼容；CLOB直接用||拼接即可，无需使用to_clob、dbms_lob.append。

注意：Live SQL中测试时，如果SQL语句的总长度超过32KB（非单个字符串），查询将会报错，或不压根返回结果，和SQLPlus、SQL Developer表现的完全不一样；可以理解为目前Live SQL中无法进行携带任何超长文本的测试。

3.3 导入开源的省市区坐标边界WKT数据

从开源库：AreaCity-JsSpider-StatsGov 里面的文档中获取到最新的省市区三级或乡镇级数据，有shp、geojson、sql格式支持，只需选择导出Oracle格式的SQL文件即可，在SQLPlus、或SQL Developer里面执行这个SQL文件即可完成导入：

@"D:/xxx/xxx.sql"; --改成实际的文件路径

SQL文件中的超长WKT文本按照上面2KB一段的长度进行了切分，使用CLOB拼接。此SQL文件结尾会自动创建索引，或者根据下文手动创建索引，建了索引后 SDO_ANYINTERACT 查询速度快100倍。

四、Oracle数据库的空间索引和查询返回WKT超长文本

4.1 给查询加速：Oracle数据库的空间索引

空间索引文档：Indexing and Querying Spatial Data，涉及user_sdo_geom_metadata这个表，这个表很重要，虽然里面只存了经纬度范围和SRID！

如果user_sdo_geom_metadata里面配置的SRID和导入到表里的数据SRID不一致，将会导致空间查询时直接报错。数据的SRID=NULL时，metadata里面的SRID也必须是NULL，其他SRID必须是一个存在的SRID值（通过 select * from MDSYS.CS_SRS order by srid 查询所有SRID）。

然后创建正常创建索引即可：

-- 先往user_sdo_geom_metadata里面插入配置数据
insert into user_sdo_geom_metadata(TABLE_NAME,COLUMN_NAME,DIMINFO,SRID)VALUES (upper('tableName'),upper('polygon'),SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', -180, 180, 0.0000001),SDO_DIM_ELEMENT('Y', -90, 90, 0.0000001)),NULL); -- SRID=NULL 或 一个具体值

-- 创建空间索引
create index indexName ON tableName(polygon) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

测试发现：修改表名会自动修改metadata里面的表名，删除表不会删除metadata里面的此表的相关配置。

表中空间数据量比较多的时候，加空间索引对查询速度的优化效果非常明细，上面导入的省市区三级的边界数据3600多条，不加索引时要7秒查询一个坐标，加了索引后只需0.06秒查询一次，查询性能提高了100倍。

4.2 如何查询返回SDO_GEOMETRY的WKT超长文本？

超长的WKT文本通过CLOB拼接的SQL语句插入数据库后，仅通过SQL查询语句从边界几何对象中读取出WKT文本似乎又是一个难题。

select SDO_GEOMETRY.GET_WKT(polygon) from tableName;

GET_WKT方法经常动不动就报错（偶尔又能正常查出来）：

ORA-13199: wk buffer merge failure
ORA-06512: 在 "MDSYS.SDO_UTIL", line 857
ORA-06512: 在 "MDSYS.SDO_UTIL", line 896
ORA-06512: 在 "MDSYS.SDO_GEOMETRY", line 36

目前暂无办法稳定的查询出SDO_GEOMETRY的WKT超长文本，Oracle难搞哦。

注：以上问题（疑难杂症）MySQL、PostgreSQL、SQLServer 均无，这样说，你应该知道怎么选了吧=。=

【完】

posted @ 2023-08-21 09:42  xiangyuecn  阅读(458)  评论(0)  编辑  收藏  举报