6 Coordinate Systems (Spatial Reference Systems)

6.1.1 Coordinate System (Spatial Reference System)

A coordinate system (also called a spatial reference system) is a means of assigning coordinates to a location and establishing relationships between sets of such coordinates. It enables the interpretation of a set of coordinates as a representation of a position in a real world space.

The term coordinate reference system has the same meaning as coordinate system for Spatial, and the terms are used interchangeably. European Petroleum Survey Group (EPSG) specifications and documentation typically use the term coordinate reference system. (EPSG has its own meaning for the term coordinate system, as noted in Section 6.7.11.)

6.1.2 Cartesian Coordinates

Cartesian coordinates are coordinates that measure the position of a point from a defined origin along axes that are perpendicular in the represented two-dimensional or three-dimensional space.

6.1.3 Geodetic Coordinates (Geographic Coordinates)

Geodetic coordinates (sometimes called geographic coordinates) are angular coordinates (longitude and latitude), closely related to spherical polar coordinates, and are defined relative to a particular Earth geodetic datum (described in Section 6.1.6). For more information about geodetic coordinate support, see Section 6.2.

6.1.4 Projected Coordinates

Projected coordinates are planar Cartesian coordinates that result from performing a mathematical mapping from a point on the Earth's surface to a plane. There are many such mathematical mappings, each used for a particular purpose.

6.1.5 Local Coordinates

Local coordinates are Cartesian coordinates in a non-Earth (non-georeferenced) coordinate system. Section 6.3 describes local coordinate support in Spatial.

6.1.6 Geodetic Datum

A geodetic datum (or datum) is a means of shifting and rotating an ellipsoid to represent the figure of the Earth, usually as an oblate spheroid, that approximates the surface of the Earth locally or globally, and is the reference for the system of geodetic coordinates.

Each geodetic coordinate system is based on a datum.

6.1.7 Transformation

Transformation is the conversion of coordinates from one coordinate system to another coordinate system.

If the coordinate system is georeferenced, transformation can involve datum transformation: the conversion of geodetic coordinates from one geodetic datum to another geodetic datum, usually involving changes in the shape, orientation, and center position of the reference ellipsoid.

6.2.1 Geodesy and Two-Dimensional Geometry

A two-dimensional geometry is a surface geometry, but the important question is: What is the surface? A flat surface (plane) is accurately represented by Cartesian coordinates. However, Cartesian coordinates are not adequate for representing the surface of a solid. A commonly used surface for spatial geometry is the surface of the Earth, and the laws of geometry there are different than they are in a plane. For example, on the Earth's surface there are no parallel lines: lines are geodesics, and all geodesics intersect. Thus, closed curved surface problems cannot be done accurately with Cartesian geometry.

Spatial provides accurate results regardless of the coordinate system or the size of the area involved, without requiring that the data be projected to a flat surface. The results are accurate regardless of where on the Earth's surface the query is focused, even in "special" areas such as the poles. Thus, you can store coordinates in any datum and projections that you choose, and you can perform accurate queries regardless of the coordinate system.

6.2.2 Choosing a Geodetic or Projected Coordinate System

For applications that deal with the Earth's surface, the data can be represented using a geodetic coordinate system or a projected plane coordinate system. In deciding which approach to take with the data, consider any needs related to accuracy and performance:

• Accuracy

  For many spatial applications, the area is sufficiently small to allow adequate computations on Cartesian coordinates in a local projection. For example, the New Hampshire State Plane local projection provides adequate accuracy for most spatial applications that use data for that state.

  However, Cartesian computations on a plane projection will never give accurate results for a large area such as Canada or Scandinavia. For example, a query asking if Stockholm, Sweden and Helsinki, Finland are within a specified distance may return an incorrect result if the specified distance is close to the actual measured distance. Computations involving large areas or requiring very precise accuracy must account for the curvature of the Earth's surface.

• Performance

  Spherical computations use more computing resources than Cartesian computations. Some operations using geodetic coordinates may take longer to complete than the same operations using Cartesian coordinates.

6.2.3 Choosing Non-Ellipsoidal or Ellipsoidal Height

This section discusses guidelines for choosing the appropriate type of height for three-dimensional data: non-ellipsoidal or ellipsoidal. Although ellipsoidal height is widely used and is the default for many GPS applications, and although ellipsoidal computations incur less performance overhead in many cases, there are applications for which a non-ellipsoidal height may be preferable or even necessary.

Also, after any initial decision, you can change the reference height type, because transformations between different height datums are supported.

6.2.4 Geodetic MBRs

To create a query window for certain operations on geodetic data, use an MBR (minimum bounding rectangle) by specifying an SDO_ETYPE value of 1003 or 2003 (optimized rectangle) and an SDO_INTERPRETATION value of 3, as described in Table 2-2 in Section 2.2.4. A geodetic MBR can be used with the following operators: SDO_FILTER, SDO_RELATE with the ANYINTERACT mask, SDO_ANYINTERACT, and SDO_WITHIN_DISTANCE.

Example 6-1 requests the names of all cola markets that are likely to interact spatially with a geodetic MBR.

SELECT c.name FROM cola_markets_cs c WHERE
   SDO_FILTER(c.shape, 
       SDO_GEOMETRY(
           2003,
           8307,    -- SRID for WGS 84 longitude/latitude
           NULL,
           SDO_ELEM_INFO_ARRAY(1,1003,3),
           SDO_ORDINATE_ARRAY(6,5, 10,10))
       ) = 'TRUE';

Example 6-1 produces the following output (assuming the data as defined in Example 6-17 in Section 6.13):

NAME
--------------------------------
cola_c
cola_b
cola_d

The following considerations apply to the use of geodetic MBRs:

• Do not use a geodetic MBR with spatial objects stored in the database. Use it only to construct a query window.

• The lower-left Y coordinate (minY) must be less than the upper-right Y coordinate (maxY). If the lower-left X coordinate (minX) is greater than the upper-right X coordinate (maxX), the window is assumed to cross the date line meridian (that is, the meridian "opposite" the prime meridian, or both 180 and -180 longitude). For example, an MBR of (-10,10, -100, 20) with longitude/latitude data goes three-fourths of the way around the Earth (crossing the date line meridian), and goes from latitude lines 10 to 20.

• When Spatial constructs the MBR internally for the query, lines along latitude lines are densified by adding points at one-degree intervals. This might affect results for objects within a few meters of the edge of the MBR (especially objects in the middle latitudes in both hemispheres).

• When an optimized rectangle spans more than 119 degrees in longitude, it is internally divided into three rectangles; and as a result, these three rectangles share an edge that is the common boundary between them. If you validate the geometry of such an optimized rectangle, error code 13351 is returned because the internal rectangles have a shared edge. You can use such an optimized rectangle for queries with only the following: SDO_ANYINTERACT operator, SDO_RELATE operator with the ANYINTERACT mask, or SDO_GEOM.RELATE function with the ANYINTERACT mask. (Any other queries on such an optimized rectangle may return incorrect results.)

The following additional examples show special or unusual cases, to illustrate how a geodetic MBR is interpreted with longitude/latitude data:

• (10,0, -110,20) crosses the date line meridian and goes most of the way around the world, and goes from the equator to latitude 20.

• (10,-90, 40,90) is a band from the South Pole to the North Pole between longitudes 10 and 40.

• (10,-90, 40,50) is a band from the South Pole to latitude 50 between longitudes 10 and 40.

• (-180,-10, 180,5) is a band that wraps the equator from 10 degrees south to 5 degrees north.

• (-180,-90, 180,90) is the whole Earth.

• (-180,-90, 180,50) is the whole Earth below latitude 50.

• (-180,50, 180,90) is the whole Earth above latitude 50.

6.2.5 Other Considerations and Requirements with Geodetic Data

The following geometries are not permitted if a geodetic coordinate system is used or if any transformation is being performed (even if the transformation is from one projected coordinate system to another projected coordinate system):

• Circles

• Circular arcs

Geodetic coordinate system support is provided only for geometries that consist of points or geodesics (lines on the ellipsoid). If you have geometries containing circles or circular arcs in a projected coordinate system, you can densify them using the SDO_GEOM.SDO_ARC_DENSIFY function (documented in Chapter 24) before transforming them to geodetic coordinates, and then perform Spatial operations on the resulting geometries.

The following size limits apply with geodetic data:

• No polygon element can have an area larger than or equal to one-half the surface of the Earth. Moreover, if the result of a union of two polygons is greater than one-half the surface of the Earth, the operation will not produce a correct result. For example, if A union B results in a polygon that is greater than one-half of the area of the Earth, the operations A difference B, A intersection B, and A XOR B are not supported, and only a relate operation with the ANYINTERACT mask is supported between those two polygons.

• In a line, the distance between two adjacent coordinates cannot be greater than or equal to one-half the perimeter (a great circle) of the Earth.

If you need to work with larger elements, first break these elements into multiple smaller elements and work with them. For example, you cannot create a geometry representing the entire ocean surface of the Earth; however, you can create multiple geometries, each representing part of the overall ocean surface. To work with a line string that is greater than or equal to one-half the perimeter of the Earth, you can add one or more intermediate points on the line so that all adjacent coordinates are less than one-half the perimeter of the Earth.

Tolerance is specified as meters for geodetic layers. If you use tolerance values that are typical for non-geodetic data, these values are interpreted as meters for geodetic data. For example, if you specify a tolerance value of 0.05 for geodetic data, this is interpreted as precise to 5 centimeters. If this value is more precise than your applications need, performance may be affected because of the internal computational steps taken to implement the specified precision. (For more information about tolerance, see Section 1.5.5.)

For geodetic layers, you must specify the dimensional extents in the index metadata as -180,180 for longitude and -90,90 for latitude. The following statement (from Example 6-17 in Section 6.13) specifies these extents (with a 10-meter tolerance value in each dimension) for a geodetic data layer:

INSERT INTO user_sdo_geom_metadata
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  'cola_markets_cs',
  'shape',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),  -- 10 meters tolerance
    SDO_DIM_ELEMENT('Latitude', -90, 90, 10)  -- 10 meters tolerance
     ),
  8307   -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);

See Section 6.10 for additional notes and restrictions relating to geodetic data.

6.5.1 Geographic 3D Coordinate Reference Systems

A geographic three-dimensional coordinate reference system is based on longitude and latitude, plus ellipsoidal height. The ellipsoidal height is the height relative to a reference ellipsoid, which is an approximation of the real Earth. All three dimensions of the CRS are based on the same ellipsoid.

Using ellipsoidal heights enables Spatial to perform internal operations with great mathematical regularity and efficiency. Compound coordinate reference systems, on the other hand, require more complex transformations, often based on offset matrixes. Some of these matrixes have to be downloaded and configured. Furthermore, they might have a significant footprint, on disk and in main memory.

The supported geographic 3D coordinate reference systems are listed in the SDO_CRS_GEOGRAPHIC3D view, described in Section 6.7.16.

6.5.2 Compound Coordinate Reference Systems

A compound three-dimensional coordinate reference system is based on a geographic or projected two-dimensional system, plus gravity-related height. Gravity-related height is the height as influenced by the Earth's gravitational force, where the base height (zero) is often an equipotential surface, and might be defined as above or below "sea level."

Gravity-related height is a more complex representation than ellipsoidal height, because of gravitational irregularities such as the following:

• Orthometric height

  Orthometric height is also referred to as the height above the geoid. The geoid is an equipotential surface that most closely (but not exactly) matches mean sea level. An equipotential surface is a surface on which each point is at the same gravitational potential level. Such a surface tends to undulate slightly, because the Earth has regions of varying density. There are multiple equipotential surfaces, and these might not be parallel to each other due to the irregular density of the Earth.

• Height relative to mean sea level, to sea level at a specific location, or to a vertical network warped to fit multiple tidal stations (for example, NGVD 29)

  Sea level is close to, but not identical to, the geoid. The sea level at a given location is often defined based on the "average sea level" at a specific port.

The supported compound coordinate reference systems are listed in the SDO_CRS_COMPOUND view, described in Section 6.7.12.

You can create a customized compound coordinate reference system, which combines a horizontal CRS with a vertical CRS. (The horizontal CRS contains two dimensions, such as X and Y or longitude and latitude, and the vertical CRS contains the third dimension, such as Z or height or altitude.) Section 6.9.4 explains how to create a compound CRS.

6.5.3 Three-Dimensional Transformations

Spatial supports three-dimensional coordinate transformations for SDO_GEOMETRY objects directly, and indirectly for point clouds and TINs. (For example, a point cloud must be transformed to a table with an SDO_GEOMETRY column.) The supported transformations include the following:

• Three-dimensional datum transformations

• Transformations between ellipsoidal and gravity-related height

For three-dimensional datum transformations, the datum transformation between the two ellipsoids is essentially the same as for two-dimensional coordinate reference systems, except that the third dimension is considered instead of ignored. Because height values are not ignored, the accuracy of the results increases, especially for transformation chains.

For transformations between ellipsoidal and gravity-related height, computations are complicated by the fact that equipotential and other gravity-related surfaces tend to undulate, compared to any ellipsoid and to each other. Transformations might be based on higher-degree polynomial functions or bilinear interpolation. In either case, a significant parameter matrix is required to define the transformation.

For transforming between gravity-related and ellipsoidal height, the process usually involves a transformation, based on an offset matrix, between geoidal and ellipsoidal height. Depending on the source or target definition of the offset matrix, a common datum transformation might have to be appended or prefixed.

Example 6-2 shows a three-dimensional datum transformation.

set numwidth 9
 
CREATE TABLE source_geoms (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  GEOMETRY SDO_GEOMETRY);
 
INSERT INTO source_geoms VALUES(
  1,
  'reference geom',
  SDO_GEOMETRY(
  3001,
  4985,
  SDO_POINT_TYPE(
     4.0,
    55.0,
    1.0),
  NULL,
  NULL));
 
INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'source_geoms',
  'GEOMETRY',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),
    SDO_DIM_ELEMENT('Latitude',   -90,  90, 10),
    SDO_DIM_ELEMENT('Height',   -1000,1000, 10)),
  4985);
 
commit;
 
--------------------------------------------------------------------------------
 
CALL SDO_CS.TRANSFORM_LAYER(
  'source_geoms',
  'GEOMETRY',
  'GEO_CS_4979',
  4979);
 
INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'GEO_CS_4979',
  'GEOMETRY',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),
    SDO_DIM_ELEMENT('Latitude',   -90,  90, 10),
    SDO_DIM_ELEMENT('Height',   -1000,1000, 10)),
  4979);
 
set lines 210;
 
--------------------------------------------------------------------------------
 
CALL SDO_CS.TRANSFORM_LAYER(
  'GEO_CS_4979',
  'GEOMETRY',
  'source_geoms2',
  4985);
 
INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'source_geoms2',
  'GEOMETRY',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),
    SDO_DIM_ELEMENT('Latitude',   -90,  90, 10),
    SDO_DIM_ELEMENT('Height',   -1000,1000, 10)),
  4985);
 
--------------------------------------------------------------------------------
 
DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'GEO_CS_4979';
DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SOURCE_GEOMS';
DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SOURCE_GEOMS2';
 
drop table GEO_CS_4979;
drop table source_geoms;
drop table source_geoms2;

As a result of the transformation in Example 6-2, (4, 55, 1) is transformed to (4.0001539, 55.0000249, 4.218).

Example 6-3 configures a transformation between geoidal and ellipsoidal height, using a Hawaii offset grid. Note that without the initial creation of a rule (using the SDO_CS.CREATE_PREF_CONCATENATED_OP procedure), the grid would not be used.

-- Create Sample operation:
insert into mdsys.sdo_coord_ops (
  COORD_OP_ID,
  COORD_OP_NAME,
  COORD_OP_TYPE,
  SOURCE_SRID,
  TARGET_SRID,
  COORD_TFM_VERSION,
  COORD_OP_VARIANT,
  COORD_OP_METHOD_ID,
  UOM_ID_SOURCE_OFFSETS,
  UOM_ID_TARGET_OFFSETS,
  INFORMATION_SOURCE,
  DATA_SOURCE,
  SHOW_OPERATION,
  IS_LEGACY,
  LEGACY_CODE,
  REVERSE_OP,
  IS_IMPLEMENTED_FORWARD,
  IS_IMPLEMENTED_REVERSE)
values (
  1000000005,
  'Test Bi-linear Interpolation',
  'CONVERSION',
  null,
  null,
  null,
  null,
  9635,
  null,
  null,
  'Oracle',
  'Oracle',
  1,
  'FALSE',
  null,
  1,
  1,
  1);
 
--Create sample parameters, pointing to the offset file
--(in this case reusing values from an existing operation):
insert into mdsys.sdo_coord_op_param_vals (
    coord_op_id,
    COORD_OP_METHOD_ID,
    PARAMETER_ID,
    PARAMETER_VALUE,
    PARAM_VALUE_FILE_REF,
    PARAM_VALUE_FILE,
    PARAM_VALUE_XML,
    UOM_ID) (
  select
    1000000005,
    9635,
    8666,
    PARAMETER_VALUE,
    PARAM_VALUE_FILE_REF,
    PARAM_VALUE_FILE,
    PARAM_VALUE_XML,
    UOM_ID
  from
    mdsys.sdo_coord_op_param_vals
  where
    coord_op_id = 999998 and
    parameter_id = 8666);
 
--Create a rule to use this operation between SRIDs 7406 and 4359: 
call sdo_cs.create_pref_concatenated_op(
    300,
    'CONCATENATED OPERATION',
    TFM_PLAN(SDO_TFM_CHAIN(7406, 1000000005, 4359)),
    NULL);
 
 
-- Now, actually perform the transformation:
set numformat 999999.99999999
 
-- Create the source table
CREATE TABLE source_geoms (
  mkt_id NUMBER PRIMARY KEY,
  name VARCHAR2(32),
  GEOMETRY SDO_GEOMETRY);
 
INSERT INTO source_geoms VALUES(
  1,
  'reference geom',
  SDO_GEOMETRY(
  3001,
  7406,
  SDO_POINT_TYPE(
    -161,
      18,
     0),
  NULL,
  NULL));
 
INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'source_geoms',
  'GEOMETRY',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),
    SDO_DIM_ELEMENT('Latitude',   -90,  90, 10),
    SDO_DIM_ELEMENT('Height',    -100, 100, 10)),
  7406);
 
commit;
 
SELECT GEOMETRY "Source" FROM source_geoms;
 
--------------------------------------------------------------------------------
 
--Perform the transformation:
CALL SDO_CS.TRANSFORM_LAYER(
  'source_geoms',
  'GEOMETRY',
  'GEO_CS_4359',
  4359);
 
INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'GEO_CS_4359',
  'GEOMETRY',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),
    SDO_DIM_ELEMENT('Latitude',   -90,  90, 10),
    SDO_DIM_ELEMENT('Height',    -100, 100, 10)),
  4359);
 
set lines 210;
 
SELECT GEOMETRY "Target" FROM GEO_CS_4359;
 
--------------------------------------------------------------------------------
 
--Transform back:
CALL SDO_CS.TRANSFORM_LAYER(
  'GEO_CS_4359',
  'GEOMETRY',
  'source_geoms2',
  7406);
 
INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'source_geoms2',
  'GEOMETRY',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 10),
    SDO_DIM_ELEMENT('Latitude',   -90,  90, 10),
    SDO_DIM_ELEMENT('Height',    -100, 100, 10)),
  7406);
 
SELECT GEOMETRY "Source2" FROM source_geoms2;
 
--------------------------------------------------------------------------------
 
--Clean up (regarding the transformation):
DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'GEO_CS_4359';
DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SOURCE_GEOMS';
DELETE FROM USER_SDO_GEOM_METADATA WHERE table_name = 'SOURCE_GEOMS2';
 
drop table GEO_CS_4359;
drop table source_geoms;
drop table source_geoms2;
 
 
--Clean up (regarding the rule):
CALL sdo_cs.delete_op(300);
 
delete from mdsys.sdo_coord_op_param_vals where coord_op_id = 1000000005;
 
delete from mdsys.sdo_coord_ops where coord_op_id = 1000000005;
 
COMMIT;

With the configuration in Example 6-3:

• Without the rule, (-161.00000000, 18.00000000, .00000000) is transformed to (-161.00127699, 18.00043360, 62.03196364), based simply on a datum transformation.

• With the rule, (-161.00000000, 18.00000000, .00000000) is transformed to (-161.00000000, 18.00000000, 6.33070000).

6.5.4 Cross-Dimensionality Transformations

You cannot directly perform a cross-dimensionality transformation (for example, from a two-dimensional geometry to a three-dimensional geometry) using the SDO_CS.TRANSFORM function or the SDO_CS.TRANSFORM_LAYER procedure. However, you can use the SDO_CS.MAKE_3D function to convert a two-dimensional geometry to a three-dimensional geometry, or the SDO_CS.MAKE_2D function to convert a three-dimensional geometry to a two-dimensional geometry; and you can use the resulting geometry to perform a transformation into a geometry with the desired number of dimensions.

For example, transforming a two-dimensional geometry into a three-dimensional geometry involves using the SDO_CS.MAKE_3D function. This function does not itself perform any coordinate transformation, but simply adds a height value and sets the target SRID. You must choose an appropriate target SRID, which should be the three-dimensional equivalent of the source SRID. For example, three-dimensional WGS84 (4327) is the equivalent of two-dimensional WGS84 (4326). If necessary, modify height values of vertices in the returned geometry.

There are many options for how to use the SDO_CS.MAKE_3D function, but the simplest is the following:

1. Transform from the two-dimensional source SRID to two-dimensional WGS84 (4326).

2. Call SDO_CS.MAKE_3D to convert the geometry to three-dimensional WGS84 (4327)

3. Transform from three-dimensional WGS84 (4327) to the three-dimensional target SRID.

Example 6-4 transforms a two-dimensional point from SRID 27700 to two-dimensional SRID 4326, converts the result of the transformation to a three-dimensional point with SRID 4327, and transforms the converted point to three-dimensional SRID 4327.

SELECT
  SDO_CS.TRANSFORM(
    SDO_CS.MAKE_3D(
      SDO_CS.TRANSFORM(
        SDO_GEOMETRY(
          2001,
          27700,
          SDO_POINT_TYPE(577274.984, 69740.4923, NULL),
          NULL,
          NULL),
        4326),
      height => 0,
      target_srid => 4327),
    4327) "27700 > 4326 > 4327 > 4327"
FROM DUAL;
 
27700 > 4326 > 4327 > 4327(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INF
--------------------------------------------------------------------------------
SDO_GEOMETRY(3001, 4327, SDO_POINT_TYPE(.498364058, 50.5006366, 0), NULL, NULL)

6.5.5 3D Equivalent for WGS 84?

There are two possible answers to the question What is 3D equivalent for the WGS 84 coordinate system? (that is, 2D Oracle SRID 8308 or EPSG SRID 4326):

• 4979 (in many or most cases), or

• It depends on what you mean by height (for example, above ground level, above or below sea level, or something else).

There are many different height datums. Height can be relative to:

• The ellipsoid, which requires the use of a coordinate system of type GEOGRAPHIC3d, for which SRID values 4327, 43229, and 4979 are predefined in Oracle Spatial.

• A non-ellipsoidal height datum, which requires the use of a coordinate system of type COMPOUND, for which a custom SRID must usually be defined. The non-ellipsoidal height may be specified in relation to the geoid, to some local or mean sea level (or a network of local sea levels), or to some other definition of height (such as above ground surface).

To define a compound coordinate system (see Section 6.5.2, "Compound Coordinate Reference Systems") based on the two dimensions of the WGS 84 coordinate system, you must first select a predefined or custom vertical coordinate reference system (see Section 6.9.3, "Creating a Vertical CRS"). To find the available vertical coordinate reference systems, enter the following statement:

SELECT srid, COORD_REF_SYS_NAME from sdo_coord_ref_sys 
  WHERE COORD_REF_SYS_KIND = 'VERTICAL' order by srid;
 
      SRID COORD_REF_SYS_NAME
---------- ---------------------------------------------------------------------
      3855 EGM2008 geoid height
      3886 Fao 1979 height
      4440 NZVD2009 height
      4458 Dunedin-Bluff 1960 height
      5600 NGPF height
      5601 IGN 1966 height
      5602 Moorea SAU 1981 height
      . . .
      5795 Guadeloupe 1951 height
      5796 Lagos 1955 height
      5797 AIOC95 height
      5798 EGM84 geoid height
      5799 DVR90 height
 
123 rows selected.

After selecting a vertical coordinate reference system, create the compound SRID by entering a statement in the following form:

INSERT INTO sdo_coord_ref_system (
  SRID,
  COORD_REF_SYS_NAME,
  COORD_REF_SYS_KIND,
  COORD_SYS_ID,
  DATUM_ID,
  GEOG_CRS_DATUM_ID,
  SOURCE_GEOG_SRID,
  PROJECTION_CONV_ID,
  CMPD_HORIZ_SRID,
  CMPD_VERT_SRID,
  INFORMATION_SOURCE,
  DATA_SOURCE,
  IS_LEGACY,
  LEGACY_CODE,
  LEGACY_WKTEXT,
  LEGACY_CS_BOUNDS,
  IS_VALID,
  SUPPORTS_SDO_GEOMETRY)
values (
  custom-SRID,
  'custom-name',
  'COMPOUND',
  NULL,
  NULL,
  6326,
  NULL,
  NULL,
  4326,
  vertical-SRID,
  'custom-information-source',
  'custom-data-source',
  'FALSE',
  NULL,
  NULL,
  NULL,
  'TRUE',
  'TRUE');

You can check the definition, based on the generated WKT, by entering a statement in the following form:

SELECT wktext3d FROM cs_srs WHERE srid = custom-SRID;
 
WKTEXT3D
------------------------------------------------------------------------------
COMPD_CS[
  "NTF (Paris) + NGF IGN69",
  GEOGCS["NTF (Paris)",
    DATUM["Nouvelle Triangulation Francaise (Paris)",
      SPHEROID[
        "Clarke 1880 (IGN)",
        6378249.2,
        293.4660212936293951,
        AUTHORITY["EPSG", "7011"]],
      TOWGS84[-168.0, -60.0, 320.0, 0.0, 0.0, 0.0, 0.0],
      AUTHORITY["EPSG", "6807"]],
    PRIMEM["Paris", 2.337229, AUTHORITY["EPSG","8903"]],
    UNIT["grad", 0.015707963267949, AUTHORITY["EPSG", "9105"]],
    AXIS["Lat", NORTH],
    AXIS["Long", EAST],
    AUTHORITY["EPSG", "4807"]],
  VERT_CS["NGF IGN69",
    VERT_DATUM["Nivellement general de la France - IGN69", 2005,
      AUTHORITY["EPSG", "5119"]],
    UNIT["metre", 1.0, AUTHORITY["EPSG", "9001"]],
    AXIS["H", UP],
    AUTHORITY["EPSG", "5720"]],
  AUTHORITY["EPSG","7400"]]

When transforming between different height datums, you might use a VERTCON matrix. For example, between the WGS 84 ellipsoid and geoid, there is an offset matrix that allows height transformation. For more information, see the following:

• Example 6-3, "Transformation Between Geoidal And Ellipsoidal Height" in Section 6.5.3, "Three-Dimensional Transformations"

• Section 6.9.6, "Creating a Transformation Operation"

• Section 6.9.7, "Using British Grid Transformation OSTN02/OSGM02 (EPSG Method 9633)"

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.

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.

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.

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.

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.

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.

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.

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.

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.)

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

• SDO_CRS_COMPOUND (Section 6.7.12)

• SDO_CRS_ENGINEERING (Section 6.7.13)

• SDO_CRS_GEOCENTRIC (Section 6.7.14)

• SDO_CRS_GEOGRAPHIC2D (Section 6.7.15)

• SDO_CRS_GEOGRAPHIC3D (Section 6.7.16)

• SDO_CRS_PROJECTED (Section 6.7.17)

• SDO_CRS_VERTICAL (Section 6.7.18)

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 Section 6.7.9. 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.

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.")

6.7.12 SDO_CRS_COMPOUND View

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

6.7.13 SDO_CRS_ENGINEERING View

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

6.7.14 SDO_CRS_GEOCENTRIC View

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

6.7.15 SDO_CRS_GEOGRAPHIC2D View

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

6.7.16 SDO_CRS_GEOGRAPHIC3D View

The SDO_CRS_GEOGRAPHIC3D view contains selected information from the SDO_COORD_REF_SYS table (described in Section 6.7.9) where the COORD_REF_SYS_KIND column value is GEOGRAPHIC3D. (For an explanation of geographic 3D coordinate reference systems, see Section 6.5.1.) This view contains the columns shown in Table 6-15.

6.7.17 SDO_CRS_PROJECTED View

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

6.7.18 SDO_CRS_VERTICAL View

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

6.7.19 SDO_DATUM_ENGINEERING View

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

6.7.20 SDO_DATUM_GEODETIC View

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

6.7.21 SDO_DATUM_VERTICAL View

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

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.

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

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.

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.

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.

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.

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.

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.epsg.org/CurrentDB.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

6.7.29 Finding Information About EPSG-Based Coordinate Systems

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

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]]

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

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

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

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

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]]

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

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)

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
 

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

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

6.8.1 MDSYS.CS_SRS Table

The MDSYS.CS_SRS reference table contains over 4000 rows, one for each valid coordinate system. This table contains the columns shown in Table 6-28.

<coordinate system> ::=
     <horz cs> | <local cs>

<horz cs> ::=
     <geographic cs> | <projected cs>


<projected cs> ::=
     PROJCS [ "<name>", <geographic cs>, <projection>, 
           {<parameter>,}* <linear unit> ]

<projection> ::=
     PROJECTION [ "<name>" ]

<parameter> ::= 
     PARAMETER [ "<name>", <number> ]

<geographic cs> ::=
     GEOGCS [ "<name>", <datum>, <prime meridian>, <angular unit> ]

<datum> ::=
     DATUM [ "<name>", <spheroid> 
     {, <shift-x>, <shift-y>, <shift-z> 
       , <rot-x>, <rot-y>, <rot-z>, <scale_adjust>}  
     ]  

<spheroid> ::=
     SPHEROID ["<name>", <semi major axis>, <inverse flattening> ]

<prime meridian> ::=
     PRIMEM ["<name>", <longitude> ]

<longitude> ::=
     <number>

<semi-major axis> ::=
     <number>

<inverse flattening> ::=
     <number>

<angular unit> ::= <unit>

<linear unit> ::= <unit>

<unit> ::=
     UNIT [ "<name>", <conversion factor> ]

<local cs> ::=
     LOCAL_CS [ "<name>", <local datum>, <linear unit>,
          <axis> {, <axis>}* ]

<local datum> ::=
     LOCAL_DATUM [ "<name>", <datum type>
          {, <shift-x>, <shift-y>, <shift-z> 
           , <rot-x>, <rot-y>, <rot-z>, <scale_adjust>} 
          ]

<datum type> ::=
     <number>

<axis> ::=
     AXIS [ "<name>", NORTH | SOUTH | EAST |
           WEST | UP | DOWN | OTHER ]

'GEOGCS [ "Longitude / Latitude (Old Hawaiian)", DATUM ["Old Hawaiian", SPHEROID
["Clarke 1866", 6378206.400000, 294.978698]], PRIMEM [ "Greenwich", 0.000000 ],
UNIT ["Decimal Degree", 0.01745329251994330]]'

'PROJCS["Wyoming 4901, Eastern Zone (1983, meters)", GEOGCS [ "GRS 80", DATUM
["GRS 80", SPHEROID ["GRS 80", 6378137.000000, 298.257222]], PRIMEM [
"Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]],
PROJECTION ["Transverse Mercator"], PARAMETER ["Scale_Factor", 0.999938],
PARAMETER ["Central_Meridian", -105.166667], PARAMETER ["Latitude_Of_Origin",
40.500000], PARAMETER ["False_Easting", 200000.000000], UNIT ["Meter",
1.000000000000]]'

LOCAL_CS [ "Non-Earth (Meter)", LOCAL_DATUM ["Local Datum", 0], UNIT ["Meter", 1.0], AXIS ["X", EAST], AXIS["Y", NORTH]]

6.8.2 MDSYS.SDO_ANGLE_UNITS View

The MDSYS.SDO_ANGLE_UNITS reference view contains one row for each valid angle UNIT specification in the well-known text (WKT) description in the coordinate system definition. The WKT is described in Section 6.8.1.1.

The MDSYS.SDO_ANGLE_UNITS view is based on the SDO_UNITS_OF MEASURE table (described in Section 6.7.27), and it contains the columns shown in Table 6-29.

6.8.3 MDSYS.SDO_AREA_UNITS View

The MDSYS.SDO_AREA_UNITS reference view contains one row for each valid area UNIT specification in the well-known text (WKT) description in the coordinate system definition. The WKT is described in Section 6.8.1.1.

The MDSYS.SDO_AREA_UNITS view is based on the SDO_UNITS_OF MEASURE table (described in Section 6.7.27), and it contains the columns shown in Table 6-30.

6.8.4 MDSYS.SDO_DATUMS_OLD_FORMAT and SDO_DATUMS_OLD_SNAPSHOT Tables

The MDSYS.SDO_DATUMS_OLD_FORMAT and MDSYS.SDO_DATUMS_OLD_SNAPSHOT reference tables contain one row for each valid DATUM specification in the well-known text (WKT) description in the coordinate system definition. (The WKT is described in Section 6.8.1.1.)

• MDSYS.SDO_DATUMS_OLD_FORMAT contains the new data in the old format (that is, EPSG-based datum specifications in a table using the format from before release 10.2).

• MDSYS.SDO_DATUMS_OLD_SNAPSHOT contains the old data in the old format (that is, datum specifications and table format from before release 10.2).

These tables contain the columns shown in Table 6-31.

The following are the names (in tabular format) of the datums in these tables:

6.8.5 MDSYS.SDO_DIST_UNITS View

The MDSYS.SDO_DIST_UNITS reference view contains one row for each valid distance UNIT specification in the well-known text (WKT) description in the coordinate system definition. The WKT is described in Section 6.8.1.1.

The MDSYS.SDO_DIST_UNITS view is based on the SDO_UNITS_OF MEASURE table (described in Section 6.7.27), and it contains the columns shown in Table 6-32.

6.8.6 MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT and SDO_ELLIPSOIDS_OLD_SNAPSHOT Tables

The MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT and MDSYS.SDO_ELLIPSOIDS_OLD_SNAPSHOT reference tables contain one row for each valid SPHEROID specification in the well-known text (WKT) description in the coordinate system definition. (The WKT is described in Section 6.8.1.1.)

• MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT contains the new data in the old format (that is, EPSG-based ellipsoid specifications in a table using the format from before release 10.2).

• MDSYS.SDO_ELLIPSOIDS_OLD_SNAPSHOT contains the old data in the old format (that is, ellipsoid specifications and table format from before release 10.2).

These tables contain the columns shown in Table 6-33.

The following are the names (in tabular format) of the ellipsoids in these tables:

6.8.7 MDSYS.SDO_PROJECTIONS_OLD_FORMAT and SDO_PROJECTIONS_OLD_SNAPSHOT Tables

The MDSYS.SDO_PROJECTIONS_OLD_FORMAT and MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT reference tables contain one row for each valid PROJECTION specification in the well-known text (WKT) description in the coordinate system definition. (The WKT is described in Section 6.8.1.1.)

• MDSYS.SDO_PROJECTIONS_OLD_FORMAT contains the new data in the old format (that is, EPSG-based projection specifications in a table using the format from before release 10.2).

• MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT contains the old data in the old format (that is, projection specifications and table format from before release 10.2).

These tables contains the column shown in Table 6-34.

The following are the names (in tabular format) of the projections in these tables:

6.9.1 Creating a Geodetic CRS

If the necessary unit of measurement, coordinate axes, SDO_COORD_SYS table row, ellipsoid, prime meridian, and datum are already defined, insert a row into the SDO_COORD_REF_SYSTEM view (described in Section 6.7.10) to define the new geodetic CRS.

Example 6-5 inserts the definition for a hypothetical geodetic CRS named My Own NAD27 (which, except for its SRID and name, is the same as the NAD27 CRS supplied by Oracle).

INSERT INTO SDO_COORD_REF_SYSTEM (
        SRID,
        COORD_REF_SYS_NAME,
        COORD_REF_SYS_KIND,
        COORD_SYS_ID,
        DATUM_ID,
        GEOG_CRS_DATUM_ID,
        SOURCE_GEOG_SRID,
        PROJECTION_CONV_ID,
        CMPD_HORIZ_SRID,
        CMPD_VERT_SRID,
        INFORMATION_SOURCE,
        DATA_SOURCE,
        IS_LEGACY,
        LEGACY_CODE,
        LEGACY_WKTEXT,
        LEGACY_CS_BOUNDS,
        IS_VALID,
        SUPPORTS_SDO_GEOMETRY)
  VALUES (
        9994267,
        'My Own NAD27',
        'GEOGRAPHIC2D',
        6422,
        6267,
        6267,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        'EPSG',
        'FALSE',
        NULL,
        NULL,
        NULL,
        'TRUE',
        'TRUE');

If the necessary information for the definition does not already exist, follow these steps, as needed, to define the information before you insert the row into the SDO_COORD_REF_SYSTEM view:

1. If the unit of measurement is not already defined in the SDO_UNITS_OF_MEASURE table (described in Section 6.7.27), insert a row into that table to define the new unit of measurement.

2. If the coordinate axes are not already defined in the SDO_COORD_AXES table (described in Section 6.7.1), insert one row into that table for each new coordinate axis.

3. If an appropriate entry for the coordinate system does not already exist in the SDO_COORD_SYS table (described in Section 6.7.11), insert a row into that table. Example 6-6 inserts the definition for a fictitious coordinate system.

   Example 6-6 Inserting a Row into the SDO_COORD_SYS Table

   INSERT INTO SDO_COORD_SYS (
           COORD_SYS_ID,
           COORD_SYS_NAME,
           COORD_SYS_TYPE,
           DIMENSION,
           INFORMATION_SOURCE,
           DATA_SOURCE)
     VALUES (
           9876543,
           'My custom CS. Axes: lat, long. Orientations: north, east. UoM: deg',
           'ellipsoidal',
           2,
           'Myself',
           'Myself');
   

4. If the ellipsoid is not already defined in the SDO_ELLIPSOIDS table (described in Section 6.7.23), insert a row into that table to define the new ellipsoid.

5. If the prime meridian is not already defined in the SDO_PRIME_MERIDIANS table (described in Section 6.7.26), insert a row into that table to define the new prime meridian.

6. If the datum is not already defined in the SDO_DATUMS table (described in Section 6.7.22), insert a row into that table to define the new datum.

6.9.2 Creating a Projected CRS

If the necessary unit of measurement, coordinate axes, SDO_COORD_SYS table row, source coordinate system, projection operation, and projection parameters are already defined, insert a row into the SDO_COORD_REF_SYSTEM view (described in Section 6.7.10) to define the new projected CRS.

Example 6-7 inserts the definition for a hypothetical projected CRS named My Own NAD27 / Cuba Norte (which, except for its SRID and name, is the same as the NAD27 / Cuba Norte CRS supplied by Oracle).

INSERT INTO SDO_COORD_REF_SYSTEM (
        SRID,
        COORD_REF_SYS_NAME,
        COORD_REF_SYS_KIND,
        COORD_SYS_ID,
        DATUM_ID,
        GEOG_CRS_DATUM_ID,
        SOURCE_GEOG_SRID,
        PROJECTION_CONV_ID,
        CMPD_HORIZ_SRID,
        CMPD_VERT_SRID,
        INFORMATION_SOURCE,
        DATA_SOURCE,
        IS_LEGACY,
        LEGACY_CODE,
        LEGACY_WKTEXT,
        LEGACY_CS_BOUNDS,
        IS_VALID,
        SUPPORTS_SDO_GEOMETRY)
  VALUES (
        9992085,
        'My Own NAD27 / Cuba Norte',
        'PROJECTED',
        4532,
        NULL,
        6267,
        4267,
        18061,
        NULL,
        NULL,
        'Institut Cubano di Hidrografia (ICH)',
        'EPSG',
        'FALSE',
        NULL,
        NULL,
        NULL,
        'TRUE',
        'TRUE');

If the necessary information for the definition does not already exist, follow these steps, as needed, to define the information before you insert the row into the SDO_COORD_REF_SYSTEM view:

1. If the unit of measurement is not already defined in the SDO_UNITS_OF_MEASURE table (described in Section 6.7.27), insert a row into that table to define the new unit of measurement.

2. If the coordinate axes are not already defined in the SDO_COORD_AXES table (described in Section 6.7.1), insert one row into that table for each new coordinate axis.

3. If an appropriate entry for the coordinate system does not already exist in SDO_COORD_SYS table (described in Section 6.7.11), insert a row into that table. (See Example 6-6 in Section 6.9.1).

4. If the projection operation is not already defined in the SDO_COORD_OPS table (described in Section 6.7.8), insert a row into that table to define the new projection operation. Example 6-8 shows the statement used to insert information about coordinate operation ID 18061, which is supplied by Oracle.

   Example 6-8 Inserting a Row into the SDO_COORD_OPS Table

   INSERT INTO SDO_COORD_OPS (
           COORD_OP_ID,
           COORD_OP_NAME,
           COORD_OP_TYPE,
           SOURCE_SRID,
           TARGET_SRID,
           COORD_TFM_VERSION,
           COORD_OP_VARIANT,
           COORD_OP_METHOD_ID,
           UOM_ID_SOURCE_OFFSETS,
           UOM_ID_TARGET_OFFSETS,
           INFORMATION_SOURCE,
           DATA_SOURCE,
           SHOW_OPERATION,
           IS_LEGACY,
           LEGACY_CODE,
           REVERSE_OP,
           IS_IMPLEMENTED_FORWARD,
           IS_IMPLEMENTED_REVERSE)
     VALUES (
           18061,
           'Cuba Norte',
           'CONVERSION',
           NULL,
           NULL,
           NULL,
           NULL,
           9801,
           NULL,
           NULL,
           NULL,
           'EPSG',
           1,
           'FALSE',
           NULL,
           1,
           1,
           1);
   

5. If the parameters for the projection operation are not already defined in the SDO_COORD_OP_PARAM_VALS table (described in Section 6.7.5), insert one row into that table for each new parameter. Example 6-9 shows the statement used to insert information about parameters with ID values 8801, 8802, 8805, 8806, and 8807, which are supplied by Oracle.

   Example 6-9 Inserting a Row into the SDO_COORD_OP_PARAM_VALS Table

   INSERT INTO SDO_COORD_OP_PARAM_VALS (
           COORD_OP_ID,
           COORD_OP_METHOD_ID,
           PARAMETER_ID,
           PARAMETER_VALUE,
           PARAM_VALUE_FILE_REF,
           UOM_ID)
     VALUES (
           18061,
           9801,
           8801,
           22.21,
           NULL,
           9110);
    
    INSERT INTO SDO_COORD_OP_PARAM_VALS (
           COORD_OP_ID,
           COORD_OP_METHOD_ID,
           PARAMETER_ID,
           PARAMETER_VALUE,
           PARAM_VALUE_FILE_REF,
           UOM_ID)
     VALUES (
           18061,
           9801,
           8802,
           -81,
           NULL,
           9110);
    
    INSERT INTO SDO_COORD_OP_PARAM_VALS (
           COORD_OP_ID,
           COORD_OP_METHOD_ID,
           PARAMETER_ID,
           PARAMETER_VALUE,
           PARAM_VALUE_FILE_REF,
           UOM_ID)
     VALUES (
           18061,
           9801,
           8805,
           .99993602,
           NULL,
           9201);
    
   INSERT INTO SDO_COORD_OP_PARAM_VALS (
           COORD_OP_ID,
           COORD_OP_METHOD_ID,
           PARAMETER_ID,
           PARAMETER_VALUE,
           PARAM_VALUE_FILE_REF,
           UOM_ID)
     VALUES (
           18061,
           9801,
           8806,
           500000,
           NULL,
           9001);
    
   INSERT INTO SDO_COORD_OP_PARAM_VALS (
           COORD_OP_ID,
           COORD_OP_METHOD_ID,
           PARAMETER_ID,
           PARAMETER_VALUE,
           PARAM_VALUE_FILE_REF,
           UOM_ID)
     VALUES (
           18061,
           9801,
           8807,
           280296.016,
           NULL,
           9001);
   

Example 6-10 provides an extended, annotated example of creating a user-defined projected coordinate system

-- Create an EPSG equivalent for the following CRS:
--
-- CS_NAME:    VDOT_LAMBERT
-- SRID:       51000000
-- AUTH_SRID:  51000000
-- AUTH_NAME:  VDOT Custom Lambert Conformal Conic
-- WKTEXT:
--
-- PROJCS[
--   "VDOT_Lambert",
--   GEOGCS[
--     "GCS_North_American_1983",
--     DATUM[
--       "D_North_American_1983",
--       SPHEROID["GRS_1980", 6378137.0, 298.257222101]],
--     PRIMEM["Greenwich", 0.0],
--     UNIT["Decimal Degree",0.0174532925199433]],
--   PROJECTION["Lambert_Conformal_Conic"],
--   PARAMETER["False_Easting", 0.0],
--   PARAMETER["False_Northing", 0.0],
--   PARAMETER["Central_Meridian", -79.5],
--   PARAMETER["Standard_Parallel_1", 37.0],
--   PARAMETER["Standard_Parallel_2", 39.5],
--   PARAMETER["Scale_Factor", 1.0],
--   PARAMETER["Latitude_Of_Origin", 36.0],
--   UNIT["Meter", 1.0]]
 
-- First, the base geographic CRS (GCS_North_American_1983) already exists in EPSG.
-- It is 4269:
-- Next, find the EPSG equivalent for PROJECTION["Lambert_Conformal_Conic"]:
select
  coord_op_method_id,
  legacy_name
from
  sdo_coord_op_methods
where
  not legacy_name is null
order by
  coord_op_method_id;
 
-- Result:
-- COORD_OP_METHOD_ID LEGACY_NAME
-- ------------------ --------------------------------------------------
--               9802 Lambert Conformal Conic
--               9803 Lambert Conformal Conic (Belgium 1972)
--               9805 Mercator
--               9806 Cassini
--               9807 Transverse Mercator
--               9829 Polar Stereographic
-- 
-- 6 rows selected.
--
-- It is EPSG method 9802. Create a projection operation 510000001, based on it:
 
insert into MDSYS.SDO_COORD_OPS (
        COORD_OP_ID,
        COORD_OP_NAME,
        COORD_OP_TYPE,
        SOURCE_SRID,
        TARGET_SRID,
        COORD_TFM_VERSION,
        COORD_OP_VARIANT,
        COORD_OP_METHOD_ID,
        UOM_ID_SOURCE_OFFSETS,
        UOM_ID_TARGET_OFFSETS,
        INFORMATION_SOURCE,
        DATA_SOURCE,
        SHOW_OPERATION,
        IS_LEGACY,
        LEGACY_CODE,
        REVERSE_OP,
        IS_IMPLEMENTED_FORWARD,
        IS_IMPLEMENTED_REVERSE)
VALUES (
        510000001,
        'VDOT_Lambert',
        'CONVERSION',
        NULL,
        NULL,
        NULL,
        NULL,
        9802,
        NULL,
        NULL,
        NULL,
        NULL,
        1,
        'FALSE',
        NULL,
        1,
        1,
        1);
 
-- Now, set the parameters. See which are required:
 
select
  use.parameter_id || ': ' ||
  use.legacy_param_name
from
  sdo_coord_op_param_use use
where
  use.coord_op_method_id = 9802;
 
-- result:
-- 8821: Latitude_Of_Origin
-- 8822: Central_Meridian
-- 8823: Standard_Parallel_1
-- 8824: Standard_Parallel_2
-- 8826: False_Easting
-- 8827: False_Northing
--
-- 6 rows selected.
 
-- Also check the most common units we will need:
 
select
  UOM_ID || ': ' ||
  UNIT_OF_MEAS_NAME
from
  sdo_units_of_measure
where
  uom_id in (9001, 9101, 9102, 9201)
order by
  uom_id;
 
-- result:
-- 9001: metre
-- 9101: radian
-- 9102: degree
-- 9201: unity
 
-- Now, configure the projection parameters:
 
-- 8821: Latitude_Of_Origin
 
    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
        COORD_OP_ID,
        COORD_OP_METHOD_ID,
        PARAMETER_ID,
        PARAMETER_VALUE,
        PARAM_VALUE_FILE_REF,
        UOM_ID)
      VALUES (
        510000001,
        9802,
        8821,
        36.0,
        NULL,
        9102);
 
-- 8822: Central_Meridian
 
    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
        COORD_OP_ID,
        COORD_OP_METHOD_ID,
        PARAMETER_ID,
        PARAMETER_VALUE,
        PARAM_VALUE_FILE_REF,
        UOM_ID)
      VALUES (
        510000001,
        9802,
        8822,
        -79.5,
        NULL,
        9102);
 
-- 8823: Standard_Parallel_1
 
    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
        COORD_OP_ID,
        COORD_OP_METHOD_ID,
        PARAMETER_ID,
        PARAMETER_VALUE,
        PARAM_VALUE_FILE_REF,
        UOM_ID)
      VALUES (
        510000001,
        9802,
        8823,
        37.0,
        NULL,
        9102);
 
-- 8824: Standard_Parallel_2
 
    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
        COORD_OP_ID,
        COORD_OP_METHOD_ID,
        PARAMETER_ID,
        PARAMETER_VALUE,
        PARAM_VALUE_FILE_REF,
        UOM_ID)
      VALUES (
        510000001,
        9802,
        8824,
        39.5,
        NULL,
        9102);
 
-- 8826: False_Easting
 
    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
        COORD_OP_ID,
        COORD_OP_METHOD_ID,
        PARAMETER_ID,
        PARAMETER_VALUE,
        PARAM_VALUE_FILE_REF,
        UOM_ID)
      VALUES (
        510000001,
        9802,
        8826,
        0.0,
        NULL,
        9001);
 
-- 8827: False_Northing
 
    insert into MDSYS.SDO_COORD_OP_PARAM_VALS (
        COORD_OP_ID,
        COORD_OP_METHOD_ID,
        PARAMETER_ID,
        PARAMETER_VALUE,
        PARAM_VALUE_FILE_REF,
        UOM_ID)
      VALUES (
        510000001,
        9802,
        8827,
        0.0,
        NULL,
        9001);
 
-- Now, create the actual projected CRS.Look at the GEOG_CRS_DATUM_ID 
-- and COORD_SYS_ID first. The GEOG_CRS_DATUM_ID is the datum of 
-- the base geog_crs (4269):
 
select datum_id from sdo_coord_ref_sys where srid = 4269;
 
--   DATUM_ID
-- ----------
--       6269
 
-- And the COORD_SYS_ID is the Cartesian CS used for the projected CRS.
-- We can use 4400, if meters will be the unit:
 
select COORD_SYS_NAME from sdo_coord_sys where COORD_SYS_ID = 4400;
 
-- Cartesian 2D CS. Axes: easting, northing (E,N). Orientations: east, north.
-- UoM: m.
 
-- Now create the projected CRS:
 
insert into MDSYS.SDO_COORD_REF_SYSTEM (
        SRID,
        COORD_REF_SYS_NAME,
        COORD_REF_SYS_KIND,
        COORD_SYS_ID,
        DATUM_ID,
        SOURCE_GEOG_SRID,
        PROJECTION_CONV_ID,
        CMPD_HORIZ_SRID,
        CMPD_VERT_SRID,
        INFORMATION_SOURCE,
        DATA_SOURCE,
        IS_LEGACY,
        LEGACY_CODE,
        LEGACY_WKTEXT,
        LEGACY_CS_BOUNDS,
        GEOG_CRS_DATUM_ID)
VALUES (
        51000000,
        'VDOT_LAMBERT',
        'PROJECTED',
        4400,
        NULL,
        4269,
        510000001,
        NULL,
        NULL,
        NULL,
        NULL,
        'FALSE',
        NULL,
        NULL,
        NULL,
        6269);
 
-- To see the result:
 
select srid, wktext from cs_srs where srid = 51000000;
 
--  51000000
-- PROJCS[
--   "VDOT_LAMBERT",
--   GEOGCS [
--     "NAD83",
--     DATUM [
--       "North American Datum 1983 (EPSG ID 6269)",
--       SPHEROID [
--         "GRS 1980 (EPSG ID 7019)",
--         6378137,
--         298.257222101]],
--     PRIMEM [ "Greenwich", 0.000000 ],
--     UNIT ["Decimal Degree", 0.01745329251994328]],
--   PROJECTION ["VDOT_Lambert"],
--   PARAMETER ["Latitude_Of_Origin", 36],
--   PARAMETER ["Central_Meridian", -79.50000000000000000000000000000000000028],
--   PARAMETER ["Standard_Parallel_1", 37],
--   PARAMETER ["Standard_Parallel_2", 39.5],
--   PARAMETER ["False_Easting", 0],
--   PARAMETER ["False_Northing", 0],
--   UNIT ["Meter", 1]]

6.9.3 Creating a Vertical CRS

A vertical CRS has only one dimension, usually height. On its own, a vertical CRS is of little use, but it can be combined with a two-dimensional CRS (geodetic or projected), to result in a compound CRS. Example 6-11 shows the statement that created the vertical CRS with SRID 5701, which is included with Spatial. This definition refers to an existing (one-dimensional) coordinate system (ID 6499; see Section 6.7.11, "SDO_COORD_SYS Table") and vertical datum (ID 5101; see Section 6.7.22, "SDO_DATUMS Table").

INSERT INTO MDSYS.SDO_COORD_REF_SYSTEM (
    SRID,
    COORD_REF_SYS_NAME,
    COORD_REF_SYS_KIND,
    COORD_SYS_ID,
    DATUM_ID,
    SOURCE_GEOG_SRID,
    PROJECTION_CONV_ID,
    CMPD_HORIZ_SRID,
    CMPD_VERT_SRID,
    INFORMATION_SOURCE,
    DATA_SOURCE,
    IS_LEGACY,
    LEGACY_CODE,
    LEGACY_WKTEXT,
    LEGACY_CS_BOUNDS)
  VALUES (
    5701,
    'Newlyn',
    'VERTICAL',
    6499,
    5101,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    'EPSG',
    'FALSE',
    NULL,
    NULL,
    NULL);

A vertical CRS might define some undulating equipotential surface. The shape of that surface, and its offset from some ellipsoid, is not actually defined in the vertical CRS record itself (other than textually). Instead, that definition is included in an operation between the vertical CRS and another CRS. Consequently, you can define several alternative operations between the same pair of geoidal and WGS84-ellipsoidal heights. For example, there are geoid offset matrixes GEOID90, GEOID93, GEOID96, GEOID99, GEOID03, GEOID06, and others, and for each of these variants there can be a separate operation. Section 6.9.6 describes such an operation.

6.9.4 Creating a Compound CRS

A compound CRS combines an existing horizontal (two-dimensional) CRS and a vertical (one-dimensional) CRS. The horizontal CRS can be geodetic or projected. Example 6-12 shows the statement that created the compound CRS with SRID 7405, which is included with Spatial. This definition refers to an existing projected CRS and vertical CRS (IDs 27700 and 5701, respectively; see Section 6.7.9, "SDO_COORD_REF_SYS Table").

INSERT INTO MDSYS.SDO_COORD_REF_SYSTEM (
    SRID,
    COORD_REF_SYS_NAME,
    COORD_REF_SYS_KIND,
    COORD_SYS_ID,
    DATUM_ID,
    SOURCE_GEOG_SRID,
    PROJECTION_CONV_ID,
    CMPD_HORIZ_SRID,
    CMPD_VERT_SRID,
    INFORMATION_SOURCE,
    DATA_SOURCE,
    IS_LEGACY,
    LEGACY_CODE,
    LEGACY_WKTEXT,
    LEGACY_CS_BOUNDS)
  VALUES (
    7405,
    'OSGB36 / British National Grid + ODN',
    'COMPOUND',
    NULL,
    NULL,
    NULL,
    NULL,
    27700,
    5701,
    NULL,
    'EPSG',
    'FALSE',
    NULL,
    NULL,
    NULL);

6.9.5 Creating a Geographic 3D CRS

A geographic 3D CRS is the combination of a geographic 2D CRS with ellipsoidal height. Example 6-13 shows the statement that created the geographic 3D CRS with SRID 4327, which is included with Spatial. This definition refers to an existing projected coordinate system (ID 6401; see Section 6.7.11, "SDO_COORD_SYS Table") and datum (ID 6326; see Section 6.7.22, "SDO_DATUMS Table").

INSERT INTO MDSYS.SDO_COORD_REF_SYSTEM (
   SRID,
   COORD_REF_SYS_NAME,
   COORD_REF_SYS_KIND,
   COORD_SYS_ID,
   DATUM_ID,
   GEOG_CRS_DATUM_ID,
   SOURCE_GEOG_SRID,
   PROJECTION_CONV_ID,
   CMPD_HORIZ_SRID,
   CMPD_VERT_SRID,
   INFORMATION_SOURCE,
   DATA_SOURCE,
   IS_LEGACY,
   LEGACY_CODE,
   LEGACY_WKTEXT,
   LEGACY_CS_BOUNDS,
   IS_VALID,
   SUPPORTS_SDO_GEOMETRY)
 VALUES (
   4327,
   'WGS 84 (geographic 3D)',
   'GEOGRAPHIC3D',
   6401,
   6326,
   6326,
   NULL,
   NULL,
   NULL,
   NULL,
   'NIMA TR8350.2 January 2000 revision. http://164.214.2.59/GandG/tr8350_2.html',
   'EPSG',
   'FALSE',
   NULL,
   NULL,
   NULL,
   'TRUE',
   'TRUE');

6.9.6 Creating a Transformation Operation

Section 6.9.2 described the creation of a projection operation, for the purpose of then creating a projected CRS. A similar requirement can arise when using a compound CRS based on orthometric height: you may want to transform from and to ellipsoidal height. The offset between the two heights is undulating and irregular.

By default, Spatial transforms between ellipsoidal and orthometric height using an identity transformation. (Between different ellipsoids, the default would instead be a datum transformation.) The identity transformation is a reasonable approximation; however, a more accurate approach involves an EPSG type 9635 operation, involving an offset matrix. Example 6-14 is a declaration of such an operation:

INSERT INTO MDSYS.SDO_COORD_OPS (
   COORD_OP_ID,
   COORD_OP_NAME,
   COORD_OP_TYPE,
   SOURCE_SRID,
   TARGET_SRID,
   COORD_TFM_VERSION,
   COORD_OP_VARIANT,
   COORD_OP_METHOD_ID,
   UOM_ID_SOURCE_OFFSETS,
   UOM_ID_TARGET_OFFSETS,
   INFORMATION_SOURCE,
   DATA_SOURCE,
   SHOW_OPERATION,
   IS_LEGACY,
   LEGACY_CODE,
   REVERSE_OP,
   IS_IMPLEMENTED_FORWARD,
   IS_IMPLEMENTED_REVERSE)
 VALUES (
   999998,
   'Test operation, based on GEOID03 model, using Hawaii grid',
   'TRANSFORMATION',
   NULL,
   NULL,
   NULL,
   NULL,
   9635,
   NULL,
   NULL,
   'NGS',
   'NGS',
   1,
   'FALSE',
   NULL,
   1,
   1,
   1);
 
INSERT INTO MDSYS.SDO_COORD_OP_PARAM_VALS (
   COORD_OP_ID,
   COORD_OP_METHOD_ID,
   PARAMETER_ID,
   PARAMETER_VALUE,
   PARAM_VALUE_FILE_REF,
   UOM_ID)
 VALUES (
   999998,
   9635,
   8666,
   NULL,
   'g2003h01.asc',
   NULL);

The second INSERT statement in Example 6-14 specifies the file name g2003h01.asc, but not yet its actual CLOB content with the offset matrix. As with NADCON and NTv2 matrixes, geoid matrixes have to be loaded into the PARAM_VALUE_FILE column. Due to space and copyright considerations, Oracle does not supply most of these matrixes; however, they are usually available for download on the Web. Good sources are the relevant government websites, and you can search by file name (such as g2003h01 in this example). Although some of these files are available in both binary format (such as .gsb) and ASCII format (such as .gsa or .asc), only the ASCII variant can be used with Spatial. The existing EPSG operations include file names in standard use.

Example 6-15 is a script for loading a set of such matrixes. It loads specified physical files (such as ntv20.gsa) into database CLOBs, based on the official file name reference (such as NTV2_0.GSB).

DECLARE
  ORCL_HOME_DIR VARCHAR2(128);
  ORCL_WORK_DIR VARCHAR2(128);
  Src_loc       BFILE;
  Dest_loc      CLOB;
  CURSOR PARAM_FILES IS
    SELECT
      COORD_OP_ID,
      PARAMETER_ID,
      PARAM_VALUE_FILE_REF
    FROM
      MDSYS.SDO_COORD_OP_PARAM_VALS
    WHERE
      PARAMETER_ID IN (8656, 8657, 8658, 8666);
  PARAM_FILE PARAM_FILES%ROWTYPE;
  ACTUAL_FILE_NAME VARCHAR2(128);
  platform NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY work_dir AS ''define_your_source_directory_here''';
 
  FOR PARAM_FILE IN PARAM_FILES LOOP
    CASE UPPER(PARAM_FILE.PARAM_VALUE_FILE_REF)
      /* NTv2, fill in your files here */
      WHEN 'NTV2_0.GSB'   THEN ACTUAL_FILE_NAME := 'ntv20.gsa';
      /* GEOID03, fill in your files here */
      WHEN 'G2003H01.ASC' THEN ACTUAL_FILE_NAME := 'g2003h01.asc';
      ELSE                     ACTUAL_FILE_NAME := NULL;
    END CASE;
 
    IF(NOT (ACTUAL_FILE_NAME IS NULL)) THEN
      BEGIN
        dbms_output.put_line('Loading file ' || actual_file_name || '...');
        Src_loc := BFILENAME('WORK_DIR', ACTUAL_FILE_NAME);
        DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
      END;
 
      UPDATE
        MDSYS.SDO_COORD_OP_PARAM_VALS
      SET
        PARAM_VALUE_FILE = EMPTY_CLOB()
      WHERE
        COORD_OP_ID = PARAM_FILE.COORD_OP_ID AND
        PARAMETER_ID = PARAM_FILE.PARAMETER_ID
      RETURNING
        PARAM_VALUE_FILE INTO Dest_loc;
 
      DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
      DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, DBMS_LOB.LOBMAXSIZE);
      DBMS_LOB.CLOSE(Dest_loc);
      DBMS_LOB.CLOSE(Src_loc);
      DBMS_LOB.FILECLOSE(Src_loc);
    END IF;
  END LOOP;
END;
/

6.9.7 Using British Grid Transformation OSTN02/OSGM02 (EPSG Method 9633)

To use British Grid Transformation OSTN02/OSGM02 (EPSG method 9633) in a projected coordinate reference system, you must first insert a modified version of the OSTN02_OSGM02_GB.txt grid file into the PARAM_VALUE_FILE column (type CLOB) of the SDO_COORD_OP_PARAM_VALS table (described in Section 6.7.5). The OSTN02_OSGM02_GB.txt file contains the offset matrix on which EPSG transformation method 9633 is based.

Follow these steps:

1. Download the following file: http://www.ordnancesurvey.co.uk/docs/gps/ostn02-osgm02-files.zip

2. From this .zip file, extract the following file: OSTN02_OSGM02_GB.txt

3. Edit your copy of OSTN02_OSGM02_GB.txt, and insert the following lines before the first line of the current file:

   SDO Header
   x: 0.0 - 700000.0
   y: 0.0 - 1250000.0
   x-intervals: 1000.0
   y-intervals: 1000.0
   End of SDO Header
   

   The is, after the editing operation, the contents of the file will look like this:

   SDO Header
   x: 0.0 - 700000.0
   y: 0.0 - 1250000.0
   x-intervals: 1000.0
   y-intervals: 1000.0
   End of SDO Header
   1,0,0,0.000,0.000,0.000,0
   2,1000,0,0.000,0.000,0.000,0
   3,2000,0,0.000,0.000,0.000,0
   4,3000,0,0.000,0.000,0.000,0
   5,4000,0,0.000,0.000,0.000,0
   . . .
   876949,698000,1250000,0.000,0.000,0.000,0
   876950,699000,1250000,0.000,0.000,0.000,0
   876951,700000,1250000,0.000,0.000,0.000,0
   

4. Save the edited file, perhaps using a different name (for example, my_OSTN02_OSGM02_GB.txt).

5. In the SDO_COORD_OP_PARAM_VALS table, for each operation of EPSG method 9633 that has PARAM_VALUE_FILE_REF value OSTN02_OSGM02_GB.TXT, update the PARAM_VALUE_FILE column to be the contents of the saved file (for example, the contents of my_OSTN02_OSGM02_GB.txt). You can use coding similar to that in Example 6-16.

   Example 6-16 Using British Grid Transformation OSTN02/OSGM02 (EPSG Method 9633)

   DECLARE
     ORCL_HOME_DIR VARCHAR2(128);
     ORCL_WORK_DIR VARCHAR2(128);
     Src_loc       BFILE;
     Dest_loc      CLOB;
     CURSOR PARAM_FILES IS
       SELECT
         COORD_OP_ID,
         PARAMETER_ID,
         PARAM_VALUE_FILE_REF
       FROM
         MDSYS.SDO_COORD_OP_PARAM_VALS
       WHERE
         PARAMETER_ID IN (8656, 8657, 8658, 8664, 8666)
       order by
         COORD_OP_ID,
         PARAMETER_ID;
     PARAM_FILE PARAM_FILES%ROWTYPE;
     ACTUAL_FILE_NAME VARCHAR2(128);
     platform NUMBER;
   BEGIN
     EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY work_dir AS ''' || system.geor_dir || '''';
    
     FOR PARAM_FILE IN PARAM_FILES LOOP
       CASE UPPER(PARAM_FILE.PARAM_VALUE_FILE_REF)
         /* NTv2 */
         WHEN 'NTV2_0.GSB'   THEN ACTUAL_FILE_NAME := 'ntv20.gsa';
         /* GEOID03 */
         WHEN 'G2003H01.ASC' THEN ACTUAL_FILE_NAME := 'g2003h01.asc';
         /* British Ordnance Survey (9633) */
         WHEN 'OSTN02_OSGM02_GB.TXT'
                             THEN ACTUAL_FILE_NAME := 'my_OSTN02_OSGM02_GB.txt';
         ELSE                ACTUAL_FILE_NAME := NULL;
       END CASE;
    
       IF(NOT (ACTUAL_FILE_NAME IS NULL)) THEN
         BEGIN
           dbms_output.put_line('Loading file ' || actual_file_name || '...');
           Src_loc := BFILENAME('WORK_DIR', ACTUAL_FILE_NAME);
           DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
         END;
    
         UPDATE
           MDSYS.SDO_COORD_OP_PARAM_VALS
         SET
           PARAM_VALUE_FILE = EMPTY_CLOB()
         WHERE
           COORD_OP_ID = PARAM_FILE.COORD_OP_ID AND
           PARAMETER_ID = PARAM_FILE.PARAMETER_ID
         RETURNING
           PARAM_VALUE_FILE INTO Dest_loc;
    
         DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
         DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, DBMS_LOB.LOBMAXSIZE);
         DBMS_LOB.CLOSE(Dest_loc);
         DBMS_LOB.CLOSE(Src_loc);
         DBMS_LOB.FILECLOSE(Src_loc);
       END IF;
     END LOOP;
   END;
   /
   

Note that adding "header" information to a grid file is required only for British Grid Transformation OSTN02/OSGM02. It is not required for NADCON, NTv2, or VERTCON matrixes, because they already have headers of varying formats.

See also the following for related information:

• Section 6.9.2, "Creating a Projected CRS"

• Section 6.9.6, "Creating a Transformation Operation"

6.10.1 Different Coordinate Systems for Geometries with Operators and Functions

For Spatial operators (described in Chapter 19) that take two geometries as input parameters, if the geometries are based on different coordinate systems, the query window (the second geometry) is transformed to the coordinate system of the first geometry before the operation is performed. This transformation is a temporary internal operation performed by Spatial; it does not affect any stored query-window geometry.

For SDO_GEOM package geometry functions (described in Chapter 24) that take two geometries as input parameters, both geometries must be based on the same coordinate system.

6.10.2 3D LRS Functions Not Supported with Geodetic Data

In the current release, the 3D formats of LRS functions (explained in Section 7.4) are not supported with geodetic data.

6.10.3 Functions Supported by Approximations with Geodetic Data

In the current release, the following functions are supported by approximations with geodetic data:

• SDO_GEOM.SDO_BUFFER

• SDO_GEOM.SDO_CENTROID

• SDO_GEOM.SDO_CONVEXHULL

When these functions are used on data with geodetic coordinates, they internally perform the operations in an implicitly generated local-tangent-plane Cartesian coordinate system and then transform the results to the geodetic coordinate system. For SDO_GEOM.SDO_BUFFER, generated arcs are approximated by line segments before the back-transform.

6.10.4 Unknown CRS and NaC Coordinate Reference Systems

The following coordinate reference systems are provided for Oracle internal use and for other possible special uses:

• unknown CRS (SRID 999999) means that the coordinate system is unknown, and its space could be geodetic or Cartesian. Contrast this with specifying a null coordinate reference system, which indicates an unknown coordinate system with a Cartesian space.

• NaC (SRID 999998) means Not-a-CRS. Its name is patterned after the NaN (Not-a-Number) value in Java. It is intended for potential use with nonspatial geometries.

The following restrictions apply to geometries based on the unknown CRS and NaC coordinate reference systems:

• You cannot perform coordinate system transformations on these geometries.

• Operations that require a coordinate system will return a null value when performed on these geometries. These operations include finding the area or perimeter of a geometry, creating a buffer, densifying an arc, and computing the aggregate centroid.