Had a situation where I was given a shape file from ESRI with EPSG 102005 which isn’t in the database. When importing the shapefile through MapBuilder it of course flamed so I needed to figure out how to add the coordinate system to the database.
Found this on MOS – Example Adding A New Coordinate System based on the EPSG model introduced in 10gR2 (Doc ID 395171.1) and was able to put together what I needed to import shapefile and work with it using MapBuilder and MapViewer Editor.
Here’s the SQL I used to insert new definitions into database –
/*
PROJCS[
"USA_Contiguous_Equidistant_Conic",
GEOGCS["GCS_North_American_1983",
DATUM[
"North_American_Datum_1983",
SPHEROID["GRS_1980",6378137,298.257222101]],
PRIMEM["Greenwich",0],
UNIT["Degree",0.017453292519943295]],
PROJECTION["Equidistant_Conic"],
PARAMETER["False_Easting",0],
PARAMETER["False_Northing",0],
PARAMETER["Longitude_Of_Center",-96],
PARAMETER["Standard_Parallel_1",33],
PARAMETER["Standard_Parallel_2",45],
PARAMETER["Latitude_Of_Center",39],
UNIT["Meter",1],
AUTHORITY["EPSG","102005"]]
*/
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 (
102005000,
'USA_Contiguous_Equidistant_Conic',
'CONVERSION',
NULL,
NULL,
NULL,
NULL,
9802,
NULL,
NULL,
NULL,
NULL,
1,
'FALSE',
NULL,
1,
1,
1);
//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 (
102005000,
9802,
8821,
39.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 (
102005000,
9802,
8822,
-96.0,
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 (
102005000,
9802,
8823,
33.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 (
102005000,
9802,
8824,
45.0,
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 (
102005000,
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 (
102005000,
9802,
8827,
0.0,
NULL,
9001);
// 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 (
102005000,
'USA_Contiguous_Equidistant_Conic',
'PROJECTED',
4530,
NULL,
4269,
102005000,
NULL,
NULL,
NULL,
NULL,
'FALSE',
NULL,
NULL,
NULL,
6269);
//Check your work
select srid, wktext from cs_srs where srid = 102005000;
select mdsys.sdo_cs.transform(
SDO_GEOMETRY( 2001, 4269, SDO_POINT_TYPE( -79.5, 36, NULL), NULL, NULL), 102005000)
from dual;