Oracle Live SQL - Script: Compute real-earth distances with SQL

Statement 1

Create a table with an SDO_GEOMETRY column to store the location information.

Create table

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

Table created.

Statement 2

Insert a row for "Munich"

insert into locations values ('Munich', apex_spatial.point(11.5174, 48.17571))

1 row(s) inserted.

Statement 3

Insert a row for "Vienna"

insert into locations values ('Vienna', apex_spatial.point(16.3688, 48.20254))

1 row(s) inserted.

Statement 4

Insert a row for "San Francisco"

insert into locations values ('San Francisco', apex_spatial.point(-122.4194200, 37.7749300 ))

1 row(s) inserted.

Statement 5

Insert a row for "London"

insert into locations values ('London', apex_spatial.point( -0.15307, 51.54931))

1 row(s) inserted.

Statement 6

Get the distance to all locations from "Munich" using the SDO_GEOM.SDO_DISTANCE function.

select  
 b.loc_name, 
 sdo_geom.sdo_distance(  
  a.loc_coordinates,  
  b.loc_coordinates,  
  1,  
  'unit=km'  
)   
from locations a, locations b  
where a.loc_name='Munich'


LOC_NAME SDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich 0
Vienna 360.664673043316
San Francisco 9452.33377799365
London 917.830786273456

4 rows selected.

LOC_NAME	SDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich	0
Vienna	360.664673043316
San Francisco	9452.33377799365
London	917.830786273456

Statement 7

Compute all distances using SDO_GEOM.SDO_DISTANCE. Using the SQL PIVOT clause to generate a matrix.

select loc_from, "London", "Munich", "San Francisco", "Vienna" 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 ( 
  'Munich' as "Munich", 
  'San Francisco' as "San Francisco", 
  'Vienna' as "Vienna", 
  'London' as "London" 
 ) 
) 
order by 1


LOC_FROM London Munich San Francisco Vienna
London 0 917.830786273456 8634.126955229 1241.42203199386
Munich 917.830786273456 0 9452.33377799365 360.664673043316
San Francisco 8634.126955229 9452.33377799365 0 9646.92272199942
Vienna 1241.42203199386 360.664673043316 9646.92272199942 0

4 rows selected.

LOC_FROM	London	Munich	San Francisco	Vienna
London	0	917.830786273456	8634.126955229	1241.42203199386
Munich	917.830786273456	0	9452.33377799365	360.664673043316
San Francisco	8634.126955229	9452.33377799365	0	9646.92272199942
Vienna	1241.42203199386	360.664673043316	9646.92272199942	0

Statement 8

Create a table with an SDO_GEOMETRY column to store the location information.

Create table

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

Table created.

Statement 9

Insert a row for "Munich"

insert into locations values ('Munich', apex_spatial.point(11.5174, 48.17571))

1 row(s) inserted.

Statement 10

Insert a row for "Vienna"

insert into locations values ('Vienna', apex_spatial.point(16.3688, 48.20254))

1 row(s) inserted.

Statement 11

Insert a row for "San Francisco"

insert into locations values ('San Francisco', apex_spatial.point(-122.4194200, 37.7749300 ))

1 row(s) inserted.

Statement 12

Insert a row for "London"

insert into locations values ('London', apex_spatial.point( -0.15307, 51.54931))

1 row(s) inserted.

Statement 13

Get the distance to all locations from "Munich" using the SDO_GEOM.SDO_DISTANCE function.

select  
 b.loc_name, 
 sdo_geom.sdo_distance(  
  a.loc_coordinates,  
  b.loc_coordinates,  
  1,  
  'unit=km'  
)   
from locations a, locations b  
where a.loc_name='Munich'


LOC_NAME SDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich 0
Vienna 360.664673043316
San Francisco 9452.33377799365
London 917.830786273456

4 rows selected.

LOC_NAME	SDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich	0
Vienna	360.664673043316
San Francisco	9452.33377799365
London	917.830786273456

Statement 14

Compute all distances using SDO_GEOM.SDO_DISTANCE. Using the SQL PIVOT clause to generate a matrix.

select loc_from, "London", "Munich", "San Francisco", "Vienna" 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 ( 
  'Munich' as "Munich", 
  'San Francisco' as "San Francisco", 
  'Vienna' as "Vienna", 
  'London' as "London" 
 ) 
) 
order by 1


LOC_FROM London Munich San Francisco Vienna
London 0 917.830786273456 8634.126955229 1241.42203199386
Munich 917.830786273456 0 9452.33377799365 360.664673043316
San Francisco 8634.126955229 9452.33377799365 0 9646.92272199942
Vienna 1241.42203199386 360.664673043316 9646.92272199942 0

4 rows selected.

LOC_FROM	London	Munich	San Francisco	Vienna
London	0	917.830786273456	8634.126955229	1241.42203199386
Munich	917.830786273456	0	9452.33377799365	360.664673043316
San Francisco	8634.126955229	9452.33377799365	0	9646.92272199942
Vienna	1241.42203199386	360.664673043316	9646.92272199942	0

Statement 15

Create a table with an SDO_GEOMETRY column to store the location information.

Create table

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

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 16

Insert a row for "Munich"

insert into locations values ('Munich', apex_spatial.point(11.5174, 48.17571))

1 row(s) inserted.

Statement 17

Insert a row for "Vienna"

insert into locations values ('Vienna', apex_spatial.point(16.3688, 48.20254))

1 row(s) inserted.

Statement 18

Insert a row for "San Francisco"

insert into locations values ('San Francisco', apex_spatial.point(-122.4194200, 37.7749300 ))

1 row(s) inserted.

Statement 19

Insert a row for "London"

insert into locations values ('London', apex_spatial.point( -0.15307, 51.54931))

1 row(s) inserted.

Statement 20

Get the distance to all locations from "Munich" using the SDO_GEOM.SDO_DISTANCE function.

select  
 b.loc_name, 
 sdo_geom.sdo_distance(  
  a.loc_coordinates,  
  b.loc_coordinates,  
  1,  
  'unit=km'  
)   
from locations a, locations b  
where a.loc_name='Munich'


LOC_NAME SDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich 0
Vienna 360.664673043316
San Francisco 9452.33377799365
London 917.830786273456
Munich 0
Vienna 360.664673043316
San Francisco 9452.33377799365
London 917.830786273456
Munich 0
Vienna 360.664673043316
San Francisco 9452.33377799365
London 917.830786273456
Munich 0
Vienna 360.664673043316
San Francisco 9452.33377799365
London 917.830786273456

16 rows selected.

LOC_NAME	SDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich	0
Vienna	360.664673043316
San Francisco	9452.33377799365
London	917.830786273456
Munich	0
Vienna	360.664673043316
San Francisco	9452.33377799365
London	917.830786273456
Munich	0
Vienna	360.664673043316
San Francisco	9452.33377799365
London	917.830786273456
Munich	0
Vienna	360.664673043316
San Francisco	9452.33377799365
London	917.830786273456

Statement 21

Compute all distances using SDO_GEOM.SDO_DISTANCE. Using the SQL PIVOT clause to generate a matrix.

select loc_from, "London", "Munich", "San Francisco", "Vienna" 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 ( 
  'Munich' as "Munich", 
  'San Francisco' as "San Francisco", 
  'Vienna' as "Vienna", 
  'London' as "London" 
 ) 
) 
order by 1


LOC_FROM London Munich San Francisco Vienna
London 0 3671.323145093824 34536.507820916 4965.68812797544
Munich 3671.323145093824 0 37809.3351119746 1442.658692173264
San Francisco 34536.507820916 37809.3351119746 0 38587.69088799768
Vienna 4965.68812797544 1442.658692173264 38587.69088799768 0

4 rows selected.

LOC_FROM	London	Munich	San Francisco	Vienna
London	0	3671.323145093824	34536.507820916	4965.68812797544
Munich	3671.323145093824	0	37809.3351119746	1442.658692173264
San Francisco	34536.507820916	37809.3351119746	0	38587.69088799768
Vienna	4965.68812797544	1442.658692173264	38587.69088799768	0

Statement 22

Create a table with an SDO_GEOMETRY column to store the location information.

Create table

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

Table created.

Statement 23

Insert a row for "Munich"

insert into locations values ('Munich', apex_spatial.point(11.5174, 48.17571))

1 row(s) inserted.

Statement 24

Insert a row for "Vienna"

insert into locations values ('Vienna', apex_spatial.point(16.3688, 48.20254))

1 row(s) inserted.

Statement 25

Insert a row for "San Francisco"

insert into locations values ('San Francisco', apex_spatial.point(-122.4194200, 37.7749300 ))

1 row(s) inserted.

Statement 26

Insert a row for "London"

insert into locations values ('London', apex_spatial.point( -0.15307, 51.54931))

1 row(s) inserted.

Statement 27

Get the distance to all locations from "Munich" using the SDO_GEOM.SDO_DISTANCE function.

select  
 b.loc_name, 
 sdo_geom.sdo_distance(  
  a.loc_coordinates,  
  b.loc_coordinates,  
  1,  
  'unit=km'  
)   
from locations a, locations b  
where a.loc_name='Munich'


LOC_NAME SDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich 0
Vienna 360.664673043316
San Francisco 9452.33377799365
London 917.830786273456

4 rows selected.

LOC_NAME	SDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich	0
Vienna	360.664673043316
San Francisco	9452.33377799365
London	917.830786273456

Statement 28

Compute all distances using SDO_GEOM.SDO_DISTANCE. Using the SQL PIVOT clause to generate a matrix.

select loc_from, "London", "Munich", "San Francisco", "Vienna" 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 ( 
  'Munich' as "Munich", 
  'San Francisco' as "San Francisco", 
  'Vienna' as "Vienna", 
  'London' as "London" 
 ) 
) 
order by 1


LOC_FROM London Munich San Francisco Vienna
London 0 917.830786273456 8634.126955229 1241.42203199386
Munich 917.830786273456 0 9452.33377799365 360.664673043316
San Francisco 8634.126955229 9452.33377799365 0 9646.92272199942
Vienna 1241.42203199386 360.664673043316 9646.92272199942 0

4 rows selected.

Compute real-earth distances with SQL

Additional Information