如何載入KML至SDO_Geometry

在Oracle Spatial Developer Guide上，可以查到 SDO_UTIL.FROM_KMLGEOMETRY. 在說明上可以看到這個函數是將KML轉換成Oracle DSO_Geometry的資料型態。如果再細看一下範例，SDO_UTIL.FROM_KMLGEOMETRY 只支援 OGC KML 2.1文件，並不包括 Google extension namespace.
所以，像是拿到這樣子的KML，是沒有辦法簡單地運用SDO_UTIL.FROM_KMLGEOMETRY直接轉換成SDO_Geometry。


<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://earth.google.com/kml/2.0">
<Document>
  <name>freeway</name>
  <description />
  <Folder>
  <Placemark>
    <name>nfb0001</name>
    <description />
    <styleUrl>#style24</styleUrl>
    <LineString>
      <tessellate>1</tessellate>
      <coordinates>
        121.735260,25.122271,0.000000
        121.734543,25.121410,0.000000
        121.733559,25.120380,0.000000
        121.731918,25.118900,0.000000
        121.730164,25.117439,0.000000
        121.729736,25.117041,0.000000
        121.728851,25.115971,0.000000
        121.728119,25.114820,0.000000
      </coordinates>
    </LineString>
  </Placemark>
  <Placemark>
    <name>nfb0003</name>
    <description />
    <styleUrl>#style90</styleUrl>
    <LineString>
      <tessellate>1</tessellate>
      <coordinates>
        121.728111,25.114799,0.000000
        121.727661,25.113951,0.000000
        121.724380,25.106050,0.000000
        121.723717,25.105150,0.000000
        121.723061,25.104570,0.000000
        121.722137,25.104031,0.000000
        121.720886,25.103621,0.000000
      </coordinates>
    </LineString>
  </Placemark>

  </Folder>
  </Document>
</kml>



所以我的想法是：先將"KML"載入Oracle XMLTable，再使用XPath截取所要的欄位，放入對應的表格與欄位，而相關的空間資訊再用SDO_UTIL.FROM_KMLGEOMETRY轉成SDO_Geometry

觀察上面範例，我會建立一表格名稱"FREEWAY"其中有欄位"NAME", "DESCRIPTION", "STYLEURL"以及"GEOM"。GEOM的資料型態為SDO_GEOMETRY。

CREATE TABLE FREEWAY
(
NAME VARCHAR2(7) NOT NULL
, STYLEURL VARCHAR2(8)
, DESCRIPTION VARCHAR2(4000)
, GEOM MDSYS.SDO_GEOMETRY
);

步驟：
將KML載入XML Table
可參考Load XML Documents

CREATE TABLE KML_TMP OF XMLType
XMLTYPE STORE AS BINARY XML;

CREATE DIRECTORY xmldir AS path_to_folder_containing_XML_file;（略）



使用SYS授予使用者讀取XMLDIR的權限

GRANT READ, WRITE ON DIRECTORY TO username;

登入該使用者，載入KML 至KML_TMP表格

INSERT INTO KML_TMP VALUES (XMLType(bfilename('XMLDIR', 'freeway.kml'), nls_charset_id('AL32UTF8')));

轉換對應至表格相關的欄位與SDO_GEOMETRY

INSERT INTO freeway(name, description, styleurl, geom)
SELECT kml.name, kml.description, kml.styleurl, SDO_UTIL.FROM_KMLGEOMETRY(TO_CLOB(kml.geom)) as geom
FROM kml_tmp,
XMLTable('declare default element namespace "http://earth.google.com/kml/2.0";  (: :)
       /kml/Document/Folder/Placemark'
       PASSING OBJECT_VALUE
  COLUMNS
  name VARCHAR2(7) PATH 'declare default element namespace "http://earth.google.com/kml/2.0";  (: :) name',
  description VARCHAR2(4000) PATH 'declare default element namespace "http://earth.google.com/kml/2.0";  (: :) description',
  styleurl VARCHAR2(8) PATH 'declare default element namespace "http://earth.google.com/kml/2.0";  (: :) styleUrl',
  geom XMLTYPE PATH 'declare default element namespace "http://earth.google.com/kml/2.0";  (: :) LineString'
  ) kml
;

從KML本身並不描述座標系統，所以轉入SDO_GEOMETRY之後，座標系的值為NULL。所以需要將轉入SDO_GEOMETRY的座標(SDO_SRID)進行設定(異動)；在Oracle DB WGS 84 Geography 3D 的 SDO_SRID 的代號是4327。(請注意所提供的KML為3維空間座標)

UPDATE freeway f
set f.geom.SDO_SRID=4327;
COMMIT;

配合使用Google Map，我們需要是2維的平面，建議設定的SDO_SRID的值為4055

我使用SDO_CS.MAKE_2D來進行轉換
先增加一2維欄位"GEOM2D"

ALTER TABLE FREEWAY
ADD (GEOM2D MDSYS.SDO_GEOMETRY );

UPDATE FREEWAY
SET GEOM2D = SDO_CS.MAKE_2D(GEOM, 4055);
COMMIT;

更新Metadata以及建立空間索引(略)

驗証是否為合法的幾何圖形，發現13356的錯誤 -- points in a geometry are redundant

修正方式:

UPDATE FREEWAY
SET GEOM2D = SDO_UTIL.REMOVE_DUPLICATE_VERTICES(GEOM2D, 0.0000005);
COMMIT;