SNAPSHOT Tables
6.9 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
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).
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');
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
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,