1. Spatial Developer's Guide
  2. Reference Information
  3. SDO_CS Package (Coordinate System Transformation)
  4. SDO_CS.GENERATE_SCRIPT_FROM_SRID

22.20 SDO_CS.GENERATE_SCRIPT_FROM_SRID

Format

GENERATE_SCRIPT_FROM_SRID(
     srid          IN NUMBER, 
     offset        IN NUMBER DEFAULT 0, 
     include_units IN NUMBER DEFAULT 1) RETURN CLOB;

Description

Returns a CLOB object that includes the SQL statements necessary to create the coordinate system with the specified SRID value.

Parameters

srid

The SRID of the coordinate reference system. Must be a value in the SRID column of the SDO_COORD_REF_SYS table (described in SDO_COORD_REF_SYS Table).

offset

A number to be added to the SRID value of the coordinate system created by the generated script. For example, specifying SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700, 50000) would cause the INSERT statements the resulting script to specify the SRID as 77700 (that is, 27700 + 50000). The purpose might be to ensure that a new unique SRID gets generated if you know that you do not have any current SRIDs equal to or greater than 77700.

The default offset value is 0 (zero).

include_units

The numeric value 0 means not to include units of measure in generated statements; the numeric value 1 (the default) means to include units of measure in generated statements.

The default value is recommended in virtually all cases. Exceptions, if any, should be rare, and only if there are “nonstandard” units of measure for attributes.

Usage Notes

Before using this function, you must use the SQL*Plus command SET LONG to increase the maximum width in bytes for column output. For example: SET LONG 20000

If you plan to use the output to help you modify a coordinate system definition or to create a new definition -- as opposed to just viewing the information -- you must edit the output as needed to ensure syntactic correctness (such as for the INSERT statements).

The script can be run on a different (target) database or on the same database on which you executed this function. In either case, there might be an existing coordinate system associated with the SRID in question; and in this case you might want to take action to deal with that scenario (such as using the offeet parameter).

You are discouraged from making changes to “standard” coordinate system definitions.

Examples

The following example returns a CLOB object that includes the statements necessary to define the coordinate system with the SRID value 27700.

SQL> SET LONG 20000

SQL> SELECT sdo_cs.GENERATE_SCRIPT_FROM_SRID(27700) FROM DUAL;

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
insert into mdsys.sdo_units_of_measure (
  UOM_ID,
  UNIT_OF_MEAS_NAME,
  SHORT_NAME,
  LEGACY_UNIT_NAME,
  UNIT_OF_MEAS_TYPE,
  TARGET_UOM_ID,
  FACTOR_B,
  FACTOR_C,
  INFORMATION_SOURCE,
  DATA_SOURCE,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  IS_LEGACY,
  LEGACY_CODE)
values (
  9001,
  'metre',
  'METRE_9001',
  'Meter',
  'length',
  9001,
  1,
  1,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'ISO 1000.',
  'EPSG',
  'FALSE',
  NULL);

insert into mdsys.sdo_units_of_measure (
  UOM_ID,
  UNIT_OF_MEAS_NAME,
  SHORT_NAME,
  LEGACY_UNIT_NAME,
  UNIT_OF_MEAS_TYPE,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  TARGET_UOM_ID,
  FACTOR_B,
  FACTOR_C,
  INFORMATION_SOURCE,
  DATA_SOURCE,
  IS_LEGACY,
  LEGACY_CODE)
values (
  9102,
  'degree',
  'DEGREE_EPSG_9102',

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'Decimal Degree',
  'angle',
  9101,
  3.14159265358979,
  180,
  NULL,
  'EPSG',
  'FALSE',
  NULL);

insert into mdsys.sdo_units_of_measure (

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  UOM_ID,
  UNIT_OF_MEAS_NAME,
  SHORT_NAME,
  LEGACY_UNIT_NAME,
  UNIT_OF_MEAS_TYPE,
  TARGET_UOM_ID,
  FACTOR_B,
  FACTOR_C,
  INFORMATION_SOURCE,
  DATA_SOURCE,
  IS_LEGACY,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  LEGACY_CODE)
values (
  9110,
  'sexagesimal DMS',
  'SEXAGESIMAL_DMS_EPSG_9110',
  NULL,
  'angle',
  9101,
  3.14159265358979,
  180,
  'EPSG',

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'EPSG',
  'FALSE',
  NULL);

insert into mdsys.sdo_units_of_measure (
  UOM_ID,
  UNIT_OF_MEAS_NAME,
  SHORT_NAME,
  LEGACY_UNIT_NAME,
  UNIT_OF_MEAS_TYPE,
  TARGET_UOM_ID,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  FACTOR_B,
  FACTOR_C,
  INFORMATION_SOURCE,
  DATA_SOURCE,
  IS_LEGACY,
  LEGACY_CODE)
values (
  9122,
  'degree (supplier to define representation)',
  'DEGREE_SUPPLIER_DEFINED_9122',
  'Decimal Degree',

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'angle',
  9101,
  3.14159265358979,
  180,
  'EPSG',
  'EPSG',
  'FALSE',
  NULL);

insert into mdsys.sdo_units_of_measure (
  UOM_ID,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  UNIT_OF_MEAS_NAME,
  SHORT_NAME,
  LEGACY_UNIT_NAME,
  UNIT_OF_MEAS_TYPE,
  TARGET_UOM_ID,
  FACTOR_B,
  FACTOR_C,
  INFORMATION_SOURCE,
  DATA_SOURCE,
  IS_LEGACY,
  LEGACY_CODE)

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
values (
  9201,
  'unity',
  'UNITY_9201',
  NULL,
  'scale',
  9201,
  1,
  1,
  NULL,
  'EPSG',

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'FALSE',
  NULL);

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,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  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)

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
values (
  19916,
  'British National Grid (EPSG OP 19916)',
  'CONVERSION',
  NULL,
  NULL,
  NULL,
  NULL,
  9807,
  NULL,
  NULL,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'Ordnance Survey of Great Britain.  http://www.gps.gov.uk/additionalInfo/image
s/A_guide_to_coord.pdf',
  'EPSG',
  1,
  'FALSE',
  NULL,
  1,
  1,
  1);

insert into mdsys.sdo_coord_op_param_vals (

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  COORD_OP_ID,
  COORD_OP_METHOD_ID,
  PARAMETER_ID,
  PARAMETER_VALUE,
  PARAM_VALUE_FILE_REF,
  PARAM_VALUE_FILE,
  PARAM_VALUE_XML,
  UOM_ID)
values (
  19916,
  9807,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  8801,
  49,
  NULL,
  null,
  null,
  9102);

insert into mdsys.sdo_coord_op_param_vals (
  COORD_OP_ID,
  COORD_OP_METHOD_ID,
  PARAMETER_ID,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  PARAMETER_VALUE,
  PARAM_VALUE_FILE_REF,
  PARAM_VALUE_FILE,
  PARAM_VALUE_XML,
  UOM_ID)
values (
  19916,
  9807,
  8802,
  -2,
  NULL,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  null,
  null,
  9102);

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,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  PARAM_VALUE_XML,
  UOM_ID)
values (
  19916,
  9807,
  8805,
  .9996012717,
  NULL,
  null,
  null,
  9201);

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------

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)
values (

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  19916,
  9807,
  8806,
  400000,
  NULL,
  null,
  null,
  9001);

insert into mdsys.sdo_coord_op_param_vals (
  COORD_OP_ID,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  COORD_OP_METHOD_ID,
  PARAMETER_ID,
  PARAMETER_VALUE,
  PARAM_VALUE_FILE_REF,
  PARAM_VALUE_FILE,
  PARAM_VALUE_XML,
  UOM_ID)
values (
  19916,
  9807,
  8807,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  -100000,
  NULL,
  null,
  null,
  9001);

insert into mdsys.sdo_ellipsoids (
  ELLIPSOID_ID,
  ELLIPSOID_NAME,
  SEMI_MAJOR_AXIS,
  UOM_ID,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  INV_FLATTENING,
  SEMI_MINOR_AXIS,
  INFORMATION_SOURCE,
  DATA_SOURCE,
  IS_LEGACY,
  LEGACY_CODE)
values (
  7001,
  'Airy 1830',
  6377563.396,
  9001,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  299.3249646,
  6356256.90923728512018673099343615524143,
  'Ordnance Survey of Great Britain.',
  'EPSG',
  'FALSE',
  8001);

insert into mdsys.sdo_prime_meridians (
  PRIME_MERIDIAN_ID,
  PRIME_MERIDIAN_NAME,
  GREENWICH_LONGITUDE,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  UOM_ID,
  INFORMATION_SOURCE,
  DATA_SOURCE)
values (
  8901,
  'Greenwich 8901',
  0,
  9110,
  NULL,
  'EPSG');


SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
insert into mdsys.sdo_datums (
  DATUM_ID,
  DATUM_NAME,
  DATUM_TYPE,
  ELLIPSOID_ID,
  PRIME_MERIDIAN_ID,
  INFORMATION_SOURCE,
  DATA_SOURCE,
  SHIFT_X,
  SHIFT_Y,
  SHIFT_Z,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  ROTATE_X,
  ROTATE_Y,
  ROTATE_Z,
  SCALE_ADJUST,
  IS_LEGACY,
  LEGACY_CODE)
values (
  6277,
  'OSGB 1936',
  'GEODETIC',
  7001,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  8901,
  'Ordnance Survey of Great Britain',
  'EPSG',
  446.448,
  -125.157,
  542.06,
  .15,
  .247,
  .842,
  -20.489,
  'FALSE',

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  NULL);

insert into mdsys.sdo_coord_sys (
  COORD_SYS_ID,
  COORD_SYS_NAME,
  COORD_SYS_TYPE,
  DIMENSION,
  INFORMATION_SOURCE,
  DATA_SOURCE)
values (
  6422,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'Ellipsoidal 2D CS. Axes: latitude, longitude. Orientations: north, east.  UoM
: deg 6422',
  'ellipsoidal',
  2,
  'EPSG',
  'EPSG');

insert into mdsys.sdo_coord_axes (
  COORD_SYS_ID,
  COORD_AXIS_NAME_ID,
  COORD_AXIS_ORIENTATION,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  COORD_AXIS_ABBREVIATION,
  UOM_ID,
  "ORDER")
values (
  6422,
  9901,
  'north',
  'Lat',
  9122,
  1);


SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
insert into mdsys.sdo_coord_axes (
  COORD_SYS_ID,
  COORD_AXIS_NAME_ID,
  COORD_AXIS_ORIENTATION,
  COORD_AXIS_ABBREVIATION,
  UOM_ID,
  "ORDER")
values (
  6422,
  9902,
  'east',

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'Long',
  9122,
  2);

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,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  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,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  SUPPORTS_SDO_GEOMETRY)
values (
  4277,
  'OSGB 1936',
  'GEOGRAPHIC2D',
  6422,
  6277,
  6277,
  NULL,
  NULL,
  NULL,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  NULL,
  NULL,
  'EPSG',
  'FALSE',
  NULL,
  NULL,
  null,
  'TRUE',
  'TRUE');

insert into mdsys.sdo_coord_sys (

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  COORD_SYS_ID,
  COORD_SYS_NAME,
  COORD_SYS_TYPE,
  DIMENSION,
  INFORMATION_SOURCE,
  DATA_SOURCE)
values (
  4400,
  'Cartesian 2D CS.  Axes: easting, northing (E,N). Orientations: east, north.
UoM: m. 4400',
  'Cartesian',

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  2,
  'EPSG',
  'EPSG');

insert into mdsys.sdo_coord_axes (
  COORD_SYS_ID,
  COORD_AXIS_NAME_ID,
  COORD_AXIS_ORIENTATION,
  COORD_AXIS_ABBREVIATION,
  UOM_ID,
  "ORDER")

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
values (
  4400,
  9906,
  'east',
  'E',
  9001,
  1);

insert into mdsys.sdo_coord_axes (
  COORD_SYS_ID,
  COORD_AXIS_NAME_ID,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  COORD_AXIS_ORIENTATION,
  COORD_AXIS_ABBREVIATION,
  UOM_ID,
  "ORDER")
values (
  4400,
  9907,
  'north',
  'N',
  9001,
  2);

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------

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,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  CMPD_VERT_SRID,
  INFORMATION_SOURCE,
  DATA_SOURCE,
  IS_LEGACY,
  LEGACY_CODE,
  LEGACY_WKTEXT,
  LEGACY_CS_BOUNDS,
  IS_VALID,
  SUPPORTS_SDO_GEOMETRY)
values (
  27700,

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'OSGB 1936 / British National Grid',
  'PROJECTED',
  4400,
  NULL,
  6277,
  4277,
  19916,
  NULL,
  NULL,
  NULL,
  'EPSG',

SDO_CS.GENERATE_SCRIPT_FROM_SRID(27700)
--------------------------------------------------------------------------------
  'FALSE',
  NULL,
  NULL,
  null,
  'TRUE',
  'TRUE');

Parent topic: SDO_CS Package (Coordinate System Transformation)