Oracle数据库经纬度坐标查询优化与结果错误原因分析、SQL中

2023-08-20 本文已影响0人高坚果兄弟

一、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 均无，这样说，你应该知道怎么选了吧=。=

【完】