Calculate distance between two lat long points in Oracle

Question

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?

Answer 1

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.