利用空間資料庫求兩點距離

前兩天在實現一個SQL的時候，需要計算平面上2點間的距離，如果使用SQL函式實現，程式碼比較長，於是想到能否利用空間資料庫的結構來實現這個功能。

 

 

測試環境為11.1.0.6，如果是9i，由於沒有建立同義詞，還需要在SDO物件的前面加上MDSYS字首。

這裡借用MDSYS的SDO_GEOMETRY物件，下面是一個簡單的例子，計算（0， 0）到（3， 4）兩點間的距離：

SQL> SELECT
  2   SDO_GEOM.SDO_DISTANCE(
  3    SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(0, 0, NULL), NULL, NULL),
  4    SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(3, 4, NULL), NULL, NULL),
  5    0.0001) DISTANCE
  6  FROM DUAL;

  DISTANCE
----------
         5

簡單看一下SDO_GEOMETRY的結構：

SQL> DESC SDO_GEOMETRY
 名稱                                               是否為空? 型別
 -------------------------------------------------- -------- --------------------
 SDO_GTYPE                                                   NUMBER
 SDO_SRID                                                    NUMBER
 SDO_POINT                                                   MDSYS.SDO_POINT_TYPE
 SDO_ELEM_INFO                                               MDSYS.SDO_ELEM_INFO_ARRAY
 SDO_ORDINATES                                               MDSYS.SDO_ORDINATE_ARRAY

METHOD
------
 MEMBER FUNCTION GET_GTYPE RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION GET_DIMS RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION GET_LRS_DIM RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION GET_WKB RETURNS BLOB

METHOD
------
 MEMBER FUNCTION GET_WKT RETURNS CLOB

METHOD
------
 MEMBER FUNCTION ST_COORDDIM RETURNS NUMBER

METHOD
------
 MEMBER FUNCTION ST_ISVALID RETURNS NUMBER

METHOD
------
 FINAL CONSTRUCTOR FUNCTION SDO_GEOMETRY RETURNS SELF AS RESULT
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 WKT                            CLOB                    IN
 SRID                           NUMBER                  IN     DEFAULT

METHOD
------
 FINAL CONSTRUCTOR FUNCTION SDO_GEOMETRY RETURNS SELF AS RESULT
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 WKT                            VARCHAR2                IN
 SRID                           NUMBER                  IN     DEFAULT

METHOD
------
 FINAL CONSTRUCTOR FUNCTION SDO_GEOMETRY RETURNS SELF AS RESULT
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
 WKB                            BLOB                    IN
 SRID                           NUMBER                  IN     DEFAULT

其中引數SDO_GTYPE的值2001表示是一個二維的點，這個型別的具體函式描述可以參考空間資料庫的官方文件。

而隨後的SDO_POINT構造了一個點的位置，然後透過SDO_GEOM.SDO_DISTANCE 函式來計算兩個點的距離。

可能二維點的計算，這種方法和SQL直接計算沒有太大的差別，而對於三維點而言，這種方式就顯得簡單得多了：

SQL> SELECT
  2   SDO_GEOM.SDO_DISTANCE(
  3    SDO_GEOMETRY(3001, NULL, SDO_POINT_TYPE(0, 0, 0), NULL, NULL),
  4    SDO_GEOMETRY(3001, NULL, SDO_POINT_TYPE(3, 4, 12), NULL, NULL),
  5    0.0001) DISTANCE
  6  FROM DUAL;

  DISTANCE
----------
        13

另外對於二維點，10g以上版本可以利用更簡單的方法來構造：

SQL> SELECT
  2   SDO_GEOM.SDO_DISTANCE(
  3    SDO_GEOMETRY('POINT(0 0)'),
  4    SDO_GEOMETRY('POINT(3 4)'),
  5    0.0001) DISTANCE
  6  FROM DUAL;

  DISTANCE
----------
         5