Does Oracle Spatial calculate geodetic distance?

Question

I've got to migrate a PostGIS database to Oracle Spatial, and am stuck on a problem: In a view, I have a column that amounts to ST_Distance(geography(geom_1), geography(geom_2)), effectively calculating geodetic distance in meters.
In Oracle, calculating the distance in WGS-84 gets me the result in degrees.
I've also tried to project the geometries to World Mercator and calculate distance, but when data gets far from the equator, the results become more and more different.
How can I replicate this code in Oracle Spatial?

Answer 1

"In Oracle, calculating the distance on WGS-84 gets me the result in degrees."

That can only happen if your geometries are defined without any explicit SRID (= SDO_SRID is set to NULL). In that case, the database has no clue about what you are actually storing: it does not know that your data is actually geodetic. All it sees is some numbers that it assumes are in some unspecified cartesian projection. As a result, the distance you get is in decimal degrees. For example:

select sdo_geom.sdo_distance(
  sdo_geometry(2001, null, sdo_point_type(-90.207591, 32.3205, null), null, null),
  sdo_geometry(2001, null, sdo_point_type(-76.51416, 37.07585, null), null, null),
  0.01
) as distance
from dual;

which returns

  DISTANCE
----------
 14.495634

in decimal degrees.

1359310.6 meters.

To get the proper result (= a distance measured in meters or any other chosen unit), you must explicitly specify that the points are in geodetic coordinates. For example:

select sdo_geom.sdo_distance(
  sdo_geometry(2001, 4326, sdo_point_type(-90.207591, 32.3205, null), null, null),
  sdo_geometry(2001, 4326, sdo_point_type(-76.51416, 37.07585, null), null, null),
  0.01
) as distance
from dual;

which returns

  DISTANCE
----------
 1359310.6

This time the result is in the default distance unit, i.e. meters.

You can also specify an explicit unit for the result:

select sdo_geom.sdo_distance(
  sdo_geometry(2001, 4326, sdo_point_type(-90.207591, 32.3205, null), null, null),
  sdo_geometry(2001, 4326, sdo_point_type(-76.51416, 37.07585, null), null, null),
  0.01,
  'unit=mile'
) as distance
from dual;

The result is now:

  DISTANCE
----------
844.636447

in miles.

I should add that storing geometries without any SRID is a very poor practice: you will not be able to do any calculations with those shapes (like measuring lengths, distances and areas) and you will not be able to match them with shapes in other coordinate systems. While you can get away with projected shapes without a SRID to some extent, having geodetic shapes without any SRID is a definite no-no.

Answer 2

Have you tried specifying the units.

Based on this https://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objgeom.htm#i857957 "Unit of measurement: a quoted string with unit= and an SDO_UNIT value from the MDSYS.SDO_DIST_UNITS table (for example, 'unit=KM'). See Section 2.8 for more information about unit of measurement specification."

I would think:

SDO_GEOM.SDO_DISTANCE(geom1 , geom2,0.01,'unit=M')

Would do the trick