It is something I often need to do in Oracle so I decided to do something about creating a function that would do this. This involved me having to hack my way into some of the mdsys coordinate system and distance units tables but I managed to come up with something that I have integrated in to the GEOM package of the free PL/SQL code available for download from my website.

The first table that we need to look at is the mdsys.SDO_DIST_UNITS table. This table is described as follows:

codesys@XE> desc mdsys.sdo_dist_units
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------
 SDO_UNIT                                                                         VARCHAR2(80)
 UNIT_NAME                                                               NOT NULL VARCHAR2(80)
 CONVERSION_FACTOR                                                                NUMBER

Let’s have a look at some of the entries of this table (I ignore NULL sdo_unit names in this article and in my code):

codesys@XE> select substr(sdo_unit,1,25) as unit,
  2         substr(unit_name,1,30) as name,
  3         conversion_factor
  4    from mdsys.sdo_dist_units
  5*  where sdo_unit is not null
codesys@XE> /

UNIT                      NAME                           CONVERSION_FACTOR
------------------------- ------------------------------ -----------------
M                         Meter                                          1
METER                     Meter                                          1
KM                        Kilometer                                   1000
KILOMETER                 Kilometer                                   1000
CM                        Centimeter                                   .01
CENTIMETER                Centimeter                                   .01
MM                        Millemeter                                  .001
MILLIMETER                Millemeter                                  .001
MILE                      Mile                                    1609.344
NAUT_MILE                 Nautical Mile                               1852
SURVEY_FOOT               U.S. Foot                              .30480061
FOOT                      Foot (International)                       .3048
INCH                      Inch                                       .0254
YARD                      Yard                                       .9144
CHAIN                     Chain                                    20.1168
ROD                       Rod                                       5.0292
LINK                      Link                                  .201166195
MOD_USFT                  Modified American Foot                .304812253
CL_FT                     Clarke's Foot                         .304797265
IND_FT                    Indian Foot                           .304799518
LINK_BEN                  Link (Benoit)                         .201167651
LINK_SRS                  Link (Sears)                          .201167651
CHN_BEN                   Chain (Benoit)                        20.1167825
CHN_SRS                   Chain (Sears)                         20.1167651
IND_YARD                  Yard (Indian)                         .914398554
SRS_YARD                  Yard (Sears)                          .914398415
FATHOM                    Fathom                                    1.8288
British foot (1936)       British foot (1936)                   .304800749

From this we can see that CONVERSION_FACTOR expresses how long a unit (1) of measure (eg FOOT) is terms of meters (eg .3048)

OK, so now we know how to convert between any two units in this table. Here is a function that will do it.

create or replace Function Convert_Unit( p_from_unit in varchar2,
                         p_value     in number,
                         p_to_unit   in varchar2 )
    return number
  Is
    v_from_conversion_factor number;
    v_to_conversion_factor   number;
  Begin
    If ( p_value is null or p_from_unit is null or p_to_unit is null ) Then
        raise_application_error( codesys.Constants.c_i_null_parameter,
                                 codesys.Constants.c_s_null_parameter,False );
    End If;
    -- Check if p_from_unit exists by getting the necessary conversion factor to meters
    BEGIN
      -- Note that the conversion_factor is a conversion factor between v_from_unit and 1 metre.
      SELECT conversion_factor
        INTO v_from_conversion_factor
        FROM mdsys.sdo_dist_units
           WHERE sdo_unit = UPPER(p_from_unit)
             AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( codesys.Constants.c_i_invalid_unit,
                                   codesys.Constants.c_s_invalid_unit || ' ' || p_from_unit);
    END;
    -- Check if p_to_unit exists by getting the necessary conversion factor to meters
    BEGIN
      -- Note that the conversion_factor is a conversion factor between v_to_unit and 1 metre.
      SELECT conversion_factor
        INTO v_to_conversion_factor
        FROM mdsys.sdo_dist_units
           WHERE sdo_unit = UPPER(p_to_unit)
             AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( codesys.Constants.c_i_invalid_unit,
                                   codesys.Constants.c_s_invalid_unit || ' ' || p_to_unit);
    END;
    -- Do the computation
    RETURN ( p_value * v_from_conversion_factor ) / v_to_conversion_factor;
  End Convert_Unit;

And some examples on how to use this function (also a part of the GEOM package):

codesys@XE> select convert_unit('CHAIN',1,'LINK') from dual;

CONVERT_UNIT('CHAIN',1,'LINK')
------------------------------
                    100.000897

But what if we have data coded to a SRID and want to convert from its unit of measure to one of those in the mdsys.sdo_dist_units table? For example, you will note that there is no sdo_unit for ‘Decimal Degrees’ in which longitude/latitude data is expressed. We need to look at the definition of a SRID to find the conversion information we need. This is held in the MDSYS.CS_SRS table.

codesys@XE> desc mdsys.cs_srs
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------
 CS_NAME                                                                          VARCHAR2(80)
 SRID                                                                    NOT NULL NUMBER(38)
 AUTH_SRID                                                                        NUMBER(38)
 AUTH_NAME                                                                        VARCHAR2(256)
 WKTEXT                                                                           VARCHAR2(2046)
 CS_BOUNDS                                                                        MDSYS.SDO_GEOMETRY

For example, let’s look at the projection information for WGS84 (SRID = 8307). I have formatted the output for readability.

codesys@XE> select wktext
  2    from mdsys.cs_srs
  3   where srid = 8307;

WKTEXT
-----------------------------------------------------------------------------------------------------------------------------------
GEOGCS [ "Longitude / Latitude (WGS 84)", 
         DATUM ["WGS 84", 
                SPHEROID ["WGS 84", 6378137, 298.257223563]
               ], 
         PRIMEM [ "Greenwich", 0.000000 ], 
         UNIT ["Decimal Degree", 0.01745329251994330]
       ]

OK, we can see that the unit of measure for this geographic coordinate system is our “Decimal Degrees”! But how do we access it?

In my downloadable PL/SQL code there is a string tokenizer which I can use as follows:

codesys@XE> SELECT rownum as id,
  2         substr(trim(both ' ' from replace(b.column_value,'"')),1,40) as token
  3    FROM mdsys.cs_srs a,
  4         TABLE(codesys.Tokenizer(a.wktext,',[]')) b
  5*  WHERE srid = 8307
codesys@XE> /

        ID TOKEN
---------- ----------------------------------------
         1 GEOGCS
         2 Longitude / Latitude (WGS 84)
         3 DATUM
         4 WGS 84
         5 SPHEROID
         6 WGS 84
         7 6378137
         8 298.257223563
         9 PRIMEM
        10 Greenwich
        11 0.000000
        12 UNIT
        13 Decimal Degree
        14 0.01745329251994330

For a projected coordinate system eg SRID 2964 “NAD27 / Alaska Albers” the tokens would be:

codesys@XE> SELECT rownum as id,
  2         substr(trim(both ' ' from replace(b.column_value,'"')),1,40) as token
  3    FROM mdsys.cs_srs a,
  4         TABLE(codesys.Tokenizer(a.wktext,',[]')) b
  5*  WHERE srid = 2964
codesys@XE> /

        ID TOKEN
---------- ----------------------------------------
         1 PROJCS
         2 NAD27 / Alaska Albers
         3 GEOGCS
         4 NAD27
         5 DATUM
         6 North American Datum 1927 (EPSG ID 6267)
         7 SPHEROID
         8 Clarke 1866 (EPSG ID 7008)
         9 6378206.4
        10 294.978698213905820761610537123195175418
        11 PRIMEM
        12 Greenwich
        13 0.000000
        14 UNIT
        15 Decimal Degree
        16 0.01745329251994328
        17 PROJECTION
        18 Alaska Albers (EPSG OP 15020)
        19 UNIT
        20 U.S. Foot
        21 .304800609601219202438404876809753619507

So, all we have to do is iterate over this list to extract the conversion unit for a coordinate system. In this case the second parameter of the SPHEROID entry (line 7 of the tokens for srid 8307). Since both (sdo_dist_unit and wktext) conversion units are expressed relative to meters we now have the ability to convert a distance expressed in the units of measure of a coodinate system to any unit of measure in the mdsys.sdo_dist_units table via a simple equation:

new_value = ( value x srid_conversion_factor ) / unit_conversion_factor

One other thing, for geographic coordinate systems (first parameter = GEOCS and not PROJCS) the value associated with the “Decimal Degrees” UNIT must be multiplied by the radius of the earth. Thus the equation would be:

new_value = ( value x srid_conversion_factor x radius_of_earth ) / unit_conversion_factor

So, we can now construct a function that will do this conversion (please excuse the length of this):

create or replace Function Convert_Distance( p_srid  in number,
                             p_value in number,
                             p_unit  in varchar2 := 'Meter' )
           Return number 
  Is
    v_unit                   varchar2(1000) := UPPER(p_unit);
    v_unit_conversion_factor number;
    v_srid_conversion_factor number;
    v_radius_of_earth        number := 6378137;  -- Default
    v_length                 number;
    v_srid                   mdsys.cs_srs.SRID%TYPE;
    v_token_id               number;
    v_token                  varchar2(4000);
    v_geocs                  boolean;
    cursor c_cs_tokens(p_srid in number)
    Is
       select rownum as id,
              substr(trim(both ' ' from replace(b.column_value,'"')),1,40) as token
         from mdsys.cs_srs a,
              table(codesys.Tokenizer(a.wktext,',[]')) b
         where srid = p_srid;
  Begin
    If ( p_srid is null ) Then
        -- Normally Oracle assumes a NULL srid is planar but 
        -- this could be planar feet, or meters etc so throw an error
        raise_application_error( codesys.Constants.c_i_null_srid,
                                 codesys.Constants.c_s_null_srid,False );
    End If;
    If ( p_value is null ) Then
        raise_application_error( codesys.Constants.c_i_null_parameter,
                                 codesys.Constants.c_s_null_parameter,False );
    End If;
    -- Check if p_unit exists by getting the necessary conversion factor to meters
    BEGIN
      -- Note that the conversion_factor is a conversion factor between v_unit and 1 metre.
      SELECT conversion_factor 
        INTO v_unit_conversion_factor
        FROM mdsys.sdo_dist_units
           WHERE sdo_unit = v_unit
             AND ROWNUM = 1;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( codesys.Constants.c_i_invalid_unit, 
                                   codesys.Constants.c_s_invalid_unit || v_unit);
    END;
    -- Check if SRID exists
    BEGIN
      SELECT srid
        INTO v_srid
        FROM mdsys.cs_srs
       WHERE srid = p_srid;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          raise_application_error( codesys.Constants.c_i_invalid_srid, 
                                   codesys.Constants.c_s_invalid_srid || p_srid);
    END;
    -- We need to get the conversion factor to meters and earth's radius for the supplied SRID. 
    -- This can only be gotten by getting the WKTEXT in mdsys.cs_srs, breaking it into tokens, 
    -- and finding the right ones:
    -- SPHEROID + 2 tokens = Radius
    -- Last UNIT + 1 = conversion unit
    -- Last UNIT + 2 = conversion unit value
    FOR rec IN c_cs_tokens(p_srid) LOOP
      If ( rec.id = 1 ) Then
        v_geocs :=  case rec.token when 'GEOGCS' then true else false end;
      ElsIf ( rec.token = 'SPHEROID' ) Then
        v_token    := rec.token;
        v_token_id := rec.id + 2;
      ElsIf ( rec.token = 'UNIT' ) Then
        v_token    := rec.token;
        v_token_id := rec.id + 2;
      End If;
      If ( rec.id = v_token_id ) Then
        If ( v_token = 'SPHEROID' ) Then
          v_radius_of_earth := to_number(rec.token);
        ElsIf ( v_token = 'UNIT' ) Then
          v_srid_conversion_factor := to_number(rec.token);
        End If;
      End If;
    END LOOP;
    If ( v_geocs ) Then
      v_srid_conversion_factor := v_srid_conversion_factor * v_radius_of_earth; 
    End If;
    -- OK, now we have a conversion factor from p_unit to meters
    -- and a conversion factor for the units to meters
    -- The returned value is: p_value * v_srid_conversion_factor (to get value in meters) / v_unit_conversion_factor (to convert from meters to the unit)
    -- 
    return ( p_value * v_srid_conversion_factor ) / v_unit_conversion_factor;
  End Convert_Distance;

Some examples:

codesys@XE> select     Convert_Distance(8311,1,'Meter') as meters_per_degree,
  2                1 / Convert_Distance(8311,1,'Meter') as degrees_per_metre
  3           from dual;

METERS_PER_DEGREE DEGREES_PER_METRE
----------------- -----------------
       111319.491        8.9832E-06

codesys@XE> select     Convert_Distance(8311,1,'Foot') as feet_per_degree,
  2                1 / Convert_Distance(8311,1,'Foot') as degrees_per_foot
  3           from dual;

FEET_PER_DEGREE DEGREES_PER_FOOT
--------------- ----------------
     365221.426       2.7381E-06

codesys@XE> select     Convert_Distance(2964,1,'Meter') as feet_per_metre,
  2                1 / Convert_Distance(2964,1,'Meter') as metres_per_foot
  3           from dual;

FEET_PER_METRE METRES_PER_FOOT
-------------- ---------------
     .30480061      3.28083333

These functions have been integrated into my GEOM PL/SQL package. These functions are useful in the context of my packages because I have recently added the ability to convert special elements of an sdo_geometry (eg rectangles, circles and circular arcs) to vertex-to-vertex connected segments. These require an arc2chord value which is expressed in dataset units. So, for geographic data this is decimal degrees not meters (unlike Oracle itself). Similarly, the tolerance parameter of the sdo_centroid function is similarly expressed in dataset units.

If anyone finds any errors in my work, please drop me a line.

Hello,
I find your articles very helpful and interesting. I was wondering if you can guide me to a link or something that may contain how to figure out surrounding blocks to a given block.

Thanks in advance
Ash Agarwal

— Ashok Agarwal Oct 15, 03:27 am #
Ash,

See my article

Simon

— Simon Greener Feb 18, 01:30 pm #
Hi Simon,
I’m Luciano from Brazil, newbe, since june taking my first steps in Oracle Spatial.
It’s the first time a write but already read yours articles several times.
My doubt is how about Oracle handles calculations of large AOIs/Geometries(that spread acros more then one UTM zone, projections).
I expect that Oracle split the aois, make calcs separated and then sums the results… but I wasn’t able to find any documentation, article, book, mention talking about this…
Do you know where I can find this information?!
Thanks in advance.
And thanks for sharing you knowledge, your posts helped me a lot!!
Best regards
Luciano

— Luciano Santana Sep 29, 08:00 am #
Luciano,

> I’m Luciano from Brazil, newbe, since june taking my first steps in Oracle Spatial.

Nice to meet you. Sorry for the delay in responding: been rather busy. Also, I have tried writing to you at your email address but your servers reject the emails.

> It’s the first time a write but already read yours articles several times.

I hope you found them useful. I have more to add but little time in which to complete them.

> My doubt is how about Oracle handles calculations of large AOIs/Geometries(that spread acros more then one UTM zone, projections). I expect that Oracle split the aois, make calcs separated and then sums the results… but I wasn’t able to find any documentation, article, book, mention talking about this… Do you know where I can find this information?!

The question is not easy to answer as Oracle, as a private software company, does not normally release details about its algorithms and implementations. This is very much the case for the Spatial team.

All I can say is that if you have data that crosses multiple UTM zones and you want to conduct processing against all the data then you two choices:

1. Choose a non-UTM projection that does not have the UTM boundary limits (eg Lambert Conformal Conic with 2 standard latitudes is normally a good one);
2. Choose a geodetic longitude/latitude datum;

After choosing you new projection/datum project your UTM data into it and then conduct your analysis.

Note. If you choose a geodetic datum then note that your processing time will be a lot slower (Oracle indicates this in the Spatial documentation) and also less precise as the max tolerance you can use is 0.05m ie 5cm. Projected data processing is much faster so consider 1) before 2).

Finally, you could try posting you question on the Oracle Spatial Forum on Technet

> And thanks for sharing you knowledge, your posts helped me a lot!!

If you have any questions that might result in a new blog post let me know.

regards
Simon

— Simon Oct 5, 02:24 pm #

<<Split Sdo_Geometry Linestring at a known point >>Using Oracle's SDO_NN Operator - Some examples

Name		Remember
E-mail
http://
Message
	Textile Help