Converting distances and units of measure in Oracle Locator
Ever wanted to know what a decimal degree was in meters, nautical miles or feet?
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.