# Compute real-earth distances with SQL

• Script Name Compute real-earth distances with SQL
• Description This SQL script shows how to use the SDO_GEOM.SDO_DISTANCE function to calculate distances between two locations.
• Area Geospatial
• Contributor Carsten Czarski (Oracle)
• Created Monday October 05, 2015
• 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_NAMESDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich0
Vienna360.664673043316
San Francisco9452.33377799365
London917.830786273456

4 rows selected.
• 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_FROMLondonMunichSan FranciscoVienna
London0917.8307862734568634.1269552291241.42203199386
Munich917.83078627345609452.33377799365360.664673043316
San Francisco8634.1269552299452.3337779936509646.92272199942
Vienna1241.42203199386360.6646730433169646.922721999420

4 rows selected.
• 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_NAMESDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich0
Vienna360.664673043316
San Francisco9452.33377799365
London917.830786273456

4 rows selected.
• 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_FROMLondonMunichSan FranciscoVienna
London0917.8307862734568634.1269552291241.42203199386
Munich917.83078627345609452.33377799365360.664673043316
San Francisco8634.1269552299452.3337779936509646.92272199942
Vienna1241.42203199386360.6646730433169646.922721999420

4 rows selected.
• 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
• 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_NAMESDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich0
Vienna360.664673043316
San Francisco9452.33377799365
London917.830786273456
Munich0
Vienna360.664673043316
San Francisco9452.33377799365
London917.830786273456
Munich0
Vienna360.664673043316
San Francisco9452.33377799365
London917.830786273456
Munich0
Vienna360.664673043316
San Francisco9452.33377799365
London917.830786273456

16 rows selected.
• 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_FROMLondonMunichSan FranciscoVienna
London03671.32314509382434536.5078209164965.68812797544
Munich3671.323145093824037809.33511197461442.658692173264
San Francisco34536.50782091637809.3351119746038587.69088799768
Vienna4965.688127975441442.65869217326438587.690887997680

4 rows selected.
• 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_NAMESDO_GEOM.SDO_DISTANCE(A.LOC_COORDINATES,B.LOC_COORDINATES,1,'UNIT=KM')
Munich0
Vienna360.664673043316
San Francisco9452.33377799365
London917.830786273456

4 rows selected.
• 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_FROMLondonMunichSan FranciscoVienna
London0917.8307862734568634.1269552291241.42203199386
Munich917.83078627345609452.33377799365360.664673043316
San Francisco8634.1269552299452.3337779936509646.92272199942
Vienna1241.42203199386360.6646730433169646.922721999420

4 rows selected.