3

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?

  • 1
    I'm pretty sure Oracle has the same functionality that they call the Round Earth model. I thought it kicked in automatically for WGS-84 so not sure why it's not working. Thought you had to specify units of measurement too. – LR1234567 Jan 21 '16 at 17:59
3

"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.

  • Kid's mistake. I've overlooked the details, and the data I've been using didn't came with SRID. Once the source was fixed, it works like a charm. Thanks – ebarbara Jan 22 '16 at 12:15
1

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

  • That is only possible if the geometries involved do contain an explicit SRID (as they properly should). If they do not have any SRID, then Oracle does not know what unit the coordinates are in and so cannot do any unit conversion. – Albert Godfrind Jan 22 '16 at 11:44
  • Makes sense. I was assuming they did since they mentioned WGS 84, but perhaps they just assumed Oracle would know that by looking at the coordinates. – LR1234567 Jan 22 '16 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.