关于数据库: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:FALSEg1 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 failureORA-06512: 在 "MDSYS.SDO_UTIL", line 857ORA-06512: 在 "MDSYS.SDO_UTIL", line 896ORA-06512: 在 "MDSYS.SDO_GEOMETRY", line 36

目前暂无方法稳固的查问出SDO_GEOMETRY的WKT超长文本，Oracle难搞哦。

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

【完】