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)
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: |
UNIT_OF_MEAS_NAME |
Name of the user-defined unit of measurement. Example: |
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 |
TARGET_UOM_ID |
Optional, but for support purposes you should enter one of the following: |
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: 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: |
DATA_SOURCE |
A phrase briefly describing the unit. Example: |
IS_LEGACY |
Specify the following: |
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