1
2
3
4

create table locations( 
loc_name varchar2(200) not null,
loc_coordinates sdo_geometry 
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

    
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('上海1',apex_spatial.point(p_lon=>'121.602599',p_lat=> '31.324333') );  
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('上海2',apex_spatial.point(p_lon=>'121.602537',p_lat=> '31.324155') );  
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('成都1',apex_spatial.point(p_lon=>'103.843106',p_lat=> '30.66018') );  
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('成都2',apex_spatial.point(p_lon=>'103.84385',p_lat=> '30.658557') );  
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('天津1',apex_spatial.point(p_lon=>'117.707523',p_lat=> '39.062358') );  
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('天津2',apex_spatial.point(p_lon=>'117.71408094618056',p_lat=> '39.06352132161458') );  
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('南京1',apex_spatial.point(p_lon=>'118.671249',p_lat=> '32.190179') );  
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('南京2',apex_spatial.point(p_lon=>'118.670827',p_lat=> '32.190565') );  
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('常州1',apex_spatial.point(p_lon=>'119.92964328342013',p_lat=> '31.872356770833335') );  
INSERT INTO locations (loc_name,loc_coordinates)VALUES ('常州2',apex_spatial.point(p_lon=>'119.930143',p_lat=> '32.871432') );

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

  
SELECT loc_from,  
       "上海1",  
       "上海2",  
       "成都1",  
       "成都2",  
       "天津1",  
       "天津2",  
       "南京1",  
       "南京2",  
       "常州1",  
       "常州2"  
FROM  
    (SELECT a.loc_name AS loc_from,  
            b.loc_name AS loc_to,  
            sdo_geom.sdo_distance(  
                    a.loc_coordinates,  
                    b.loc_coordinates,  
                    1,  
                    'unit=km'  
            )             dist  
     FROM locations a,  
          locations b)  
        PIVOT (SUM(dist) FOR loc_to IN (  
        '上海1' AS "上海1",  
        '上海2' AS "上海2",  
        '成都1' AS "成都1",  
        '成都2' AS "成都2",  
        '天津1' AS "天津1",  
        '天津2' AS "天津2",  
        '南京1' AS "南京1",  
        '南京2' AS "南京2",  
        '常州1' AS "常州1",  
        '常州2' AS "常州2"  
        )  
        )  
ORDER BY 1;