CODEWITHSUNDEEP
oracle-databaseplsqlgeometrygeocodingoracle-spatial
Para Kan
Para Kan

Reputation: 121

Calculate distance between two lat long points in Oracle

I'm trying to calculate the distance between two lat long points in Oracle using SDO_GEOM Package. Most of the solutions I read on the internet suggests below query:

select sdo_geom.sdo_distance(
  sdo_geometry(2001, 4326, sdo_point_type(40.0, 10.0, null), null, null),
  sdo_geometry(2001, 4326, sdo_point_type(40.0, 11.0, null), null, null),
  0.01,
  'unit=KM'
) as distance
from dual;

However, when I use an online tool to validate the result, it seems to be incorrect:

Try (40,10), (40,11) on:

http://www.meridianoutpost.com/resources/etools/calculators/calculator-latitude-longitude-distance.php

Any idea why the distance calculation is incorrect even if I'm using a geographic coordinate system (4326) in the query?

Upvotes: 4

Views: 10464

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21075

Your problem is in swaping the longitude and latitude

Here is an example calculation the distance of the Central Park and Broadway

select sdo_geom.sdo_distance(         --  longitude,  Latitude 
  sdo_geometry(2001, 4326, sdo_point_type(-73.968285, 40.785091, null), null, null),
  sdo_geometry(2001, 4326, sdo_point_type(-73.974709, 40.790886, null), null, null),
  0.01,
  'unit=KM'
) as distance
from dual;

which return

0,841504644128136

The link you mentioned gives (provided correct input) comparable result

 From: (40°47'6.3276000000001"N -73°58'5.826"E) To: (40°47'27.1896"N -73°58'28.9524"E)
0.52 miles / 0.84 kilometers / 0.45 nautical miles

Upvotes: 2

Albert Godfrind
Albert Godfrind

Reputation: 2078

select sdo_geom.sdo_distance(
  sdo_geometry(2001, 4326, sdo_point_type(40.0, 10.0, null), null, null),
  sdo_geometry(2001, 4326, sdo_point_type(40.0, 11.0, null), null, null),
  0.01,
  'unit=KM'
) as distance
from dual;

DISTANCE
----------
110.611186

The Oracle calculation returns 110.611186 km

The one on your site returns 111.19 km so a difference of 580 m. That difference is because your online calculator uses simple math assuming a spherical earth, whereas Oracle use the proper ellipsoidal shape of the earth (the WGS84 ellipsoid).

You can see the difference on https://www.fai.org/page/world-distance-calculator If you choose the WGS84 earth model (the WGS84 ellipsoid) then you get the same result as Oracle (110.611186562098). If you change it to FAI Sphere then you get 111.19492643325476, the same as your comparator.

The correct distance is 110.611 km. Whether computing it with 580 m off is important depends on your application. If you measure short distances (like a km or less), the error is negligible. But for long distance it can be significant. Here it would mean missing a target by 1/2 km!

It does show the importance of using the correct earth model for those computations.

EDIT: And the Oracle representation (and pretty much all GIS tools) is longitude, latitude. If you swap the numbers, then you will get very different results.

Upvotes: 5

Related Questions