乐趣区

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

jiezi

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

【完】

退出移动版