Oracle 關於WKT構造SDO_GEOMETRY的問題。

由於系統前端使用OpenLayers架構，後台資料庫使用oracle spatial。大家知道Oracle spatial的SDO_GEOMETRY十分複雜，如果使用期java api ，那就坑爹了，要處理相當多的邏輯和資料類型。今天我有這麼一個需求，從前進行多邊形查詢，我想到了一個好辦法，由openlayers構造一個wkt空間串，然後調用SDO_UTIL.FROM_WKTGEOMETRY函數將wkt轉為SDO_GEOMETRY,然後再調用SDO_ANYINTERACT進行空間查詢，這種方法貌似木有問題，sql如下：select *
  from TBSVRC_RESPUBLISHITEMS t
 where SDO_ANYINTERACT(F_SPATIALEXTENT,SDO_UTIL.from_wktgeometry('POLYGON((80.83422302246095
                                                          20.518481140136714,
                                                          120.4135076904297
                                                          20.518481140136714,
                                                          120.4135076904297
                                                          50.314989929199214,
                                                          80.83422302246095
                                                          50.314989929199214,
                                                          80.83422302246095
                                                          20.518481140136714))') )='TRUE'

將sql在資料庫中運行，結果報錯：傳入的sdo_geometry和資料庫表sdo_geometry的srid不同，不能進行空間查詢。忽然想起WKT可以表達空間資料資訊，但是木有SRID資訊。一下傻眼了，那怎麼辦呢？看看SDO_UTIL包的函數，也都木有將SDO_GEOMETRY設定SRID的函數。於是在網上搜尋半天，也無解，搜搜oracle spatial官方文檔，也是半天無解。我於是想還是好好分析SDO_GEOMETRY吧。嘿，忽然發現桃花源，SDO_GEOMETRY是一個類型，本身也是一個函數。再仔細看看還有構造參數，喔，問題解決了，sql如下：

select *
  from TBSVRC_RESPUBLISHITEMS t
 where SDO_ANYINTERACT(F_SPATIALEXTENT,sdo_geometry('POLYGON((80.83422302246095
                                                          20.518481140136714,
                                                          120.4135076904297
                                                          20.518481140136714,
                                                          120.4135076904297
                                                          50.314989929199214,
                                                          80.83422302246095
                                                          50.314989929199214,
                                                          80.83422302246095
                                                          20.518481140136714))',8307))='TRUE'

oracle spatial 愛死你了！