一、Oracle幾何空間數(shù)據(jù)對象和其他數(shù)據(jù)庫的差異

和MySQL收毫、PostgreSQL等數(shù)據(jù)庫相比攻走，Oracle數(shù)據(jù)庫的地理幾何空間更難學習和上手使用殷勘，我總結(jié)的一點原因是Oracle Spatial文檔中闡述了太多的和其他數(shù)據(jù)庫不通用的幾何對象構(gòu)建、查詢方法陋气，需要單獨深入學習研究Oracle Spatial文檔劳吠，知識碎片化嚴重，實現(xiàn)一個相同功能可能有N個功能相近的函數(shù)可以給你調(diào)用（茴香豆的茴字的N種寫法）巩趁，學習成本太高痒玩，還稍不注意就容易踩坑。

WKT（Well Known Text）是通用的幾何空間對象的文本表示方法议慰，相比于同樣通用的GeoJSON文本蠢古，WKT更簡單，易于理解也容易生成别凹，重要的是幾乎所有支持地理幾何空間的數(shù)據(jù)庫都支持WKT文本草讶。

• MySQL、PostgreSQL中可用ST_GeomFromText('wkt',SRID)來構(gòu)造幾何對象
• SQLServer中可用geometry::STGeomFromText('wkt',SRID)來構(gòu)造幾何對象
• Oracle中可用SDO_GEOMETRY('wkt',SRID)來構(gòu)造幾何對象炉菲，但此處有坑

SQL語句中手寫的單個文本字符串長度堕战，在Oracle中也有限制，測試發(fā)現(xiàn)SQLPlus中最長的手寫單個文本長度3000左右拍霜，SQL Developer中32767左右嘱丢，超過了長度SQL就沒法執(zhí)行，直接報語法解析錯誤祠饺，這在其他數(shù)據(jù)庫中是聞所未聞的越驻。復(fù)雜的一個地理坐標邊界圖形的WKT可能有上百KB甚至上MB的超長文本，這在Oracle中單純的使用SQL語句將會是一個很大的挑戰(zhàn)道偷。

本地測試所使用的數(shù)據(jù)庫版本：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
開源省市區(qū)坐標邊界數(shù)據(jù)（可導(dǎo)入Oracle）：https://github.com/xiangyuecn/AreaCity-JsSpider-StatsGov github可換成gitee

二缀旁、Oracle查詢一個經(jīng)緯度坐標是否在邊界內(nèi)部

2.1 查詢條件

【此處放圖】

• 邊界幾何圖形：POLYGON ((53 20, 52 20, 52 23, 57 23, 57 20, 56 20, 56 22, 53 22, 53 20))，類似一個倒過來的凹字
• 坐標點：POINT (55 21)勺鸦，這個點位于凹進去的里面并巍，但不在凹字內(nèi)部

查詢這個坐標點是否在圖形內(nèi)部，結(jié)果應(yīng)當是不在內(nèi)部祝旷。

2.2 查詢結(jié)果錯誤履澳，似乎是僅做了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個邊界的位置關(guān)系
    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中執(zhí)行后輸出結(jié)果（和Live SQL中測試結(jié)果相同）：

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

可以看到順時針表示的邊界WKT g1 居然包含了這個坐標點：g1:TRUE 吻谋；并且檢查g1的有效性，顯示圖形是無效的现横。

嘗試將坐標點改成POINT (55 10)漓拾，此坐標完全在凹字外面阁最，查詢結(jié)果就是正確的，因此可以得出：邊界g1和坐標p的位置計算只進行了外接矩形（MBR）的匹配骇两，沒有進行精確的計算速种，p坐標點在g1的MBR內(nèi)部就直接返回了匹配，導(dǎo)致結(jié)果錯誤低千。

或者去掉SRID（刪掉,4326配阵，即SRID=NULL），查詢結(jié)果也是正確的示血，這個就很奇葩了棋傍，代碼正在以奇怪的方式正確運行。难审。瘫拣。很有迷惑性。

相同的邊界圖形WKT 和 坐標點告喊，在MySQL麸拄、PostgreSQL、SQLServer中測試均結(jié)果正確黔姜，也不存在圖形無效的問題拢切，僅僅是Oracle上有問題。

2.3 錯誤原因

這個錯誤結(jié)果困擾了一個多禮拜地淀，SDO_ANYINTERACT和SDO_RELATE的文檔中沒有找到關(guān)于圖形坐標方向的說明失球，一度以為是提供了SRID的問題（只因SRID=NULL時查詢結(jié)果又是正確的），還好后面在 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里面邊界圖形的外環(huán)必須逆時針的帮毁。但SDO_ANYINTERACT查詢的時候实苞，對于順時針的錯誤圖形，Oracle既不報錯還返回錯誤結(jié)果烈疚，這就很離譜黔牵，這點的原因還沒翻到文檔說明。注：其他幾個數(shù)據(jù)庫并不要求環(huán)的方向順序爷肝。

2.4 解決辦法

根據(jù)文檔提示猾浦，使用 SDO_MIGRATE.TO_CURRENT 方法對SDO_GEOMETRY構(gòu)造出來的邊界幾何圖形坐標的順序進行處理，達到方向順序要求

    -- 在原來的基礎(chǔ)上灯抛，套一層TO_CURRENT()調(diào)用金赦，提供一個dim數(shù)組即可
    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中執(zhí)行含超長文本的SQL報錯

修改并運行以下代碼：

-- 字符串里面放50k的字符串纵竖，可瀏覽器控制臺里面執(zhí)行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已脓，其他數(shù)據(jù)庫從來沒有這種問題珊楼。Longer values may only be entered using bind variables.提示的這個解決辦法目測是針對編程環(huán)境下的PreparedStatement，我都寫SQL了度液，SQL文件里面綁個錘子的變量厕宗。

開頭也說過了復(fù)雜地理坐標邊界圖形的WKT可能有上百KB甚至幾MB的超長文本，其他數(shù)據(jù)庫直接簡單的通過類似insert tab values('100kb wkt')即可完成插入恨诱，現(xiàn)在Oracle由于這個書寫字符串長度的限制媳瞪，勢必要額外編寫非常規(guī)的代碼。

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文本轉(zhuǎn)成幾何空間對象，修正坐標方向剂碴，插入數(shù)據(jù)庫
    -- 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（非單個字符串）亚兄，查詢將會報錯，或不壓根返回結(jié)果采驻，和SQLPlus审胚、SQL Developer表現(xiàn)的完全不一樣；可以理解為目前Live SQL中無法進行攜帶任何超長文本的測試礼旅。

3.3 導(dǎo)入開源的省市區(qū)坐標邊界WKT數(shù)據(jù)

從開源庫：AreaCity-JsSpider-StatsGov 里面的文檔中獲取到最新的省市區(qū)三級或鄉(xiāng)鎮(zhèn)級數(shù)據(jù)膳叨，有shp、geojson痘系、sql格式支持懒鉴，只需選擇導(dǎo)出Oracle格式的SQL文件即可，在SQLPlus碎浇、或SQL Developer里面執(zhí)行這個SQL文件即可完成導(dǎo)入：

@"D:/xxx/xxx.sql"; --改成實際的文件路徑

SQL文件中的超長WKT文本按照上面2KB一段的長度進行了切分临谱，使用CLOB拼接。此SQL文件結(jié)尾會自動創(chuàng)建索引奴璃，或者根據(jù)下文手動創(chuàng)建索引悉默，建了索引后 SDO_ANYINTERACT 查詢速度快100倍。

四苟穆、Oracle數(shù)據(jù)庫的空間索引和查詢返回WKT超長文本

4.1 給查詢加速：Oracle數(shù)據(jù)庫的空間索引

空間索引文檔：Indexing and Querying Spatial Data抄课，涉及user_sdo_geom_metadata這個表，這個表很重要雳旅，雖然里面只存了經(jīng)緯度范圍和SRID跟磨！

如果user_sdo_geom_metadata里面配置的SRID和導(dǎo)入到表里的數(shù)據(jù)SRID不一致，將會導(dǎo)致空間查詢時直接報錯攒盈。數(shù)據(jù)的SRID=NULL時抵拘，metadata里面的SRID也必須是NULL，其他SRID必須是一個存在的SRID值（通過 select * from MDSYS.CS_SRS order by srid 查詢所有SRID）型豁。

然后創(chuàng)建正常創(chuàng)建索引即可：

-- 先往user_sdo_geom_metadata里面插入配置數(shù)據(jù)
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 或 一個具體值

-- 創(chuàng)建空間索引
create index indexName ON tableName(polygon) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

測試發(fā)現(xiàn)：修改表名會自動修改metadata里面的表名僵蛛，刪除表不會刪除metadata里面的此表的相關(guān)配置。

表中空間數(shù)據(jù)量比較多的時候迎变，加空間索引對查詢速度的優(yōu)化效果非常明細充尉，上面導(dǎo)入的省市區(qū)三級的邊界數(shù)據(jù)3600多條，不加索引時要7秒查詢一個坐標衣形，加了索引后只需0.06秒查詢一次驼侠，查詢性能提高了100倍。

4.2 如何查詢返回SDO_GEOMETRY的WKT超長文本谆吴？

超長的WKT文本通過CLOB拼接的SQL語句插入數(shù)據(jù)庫后倒源，僅通過SQL查詢語句從邊界幾何對象中讀取出WKT文本似乎又是一個難題。

select SDO_GEOMETRY.GET_WKT(polygon) from tableName;

GET_WKT方法經(jīng)常動不動就報錯（偶爾又能正常查出來）：

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

目前暫無辦法穩(wěn)定的查詢出SDO_GEOMETRY的WKT超長文本纪铺，Oracle難搞哦相速。

注：以上問題（疑難雜癥）MySQL、PostgreSQL鲜锚、SQLServer 均無突诬，這樣說，你應(yīng)該知道怎么選了吧=芜繁。=

【完】