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.
- Creating a Geodetic CRS
- Creating a Projected CRS
- Creating a Vertical CRS
- Creating a Compound CRS
- Creating a Geographic 3D CRS
- Creating a Transformation Operation
- Using British Grid Transformation OSTN02/OSGM02 (EPSG Method 9633)
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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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:
- 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.
- 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.
- 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).
- 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.
- 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 not supported in Oracle Autonomous Database Serverless deployments.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:
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