大家好，今天来为大家解答Oracle数据库地理坐标查询优化及错误原因解析——WKT文本处理策略这个问题的一些问题点，包括也一样很多人还不知道，因此呢，今天就来为大家分析分析，现在让我们一起来看看吧！如果解决了您的问题，还望您关注下本站哦，谢谢~
WKT（Well Known Text）是一种用于几何空间对象的通用文本表示方法。与同样通用的GeoJSON 文本相比，WKT 更简单、更容易理解、更容易生成。重要的是，几乎所有支持地理几何空间的数据库都支持WKT。文本。
在MySQL 和PostgreSQL 中，ST_GeomFromText("wkt",SRID) 可用于构造几何对象。在SQL Server 中，geometry:STGeomFromText("wkt",SRID) 可用于构造几何对象。在Oracle中，SDO_GEOMETRY("wkt",SRID)可用于构造几何对象。 010- 59000 SQL 语句中单个手写文本字符串的长度在Oracle 中也受到限制。测试发现，在SQLPlus中最长的手写单个文本字符串长度约为3000，在SQL Developer中约为32767、但此处有坑。直接报语法解析错误。这在其他数据库中是闻所未闻的。一个复杂的地理坐标边界图形的WKT可能有数百KB甚至MB的超长文本，这对于在Oracle中使用SQL语句来说是一个很大的挑战。
用于本地测试的数据库版本：Oracle Database 21c Express Edition Release 21.0.0.0.0
在线测试使用的Live SQL版本：Live SQL 23.3.1，运行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-StatsGovgithub 可以替换为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匹配
编写查询语句。以下是绘制同一图形的两种方法：顺时针和逆时针：
宣布
-- 定义坐标点
p SDO_GEOMETRY:=SDO_GEOMETRY("点(55 21)",4326);
-- 定义边界几何形状（顺时针）。如果你的WKT是这样的话，放到Oracle里就惨了。
g1 SDO_GEOMETRY:=SDO_GEOMETRY("多边形((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("多边形((53 20, 53 22, 56 22, 56 20, 57 20, 57 23, 52 23, 52 20, 53 20))",4326);
v1 块； v2 块；
开始
-- 计算坐标点与两个边界的位置关系
从Dual 选择SDO_ANYINTERACT(g1,p),SDO_ANYINTERACT(g2,p) 到v1,v2；
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:真g2:假
g1 IsValid:13367 [元素1] [环1]
g2 IsValid:TRUE 可以看到顺时针边界WKTg1实际上包含这个坐标点：g1:TRUE；并检查g1的有效性，显示的图形无效。
尝试将坐标点更改为POINT (55 10)。这个坐标完全在凹字符之外，查询结果是正确的。因此，可以得出边界g1和坐标p的位置计算仅匹配外接矩形（MBR）。如果没有经过精确的计算，p坐标点直接返回g1的MBR内部的匹配，导致结果不正确。
或者把SRID去掉（delete,4326，即SRID=NULL），查询结果也是正确的，这就很奇怪了，代码运行正确就奇怪了。非常混乱。
相同的边界图形WKT和坐标点，在MySQL、PostgreSQL、SQLServer中测试结果都是正确的，不存在无效图形的问题。这只是Oracle上的一个问题。
2.3 错误原因
这个错误结果已经困扰我一个多星期了。 SDO_ANYINTERACT和SDO_RELATE的文档中没有关于图形坐标方向的描述。我以为是提供的SRID有问题（只是因为SRID=NULL时查询结果是正确的）。幸运的是，我后来在SDO_ELEM_INFO 以及加载和验证空间数据文档的建议中得到了确切的答案：
仅当您不知道简单多边形是外部还是内部时，才应指定3，然后应使用SDO_MIGRATE.TO_CURRENT 过程将表或图层升级到当前格式，如SDO_MIGRATE 包（升级）中所述。
…………
1005: 外部多边形环（必须按逆时针顺序指定）
2005: 内部多边形环（必须按顺时针顺序指定）
对于方向错误或ETYPE 或GTYPE 值无效的任何几何图形，请对这些无效几何图形使用SDO_MIGRATE.TO_CURRENT 来修复它们。
这意味着Oracle中边界图形的外环必须是逆时针方向。但查询SDO_ANYINTERACT时，对于顺时针错误图形，Oracle既不报告错误，也不返回错误结果。这太离谱了。文档中没有解释其原因。注意：其他一些数据库不需要环方向顺序。
2.4 解决办法
根据文档提示，使用SDO_MIGRATE.TO_CURRENT方法处理SDO_GEOMETRY构造的边界几何坐标的顺序，以满足方向顺序要求。
-- 在原来的基础上，增加一层TO_CURRENT()调用，并提供一个dim数组。
g1 SDO_GEOMETRY:=SDO_MIGRATE.TO_CURRENT(SDO_GEOMETRY("多边形((53 20, 52 20, 52 23, 57 23, 57 20, 56 20, 56 22, 53 22, 53 20))",4326),SDO_DIM_ARRAY （SDO_DIM_元素（ "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")
从Dual 中选择“在此输入50,000 个字符.”； SQL Developer中直接报错（Live SQL支持更差）：
ORA-01704: 字符串文字太长
01704.00000-“字符串文字太长”
*Cause: 字符串文字长度超过4000 个字符。
*Action: 使用最多4000 个字符的字符串文字。
较长的值只能使用绑定变量输入。大家好，这是我第一次看到SQL 语句中字符串长度的限制。我查看了文档，发现PL/SQL程序限制，字符串文字的大小（字节）: 32767，我从未在其他数据库中见过它。不存在这样的问题。较长的值只能使用绑定变量输入。建议的解决方案直观地针对编程环境中的PreparedStatement。我已经编写了SQL 并将锤子变量绑定到SQL 文件。
正如一开始提到的，复杂地理坐标边界图形的WKT可能有数百KB甚至数MB的超长文本。其他数据库可以简单地插入制表符值（"100kb wkt"）来完成插入。现在Oracle由于要限制写入字符串的长度，所以需要编写额外的非常规代码。
3.2 使用CLOB无限拼接得到超长文本
Oracle的CLOB类型可以容纳4GB的文本，因此可以将超长的WKT文本切割成小块文本拼接到clob变量中，避免单个字符串过长，可以解决这个问题。
声明txt clob:="";开始
txt:=txt||"POLYGON(( . 2KB 字符串被WKT 分割";
txt:=txt||". 2KB 字符串被WKT 分割";
txt:=txt||". 2KB 字符串被WKT 分割";
--.剩余2KB字符串
dbms_output.put_line("txt:"||dbms_lob.getlength(txt));
-- 将超长WKT文本转换为几何空间对象，修正坐标方向，插入数据库
- 插入tablename（polygon）值（sdo_migrate.to_current（sdo_geometry（txt，4326），sdo_dim_array（sdo_dim_element（"x"，-180，180，180，0.0000001），SDO_DIM_ELMENT（SDO_DIM_ELMENT（SDO_DIM_ELEMENT）（ ) )) );
结尾；
/不同Oracle终端（SQLPlus、SQL Developer）中SQL可以写入的单个字符串的长度比较混乱，范围从3k-32k，所以长字符串采用2KB长度分段来支持，以达到最大的兼容性； CLOB直接与||拼接就这样，不需要使用to_clob、dbms_lob.append。
注意：在Live SQL中测试时，如果SQL语句总长度超过32KB（不是单个字符串），查询会报错或者根本不返回结果，这与SQLPlus和SQL的性能完全不同开发商；可以理解的是，当前的Live Tests携带任何很长的文本在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数据库的空间索引
空间索引文档：空间数据索引和查询，涉及user_sdo_geom_metadata表。这张表非常重要，虽然它只存储了经纬度范围和SRID！
如果user_sdo_geom_metadata中配置的SRID与导入表的数据的SRID不一致，则空间查询时会直接报错。当数据的SRID为NULL时，元数据中的SRID也必须为NULL，其他SRID必须是现有的SRID值（通过select * from MDSYS.CS_SRS order by srid查询所有SRID）。
然后正常创建索引：
-- 首先将配置数据插入user_sdo_geom_metadata
插入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 或特定值
--创建空间索引
创建索引indexName ON tableName(polygon) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;测试发现修改表名会自动修改元数据中的表名，删除表不会删除元数据中表的相关配置。
当表中有大量空间数据时，添加空间索引对查询速度的优化效果是非常细致的。上述导入的省市三级边界数据共3600余条。如果没有索引，查询一个坐标需要7秒。建立索引后，查询一次仅需0.06秒，查询性能提升100倍。
4.2 如何查询返回SDO_GEOMETRY的WKT超长文本？
通过CLOB拼接SQL语句将超长WKT文本插入数据库后，仅通过SQL查询语句从边界几何对象中读取WKT文本似乎又是一个问题。
从表名中选择SDO_GEOMETRY.GET_WKT(多边形)； GET_WKT方法经常报错（偶尔能正常找到）：
ORA-13199: wk 缓冲区合并失败
ORA-06512: 在"MDSYS.SDO_UTIL"，第857 行
ORA-06512: 在"MDSYS.SDO_UTIL"，第896 行
ORA-06512: 在"MDSYS.SDO_GEOMETRY"第36行中，目前没有办法稳定查询SDO_GEOMETRY的WKT长文本，这对Oracle来说是困难的。
【Oracle数据库地理坐标查询优化及错误原因解析——WKT文本处理策略】相关文章：
2.米颠拜石
3.王羲之临池学书
8.郑板桥轶事十则
用户评论
终于有人研究了 Oracle 数据库地理空间数据查询的效率！
有7位网友表示赞同！
这个标题听起来太专业了，我是小白，能听得懂吗？
有5位网友表示赞同！
经纬度坐标查询优化是热门话题啊，想知道有没有什么实用的技巧。
有15位网友表示赞同！
超长文本字符串真的让人头疼，Oracle WKT 处理有捷径吗？
有11位网友表示赞同！
结果错误分析也很重要啊，遇到问题才能更好地解决
有12位网友表示赞同！
期待这篇文章能教会我如何提高地理空间数据查询的效率!
有18位网友表示赞同！
我一直遇到SQL中超长文本字符串的问题，希望这篇论文能给我答案。
有16位网友表示赞同！
Oracle 数据库在处理经纬度坐标上有没有什么优势？
有16位网友表示赞同！
WKT 字符串处理是数据库开发中最难的部分之一我觉得！
有19位网友表示赞同！
想要了解 Oracle 地理空间数据功能的进阶技巧，这篇文章看起来很有用啊!
有16位网友表示赞同！
学习 Oracle 的 GEOGRAPHIC 文档真的不容易，感谢有这份研究成果可以参考。
有12位网友表示赞同！
结果错误分析能让我更好地理解数据库报错的原因
有9位网友表示赞同！
这个标题是不是针对一些专业开发者写的？ layman 能不能看懂？
有20位网友表示赞同！
oracle 的地理空间查询功能越来越强大了吧！
有18位网友表示赞同！
经纬度坐标、超长字符串、结果错误分析，这篇文章涵盖了比较多方面的知识点
有10位网友表示赞同！
数据库开发需要不断学习新技术，这篇文章应该很有帮助!
有11位网友表示赞同！
期待深入了解 Oracle 数据库优化方法，提升工作效率。
有6位网友表示赞同！
看标题就知道这篇文章很专业了！不过我还是想了解一下它的具体内容。
有15位网友表示赞同！
Oracle 数据库的GEOGRAPHIC datatype 让我很感兴趣，希望能学到更多
有20位网友表示赞同！
解决数据库问题有时候真的很费劲，希望这篇文章能给我一些启发!
有8位网友表示赞同！