1. Spatial Developer's Guide
  2. Conceptual and Usage Information
  3. Coordinate Systems (Spatial Reference Systems)
  4. Creating a User-Defined Coordinate Reference System

6.9 Creating a User-Defined Coordinate Reference System

If the coordinate systems supplied by Oracle are not sufficient for your needs, you can create user-defined coordinate reference systems.

Note:

As mentioned in Coordinate System (Spatial Reference System) , the terms coordinate system and coordinate reference system (CRS) are often used interchangeably, although coordinate reference systems must be Earth-based.

The exact steps for creating a user-defined CRS depend on whether it is geodetic or projected. In both cases, supply information about the coordinate system (coordinate axes, axis names, unit of measurement, and so on). For a geodetic CRS, supply information about the datum (ellipsoid, prime meridian, and so on), as explained in Creating a Geodetic CRS. For a projected CRS, supply information about the source (geodetic) CRS and the projection (operation and parameters), as explained in Creating a Projected CRS.

For any user-defined coordinate system, the SRID value should be 5000000 to 6000000 (5 million to 6 million) which are available for user-defined coordinate systems.

Parent topic: Coordinate Systems (Spatial Reference Systems)

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

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 SDO_UNITS_OF_MEASURE Table), 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 SDO_COORD_AXES Table), 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 SDO_COORD_SYS Table), insert a row into that table. Example 6-6 inserts the definition for a fictitious coordinate system.
  4. If the ellipsoid is not already defined in the SDO_ELLIPSOIDS table (described in SDO_ELLIPSOIDS Table), 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 SDO_PRIME_MERIDIANS Table), 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 SDO_DATUMS Table), insert a row into that table to define the new datum.

Example 6-5 Creating a User-Defined Geodetic Coordinate Reference System

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');

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');

Parent topic: Creating a User-Defined Coordinate Reference System

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

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 SDO_UNITS_OF_MEASURE Table), 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 SDO_COORD_AXES Table), 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 SDO_COORD_SYS Table), insert a row into that table. (See Example 6-6 in Creating a Geodetic CRS).
  4. If the projection operation is not already defined in the SDO_COORD_OPS table (described in SDO_COORD_OPS Table), 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.
  5. If the parameters for the projection operation are not already defined in the SDO_COORD_OP_PARAM_VALS table (described in SDO_COORD_OP_PARAM_VALS Table), 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-7 Creating a User-Defined Projected Coordinate Reference System

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');

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

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 Creating a User-Defined Projected CRS: Extended Example

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

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

Parent topic: Creating a User-Defined Coordinate Reference System

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 SDO_COORD_SYS Table) and vertical datum (ID 5101; see SDO_DATUMS Table).

Example 6-11 Creating a Vertical Coordinate Reference System

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 WGS 84-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. Creating a Transformation Operation describes such an operation.

Parent topic: Creating a User-Defined Coordinate Reference System

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

Example 6-12 Creating a Compound Coordinate Reference System

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

Parent topic: Creating a User-Defined Coordinate Reference System

6.9.5 Creating a Geographic 3D CRS

A geographic 3D CRS is the combination of a geographic 2D CRS with ellipsoidal height.

Note:

Creating a 3D CRS is supported only if Oracle JVM is enabled on your Oracle Autonomous Database Serverless deployments. To enable Oracle JVM, see Use Oracle Java in Using Oracle Autonomous Database Serverless for more information.

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 SDO_COORD_SYS Table) and datum (ID 6326; see SDO_DATUMS Table).

Example 6-13 Creating a Geographic 3D Coordinate Reference System

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');

Parent topic: Creating a User-Defined Coordinate Reference System

6.9.6 Creating a Transformation Operation

Creating a Projected CRS 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:

Example 6-14 Creating a Transformation 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 Loading Offset Matrixes

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;
  dest_offset  number := 1;
  src_offset   number := 1;
  lang_context number := 0;
  warning      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.LOADCLOBFROMFILE(Dest_loc, Src_loc, DBMS_LOB.LOBMAXSIZE);
      declare
        src_offset number := 1 ;
        dst_offset number := 1 ;
        lang_ctx   number := dbms_lob.default_lang_ctx;
        warning    number;
      begin
        DBMS_LOB.LOADCLOBFROMFILE(Dest_loc, Src_loc, DBMS_LOB.LOBMAXSIZE,
          dst_offset,
          src_offset,                              
          dbms_lob.default_csid,
          lang_ctx,
          warning) ;
        if (warning = dbms_lob.warn_inconvertible_char) then
          dbms_output.put_line('Warning: Inconvertible character');
        end if;
      end;

      DBMS_LOB.CLOSE(Dest_loc);
      DBMS_LOB.CLOSE(Src_loc);
      DBMS_LOB.FILECLOSE(Src_loc);
    END IF;
  END LOOP;
END;
/

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

Parent topic: Creating a User-Defined Coordinate Reference System

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 SDO_COORD_OP_PARAM_VALS Table). 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.LOADCLOBFROMFILE(Dest_loc, Src_loc, DBMS_LOB.LOBMAXSIZE);
      declare
        src_offset number := 1 ;
        dst_offset number := 1 ;
        lang_ctx   number := dbms_lob.default_lang_ctx;
        warning    number;
      begin
        DBMS_LOB.LOADCLOBFROMFILE(Dest_loc, Src_loc, DBMS_LOB.LOBMAXSIZE,
          dst_offset,
          src_offset,                              
          dbms_lob.default_csid,
          lang_ctx,
          warning) ;
        if (warning = dbms_lob.warn_inconvertible_char) then
          dbms_output.put_line('Warning: Inconvertible character');
        end if;
      end;
      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:

Parent topic: Creating a User-Defined Coordinate Reference System