Oracle PL/SQL 查找最近的對象

有兩個包含對象及其座標的表。一個包含酒店，另一個包含餐廳。如何建立一個表格，為每個酒店列出最近的餐廳名稱和距離？

WITH restaurant AS (
          SELECT 'A' r_name, 0.0 x, 0.0 y FROM dual UNION ALL
          SELECT 'B' r_name, 0.0 x, 6.0 y FROM dual UNION ALL
          SELECT 'C' r_name, 9.5 x, 0.5 y FROM dual UNION ALL
          SELECT 'D' r_name, 8.2 x, 4.7 y FROM dual -- etc           
          ),

    hotel AS ( 
          SELECT 'First_Hotel' h_name, 1.0 x, 6.0 y FROM dual UNION ALL  
          SELECT 'Second_Hotel' h_name, -4.0 x, 3.0 y FROM dual -- etc
          )               

    select h.h_name, r.r_name as nearest_restaurant, distance from restaurant r, hotel h  /* some query */

應該返回

hotel          nearest_restaurant    distance
First_Hotel       B                    1     -- B is the nearest restaurant to First_Hotel. Distance is sqrt((1.0-0.0)^2+(6.0-6.0)^2)=1
Second_Hotel      A                    5     --if both A and B on the same distance choose based on r_name 

---

編輯：我也有geolocx 和 y 座標的兩個表。geoloc 可以幫助建構查詢嗎？

這個查詢對我有用。它使用 Oracle Spartial 的 GEOLOC

   create table my_result_table as  (
   select n.nodeid, n.geoloc.sdo_point.y as node_y, n.geoloc.sdo_point.x as node_x, 
       r.geoloc.sdo_point.y as sl_y, r.geoloc.sdo_point.x as sl_x,
       sdo_nn_distance(1)  as sld
      from  my_nodes n,  my_restaurants r
      where
      sdo_nn   ( r.GEOLOC, n.geoloc,  'Unit = MILE sdo_num_res = 1', 1) = 'TRUE'
       );

請注意，第一個參數是餐廳，而不是節點。

引用自：https://dba.stackexchange.com/questions/228715