Unit of Measurement Support

2.11 Unit of Measurement Support

Geometry functions that involve measurement allow an optional unit parameter to specify the unit of measurement for a specified distance or area, if a georeferenced coordinate system (SDO_SRID value) is associated with the input geometry or geometries.

The unit parameter is not valid for geometries with a null SDO_SRID value (that is, an orthogonal Cartesian system). For information about support for coordinate systems, see Coordinate Systems (Spatial Reference Systems).

The default unit of measure is the one associated with the georeferenced coordinate system. The unit of measure for most coordinate systems is the meter, and in these cases the default unit for distances is meter and the default unit for areas is square meter. By using the unit parameter, however, you can have Spatial automatically convert and return results that are more meaningful to application users, for example, displaying the distance to a restaurant in miles.

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 (described in SDO_UNITS_OF_MEASURE Table). For example, 'unit=KM' in the following example (using data and definitions from Example 6-17 in Example of Coordinate System Transformation) specifies kilometers as the unit of measurement:

SELECT c.name, SDO_GEOM.SDO_LENGTH(c.shape, m.diminfo, 'unit=KM')
  FROM cola_markets_cs c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS_CS' AND m.column_name = 'SHAPE';

Spatial uses the information in the SDO_UNITS_OF_MEASURE table (described in SDO_UNITS_OF_MEASURE Table) to determine which unit names are valid and what ratios to use in comparing or converting between different units. For convenience, you can also use the following legacy views to see the angle, area, and distance units of measure:

MDSYS.SDO_ANGLE_UNITS (described in MDSYS.SDO_ANGLE_UNITS View)
MDSYS.SSDO_AREA_UNITS (described in MDSYS.SDO_AREA_UNITS View)
MDSYS.SSDO_DIST_UNITS (described in MDSYS.SDO_DIST_UNITS View)

Creating a User-Defined Unit of Measurement

Parent topic: Spatial Data Types and Metadata

2.11.1 Creating a User-Defined Unit of Measurement

If the area and distance units of measurement supplied by Oracle are not sufficient for your needs, you can create user-defined area and distance units. (You cannot create a user-defined angle unit.) To do so, you must connect to the database as a user that has been granted the DBA role, and insert a row for each desired unit to the SDO_UNITS_OF_MEASURE table (described in SDO_UNITS_OF_MEASURE Table)

Table 2-16 lists the columns in the SDO_UNITS_OF_MEASURE table and the requirements and recommendations for each if you are inserting a row for a user-defined unit of measurement.

Table 2-16 SDO_UNITS_OF_MEASURE Table Entries for User-Defined Unit

Column Name	Description
UOM_ID	Any unit of measure ID number not currently used for an Oracle-supplied unit or another user-defined unit. Example: `1000001`
UNIT_OF_MEAS_NAME	Name of the user-defined unit of measurement. Example: `HALF_METER`
SHORT_NAME	Optional short name (if any) of the unit of measurement.
UNIT_OF_MEAS_TYPE	Type of measure for which the unit is used. Must be either `area` (for an area unit) or `length` (for a distance unit).
TARGET_UOM_ID	Optional, but for support purposes you should enter one of the following: `10008` for an area unit (10008 = UOM_ID for SQ_METER) or `10032` for a distance unit (10032 = UOM_ID for METER).
FACTOR_B	For a value that can be expressed as a floating point number, specify how many square meters (for an area unit) or meters (for a distance unit) are equal to one of the user-defined unit. For example, for a unit defined as one-half of a standard meter, specify: `.5` For a value that cannot be expressed as a simple floating point number, specify the dividend for the expression FACTOR_B/FACTOR_C that determines how many square meters (for an area unit) or meters (for a distance unit) are equal to one of the user-defined unit.
FACTOR_C	For a value that can be expressed as a floating point number, specify 1. For a value that cannot be expressed as a simple floating point number, specify the divisor for the expression FACTOR_B/FACTOR_C that determines how many square meters (for an area unit) or meters (for a distance unit) are equal to one of the user-defined unit.
INFORMATION_SOURCE	Specify the following: `USER_DEFINED`
DATA_SOURCE	A phrase briefly describing the unit. Example: `User-defined half meter`
IS_LEGACY	Specify the following: `FALSE`.
LEGACY_CODE	(Do not use this for a user-defined unit.)

Example 2-22 creates a user-defined distance unit named HALF_METER, and uses it in a query to find all customers within 400,000 half-meters (200 kilometers) of a specified store.

Example 2-22 Creating and Using a User-Defined Unit of Measurement

-- Distance unit: HALF_METER
-- FACTOR_B specifies how many meters = one of this unit.

INSERT INTO MDSYS.SDO_UNITS_OF_MEASURE
  (UOM_ID, UNIT_OF_MEAS_NAME, UNIT_OF_MEAS_TYPE, TARGET_UOM_ID,
   FACTOR_B, FACTOR_C, INFORMATION_SOURCE, DATA_SOURCE, IS_LEGACY)
VALUES
  (100001, 'HALF_METER', 'length', 100001,
   .5, 1, 'User-defined half meter', 'USER_DEFINED', 'FALSE');
 
. . .
-- Find all the customers within 400,000 half-meters of store_id = 101
SELECT /*+ordered*/
    c.customer_id,
    c.first_name,
    c.last_name
FROM stores s,
    customers c
WHERE s.store_id = 101
AND sdo_within_distance (c.cust_geo_location,
    s.store_geo_location,
    'distance = 400000 unit = HALF_METER') = 'TRUE';
 
CUSTOMER_ID FIRST_NAME                     LAST_NAME                           
----------- ------------------------------ ------------------------------      
       1005 Carla                          Rodriguez                           
       1004 Thomas                         Williams                            
       1003 Marian                         Chang                               
       1001 Alexandra                      Nichols

Parent topic: Unit of Measurement Support