Valid conversion unit values for Oracle sdo_geom.sdo_length()

The SDO_LENGTH function within SDO_GEOM package can be called with an optional unit parameter as in the following call signature:

 SDO_GEOM.SDO_LENGTH(
      geom IN SDO_GEOMETRY,
      tol IN NUMBER
      [, unit IN VARCHAR2]
      [, count_shared_edges IN NUMBER]
      ) RETURN NUMBER;

But what are a valid list of values for the unit parameter?

For a start, if the SDO_GEOMETRY has a NULL SDO_SRID value then the unit parameter can’t be used at all! For other cases, let’s see what we can find out.

The documentation describes the unit parameter as:

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.10 for more information about unit of measurement specification.

If this parameter is not specified, the unit of measurement associated with the data is assumed. For geodetic data, the default unit of measurement is meters.

Section 2.10 says about the SDO_UNIT value that:

The unit parameter must be enclosed in single quotation marks and contain the string unit= and a valid UNIT_OF_MEAS_NAME value from the SDO_UNITS_OF_MEASURE table.

However, there is a serious inconsistency here related to the SDO_LENGTH documentation saying that the unit= parameter must use a valid value from the SDO_UNIT column in the SDO_DIST_UNITS (is not a table but a view defined over the table SDO_UNITS_OF_MEASURE) defined as:

 CREATE OR REPLACE FORCE VIEW "MDSYS"."SDO_DIST_UNITS" 
 ("SDO_UNIT", "UNIT_NAME", "CONVERSION_FACTOR")
 AS
   SELECT SHORT_NAME          "SDO_UNIT",
          UNIT_OF_MEAS_NAME   "UNIT_NAME",
          (FACTOR_B/FACTOR_C) "CONVERSION_FACTOR"
     FROM MDSYS.SDO_UNITS_OF_MEASURE
    WHERE lower(UNIT_OF_MEAS_TYPE)='length';

Yet, Section 2.10 say that “unit= [should use] … a valid UNIT_OF_MEAS_NAME value from the SDO_UNITS_OF_MEASURE table”. Yet this is the texual UNIT_NAME column in SDO_DIST_UNITS!

Let’s test this.

First, what is SDO_UNIT values in SDO_DIST_UNITS are valid? The view does contain NULL SDO_UNIT values which are incorrect so we will ignore them. Also, the SDO_UNIT codeds as “British foot (1936)” doesn’t work either.

 SELECT mdsys.sdo_geom.sdo_length(a.geom,0.05,'unit='||b.sdo_unit) as length
   FROM (SELECT MDSYS.SDO_GEOMETRY(2003, 8307, NULL, 
                    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), 
                    MDSYS.SDO_ORDINATE_ARRAY(1,1,5,1,5,7,1,7,1,1)) as geom 
           FROM dual) a,
           sdo_dist_units b
  WHERE sdo_unit = 'British foot (1936)';
 
 ORA-13291: conversion error between the specified unit and standard unit
 ORA-06512: at "MDSYS.SDO_3GL", line 1766
 ORA-06512: at "MDSYS.SDO_GEOM", line 1668
 ORA-06512: at "MDSYS.SDO_GEOM", line 1610
 ORA-06512: at line 1

OK, so cutting this one out and trying all the rest gives us:

 SELECT b.sdo_unit,
        mdsys.sdo_geom.sdo_length(a.geom,0.05,'unit='||b.sdo_unit) as length
   FROM (SELECT MDSYS.SDO_GEOMETRY(2003, 8307, NULL, 
                    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), 
                    MDSYS.SDO_ORDINATE_ARRAY(1,1,5,1,5,7,1,7,1,1)) as geom 
           FROM dual) a,
         sdo_dist_units b
   WHERE sdo_unit is not null
     AND sdo_unit <> unit_name  -- gets rid of 'British foot (1936)'
   ORDER BY sdo_unit;

So, they all work.

Now, let’s try the “valid UNIT_OF_MEAS_NAME value[s] from the SDO_UNITS_OF_MEASURE”:

 DECLARE
    conversion_error EXCEPTION;
    PRAGMA EXCEPTION_INIT(conversion_error, -13291);
    geom   mdsys.sdo_geometry := MDSYS.SDO_GEOMETRY(2002, 8307, NULL, 
                   MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), 
                   MDSYS.SDO_ORDINATE_ARRAY(1,1,2,1));
    len number;
    uom varchar2(1000);
 BEGIN
   FOR rec IN (SELECT b.UNIT_OF_MEAS_NAME, 
                      NVL(b.SHORT_NAME,'NULL') || '|' || NVL(b.DATA_SOURCE,'ORA') SHORT_DATA_SOURCE
                 FROM SDO_UNITS_OF_MEASURE b
                WHERE b.unit_of_meas_type = 'length' 
                  AND b.UNIT_OF_MEAS_NAME is not null
             ORDER BY b.UNIT_OF_MEAS_NAME) LOOP
     BEGIN
        uom := rec.UNIT_OF_MEAS_NAME;
        len := mdsys.sdo_geom.sdo_length(geom,0.05,'unit='||rec.UNIT_OF_MEAS_NAME);
        dbms_output.put_line('Passed: ' || UOM || ' (' || rec.SHORT_DATA_SOURCE || ') = ' || len);
        EXCEPTION 
          WHEN conversion_error THEN
             dbms_output.put_line('Failed: ' || UOM|| ' (' || rec.SHORT_DATA_SOURCE || ')');
     END;
   END LOOP;
 END;
 /

Failed: Bin width 12.5 metres (NULL|EPSG)
Failed: Bin width 165 US survey feet (NULL|EPSG)
Failed: Bin width 25 metres (NULL|EPSG)
Failed: Bin width 3.125 metres (NULL|EPSG)
Failed: Bin width 330 US survey feet (NULL|EPSG)
Failed: Bin width 37.5 metres (NULL|EPSG)
Failed: Bin width 6.25 metres (NULL|EPSG)
Failed: Bin width 82.5 US survey feet (NULL|EPSG)
Failed: British chain (Benoit 1895 A) (NULL|EPSG)
Failed: British chain (Benoit 1895 B) (NULL|EPSG)
Failed: British chain (Sears 1922) (NULL|EPSG)
Failed: British foot (1865) (NULL|EPSG)
Failed: British foot (1936) (British foot (1936)|EPSG)
Failed: British foot (Benoit 1895 A) (NULL|EPSG)
Failed: British foot (Benoit 1895 B) (NULL|EPSG)
Failed: British foot (Sears 1922) (NULL|EPSG)
Failed: British link (Benoit 1895 A) (NULL|EPSG)
Failed: British link (Benoit 1895 B) (NULL|EPSG)
Failed: British link (Sears 1922) (NULL|EPSG)
Failed: British yard (Benoit 1895 A) (NULL|EPSG)
Failed: British yard (Benoit 1895 B) (NULL|EPSG)
Failed: British yard (Sears 1922) (NULL|EPSG)
Passed: Centimeter (CENTIMETER|ORA) = 11130264.9339446
Passed: Centimeter (CM|ORA) = 11130264.9339446
Passed: Chain (CHAIN|ORA) = 5532.82079353803
Failed: Chain (Benoit) (CHN_BEN|ORA)
Failed: Chain (Sears) (CHN_SRS|ORA)
Passed: Clarke’s Foot (CL_FT|ORA) = 365166.17237351
Passed: Clarke’s chain (NULL|EPSG) = 5532.82079353803
Passed: Clarke’s foot (NULL|EPSG) = 365166.17237351
Passed: Clarke’s link (NULL|EPSG) = 553287.043843245
Passed: Clarke’s yard (NULL|EPSG) = 121722.057457837
Passed: Fathom (FATHOM|ORA) = 60861.0287289183
Failed: Foot (International) (FOOT|ORA)
Failed: German legal metre (NULL|EPSG)
Passed: Gold Coast foot (NULL|EPSG) = 365166.17237351
Passed: Inch (INCH|ORA) = 4381994.06848212
Passed: Indian Foot (IND_FT|ORA) = 365166.17237351
Passed: Indian foot (NULL|EPSG) = 365166.17237351
Failed: Indian foot (1937) (NULL|EPSG)
Failed: Indian foot (1962) (NULL|EPSG)
Failed: Indian foot (1975) (NULL|EPSG)
Passed: Indian yard (NULL|EPSG) = 121722.057457837
Failed: Indian yard (1937) (NULL|EPSG)
Failed: Indian yard (1962) (NULL|EPSG)
Failed: Indian yard (1975) (NULL|EPSG)
Passed: Kilometer (KM|ORA) = 111.302649339446
Passed: Kilometer (KILOMETER|ORA) = 111.302649339446
Passed: Link (LINK|ORA) = 553287.043843245
Failed: Link (Benoit) (LINK_BEN|ORA)
Failed: Link (Sears) (LINK_SRS|ORA)
Passed: Meter (M|ORA) = 111302.649339446
Passed: Meter (METER|ORA) = 111302.649339446
Passed: Mile (MILE|ORA) = 69.1602599192254
Passed: Millimeter (MM|ORA) = 111302649.339446
Passed: Millimeter (MILLIMETER|ORA) = 111302649.339446
Passed: Modified American Foot (MOD_USFT|ORA) = 365166.17237351
Passed: Nautical Mile (NAUT_MILE|ORA) = 69.1602599192254
Passed: Rod (ROD|ORA) = 22131.2831741521
Passed: Statute mile (NULL|EPSG) = 69.1602599192254
Passed: U.S. Foot (SURVEY_FOOT|ORA) = 365166.17237351
Passed: US survey chain (NULL|EPSG) = 5532.82079353803
Passed: US survey foot (NULL|EPSG) = 365166.17237351
Passed: US survey link (NULL|EPSG) = 553287.043843245
Passed: US survey mile (NULL|EPSG) = 69.1602599192254
Passed: USER–HALF–METER (NULL|USER_DEFINED) = 222605.298678892
Passed: Yard (YARD|ORA) = 121722.057457837
Failed: Yard (Indian) (IND_YARD|ORA)
Failed: Yard (Sears) (SRS_YARD|ORA)
Passed: fathom (NULL|EPSG) = 60861.0287289183
Passed: foot (NULL|EPSG) = 365166.17237351
Failed: kilometre (NULL|EPSG)
Failed: metre (NULL|EPSG)
Passed: nautical mile (NULL|EPSG) = 69.1602599192254

So, I think my observation that they are inconsistent is correct up to a point. The inconsistency is that one says “use SDO_UNIT which is based on SHORT_NAME SHORT_NAME” while the other says “use the SDO_UNITS_OF_MEASURE table’s UNIT_OF_MEAS_NAME”.

The answer appears to me to be that the SDO_UNIT/SDO_DIST_UNIT approach is pre the alignment of the Oracle SDO_CS functionality with EPSG while the UNIT_OF_MEAS_NAME/SDO_UNITS_OF_MEASURE.

I have not yet discovered what defines a “valid” UNIT_OF_MEAS_NAME. When I find out I will extend this blog article.