1. Spatial Developer's Guide
  2. Conceptual and Usage Information
  3. Coordinate Systems (Spatial Reference Systems)
  4. Coordinate Systems Data Structures

6.7 Coordinate Systems Data Structures

The coordinate systems functions and procedures use information provided in the tables and views supplied with Oracle Spatial. The tables and views are part of the MDSYS schema; however, public synonyms are defined, so you do not need to specify MDSYS. before the table or view name.

The definitions and data in these tables and views are based on the EPSG data model and dataset, as explained in EPSG Model and Spatial.

The coordinate system tables fit into several general categories:

  • Coordinate system general information: SDO_COORD_SYS, SDO_COORD_REF_SYS

  • Elements or aspects of a coordinate system definition: SDO_DATUMS, SDO_ELLIPSOIDS, SDO_PRIME_MERIDIANS

  • Datum transformation support: SDO_COORD_OPS, SDO_COORD_OP_METHODS, SDO_COORD_OP_PARAM_USE, SDO_COORD_OP_PARAM_VALS, SDO_COORD_OP_PARAMS, SDO_COORD_OP_PATHS, SDO_PREFERRED_OPS_SYSTEM, SDO_PREFERRED_OPS_USER

  • Others related to coordinate system definition: SDO_COORD_AXES, SDO_COORD_AXIS_NAMES, SDO_UNITS_OF_MEASURE

Several views are provided that are identical to or subsets of coordinate system tables:

  • SDO_COORD_REF_SYSTEM, which contains the same columns as the SDO_COORD_REF_SYS table. Use the SDO_COORD_REF_SYSTEM view instead of the COORD_REF_SYS table for any insert, update, or delete operations.

  • Subsets of SDO_DATUMS, selected according to the value in the DATUM_TYPE column: SDO_DATUM_ENGINEERING, SDO_DATUM_GEODETIC, SDO_DATUM_VERTICAL.

  • Subsets of SDO_COORD_REF_SYS, selected according to the value in the COORD_REF_SYS_KIND column: SDO_CRS_COMPOUND, SDO_CRS_ENGINEERING, SDO_CRS_GEOCENTRIC, SDO_CRS_GEOGRAPHIC2D, SDO_CRS_GEOGRAPHIC3D, SDO_CRS_PROJECTED, SDO_CRS_VERTICAL.

Most of the rest of this section explains these tables and views, in alphabetical order. (Many column descriptions are adapted or taken from EPSG descriptions.) Relationships Among Coordinate System Tables and Views describes relationships among the tables and views, and it lists EPSG table names and their corresponding Oracle Spatial names. Finding Information About EPSG-Based Coordinate Systems describes how to find information about EPSG-based coordinate systems, and it provides several examples.

In addition to the tables and views in this section, Spatial provides several legacy tables whose definitions and data match those of certain Spatial system tables used in previous releases. Legacy Tables and Views describes the legacy tables.

Note:

You should not modify or delete any Oracle-supplied information in any of the tables or views that are used for coordinate system support.

If you want to create a user-defined coordinate system, see Creating a User-Defined Coordinate Reference System.

Parent topic: Coordinate Systems (Spatial Reference Systems)

6.7.1 SDO_COORD_AXES Table

The SDO_COORD_AXES table contains one row for each coordinate system axis definition. This table contains the columns shown in Table 6-1.

Table 6-1 SDO_COORD_AXES Table

Column Name Data Type Description

COORD_SYS_ID

NUMBER(10)

ID number of the coordinate system to which this axis applies.

COORD_AXIS_NAME_ID

NUMBER(10)

ID number of a coordinate system axis name. Matches a value in the COORD_AXIS_NAME_ID column of the SDO_COORD_AXIS_NAMES table (described in SDO_COORD_AXIS_NAMES Table). Example: 9901 (for Geodetic latitude)

COORD_AXIS_ORIENTATION

VARCHAR2(24)

The direction of orientation for the coordinate system axis. Example: east

COORD_AXIS_ABBREVIATION

VARCHAR2(24)

The abbreviation for the coordinate system axis orientation. Example: E

UOM_ID

NUMBER(10)

ID number of the unit of measurement associated with the axis. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in SDO_UNITS_OF_MEASURE Table).

ORDER

NUMBER(5)

Position of this axis within the coordinate system (1, 2, or 3).

Parent topic: Coordinate Systems Data Structures

6.7.2 SDO_COORD_AXIS_NAMES Table

The SDO_COORD_AXIS_NAMES table contains one row for each axis that can be used in a coordinate system definition. This table contains the columns shown in Table 6-2.

Table 6-2 SDO_COORD_AXIS_NAMES Table

Column Name Data Type Description

COORD_AXIS_NAME_ID

NUMBER(10)

ID number of the coordinate axis name. Example: 9926

COORD_AXIS_NAME

VARCHAR2(80)

Name of the coordinate axis. Example: Spherical latitude

Parent topic: Coordinate Systems Data Structures

6.7.3 SDO_COORD_OP_METHODS Table

The SDO_COORD_OP_METHODS table contains one row for each coordinate systems transformation method. This table contains the columns shown in Table 6-3.

Table 6-3 SDO_COORD_OP_METHODS Table

Column Name Data Type Description

COORD_OP_METHOD_ID

NUMBER(10)

ID number of the coordinate system transformation method. Example: 9613

COORD_OP_METHOD_NAME

VARCHAR2(50)

Name of the method. Example: NADCON

LEGACY_NAME

VARCHAR2(50)

Name for this transformation method in the legacy WKT strings. This name might differ syntactically from the name used by EPSG.

REVERSE_OP

NUMBER(1)

Contains 1 if reversal of the transformation (from the current target coordinate system to the source coordinate system) can be achieved by reversing the sign of each parameter value; contains 0 if a separate operation must be defined for reversal of the transformation.

INFORMATION_SOURCE

VARCHAR2(254)

Origin of this information. Example: US Coast and geodetic Survey - http://www.ngs.noaa.gov

DATA_SOURCE

VARCHAR2(40)

Organization providing the data for this record. Example: EPSG

IS_IMPLEMENTED_FORWARD

NUMBER(1)

Contains 1 if the forward operation is implemented; contains 0 if the forward operation is not implemented.

IS_IMPLEMENTED_REVERSE

NUMBER(1)

Contains 1 if the reverse operation is implemented; contains 0 if the reverse operation is not implemented.

Parent topic: Coordinate Systems Data Structures

6.7.4 SDO_COORD_OP_PARAM_USE Table

The SDO_COORD_OP_PARAM_USE table contains one row for each combination of transformation method and transformation operation parameter that is available for use. This table contains the columns shown in Table 6-4.

Table 6-4 SDO_COORD_OP_PARAM_USE Table

Column Name Data Type Description

COORD_OP_METHOD_ID

NUMBER(10)

ID number of the coordinate system transformation method. Matches a value in the COORD_OP_METHOD_ID column of the COORD_OP_METHODS table (described in SDO_COORD_OP_METHODS Table).

PARAMETER_ID

NUMBER(10)

ID number of the parameter for transformation operations. Matches a value in the PARAMETER_ID column of the SDO_COORD_OP_PARAMS table (described in SDO_COORD_OP_PARAMS Table).

LEGACY_PARAM_NAME

VARCHAR2(80)

Open GeoSpatial Consortium (OGC) name for the parameter.

SORT_ORDER

NUMBER(5)

A number indicating the position of this parameter in the sequence of parameters for this method. Example: 2 for the second parameter

PARAM_SIGN_REVERSAL

VARCHAR2(3)

Yes if reversal of the transformation (from the current target coordinate system to the source coordinate system) can be achieved by reversing the sign of each parameter value; No if a separate operation must be defined for reversal of the transformation.

Parent topic: Coordinate Systems Data Structures

6.7.5 SDO_COORD_OP_PARAM_VALS Table

The SDO_COORD_OP_PARAM_VALS table contains information about parameter values for each coordinate system transformation method. This table contains the columns shown in Table 6-5.

Table 6-5 SDO_COORD_OP_PARAM_VALS Table

Column Name Data Type Description

COORD_OP_ID

NUMBER(10)

ID number of the coordinate transformation operation. Matches a value in the COORD_OP_ID column of the SDO_COORD_OPS table (described in SDO_COORD_OPS Table).

COORD_OP_METHOD_ID

NUMBER(10)

Coordinate operation method ID. Must match a COORD_OP_METHOD_ID value in the SDO_COORD_OP_METHODS table (see SDO_COORD_OP_METHODS Table).

PARAMETER_ID

NUMBER(10)

ID number of the parameter for transformation operations. Matches a value in the PARAMETER_ID column of the SDO_COORD_OP_PARAMS table (described in SDO_COORD_OP_PARAMS Table).

PARAMETER_VALUE

FLOAT(49)

Value of the parameter for this operation.

PARAM_VALUE_FILE_REF

VARCHAR2(254)

Name of the file (as specified in the original EPSG database) containing the value data, if a single value for the parameter is not sufficient.

PARAM_VALUE_FILE

CLOB

The ASCII content of the file specified in the PARAM_VALUE_FILE_REF column. Used only for grid file parameters (for NADCON, NTv2, and height transformations "Geographic3D to Geographic2D+GravityRelatedHeight").

PARAM_VALUE_XML

XMLTYPE

An XML representation of the content of the file specified in the PARAM_VALUE_FILE_REF column. (Optional, and currently only used for documentation.)

UOM_ID

NUMBER(10)

ID number of the unit of measurement associated with the operation. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in SDO_UNITS_OF_MEASURE Table).

Parent topic: Coordinate Systems Data Structures

6.7.6 SDO_COORD_OP_PARAMS Table

The SDO_COORD_OP_PARAMS table contains one row for each available parameter for transformation operations. This table contains the columns shown in Table 6-6.

Table 6-6 SDO_COORD_OP_PARAMS Table

Column Name Data Type Description

PARAMETER_ID

NUMBER(10)

ID number of the parameter. Example: 8608

PARAMETER_NAME

VARCHAR2(80)

Name of the operation. Example: X-axis rotation

INFORMATION_SOURCE

VARCHAR2(254)

Origin of this information. Example: EPSG guidance note number 7.

DATA_SOURCE

VARCHAR2(40)

Organization providing the data for this record. Example: EPSG

Parent topic: Coordinate Systems Data Structures

6.7.7 SDO_COORD_OP_PATHS Table

The SDO_COORD_OP_PATHS table contains one row for each atomic step in a concatenated operation. This table contains the columns shown in Table 6-7.

Table 6-7 SDO_COORD_OP_PATHS Table

Column Name Data Type Description

CONCAT_OPERATION_ID

NUMBER(10)

ID number of the concatenation operation. Must match a COORD_OP_ID value in the SDO_COORD_OPS table (described in SDO_COORD_OPS Table) for which the COORD_OP_TYPE value is CONCATENATION.

SINGLE_OPERATION_ID

NUMBER(10)

ID number of the single coordinate operation for this step (atomic operation) in a concatenated operation. Must match a COORD_OP_ID value in the SDO_COORD_OPS table (described in SDO_COORD_OPS Table).

SINGLE_OP_SOURCE_ID

NUMBER(10)

ID number of source coordinate reference system for the single coordinate operation for this step. Must match an SRID value in the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table).

SINGLE_OP_TARGET_ID

NUMBER(10)

ID number of target coordinate reference system for the single coordinate operation for this step. Must match an SRID value in the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table).

OP_PATH_STEP

NUMBER(5)

Sequence number of this step (atomic operation) within this concatenated operation.

Parent topic: Coordinate Systems Data Structures

6.7.8 SDO_COORD_OPS Table

The SDO_COORD_OPS table contains one row for each transformation operation between coordinate systems. This table contains the columns shown in Table 6-8.

Table 6-8 SDO_COORD_OPS Table

Column Name Data Type Description

COORD_OP_ID

NUMBER(10)

ID number of the coordinate transformation operation. Example: 101

COORD_OP_NAME

VARCHAR2(80)

Name of the operation. Example: ED50 to WGS 84 (14)

COORD_OP_TYPE

VARCHAR2(24)

Type of operation. One of the following: CONCATENATED OPERATION, CONVERSION, or TRANSFORMATION

SOURCE_SRID

NUMBER(10)

SRID of the coordinate system from which to perform the transformation. Example: 4230

TARGET_SRID

NUMBER(10)

SRID of the coordinate system into which to perform the transformation. Example: 4326

COORD_TFM_VERSION

VARCHAR2(24)

Name assigned by EPSG to the coordinate transformation. Example: 5Nat-NSea90

COORD_OP_VARIANT

NUMBER(5)

A variant of the more generic method specified in COORD_OP_METHOD_ID. Example: 14

COORD_OP_METHOD_ID

NUMBER(10)

Coordinate operation method ID. Must match a COORD_OP_METHOD_ID value in the SDO_COORD_OP_METHODS table (see SDO_COORD_OP_METHODS Table). Several operations can use a method. Example: 9617

UOM_ID_SOURCE_OFFSETS

NUMBER(10)

ID number of the unit of measurement for offsets in the source coordinate system. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in SDO_UNITS_OF_MEASURE Table).

UOM_ID_TARGET_OFFSETS

NUMBER(10)

ID number of the unit of measurement for offsets in the target coordinate system. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in SDO_UNITS_OF_MEASURE Table).

INFORMATION_SOURCE

VARCHAR2(254)

Origin of this information. Example: Institut de Geomatica; Barcelona

DATA_SOURCE

VARCHAR2(40)

Organization providing the data for this record. Example: EPSG

SHOW_OPERATION

NUMBER(3)

(Not currently used.)

IS_LEGACY

VARCHAR2(5)

TRUE if the operation was included in Oracle Spatial before release 10.2; FALSE if the operation was new in Oracle Spatial release 10.2.

LEGACY_CODE

NUMBER(10)

For any EPSG coordinate transformation operation that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the COORD_OP_ID value of the legacy coordinate transformation operation.

REVERSE_OP

NUMBER(1)

Contains 1 if reversal of the transformation (from the current target coordinate system to the source coordinate system) is defined as achievable by reversing the sign of each parameter value; contains 0 if a separate operation must be defined for reversal of the transformation. If REVERSE_OP contains 1, the operations that are actually implemented are indicated by the values for IS_IMPLEMENTED_FORWARD and IS_IMPLEMENTED_REVERSE.

IS_IMPLEMENTED_FORWARD

NUMBER(1)

Contains 1 if the forward operation is implemented; contains 0 if the forward operation is not implemented.

IS_IMPLEMENTED_REVERSE

NUMBER(1)

Contains 1 if the reverse operation is implemented; contains 0 if the reverse operation is not implemented.

Parent topic: Coordinate Systems Data Structures

6.7.9 SDO_COORD_REF_SYS Table

The SDO_COORD_REF_SYS table contains one row for each coordinate reference system. This table contains the columns shown in Table 6-9. (The SDO_COORD_REF_SYS table is roughly patterned after the EPSG Coordinate Reference System table.)

Note:

If you need to perform an insert, update, or delete operation, you must perform it on the SDO_COORD_REF_SYSTEM view, which contains the same columns as the SDO_COORD_REF_SYS table. The SDO_COORD_REF_SYSTEM view is described in SDO_COORD_REF_SYSTEM View.

Table 6-9 SDO_COORD_REF_SYS Table

Column Name Data Type Description

SRID

NUMBER(10)

ID number of the coordinate reference system. Example: 8307

COORD_REF_SYS_NAME

VARCHAR2(80)

Name of the coordinate reference system. Example: Longitude / Latitude (WGS 84)

COORD_REF_SYS_KIND

VARCHAR2(24)

Category for the coordinate system. Example: GEOGRAPHIC2D

COORD_SYS_ID

NUMBER(10)

ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see SDO_COORD_SYS Table).

DATUM_ID

NUMBER(10)

ID number of the datum used for the coordinate reference system. Null for a projected coordinate system. For a geodetic coordinate system, must match a DATUM_ID value in the SDO_DATUMS table (see SDO_DATUMS Table). Example: 10115

GEOG_CRS_DATUM_ID

NUMBER(10)

ID number of the datum used for the coordinate reference system. For a projected coordinate system, must match the DATUM_ID value (in the SDO_DATUMS table, described in SDO_DATUMS Table) of the geodetic coordinate system on which the projected coordinate system is based. For a geodetic coordinate system, must match the DATUM_ID value. Example: 10115

SOURCE_GEOG_SRID

NUMBER(10)

For a projected coordinate reference system, the ID number for the associated geodetic coordinate system.

PROJECTION_CONV_ID

NUMBER(10)

For a projected coordinate reference system, the COORD_OP_ID value of the conversion operation used to convert the projected coordinated system to and from the source geographic coordinate system.

CMPD_HORIZ_SRID

NUMBER(10)

(EPSG-assigned value; not used by Oracle Spatial. The EPSG description is: "For compound CRS only, the code of the horizontal component of the Compound CRS.")

CMPD_VERT_SRID

NUMBER(10)

(EPSG-assigned value; not used by Oracle Spatial. The EPSG description is: "For compound CRS only, the code of the vertical component of the Compound CRS.")

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition for the coordinate system (Oracle for all rows supplied by Oracle).

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

IS_LEGACY

VARCHAR2(5)

TRUE if the coordinate system definition was included in Oracle Spatial before release 10.2; FALSE if the coordinate system definition was new in Oracle Spatial release 10.2.

LEGACY_CODE

NUMBER(10)

For any EPSG coordinate reference system that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the SRID value of the legacy coordinate system.

LEGACY_WKTEXT

VARCHAR2(2046)

If IS_LEGACY is TRUE, contains the well-known text description of the coordinate system. Example: GEOGCS [ "Longitude / Latitude (WGS 84)", DATUM ["WGS 84", SPHEROID ["WGS 84", 6378137, 298.257223563]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]]

LEGACY_CS_BOUNDS

SDO_GEOMETRY

For a legacy coordinate system, the dimensional boundary (if any).

IS_VALID

VARCHAR2(5)

TRUE if the EPSG record for the coordinate reference system is completely defined; FALSE if the EPSG record for the coordinate reference system is not completely defined.

SUPPORTS_SDO_GEOMETRY

VARCHAR2(5)

TRUE if the COORD_REF_SYS_KIND column contains ENGINEERING, GEOGRAPHIC2D, or PROJECTED CRS; FALSE if the COORD_REF_SYS_KIND column contains any other value.

See also the information about the following views that are defined based on the value of the COORD_REF_SYS_KIND column:

Parent topic: Coordinate Systems Data Structures

6.7.10 SDO_COORD_REF_SYSTEM View

The SDO_COORD_REF_SYSTEM view contains the same columns as the SDO_COORD_REF_SYS table, which is described in SDO_COORD_REF_SYS Table. However, the SDO_COORD_REF_SYSTEM view has a trigger defined on it, so that any insert, update, or delete operations performed on the view cause all relevant Spatial system tables to have the appropriate operations performed on them.

Therefore, if you need to perform an insert, update, or delete operation, you must perform it on the SDO_COORD_REF_SYSTEM view, not the SDO_COORD_REF_SYS table.

Parent topic: Coordinate Systems Data Structures

6.7.11 SDO_COORD_SYS Table

The SDO_COORD_SYS table contains rows with information about coordinate systems. This table contains the columns shown in Table 6-10. (The SDO_COORD_SYS table is roughly patterned after the EPSG Coordinate System table, where a coordinate system is described as "a pair of reusable axes.")

Table 6-10 SDO_COORD_SYS Table

Column Name Data Type Description

COORD_SYS_ID

NUMBER(10)

ID number of the coordinate system. Example: 6405

COORD_SYS_NAME

VARCHAR2(254)

Name of the coordinate system. Example: Ellipsoidal 2D CS. Axes: latitude, longitude. Orientations: north, east. UoM: dec deg

COORD_SYS_TYPE

VARCHAR2(24)

Type of coordinate system. Example: ellipsoidal

DIMENSION

NUMBER(5)

Number of dimensions represented by the coordinate system.

INFORMATION_SOURCE

VARCHAR2(254)

Origin of this information.

DATA_SOURCE

VARCHAR2(50)

Organization providing the data for this record.

Parent topic: Coordinate Systems Data Structures

6.7.12 SDO_CRS_COMPOUND View

The SDO_CRS_COMPOUND view contains selected information from the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table) where the COORD_REF_SYS_KIND column value is COMPOUND. (For an explanation of compound coordinate reference systems, see Compound Coordinate Reference Systems.) This view contains the columns shown in Table 6-11.

Table 6-11 SDO_CRS_COMPOUND View

Column Name Data Type Description

SRID

NUMBER(10)

ID number of the coordinate reference system.

COORD_REF_SYS_NAME

VARCHAR2(80)

Name of the coordinate reference system.

CMPD_HORIZ_SRID

NUMBER(10)

(EPSG-assigned value; not used by Oracle Spatial. The EPSG description is: "For compound CRS only, the code of the horizontal component of the Compound CRS.")

CMPD_VERT_SRID

NUMBER(10)

(EPSG-assigned value; not used by Oracle Spatial. The EPSG description is: "For compound CRS only, the code of the vertical component of the Compound CRS.")

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition for the coordinate system (Oracle for all rows supplied by Oracle).

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

Parent topic: Coordinate Systems Data Structures

6.7.13 SDO_CRS_ENGINEERING View

The SDO_CRS_ENGINEERING view contains selected information from the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table) where the COORD_REF_SYS_KIND column value is ENGINEERING. This view contains the columns shown in Table 6-12.

Table 6-12 SDO_CRS_ENGINEERING View

Column Name Data Type Description

SRID

NUMBER(10)

ID number of the coordinate reference system.

COORD_REF_SYS_NAME

VARCHAR2(80)

Name of the coordinate reference system.

COORD_SYS_ID

NUMBER(10)

ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see SDO_COORD_SYS Table).

DATUM_ID

NUMBER(10)

ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see SDO_DATUMS Table).

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition for the coordinate system (Oracle for all rows supplied by Oracle).

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

Parent topic: Coordinate Systems Data Structures

6.7.14 SDO_CRS_GEOCENTRIC View

The SDO_CRS_GEOCENTRIC view contains selected information from the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table) where the COORD_REF_SYS_KIND column value is GEOCENTRIC. This view contains the columns shown in Table 6-13.

Table 6-13 SDO_CRS_GEOCENTRIC View

Column Name Data Type Description

SRID

NUMBER(10)

ID number of the coordinate reference system.

COORD_REF_SYS_NAME

VARCHAR2(80)

Name of the coordinate reference system.

COORD_SYS_ID

NUMBER(10)

ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see SDO_COORD_SYS Table).

DATUM_ID

NUMBER(10)

ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see SDO_DATUMS Table).

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition for the coordinate system (Oracle for all rows supplied by Oracle).

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

Parent topic: Coordinate Systems Data Structures

6.7.15 SDO_CRS_GEOGRAPHIC2D View

The SDO_CRS_GEOGRAPHIC2D view contains selected information from the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table) where the COORD_REF_SYS_KIND column value is GEOGRAPHIC2D. This view contains the columns shown in Table 6-14.

Table 6-14 SDO_CRS_GEOGRAPHIC2D View

Column Name Data Type Description

SRID

NUMBER(10)

ID number of the coordinate reference system.

COORD_REF_SYS_NAME

VARCHAR2(80)

Name of the coordinate reference system.

COORD_SYS_ID

NUMBER(10)

ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see SDO_COORD_SYS Table).

DATUM_ID

NUMBER(10)

ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see SDO_DATUMS Table).

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition for the coordinate system (Oracle for all rows supplied by Oracle).

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

Parent topic: Coordinate Systems Data Structures

6.7.16 SDO_CRS_GEOGRAPHIC3D View

The SDO_CRS_GEOGRAPHIC3D view contains selected information from the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table) where the COORD_REF_SYS_KIND column value is GEOGRAPHIC3D. (For an explanation of geographic 3D coordinate reference systems, see Geographic 3D Coordinate Reference Systems.)

Note:

SDO_CRS_GEOGRAPHIC3D view is not supported in Oracle Autonomous Database Serverless deployments.

This view contains the columns shown in Table 6-15.

Table 6-15 SDO_CRS_GEOGRAPHIC3D View

Column Name Data Type Description

SRID

NUMBER(10)

ID number of the coordinate reference system.

COORD_REF_SYS_NAME

VARCHAR2(80)

Name of the coordinate reference system.

COORD_SYS_ID

NUMBER(10)

ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see SDO_COORD_SYS Table).

DATUM_ID

NUMBER(10)

ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see SDO_DATUMS Table).

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition for the coordinate system (Oracle for all rows supplied by Oracle).

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

Parent topic: Coordinate Systems Data Structures

6.7.17 SDO_CRS_PROJECTED View

The SDO_CRS_PROJECTED view contains selected information from the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table) where the COORD_REF_SYS_KIND column value is PROJECTED. This view contains the columns shown in Table 6-16.

Table 6-16 SDO_CRS_PROJECTED View

Column Name Data Type Description

SRID

NUMBER(10)

ID number of the coordinate reference system.

COORD_REF_SYS_NAME

VARCHAR2(80)

Name of the coordinate reference system.

COORD_SYS_ID

NUMBER(10)

ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see SDO_COORD_SYS Table).

SOURCE_GEOG_SRID

NUMBER(10)

ID number for the associated geodetic coordinate system.

PROJECTION_CONV_ID

NUMBER(10)

COORD_OP_ID value of the conversion operation used to convert the projected coordinated system to and from the source geographic coordinate system.

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition for the coordinate system (Oracle for all rows supplied by Oracle).

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

Parent topic: Coordinate Systems Data Structures

6.7.18 SDO_CRS_VERTICAL View

The SDO_CRS_VERTICAL view contains selected information from the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table) where the COORD_REF_SYS_KIND column value is VERTICAL. This view contains the columns shown in Table 6-17.

Table 6-17 SDO_CRS_VERTICAL View

Column Name Data Type Description

SRID

NUMBER(10)

ID number of the coordinate reference system.

COORD_REF_SYS_NAME

VARCHAR2(80)

Name of the coordinate reference system.

COORD_SYS_ID

NUMBER(10)

ID number of the coordinate system used for the coordinate reference system. Must match a COORD_SYS_ID value in the SDO_COORD_SYS table (see SDO_COORD_SYS Table).

DATUM_ID

NUMBER(10)

ID number of the datum used for the coordinate reference system. Must match a DATUM_ID value in the SDO_DATUMS table (see SDO_DATUMS Table).

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition for the coordinate system (Oracle for all rows supplied by Oracle).

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

Parent topic: Coordinate Systems Data Structures

6.7.19 SDO_DATUM_ENGINEERING View

The SDO_DATUM_ENGINEERING view contains selected information from the SDO_DATUMS table (described in SDO_DATUMS Table) where the DATUM_TYPE column value is ENGINEERING. This view contains the columns shown in Table 6-18.

Table 6-18 SDO_DATUM_ENGINEERING View

Column Name Data Type Description

DATUM_ID

NUMBER(10)

ID number of the datum.

DATUM_NAME

VARCHAR2(80)

Name of the datum.

ELLIPSOID_ID

NUMBER(10)

ID number of the ellipsoid used in the datum definition. Must match an ELLIPSOID_ID value in the SDO_ELLIPSOIDS table (see SDO_ELLIPSOIDS Table). Example: 8045

PRIME_MERIDIAN_ID

NUMBER(10)

ID number of the prime meridian used in the datum definition. Must match a PRIME_MERIDIAN_ID value in the SDO_PRIME_MERIDIANS table (see SDO_PRIME_MERIDIANS Table). Example: 8950

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition of the datum. Example: Ordnance Survey of Great Britain.

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

SHIFT_X

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the x-axis.

SHIFT_Y

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the y-axis.

SHIFT_Z

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the z-axis.

ROTATE_X

NUMBER

Number of arc-seconds of rotation about the x-axis.

ROTATE_Y

NUMBER

Number of arc-seconds of rotation about the y-axis.

ROTATE_Z

NUMBER

Number of arc-seconds of rotation about the z-axis.

SCALE_ADJUST

NUMBER

A value to be used in adjusting the X, Y, and Z values after any shifting and rotation, according to the formula: 1.0 + (SCALE_ADJUST * 10-6)

Parent topic: Coordinate Systems Data Structures

6.7.20 SDO_DATUM_GEODETIC View

The SDO_DATUM_GEODETIC view contains selected information from the SDO_DATUMS table (described in SDO_DATUMS Table) where the DATUM_TYPE column value is GEODETIC. This view contains the columns shown in Table 6-19.

Table 6-19 SDO_DATUM_GEODETIC View

Column Name Data Type Description

DATUM_ID

NUMBER(10)

ID number of the datum.

DATUM_NAME

VARCHAR2(80)

Name of the datum.

ELLIPSOID_ID

NUMBER(10)

ID number of the ellipsoid used in the datum definition. Must match an ELLIPSOID_ID value in the SDO_ELLIPSOIDS table (see SDO_ELLIPSOIDS Table). Example: 8045

PRIME_MERIDIAN_ID

NUMBER(10)

ID number of the prime meridian used in the datum definition. Must match a PRIME_MERIDIAN_ID value in the SDO_PRIME_MERIDIANS table (see SDO_PRIME_MERIDIANS Table). Example: 8950

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition of the datum. Example: Ordnance Survey of Great Britain.

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

SHIFT_X

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the x-axis.

SHIFT_Y

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the y-axis.

SHIFT_Z

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the z-axis.

ROTATE_X

NUMBER

Number of arc-seconds of rotation about the x-axis.

ROTATE_Y

NUMBER

Number of arc-seconds of rotation about the y-axis.

ROTATE_Z

NUMBER

Number of arc-seconds of rotation about the z-axis.

SCALE_ADJUST

NUMBER

A value to be used in adjusting the X, Y, and Z values after any shifting and rotation, according to the formula: 1.0 + (SCALE_ADJUST * 10-6)

Parent topic: Coordinate Systems Data Structures

6.7.21 SDO_DATUM_VERTICAL View

The SDO_DATUM_VERTICAL view contains selected information from the SDO_DATUMS table (described in SDO_DATUMS Table) where the DATUM_TYPE column value is VERTICAL. This view contains the columns shown in Table 6-20.

Table 6-20 SDO_DATUM_VERTICAL View

Column Name Data Type Description

DATUM_ID

NUMBER(10)

ID number of the datum.

DATUM_NAME

VARCHAR2(80)

Name of the datum.

ELLIPSOID_ID

NUMBER(10)

ID number of the ellipsoid used in the datum definition. Must match an ELLIPSOID_ID value in the SDO_ELLIPSOIDS table (see SDO_ELLIPSOIDS Table). Example: 8045

PRIME_MERIDIAN_ID

NUMBER(10)

ID number of the prime meridian used in the datum definition. Must match a PRIME_MERIDIAN_ID value in the SDO_PRIME_MERIDIANS table (see SDO_PRIME_MERIDIANS Table). Example: 8950

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition of the datum. Example: Ordnance Survey of Great Britain.

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle).

SHIFT_X

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the x-axis.

SHIFT_Y

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the y-axis.

SHIFT_Z

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the z-axis.

ROTATE_X

NUMBER

Number of arc-seconds of rotation about the x-axis.

ROTATE_Y

NUMBER

Number of arc-seconds of rotation about the y-axis.

ROTATE_Z

NUMBER

Number of arc-seconds of rotation about the z-axis.

SCALE_ADJUST

NUMBER

A value to be used in adjusting the X, Y, and Z values after any shifting and rotation, according to the formula: 1.0 + (SCALE_ADJUST * 10-6)

Parent topic: Coordinate Systems Data Structures

6.7.22 SDO_DATUMS Table

The SDO_DATUMS table contains one row for each datum. This table contains the columns shown in Table 6-21.

Table 6-21 SDO_DATUMS Table

Column Name Data Type Description

DATUM_ID

NUMBER(10)

ID number of the datum. Example: 10115

DATUM_NAME

VARCHAR2(80)

Name of the datum. Example: WGS 84

DATUM_TYPE

VARCHAR2(24)

Type of the datum. Example: GEODETIC

ELLIPSOID_ID

NUMBER(10)

ID number of the ellipsoid used in the datum definition. Must match an ELLIPSOID_ID value in the SDO_ELLIPSOIDS table (see SDO_ELLIPSOIDS Table). Example: 8045

PRIME_MERIDIAN_ID

NUMBER(10)

ID number of the prime meridian used in the datum definition. Must match a PRIME_MERIDIAN_ID value in the SDO_PRIME_MERIDIANS table (see SDO_PRIME_MERIDIANS Table). Example: 8950

INFORMATION_SOURCE

VARCHAR2(254)

Provider of the definition of the datum. Example: Ordnance Survey of Great Britain.

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle). Example: EPSG

SHIFT_X

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the x-axis.

SHIFT_Y

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the y-axis.

SHIFT_Z

NUMBER

Number of meters to shift the ellipsoid center relative to the center of the WGS 84 ellipsoid on the z-axis.

ROTATE_X

NUMBER

Number of arc-seconds of rotation about the x-axis.

ROTATE_Y

NUMBER

Number of arc-seconds of rotation about the y-axis.

ROTATE_Z

NUMBER

Number of arc-seconds of rotation about the z-axis.

SCALE_ADJUST

NUMBER

A value to be used in adjusting the X, Y, and Z values after any shifting and rotation, according to the formula: 1.0 + (SCALE_ADJUST * 10-6)

IS_LEGACY

VARCHAR2(5)

TRUE if the datum definition was included in Oracle Spatial before release 10.2; FALSE if the datum definition was new in Oracle Spatial release 10.2.

LEGACY_CODE

NUMBER(10)

For any EPSG datum that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the DATUM_ID value of the legacy datum.

See also the information about the following views that are defined based on the value of the DATUM_TYPE column: SDO_DATUM_ENGINEERING (SDO_DATUM_ENGINEERING View), SDO_DATUM_GEODETIC (SDO_DATUM_GEODETIC View), and SDO_DATUM_VERTICAL (SDO_DATUM_VERTICAL View).

Parent topic: Coordinate Systems Data Structures

6.7.23 SDO_ELLIPSOIDS Table

The SDO_ELLIPSOIDS table contains one row for each ellipsoid. This table contains the columns shown in Table 6-22.

Table 6-22 SDO_ELLIPSOIDS Table

Column Name Data Type Description

ELLIPSOID_ID

NUMBER

ID number of the ellipsoid (spheroid). Example: 8045

ELLIPSOID_NAME

VARCHAR2(80)

Name of the ellipsoid. Example: WGS 84

SEMI_MAJOR_AXIS

NUMBER

Radius in meters along the semi-major axis (one-half of the long axis of the ellipsoid).

UOM_ID

NUMBER

ID number of the unit of measurement for the ellipsoid. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in SDO_UNITS_OF_MEASURE Table). Example: 9001

INV_FLATTENING

NUMBER

Inverse flattening of the ellipsoid. That is, 1/f, where f = (a-b)/a, and a is the semi-major axis and b is the semi-minor axis.

SEMI_MINOR_AXIS

NUMBER

Radius in meters along the semi-minor axis (one-half of the short axis of the ellipsoid).

INFORMATION_SOURCE

VARCHAR2(254)

Origin of this information. Example: Kort og Matrikelstyrelsen (KMS), Copenhagen.

DATA_SOURCE

VARCHAR2(40)

Organization that supplied the data for this record (if not Oracle). Example: EPSG

IS_LEGACY

VARCHAR2(5)

TRUE if the ellipsoid definition was included in Oracle Spatial before release 10.2; FALSE if the ellipsoid definition was new in Oracle Spatial release 10.2.

LEGACY_CODE

NUMBER

For any EPSG ellipsoid that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the ELLIPSOID_ID value of the legacy ellipsoid.

Parent topic: Coordinate Systems Data Structures

6.7.24 SDO_PREFERRED_OPS_SYSTEM Table

The SDO_PREFERRED_OPS_SYSTEM table contains one row for each specification of the user-defined default preferred coordinate transformation operation for a source and target SRID combination. If you insert a row into the SDO_PREFERRED_OPS_SYSTEM table, you are overriding the Oracle default operation for transformations between the specified source and target coordinate systems. The SDO_CS.CREATE_OBVIOUS_EPSG_RULES procedure inserts many rows into this table. The SDO_CS.DELETE_ALL_EPSG_RULES procedure deletes all rows from this table if the use_case parameter is null. This table contains the columns shown in Table 6-23.

Table 6-23 SDO_PREFERRED_OPS_SYSTEM Table

Column Name Data Type Description

SOURCE_SRID

NUMBER(10)

ID number of the coordinate system (spatial reference system) from which to perform coordinate transformation, using the operation specified by COORD_OP_ID as the default preferred method for transforming to the specified target SRID.

COORD_OP_ID

NUMBER(10)

ID number of the coordinate transformation operation. Matches a value in the COORD_OP_ID column of the SDO_COORD_OPS table (described in SDO_COORD_OPS Table).

TARGET_SRID

NUMBER(10)

ID number of coordinate system (spatial reference system) into which to perform coordinate transformation using the operation specified by COORD_OP_ID.

Parent topic: Coordinate Systems Data Structures

6.7.25 SDO_PREFERRED_OPS_USER Table

The SDO_PREFERRED_OPS_USER table contains one row for each specification of a user-defined source and target SRID and coordinate transformation operation. If you insert a row into the SDO_PREFERRED_OPS_USER table, you create a custom transformation between the source and target coordinate systems, and you can specify the name (the USE_CASE column value) of the transformation operation as the use_case parameter value with several SDO_CS functions and procedures. If you specify a use case with the SDO_CS.DELETE_ALL_EPSG_RULES procedure, rows associated with that use case are deleted from this table. This table contains the columns shown in Table 6-24.

Table 6-24 SDO_PREFERRED_OPS_USER Table

Column Name Data Type Description

USE_CASE

VARCHAR2(32)

Name of this specification of a source and target SRID and coordinate transformation operation.

SOURCE_SRID

NUMBER(10)

ID number of the coordinate system (spatial reference system) from which to perform the transformation.

COORD_OP_ID

NUMBER(10)

ID number of the coordinate transformation operation. Matches a value in the COORD_OP_ID column of the SDO_COORD_OPS table (described in SDO_COORD_OPS Table).

TARGET_SRID

NUMBER(10)

ID number of the coordinate system (spatial reference system) into which to perform the transformation.

Parent topic: Coordinate Systems Data Structures

6.7.26 SDO_PRIME_MERIDIANS Table

The SDO_PRIME_MERIDIANS table contains one row for each prime meridian that can be used in a datum specification. This table contains the columns shown in Table 6-25.

Table 6-25 SDO_PRIME_MERIDIANS Table

Column Name Data Type Description

PRIME_MERIDIAN_ID

NUMBER(10)

ID number of the prime meridian. Example: 8907

PRIME_MERIDIAN_NAME

VARCHAR2(80)

Name of the prime meridian. Example: Bern

GREENWICH_LONGITUDE

FLOAT(49)

Longitude of the prime meridian as an offset from the Greenwich meridian. Example: 7.26225

UOM_ID

NUMBER(10)

ID number of the unit of measurement for the prime meridian. Matches a value in the UOM_ID column of the SDO_UNITS_OF_MEASURE table (described in SDO_UNITS_OF_MEASURE Table). Example: 9110 for sexagesimal degree

INFORMATION_SOURCE

VARCHAR2(254)

Origin of this information. Example: Bundesamt fur Landestopographie

DATA_SOURCE

VARCHAR2(254)

Organization that supplied the data for this record (if not Oracle). Example: EPSG

Parent topic: Coordinate Systems Data Structures

6.7.27 SDO_UNITS_OF_MEASURE Table

The SDO_UNITS_OF_MEASURE table contains one row for each unit of measurement. This table contains the columns shown in Table 6-26.

Table 6-26 SDO_UNITS_OF_MEASURE Table

Column Name Data Type Description

UOM_ID

NUMBER(10)

ID number of the unit of measurement. Example: 10032

UNIT_OF_MEAS_NAME

VARCHAR2(2083)

Name of the unit of measurement; can also be a URL or URI. Example: Meter

SHORT_NAME

VARCHAR2(80)

Short name (if any) of the unit of measurement. Example: METER

UNIT_OF_MEAS_TYPE

VARCHAR2(50)

Type of measure for which the unit is used: angle for angle unit, area for area unit, length for distance unit, scale for scale unit, or volume for volume unit.

TARGET_UOM_ID

NUMBER(10)

ID number of a target unit of measurement. Corresponds to the TARGET_UOM_CODE column in the EPSG Unit of Measure table, which has the following description: "Other UOM of the same type into which the current UOM can be converted using the formula (POSC); POSC factors A and D always equal zero for EPSG supplied units of measure."

FACTOR_B

NUMBER

Corresponds to the FACTOR_B column in the EPSG Unit of Measure table, which has the following description: "A quantity in the target UOM (y) is obtained from a quantity in the current UOM (x) through the conversion: y = (B/C).x"

In a user-defined unit of measurement, FACTOR_B is usually the number of square meters or meters equal to one of the unit. For information about user-defined units, see Creating a User-Defined Unit of Measurement.

FACTOR_C

NUMBER

Corresponds to the FACTOR_C column in the EPSG Unit of Measure table.

For FACTOR_C in a user-defined unit of measurement, see Creating a User-Defined Unit of Measurement.

INFORMATION_SOURCE

VARCHAR2(254)

Origin of this information. Example: ISO 1000.

DATA_SOURCE

VARCHAR2(40)

Organization providing the data for this record. Example: EPSG

IS_LEGACY

VARCHAR2(5)

TRUE if the unit of measurement definition was included in Oracle Spatial before release 10.2; FALSE if the unit of measurement definition was new in Oracle Spatial release 10.2.

LEGACY_CODE

NUMBER(10)

For any EPSG unit of measure that has a semantically identical legacy (in Oracle Spatial before release 10.2) counterpart, the UOM_ID value of the legacy unit of measure.

Parent topic: Coordinate Systems Data Structures

6.7.28 Relationships Among Coordinate System Tables and Views

Because the definitions in Spatial system tables and views are based on the EPSG data model and dataset, the EPSG entity-relationship (E-R) diagram provides a good overview of the relationships among the Spatial coordinate system data structures. The EPSG E-R diagram is included in the following document: http://www.ihsenergy.com/epsg/geod_arch.html

However, Oracle Spatial does not use the following from the EPSG E-R diagram:

  • Area of Use (yellow box in the upper center of the diagram)

  • Deprecation, Alias, and others represented by pink boxes in the lower right corner of the diagram

In addition, Spatial changes the names of some tables to conform to its own naming conventions, and it does not use some tables, as shown in Table 6-27

Table 6-27 EPSG Table Names and Oracle Spatial Names

EPSG Name Oracle Name

Coordinate System

SDO_COORD_SYS

Coordinate Axis

SDO_COORD_AXES

Coordinate Reference System

SDO_COORD_REF_SYSTEM

Area Of Use

(Not used)

Datum

SDO_DATUMS

Prime Meridian

SDO_PRIME_MERIDIANS

Ellipsoid

SDO_ELLIPSOIDS

Unit Of Measure

SDO_UNITS_OF_MEASURE

Coordinate Operation

SDO_COORD_OPS

Coord. Operation Parameter ValueCoord

SDO_COORD_OP_PARAM_VALS

Operation Parameter UsageCoord.

SDO_COORD_OP_PARAM_USE

Operation Parameter

SDO_COORD_OP_PARAMS

Coordinate Operation Path

SDO_COORD_OP_PATHS

Coordinate Operation Method

SDO_COORD_OP_METHODS

Change

(Not used)

Deprecation

(Not used)

Supersession

(Not used)

Naming System

(Not used)

Alias

(Not used)

Any Entity

(Not used)

Parent topic: Coordinate Systems Data Structures

6.7.29 Finding Information About EPSG-Based Coordinate Systems

This section explains how to query the Oracle Spatial coordinate systems data structures for information about geodetic and projected EPSG-based coordinate systems.

Parent topic: Coordinate Systems Data Structures

6.7.29.1 Geodetic Coordinate Systems

A human-readable summary of a CRS is the WKT string. For example:

SQL> select wktext from cs_srs where srid = 4326;
 
WKTEXT
--------------------------------------------------------------------------------
GEOGCS [ "WGS 84", DATUM ["World Geodetic System 1984 (EPSG ID 6326)", SPHEROID
["WGS 84 (EPSG ID 7030)", 6378137, 298.257223563]], PRIMEM [ "Greenwich", 0.0000
00 ], UNIT ["Decimal Degree", 0.01745329251994328]]

EPSG WKTs have been automatically generated by Spatial, for backward compatibility. Note that EPSG WKTs also contain numeric ID values (such as EPSG ID 6326 in the preceding example) for convenience. However, for more detailed information you should access the EPSG data stored in the coordinate systems data structures. The following example returns information about the ellipsoid, datum shift, rotation, and scale adjustment for SRID 4123: 

SQL> select
  ell.semi_major_axis,
  ell.inv_flattening,
  ell.semi_minor_axis,
  ell.uom_id,
  dat.shift_x,
  dat.shift_y,
  dat.shift_z,
  dat.rotate_x,
  dat.rotate_y,
  dat.rotate_z,
  dat.scale_adjust
from
  sdo_coord_ref_system crs,
  sdo_datums dat,
  sdo_ellipsoids ell
where
  crs.srid = 4123 and
  dat.datum_id = crs.datum_id and
  ell.ellipsoid_id = dat.ellipsoid_id;
 
SEMI_MAJOR_AXIS INV_FLATTENING SEMI_MINOR_AXIS     UOM_ID    SHIFT_X    SHIFT_Y    SHIFT_Z   ROTATE_X   ROTATE_Y   ROTATE_Z SCALE_ADJUST
--------------- -------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------
        6378388            297      6356911.95       9001      -90.7     -106.1     -119.2       4.09       .218      -1.05         1.37

In the preceding example, the UOM_ID represents the unit of measure for SEMI_MAJOR_AXIS (a) and SEMI_MINOR_AXIS (b). INV_FLATTENING is a/(a-b) and has no associated unit. Shifts are in meters, rotation angles are given in arc seconds, and scale adjustment in parts per million.

To interpret the UOM_ID, you can query the units table, as shown in the following example:

SQL> select UNIT_OF_MEAS_NAME from sdo_units_of_measure where UOM_ID = 9001;
 
UNIT_OF_MEAS_NAME
--------------------------------------------------------------------------------
metre

Conversion factors for units of length are given relative to meters, as shown in the following example:

SQL> select UNIT_OF_MEAS_NAME, FACTOR_B/FACTOR_C from sdo_units_of_measure where UOM_ID = 9002;
 
UNIT_OF_MEAS_NAME
--------------------------------------------------------------------------------
FACTOR_B/FACTOR_C
-----------------
foot
            .3048

Conversion factors for units of angle are given relative to radians, as shown in the following example:

SQL> select UNIT_OF_MEAS_NAME, FACTOR_B/FACTOR_C from sdo_units_of_measure where UOM_ID = 9102;
 
UNIT_OF_MEAS_NAME
--------------------------------------------------------------------------------
FACTOR_B/FACTOR_C
-----------------
degree
       .017453293

Parent topic: Finding Information About EPSG-Based Coordinate Systems

6.7.29.2 Projected Coordinate Systems

As mentioned in Geodetic Coordinate Systems, the WKT is a human-readable summary of a CRS, but the actual EPSG data is stored in the Spatial coordinate systems data structures. The following example shows the WKT string for a projected coordinate system: 

SQL> select wktext from cs_srs where srid = 32040;
 
WKTEXT
--------------------------------------------------------------------------------
PROJCS["NAD27 / Texas South Central", GEOGCS [ "NAD27", DATUM ["North American D
atum 1927 (EPSG ID 6267)", SPHEROID ["Clarke 1866 (EPSG ID 7008)", 6378206.4, 29
4.978698213905820761610537123195175418]], PRIMEM [ "Greenwich", 0.000000 ], UNIT
 ["Decimal Degree", 0.01745329251994328]], PROJECTION ["Texas CS27 South Central
 zone (EPSG OP 14204)"], PARAMETER ["Latitude_Of_Origin", 27.8333333333333333333
3333333333333333333], PARAMETER ["Central_Meridian", -98.99999999999999999999999
999999999999987], PARAMETER ["Standard_Parallel_1", 28.3833333333333333333333333
3333333333333], PARAMETER ["Standard_Parallel_2", 30.283333333333333333333333333
33333333333], PARAMETER ["False_Easting", 2000000], PARAMETER ["False_Northing",
 0], UNIT ["U.S. Foot", .3048006096012192024384048768097536195072]]

To determine the base geographic CRS for a projected CRS, you can query the SDO_COORD_REF_SYSTEM table, as in the following example:

SQL> select SOURCE_GEOG_SRID from sdo_coord_ref_system where srid = 32040;
 
SOURCE_GEOG_SRID
----------------
            4267

The following example returns the projection method for the projected CRS 32040:

SQL> select
  m.coord_op_method_name
from
  sdo_coord_ref_sys crs,
  sdo_coord_ops ops,
  sdo_coord_op_methods m
where
  crs.srid = 32040 and
  ops.coord_op_id = crs.projection_conv_id and
  m.coord_op_method_id = ops.coord_op_method_id;
 
COORD_OP_METHOD_NAME
--------------------------------------------------
Lambert Conic Conformal (2SP)

The following example returns the projection parameters for the projected CRS 32040:

SQL> select
  params.parameter_name || ' = ' ||
  vals.parameter_value || ' ' ||
  uom.unit_of_meas_name "Projection parameters"
from
  sdo_coord_ref_sys crs,
  sdo_coord_op_param_vals vals,
  sdo_units_of_measure uom,
  sdo_coord_op_params params
where
  crs.srid = 32040 and
  vals.coord_op_id = crs.projection_conv_id and
  uom.uom_id = vals.uom_id and
  params.parameter_id = vals.parameter_id;
 
Projection parameters
--------------------------------------------------------------------------------
Latitude of false origin = 27.5 sexagesimal DMS
Longitude of false origin = -99 sexagesimal DMS
Latitude of 1st standard parallel = 28.23 sexagesimal DMS
Latitude of 2nd standard parallel = 30.17 sexagesimal DMS
Easting at false origin = 2000000 US survey foot
Northing at false origin = 0 US survey foot

The following example is essentially the same query as the preceding example, but it also converts the values to the base unit:

SQL> select
  params.parameter_name || ' = ' ||
  vals.parameter_value || ' ' ||
  uom.unit_of_meas_name || ' = ' ||
  sdo_cs.transform_to_base_unit(vals.parameter_value, vals.uom_id) || ' ' ||
  decode(
    uom.unit_of_meas_type,
    'area', 'square meters',
    'angle', 'radians',
    'length', 'meters',
    'scale', '', '') "Projection parameters"
from
  sdo_coord_ref_sys crs,
  sdo_coord_op_param_vals vals,
  sdo_units_of_measure uom,
  sdo_coord_op_params params
where
  crs.srid = 32040 and
  vals.coord_op_id = crs.projection_conv_id and
  uom.uom_id = vals.uom_id and
  params.parameter_id = vals.parameter_id;
 
Projection parameters
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Latitude of false origin = 27.5 sexagesimal DMS = .485783308471754564814814814814814814815 radians
Longitude of false origin = -99 sexagesimal DMS = -1.7278759594743845 radians
Latitude of 1st standard parallel = 28.23 sexagesimal DMS = .495382619357723367592592592592592592593 radians
Latitude of 2nd standard parallel = 30.17 sexagesimal DMS = .528543875145615595370370370370370370371 radians
Easting at false origin = 2000000 US survey foot = 609601.219202438404876809753619507239014 meters
Northing at false origin = 0 US survey foot = 0 meters

The following example returns the projection unit of measure for the projected CRS 32040. (The projection unit might be different from the length unit used for the projection parameters.)

SQL> select
  axes.coord_axis_abbreviation || ': ' ||
  uom.unit_of_meas_name "Projection units"
from
  sdo_coord_ref_sys crs,
  sdo_coord_axes axes,
  sdo_units_of_measure uom
where
  crs.srid = 32040 and
  axes.coord_sys_id = crs.coord_sys_id and
  uom.uom_id = axes.uom_id;
 
Projection units
------------------------------------------------------------------------------
X: US survey foot
Y: US survey foot

Parent topic: Finding Information About EPSG-Based Coordinate Systems