主页 / dba / 问题 / 228715
Accepted
Stepan
Asked: 2019-02-02 13:09:33 +0800 CST

Oracle PL/SQL 查找最近的对象

  • 772

有两个包含对象及其坐标的表。一个包含酒店，另一个包含餐厅。如何建立一个表，为每家酒店列出最近的餐厅名称和距离？

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

编辑：我也有geoloc两个带有 x 和 y 坐标的表。geoloc 可以帮助构建查询吗？

plsql oracle-12c

1 个回答

  1. Best Answer

    这个查询对我有用。它使用来自 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'
        );

    请注意，第一个参数是餐厅，而不是节点。

    • 0

相关问题