Skip Headers
Oracle® Spatial User's Guide and Reference
10g Release 2 (10.2)

Part Number B14255-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

13 SDO_CS Package (Coordinate System Transformation)

The MDSYS.SDO_CS package contains subprograms for working with coordinate systems. You can perform explicit coordinate transformations on a single geometry or an entire layer of geometries (that is, all geometries in a specified column in a table).

To use the subprograms in this chapter, you must understand the conceptual information about coordinate systems in Section 1.5.4 and Chapter 6.

Table 13-1 lists the coordinate system transformation subprograms.

Table 13-1 Subprograms for Coordinate System Transformation

Subprogram Description

SDO_CS.ADD_PREFERENCE_FOR_OP


Adds a preference for an operation between a source coordinate system and a target coordinate system.

SDO_CS.CONVERT_NADCON_TO_XML


Converts a NADCON (North American Datum Conversion) grid in ASCII format to an Oracle Spatial XML representation.

SDO_CS.CONVERT_NTV2_TO_XML


Converts an NTv2 (National Transformation Version 2) grid in ASCII format to an Oracle Spatial XML representation.

SDO_CS.CONVERT_XML_TO_NADCON


Converts an Oracle Spatial XML representation of a NADCON (North American Datum Conversion) grid to NADCON ASCII format.

SDO_CS.CONVERT_XML_TO_NTV2


Converts an Oracle Spatial XML representation of an NTv2 (National Transformation Version 2) grid to NTv2 ASCII format.

SDO_CS.CREATE_CONCATENATED_OP


Creates a concatenated operation.

SDO_CS.CREATE_OBVIOUS_EPSG_RULES


Creates a basic set of EPSG rules to be applied in certain transformations.

SDO_CS.CREATE_PREF_CONCATENATED_OP


Creates a concatenated operation, associating it with a transformation plan and making it preferred either systemwide or for a specified use case.

SDO_CS.DELETE_ALL_EPSG_RULES


Deletes the basic set of EPSG rules to be applied in certain transformations.

SDO_CS.DELETE_OP


Deletes a concatenated operation.

SDO_CS.DETERMINE_CHAIN


Returns the query chain, based on the system rule set, to be used in transformations from one coordinate reference system to another coordinate reference system.

SDO_CS.DETERMINE_DEFAULT_CHAIN


Returns the default chain of SRID values in transformations from one coordinate reference system to another coordinate reference system.

SDO_CS.FIND_GEOG_CRS


Returns the SRID values of geodetic (geographic) coordinate reference systems that have the same well-known text (WKT) numeric values as the coordinate reference system with the specified reference SRID value.

SDO_CS.FIND_PROJ_CRS


Returns the SRID values of projected coordinate reference systems that have the same well-known text (WKT) numeric values as the coordinate reference system with the specified reference SRID value.

SDO_CS.FROM_OGC_SIMPLEFEATURE_SRS


Converts a well-known text string from the Open Geospatial Consortium simple feature format without the TOWGS84 keyword to the format that includes the TOWGS84 keyword.

SDO_CS.MAP_EPSG_SRID_TO_ORACLE


Returns the ORacle Spatial SRID values corresponding to the specified EPSG SRID value.

SDO_CS.MAP_ORACLE_SRID_TO_EPSG


Returns the EPSG SRID value corresponding to the specified Oracle Spatial SRID value.

SDO_CS.REVOKE_PREFERENCE_FOR_OP


Revokes a preference for an operation between a source coordinate system and a target coordinate system.

SDO_CS.TO_OGC_SIMPLEFEATURE_SRS


Converts a well-known text string from the Open Geospatial Consortium simple feature format that includes the TOWGS84 keyword to the format without the TOWGS84 keyword.

SDO_CS.TRANSFORM


Transforms a geometry representation using a coordinate system (specified by SRID or name).

SDO_CS.TRANSFORM_LAYER


Transforms an entire layer of geometries (that is, all geometries in a specified column in a table).

SDO_CS.UPDATE_WKTS_FOR_ALL_EPSG_CRS


Updates the well-known text (WKT) description for all EPSG coordinate reference systems.

SDO_CS.UPDATE_WKTS_FOR_EPSG_CRS


Updates the well-known text (WKT) description for the EPSG coordinate reference system associated with a specified SRID.

SDO_CS.UPDATE_WKTS_FOR_EPSG_DATUM


Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified datum.

SDO_CS.UPDATE_WKTS_FOR_EPSG_ELLIPS


Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified ellipsoid.

SDO_CS.UPDATE_WKTS_FOR_EPSG_OP


Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified coordinate transformation operation.

SDO_CS.UPDATE_WKTS_FOR_EPSG_PARAM


Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified coordinate transformation operation and parameter for transformation operations.

SDO_CS.UPDATE_WKTS_FOR_EPSG_PM


Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified prime meridian.

SDO_CS.VALIDATE_WKT


Validates the well-known text (WKT) description associated with a specified SRID.

SDO_CS.VIEWPORT_TRANSFORM (deprecated)

Transforms an optimized rectangle into a valid polygon for use with Spatial operators and functions.


The rest of this chapter provides reference information on the subprograms, listed in alphabetical order.


SDO_CS.ADD_PREFERENCE_FOR_OP

Format

SDO_CS.ADD_PREFERENCE_FOR_OP(

     op_id IN NUMBER,

     source_crs IN NUMBER DEFAULT NULL,

     target_crs IN NUMBER DEFAULT NULL,

     use_case IN VARCHAR2 DEFAULT NULL);

Description

Adds a preference for an operation between a source coordinate system and a target coordinate system.

Parameters

op_id

ID number of the operation. Must be a value in the COORD_OP_ID column of the SDO_COORD_OPS table (described in Section 6.6.8).

source_crs

The SRID of the source coordinate reference system. Must be null or a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

target_crs

The SRID of the target coordinate reference system. Must be null or a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

use_case

Name of the use case to be associated with this preference. Must be null or a value from the USE_CASE column of the SDO_PREFERRED_OPS_USER table (described in Section 6.6.25).

Usage Notes

If use_case is null, the transformation plan associated with the operation is a systemwide preference, and a row is added (or two rows are added if a reverse operation exists) to the SDO_PREFERRED_OPS_SYSTEM table (described in Section 6.6.24). If use_case is not null, the transformation plan associated with the operation is a preference associated with the specified use case, and a row is added (or two rows are added if a reverse operation exists) to the SDO_PREFERRED_OPS_USER table (described in Section 6.6.25).

To create a concatenated operation and make it preferred either systemwide or for a specified use case, you can use the SDO_CS.CREATE_PREF_CONCATENATED_OP convenience procedure.

To revoke a preference for an operation between a source coordinate system and a target coordinate system, use the SDO_CS.REVOKE_PREFERENCE_FOR_OP procedure.

Examples

The following example adds a preference for operation 19977 to be used in transformations from SRID 4301 to SRID 4326 when use case use_case_B is specified for the transformation.

EXECUTE SDO_CS.ADD_PREFERENCE_FOR_OP(19977, 4301, 4326, 'use_case_B');

SDO_CS.CONVERT_NADCON_TO_XML

Format

SDO_CS.CONVERT_NADCON_TO_XML(

     laa_clob IN CLOB,

     loa_clob IN CLOB,

     xml_grid OUT XMLTYPE );

Description

Converts a NADCON (North American Datum Conversion) grid in ASCII format to an Oracle Spatial XML representation.

Parameters

laa_clob

Latitude values of the NADCON grid in a CLOB object.

loa_clob

Longitude values of the NADCON grid in a CLOB object.

xml_grid

Output XML document containing the Oracle Spatial XML representation of the NADCON grid.

Usage Notes

To convert an Oracle Spatial XML representation to a NADCON grid, use the SDO_CS.CONVERT_XML_TO_NADCON procedure.

Examples

The following example converts a NADCON grid in ASCII format to an Oracle Spatial XML representation, converts the resulting XML representation back to a NADCON ASCII representation, and displays the resulting ASCII representation. (Only part of the output is shown.)

set lines 32000
set long 2000000000
 
DECLARE
  laa       CLOB;
  loa       CLOB;
  xml       XMLTYPE;
  laa_file  BFILE;
  loa_file  BFILE;
BEGIN
  laa_file := BFILENAME('MY_WORK_DIR', 'samplenadcon.laa');
  loa_file := BFILENAME('MY_WORK_DIR', 'samplenadcon.loa');
  DBMS_LOB.OPEN(laa_file, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.OPEN(loa_file, DBMS_LOB.LOB_READONLY);
 
  DBMS_LOB.CREATETEMPORARY(laa, TRUE, DBMS_LOB.SESSION);
  DBMS_LOB.CREATETEMPORARY(loa, TRUE, DBMS_LOB.SESSION);
  DBMS_LOB.OPEN(laa, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.OPEN(loa, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.LOADFROMFILE(laa, laa_file, DBMS_LOB.LOBMAXSIZE);
  DBMS_LOB.LOADFROMFILE(loa, loa_file, DBMS_LOB.LOBMAXSIZE);
  DBMS_LOB.CLOSE(laa);
  DBMS_LOB.CLOSE(loa);
  DBMS_LOB.CLOSE(laa_file);
  DBMS_LOB.CLOSE(loa_file);
 
  SDO_CS.convert_NADCON_to_XML(laa, loa, xml);
  SDO_CS.convert_XML_to_NADCON(xml, laa, loa);
  DBMS_OUTPUT.PUT_LINE(SUBSTR(laa, 1, 32000));
  DBMS_OUTPUT.PUT_LINE(SUBSTR(loa, 1, 32000));
END;
/
NADCON EXTRACTED REGION                                 NADGRD  
  33  49   1  -107.00000      .25000    25.00000      .25000      .00000
     .006731     .006444     .006208     .006036     .005935     .005904
     .005932     .006002     .006092     .006174     .006218     .006198
     .006087     .005867     .005522     .005045     .004432     .003688
     .002818     .001836     .000759    -.000385    -.001559    -.002704
. . .
NADCON EXTRACTED REGION                                 NADGRD  
  33  49   1  -107.00000      .25000    25.00000      .25000      .00000
     .008509     .007147     .005756     .004331     .002879     .001410
    -.000060    -.001507    -.002904    -.004222    -.005431    -.006498
    -.007395    -.008095    -.008579    -.008832    -.008848    -.008632
    -.008200    -.007577    -.006800    -.005911    -.004957    -.003974
. . .

SDO_CS.CONVERT_NTV2_TO_XML

Format

SDO_CS.CONVERT_NTV2_TO_XML(

     ntv2_clob IN CLOB,

     xml_grid OUT XMLTYPE );

Description

Converts an NTv2 (National Transformation Version 2) grid in ASCII format to an Oracle Spatial XML representation.

Parameters

ntv2_clob

NTv2 grid values in a CLOB object.

xml_grid

Output XML document containing the Oracle Spatial XML representation of the NTv2 grid.

Usage Notes

To convert an Oracle Spatial XML representation to an NTv2 grid, use the SDO_CS.CONVERT_XML_TO_NTV2 procedure.

Examples

The following example converts an NTv2 grid in ASCII format to an Oracle Spatial XML representation, converts the resulting XML representation back to an NTv2 ASCII representation, and displays the resulting ASCII representation. (Only part of the output is shown.)

set lines 32000
set long 2000000000
 
DECLARE
  ntv2      CLOB;
  xml       XMLTYPE;
  ntv2_file BFILE;
BEGIN
  ntv2_file := BFILENAME('MY_WORK_DIR', 'samplentv2.gsa');
  DBMS_LOB.OPEN(ntv2_file, DBMS_LOB.LOB_READONLY);
 
  DBMS_LOB.CREATETEMPORARY(ntv2, TRUE, DBMS_LOB.SESSION);
  DBMS_LOB.OPEN(ntv2, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.LOADFROMFILE(ntv2, ntv2_file, DBMS_LOB.LOBMAXSIZE);
  DBMS_LOB.CLOSE(ntv2);
  DBMS_LOB.CLOSE(ntv2_file);
 
  SDO_CS.convert_NTv2_to_XML(ntv2, xml);
  SDO_CS.convert_XML_to_NTv2(xml, ntv2);
  DBMS_OUTPUT.PUT_LINE(SUBSTR(ntv2, 1, 32000));
END;
/
NUM_OREC 11
NUM_SREC 11
NUM_FILE  2
GS_TYPE SECONDS 
VERSION NTv2.0  
DATUM_F NAD27   
DATUM_T NAD83   
MAJOR_F  6378206.400
MINOR_F  6356583.800
MAJOR_T  6378137.000
MINOR_T  6356752.314
SUB_NAMEALbanff 
PARENT  NONE    
CREATED 95-06-29
UPDATED 95-07-04
S_LAT     183900.000000
N_LAT     184500.000000
E_LONG    415800.000000
W_LONG    416100.000000
LAT_INC       30.000000
LONG_INC      30.000000
GS_COUNT   231
  0.084020  3.737300  0.005000  0.008000
  0.083029  3.738740  0.017000  0.011000
  0.082038  3.740180  0.029000  0.015000
. . .

SDO_CS.CONVERT_XML_TO_NADCON

Format

SDO_CS.CONVERT_XML_TO_NADCON(

     xml_grid IN XMLTYPE,

     laa_clob OUT CLOB,

     loa_clob OUT CLOB);

Description

Converts an Oracle Spatial XML representation of a NADCON (North American Datum Conversion) grid to NADCON ASCII format.

Parameters

xml_grid

XML document containing the Oracle Spatial XML representation of the NADCON grid.

laa_clob

Output CLOB object containing the latitude values of the NADCON grid.

loa_clob

Output CLOB object containing the longitude values of the NADCON grid.

Usage Notes

To convert a NADCON grid in ASCII format to an Oracle Spatial XML representation, use the SDO_CS.CONVERT_NADCON_TO_XML procedure.

Examples

The following example converts a NADCON grid in ASCII format to an Oracle Spatial XML representation, converts the resulting XML representation back to a NADCON ASCII representation, and displays the resulting ASCII representation. (Only part of the output is shown.)

set lines 32000
set long 2000000000
 
DECLARE
  laa       CLOB;
  loa       CLOB;
  xml       XMLTYPE;
  laa_file  BFILE;
  loa_file  BFILE;
BEGIN
  laa_file := BFILENAME('MY_WORK_DIR', 'samplenadcon.laa');
  loa_file := BFILENAME('MY_WORK_DIR', 'samplenadcon.loa');
  DBMS_LOB.OPEN(laa_file, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.OPEN(loa_file, DBMS_LOB.LOB_READONLY);
 
  DBMS_LOB.CREATETEMPORARY(laa, TRUE, DBMS_LOB.SESSION);
  DBMS_LOB.CREATETEMPORARY(loa, TRUE, DBMS_LOB.SESSION);
  DBMS_LOB.OPEN(laa, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.OPEN(loa, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.LOADFROMFILE(laa, laa_file, DBMS_LOB.LOBMAXSIZE);
  DBMS_LOB.LOADFROMFILE(loa, loa_file, DBMS_LOB.LOBMAXSIZE);
  DBMS_LOB.CLOSE(laa);
  DBMS_LOB.CLOSE(loa);
  DBMS_LOB.CLOSE(laa_file);
  DBMS_LOB.CLOSE(loa_file);
 
  SDO_CS.convert_NADCON_to_XML(laa, loa, xml);
  SDO_CS.convert_XML_to_NADCON(xml, laa, loa);
  DBMS_OUTPUT.PUT_LINE(SUBSTR(laa, 1, 32000));
  DBMS_OUTPUT.PUT_LINE(SUBSTR(loa, 1, 32000));
END;
/
NADCON EXTRACTED REGION                                 NADGRD  
  33  49   1  -107.00000      .25000    25.00000      .25000      .00000
     .006731     .006444     .006208     .006036     .005935     .005904
     .005932     .006002     .006092     .006174     .006218     .006198
     .006087     .005867     .005522     .005045     .004432     .003688
     .002818     .001836     .000759    -.000385    -.001559    -.002704
. . .
NADCON EXTRACTED REGION                                 NADGRD  
  33  49   1  -107.00000      .25000    25.00000      .25000      .00000
     .008509     .007147     .005756     .004331     .002879     .001410
    -.000060    -.001507    -.002904    -.004222    -.005431    -.006498
    -.007395    -.008095    -.008579    -.008832    -.008848    -.008632
    -.008200    -.007577    -.006800    -.005911    -.004957    -.003974
. . .

SDO_CS.CONVERT_XML_TO_NTV2

Format

SDO_CS.CONVERT_XML_TO_NTV2(

     xml_grid IN XMLTYPE,

     ntv2_clob OUT CLOB);

Description

Converts an Oracle Spatial XML representation of an NTv2 (National Transformation Version 2) grid to NTv2 ASCII format.

Parameters

xml_grid

XML document containing the Oracle Spatial XML representation of the NTv2 grid.

ntv2_clob

Output CLOB object containing the values for the NTv2 grid.

Usage Notes

To convert an NTv2 grid in ASCII format to an Oracle Spatial XML representation, use the SDO_CS.CONVERT_NTV2_TO_XML procedure.

Examples

The following example converts an NTv2 grid in ASCII format to an Oracle Spatial XML representation, converts the resulting XML representation back to an NTv2 ASCII representation, and displays the resulting ASCII representation. (Only part of the output is shown.)

set lines 32000
set long 2000000000
 
DECLARE
  ntv2      CLOB;
  xml       XMLTYPE;
  ntv2_file BFILE;
BEGIN
  ntv2_file := BFILENAME('MY_WORK_DIR', 'samplentv2.gsa');
  DBMS_LOB.OPEN(ntv2_file, DBMS_LOB.LOB_READONLY);
 
  DBMS_LOB.CREATETEMPORARY(ntv2, TRUE, DBMS_LOB.SESSION);
  DBMS_LOB.OPEN(ntv2, DBMS_LOB.LOB_READWRITE);
  DBMS_LOB.LOADFROMFILE(ntv2, ntv2_file, DBMS_LOB.LOBMAXSIZE);
  DBMS_LOB.CLOSE(ntv2);
  DBMS_LOB.CLOSE(ntv2_file);
 
  SDO_CS.convert_NTv2_to_XML(ntv2, xml);
  SDO_CS.convert_XML_to_NTv2(xml, ntv2);
  DBMS_OUTPUT.PUT_LINE(SUBSTR(ntv2, 1, 32000));
END;
/
NUM_OREC 11
NUM_SREC 11
NUM_FILE  2
GS_TYPE SECONDS 
VERSION NTv2.0  
DATUM_F NAD27   
DATUM_T NAD83   
MAJOR_F  6378206.400
MINOR_F  6356583.800
MAJOR_T  6378137.000
MINOR_T  6356752.314
SUB_NAMEALbanff 
PARENT  NONE    
CREATED 95-06-29
UPDATED 95-07-04
S_LAT     183900.000000
N_LAT     184500.000000
E_LONG    415800.000000
W_LONG    416100.000000
LAT_INC       30.000000
LONG_INC      30.000000
GS_COUNT   231
  0.084020  3.737300  0.005000  0.008000
  0.083029  3.738740  0.017000  0.011000
  0.082038  3.740180  0.029000  0.015000
. . .

SDO_CS.CREATE_CONCATENATED_OP

Format

SDO_CS.CREATE_CONCATENATED_OP(

     op_id IN NUMBER,

     op_name IN VARCHAR2,

     use_plan IN TFM_PLAN);

Description

Creates a concatenated operation.

Parameters

op_id

ID number of the concatenated operation.

op_name

Name to be associated with the concatenated operation.

use_plan

Transformation plan. The TFM_PLAN object type is explained in Section 6.5.

Usage Notes

A concatenated operation is the concatenation (chaining) of two or more atomic operations.

To create a concatenated operation and make it preferred either systemwide or for a specified use case, you can use the SDO_CS.CREATE_PREF_CONCATENATED_OP convenience procedure.

Examples

The following example creates a concatenation operation with the operation ID 2999 and the name CONCATENATED_OPERATION_2999.

DECLARE
BEGIN
SDO_CS.CREATE_CONCATENATED_OP(
  2999,
  'CONCATENATED_OPERATION_2999',
  TFM_PLAN(SDO_TFM_CHAIN(4242, 19910, 24200, 1000000000, 24200)));
END;
/

SDO_CS.CREATE_OBVIOUS_EPSG_RULES

Format

SDO_CS.CREATE_OBVIOUS_EPSG_RULES(

     use_case IN VARCHAR2 DEFAULT NULL);

Description

Creates a basic set of EPSG rules to be applied in certain transformations.

Parameters

use_case

Name of the use case to be associated with the application of the EPSG rules that are created. Must be a value from the USE_CASE column of the SDO_PREFERRED_OPS_USER table (described in Section 6.6.25).

Usage Notes

This procedure creates rules to implement the main EPSG-defined transformations between specific coordinate reference systems. For transformations between some coordinate reference systems, EPSG has specified rules that should be applied. For any given transformation from one coordinate reference system to another, the EPSG rule might be different from the default Oracle Spatial rule. If you execute this procedure, the EPSG rules are applied in any such cases. If you do not execute this procedure, the default Spatial rules are used in such cases.

This procedure inserts many rows into the SDO_PREFERRED_OPS_SYSTEM table (see Section 6.6.24).

To delete the EPSG rules created by this procedure, and thus cause the default Spatial rules to be used in all cases, use the SDO_CS.DELETE_ALL_EPSG_RULES procedure.

Examples

The following example creates a basic set of EPSG rules to be applied in certain transformations.

EXECUTE SDO_CS.CREATE_OBVIOUS_EPSG_RULES;

SDO_CS.CREATE_PREF_CONCATENATED_OP

Format

SDO_CS.CREATE_PREF_CONCATENATED_OP(

     op_id IN NUMBER,

     op_name IN VARCHAR2,

     use_plan IN TFM_PLAN,

     use_case IN VARCHAR2 DEFAULT NULL);

Description

Creates a concatenated operation, associating it with a transformation plan and making it preferred either systemwide or for a specified use case.

Parameters

op_id

ID number of the concatenated operation to be created.

op_name

Name to be associated with the concatenated operation.

use_plan

Transformation plan. The TFM_PLAN object type is explained in Section 6.5.

use_case

Use case to which this preferred concatenated operation applies. Must be a null or a value from the USE_CASE column of the SDO_PREFERRED_OPS_USER table (described in Section 6.6.25).

Usage Notes

This convenience procedure combines the operations of the SDO_CS.CREATE_CONCATENATED_OP and SDO_CS.ADD_PREFERENCE_FOR_OP procedures.

A concatenated operation is the concatenation (chaining) of two or more atomic operations.

If use_case is null, the transformation plan associated with the operation is a systemwide preference, and a row is added (or two rows are added if a reverse operation exists) to the SDO_PREFERRED_OPS_SYSTEM table (described in Section 6.6.24). If use_case is not null, the transformation plan associated with the operation is a preference associated with the specified use case, and a row is added (or two rows are added if a reverse operation exists) to the SDO_PREFERRED_OPS_USER table (described in Section 6.6.25).

To create a concatenation without making it preferred either systemwide or for a specified use case, use the SDO_CS.CREATE_CONCATENATED_OP procedure

To delete a concatenated operation, use the SDO_CS.DELETE_OP procedure.

Examples

The following example creates a concatenation operation with the operation ID 300 and the name MY_CONCATENATION_OPERATION, and causes Spatial to use the specified transformation plan in all cases (because use_case is null) when this operation is used.

DECLARE
BEGIN
SDO_CS.CREATE_PREF_CONCATENATED_OP(
  300,
  'MY_CONCATENATED_OPERATION',
  TFM_PLAN(SDO_TFM_CHAIN(4242, 19910, 24200, 1000000000, 24200)),
  NULL);
END;
/

SDO_CS.DELETE_ALL_EPSG_RULES

Format

SDO_CS.DELETE_ALL_EPSG_RULES(

     use_case IN VARCHAR2 DEFAULT NULL);

Description

Deletes the basic set of EPSG rules to be applied in certain transformations.

Parameters

use_case

Name of the use case to be associated with the application of the EPSG rules that are created. Must match the value that was used for the use_case parameter value (either null or a specified value) when the SDO_CS.CREATE_OBVIOUS_EPSG_RULES procedure was called.

Usage Notes

This procedure deletes the EPSG rules that were previously created by the SDO_CS.CREATE_OBVIOUS_EPSG_RULES procedure, and thus causes the default Spatial rules to be used in all cases. (See the Usage Notes for the SDO_CS.CREATE_OBVIOUS_EPSG_RULES procedure for more information.)

If use_case is null, this procedure deletes all rows from the SDO_PREFERRED_OPS_SYSTEM table (see Section 6.6.24). If use_case is not null, this procedure deletes the rows associated with the specified use case from the SDO_PREFERRED_OPS_USER table (see Section 6.6.25).

Examples

The following example deletes the basic set of EPSG rules to be applied in certain transformations.

EXECUTE SDO_CS.DELETE_ALL_EPSG_RULES;

SDO_CS.DELETE_OP

Format

SDO_CS.DELETE_OP(

     op_id IN NUMBER);

Description

Deletes a concatenated operation.

Parameters

op_id

ID number of the operation to be deleted.

Usage Notes

To create a concatenated operation and make it preferred systemwide or only for a specified use case, use the SDO_CS.CREATE_CONCATENATED_OP procedure.

Examples

The following example deletes the operation with the ID number 300.

EXECUTE SDO_CS.DELETE_OP(300);

SDO_CS.DETERMINE_CHAIN

Format

SDO_CS.DETERMINE_CHAIN(

     transient_rule_set IN SDO_TRANSIENT_RULE_SET,

     use_case IN VARCHAR2,

     source_srid IN NUMBER,

     target_srid IN NUMBER) RETURN TFM_PLAN;

Description

Returns the query chain, based on the system rule set, to be used in transformations from one coordinate reference system to another coordinate reference system.

Parameters

transient_rule_set

Rule set to be used for the transformation. If you specify a null value, the Oracle system rule set is used.

use_case

Use case for which to determine the query chain. Must be a null value or a value from the USE_CASE column of the SDO_PREFERRED_OPS_USER table (described in Section 6.6.25).

source_srid

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

target_srid

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

Usage Notes

This function returns an object of type TFM_PLAN, which is explained in Section 6.5.

The transient_rule_set parameter is of type SDO_TRANSIENT_RULE_SET, which has the following definition:

CREATE TYPE sdo_transient_rule_set AS OBJECT (
  source_srid  NUMBER,
  target_srid  NUMBER,
  tfm          NUMBER);

Examples

The following example returns the query chain based on the system rule set.

SELECT MDSYS.SDO_CS.DETERMINE_CHAIN(NULL, NULL, 4804, 4257) FROM DUAL;
 
MDSYS.SDO_CS.DETERMINE_CHAIN(NULL,NULL,4804,4257)(THE_PLAN)                     
--------------------------------------------------------------------------------
TFM_PLAN(SDO_TFM_CHAIN(4804, -2, 4257))

The next example creates a preferred concatenated operation (with operation ID 300) with a specified chain for transformations from SRID 4804 to SRID 4257, and then calls the DETERMINE_CHAIN function, returning a different result. (The operation created in this example is not meaningful or useful, and it was created only for illustration.)

CALL SDO_CS.CREATE_PREF_CONCATENATED_OP(
    300,
    'CONCATENATED OPERATION',
    TFM_PLAN(
      SDO_TFM_CHAIN(
                    4804,
        1000000001, 4804,
        1000000002, 4804,
        1000000001, 4804,
        1000000001, 4804,
        1000000002, 4804,
        1000000002, 4804,
        1000000001, 4804,
        1000000001, 4804,
        1000000001, 4804,
        1000000002, 4804,
        1000000002, 4804,
        1000000002, 4257)),
    NULL);
 
SELECT MDSYS.SDO_CS.DETERMINE_CHAIN(NULL, NULL, 4804, 4257) FROM DUAL;
 
MDSYS.SDO_CS.DETERMINE_CHAIN(NULL,NULL,4804,4257)(THE_PLAN)                     
--------------------------------------------------------------------------------
TFM_PLAN(SDO_TFM_CHAIN(4804, 300, 4257))

SDO_CS.DETERMINE_DEFAULT_CHAIN

Format

SDO_CS.DETERMINE_DEFAULT_CHAIN(

     source_srid IN NUMBER,

     target_srid IN NUMBER) RETURN SDO_SRID_CHAIN;

Description

Returns the default chain of SRID values in transformations from one coordinate reference system to another coordinate reference system.

Parameters

source_srid

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

target_srid

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

Usage Notes

This function returns an object of type SDO_SRID_CHAIN, which is defined as VARRAY(1048576) OF NUMBER.

Examples

The following example returns the default chain of SRID values in transformations from SRID 4804 to SRID 4257.

SELECT MDSYS.SDO_CS.DETERMINE_DEFAULT_CHAIN(4804, 4257) FROM DUAL;
 
MDSYS.SDO_CS.DETERMINE_DEFAULT_CHAIN(4804,4257)                                 
--------------------------------------------------------------------------------
SDO_SRID_CHAIN(NULL, 4804, 4257, NULL)

SDO_CS.FIND_GEOG_CRS

Format

SDO_CS.FIND_GEOG_CRS(

     reference_srid IN NUMBER,

     is_legacy IN VARCHAR2,

     max_rel_num_difference IN NUMBER DEFAULT 0.000001) RETURN SDO_SRID_LIST;

Description

Returns the SRID values of geodetic (geographic) coordinate reference systems that have the same well-known text (WKT) numeric values as the coordinate reference system with the specified reference SRID value.

Parameters

reference_srid

The SRID of the coordinate reference system for which to find all other geodetic coordinate reference systems that have the same WKT numeric values. Must be a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

is_legacy

TRUE limits the results to geodetic coordinate reference systems for which the IS_LEGACY column value is TRUE in the SDO_COORD_REF_SYS table (described in Section 6.6.9); FALSE limits the results to geodetic coordinate reference systems for which the IS_LEGACY column value is FALSE in the SDO_COORD_REF_SYS table. If you specify a null value for this parameter, the IS_LEGACY column value in the SDO_COORD_REF_SYS table is ignored in determining the results.

max_rel_num_difference

A numeric value indicating how closely WKT values must match in order for a projected coordinate reference system to be considered a match. The default value is 0.000001. The value for each numeric WKT item is compared with its corresponding value in the WKT for the reference SRID or in the specified list of parameters to this function; and if the difference in all cases is less than or equal to the max_rel_num_difference value, the SRID for that coordinate reference system is included in the results.

Usage Notes

This function returns an object of type SDO_SRID_LIST, which is defined as VARRAY(1048576) OF NUMBER.

The well-known text (WKT) format is described in Section 6.7.1.1.

Examples

The following examples show the effect of the is_legacy parameter value on the results. The first example returns the SRID values of all geodetic legacy coordinate reference systems that have the same WKT numeric values as the coordinate reference system with the SRID value of 8307.

SELECT SDO_CS.FIND_GEOG_CRS(
  8307,
  'TRUE') FROM DUAL;

SDO_CS.FIND_GEOG_CRS(8307,'TRUE')                                               
--------------------------------------------------------------------------------
SDO_SRID_LIST(8192, 8265, 8307, 8311, 8320, 524288, 2000002, 2000006, 2000012, 2
000015, 2000023, 2000028)     

The next example returns the SRID values of all geodetic non-legacy coordinate reference systems that have the same WKT numeric values as the coordinate reference system with the SRID value of 8307.

SELECT SDO_CS.FIND_GEOG_CRS(
  8307,
  'FALSE') FROM DUAL;

SDO_CS.FIND_GEOG_CRS(8307,'FALSE')                                              
--------------------------------------------------------------------------------
SDO_SRID_LIST(4019, 4030, 4031, 4032, 4033, 4041, 4121, 4122, 4126, 4130, 4133, 
4140, 4141, 4148, 4151, 4152, 4163, 4166, 4167, 4170, 4171, 4172, 4173, 4176, 41
80, 4189, 4190, 4258, 4269, 4283, 4318, 4319, 4326, 4610, 4612, 4617, 4619, 4624
, 4627, 4640, 4659, 4661, 4667, 4669, 4670) 

The next example returns the SRID values of all geodetic coordinate reference systems (legacy and non-legacy) that have the same WKT numeric values as the coordinate reference system with the SRID value of 8307.

SELECT SDO_CS.FIND_GEOG_CRS(
  8307,
  NULL) FROM DUAL;
 
SDO_CS.FIND_GEOG_CRS(8307,NULL)                                                 
--------------------------------------------------------------------------------
SDO_SRID_LIST(4019, 4030, 4031, 4032, 4033, 4041, 4121, 4122, 4126, 4130, 4133, 
4140, 4141, 4148, 4151, 4152, 4163, 4166, 4167, 4170, 4171, 4172, 4173, 4176, 41
80, 4189, 4190, 4258, 4269, 4283, 4318, 4319, 4326, 4610, 4612, 4617, 4619, 4624
, 4627, 4640, 4659, 4661, 4667, 4669, 4670, 8192, 8265, 8307, 8311, 8320, 524288
, 2000002, 2000006, 2000012, 2000015, 2000023, 2000028) 

SDO_CS.FIND_PROJ_CRS

Format

SDO_CS.FIND_PROJ_CRS(

     reference_srid IN NUMBER,

     is_legacy IN VARCHAR2,

     max_rel_num_difference IN NUMBER DEFAULT 0.000001) RETURN SDO_SRID_LIST;

Description

Returns the SRID values of projected coordinate reference systems that have the same well-known text (WKT) numeric values as the coordinate reference system with the specified reference SRID value.

Parameters

reference_srid

The SRID of the coordinate reference system for which to find all other projected coordinate reference systems that have the same WKT numeric values. Must be a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

is_legacy

TRUE limits the results to projected coordinate reference systems for which the IS_LEGACY column value is TRUE in the SDO_COORD_REF_SYS table (described in Section 6.6.9); FALSE limits the results to projected coordinate reference systems for which the IS_LEGACY column value is FALSE in the SDO_COORD_REF_SYS table. If you specify a null value for this parameter, the IS_LEGACY column value in the SDO_COORD_REF_SYS table is ignored in determining the results.

max_rel_num_difference

A numeric value indicating how closely WKT values must match in order for a coordinate reference system to be considered a match. The default value is 0.000001. The value for each numeric WKT item is compared with its corresponding value in the WKT for the reference SRID or in the specified list of parameters to this function; and if the difference in all cases is less than or equal to the max_rel_num_difference value, the SRID for that coordinate reference system is included in the results.

Usage Notes

This function returns an object of type SDO_SRID_LIST, which is defined as VARRAY(1048576) OF NUMBER.

The well-known text (WKT) format is described in Section 6.7.1.1.

Examples

The following examples show the effect of the is_legacy parameter value on the results. The first example returns the SRID values of all projected legacy coordinate reference systems that have the same WKT numeric values as the coordinate reference system with the SRID value of 2007. The returned result list is empty, because there are no legacy projected legacy coordinate reference systems that meet the search criteria.

SELECT SDO_CS.FIND_PROJ_CRS(
  2007,
  'TRUE') FROM DUAL;
 
SDO_CS.FIND_PROJ_CRS(2007,'TRUE')                                               
--------------------------------------------------------------------------------
SDO_SRID_LIST()

The next example returns the SRID values of all projected non-legacy coordinate reference systems that have the same WKT numeric values as the coordinate reference system with the SRID value of 2007.

SELECT SDO_CS.FIND_PROJ_CRS(
  2007,
  'FALSE') FROM DUAL;
 
SDO_CS.FIND_PROJ_CRS(2007,'FALSE')                                              
--------------------------------------------------------------------------------
SDO_SRID_LIST(2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 21291)

The next example returns the SRID values of all projected coordinate reference systems (legacy and non-legacy) that have the same WKT numeric values as the coordinate reference system with the SRID value of 2007. The returned result list is the same as for the preceding example.

SELECT SDO_CS.FIND_PROJ_CRS(
  2007,
  NULL) FROM DUAL;
 
SDO_CS.FIND_PROJ_CRS(2007,NULL)                                                 
--------------------------------------------------------------------------------
SDO_SRID_LIST(2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 21291)

SDO_CS.FROM_OGC_SIMPLEFEATURE_SRS

Format

SDO_CS.FROM_OGC_SIMPLEFEATURE_SRS(

     wkt IN VARCHAR2) RETURN VARCHAR2;

Description

Converts a well-known text string from the Open Geospatial Consortium simple feature format without the TOWGS84 keyword to the format that includes the TOWGS84 keyword.

Parameters

wkt

Well-known text string.

Usage Notes

To convert a well-known text string from the Open Geospatial Consortium simple feature format that includes the TOWGS84 keyword to the format without the TOWGS84 keyword, use the SDO_CS.TO_OGC_SIMPLEFEATURE_SRS function.

Examples

The following example converts a well-known text string from the Open Geospatial Consortium simple feature format without the TOWGS84 keyword to the format that includes the TOWGS84 keyword.

SELECT sdo_cs.from_OGC_SimpleFeature_SRS('GEOGCS [ "Longitude / Latitude (DHDN)", 
  DATUM ["", SPHEROID ["Bessel 1841", 6377397.155, 299.1528128], 
  582.000000, 105.000000, 414.000000, -1.040000, -0.350000, 3.080000, 8.300000 ], 
  PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]]') 
FROM DUAL;
 
MDSYS.SDO_CS.FROM_OGC_SIMPLEFEATURE_SRS('GEOGCS["LONGITUDE/LATITUDE(DHDN)",DATUM
--------------------------------------------------------------------------------
GEOGCS [ "Longitude / Latitude (DHDN)", DATUM ["", SPHEROID ["Bessel 1841", 6377
397.155, 299.1528128], TOWGS84[ 582.000000, 105.000000, 414.000000, -1.040000, -
0.350000, 3.080000, 8.300000]], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal
 Degree", 0.01745329251994330]]

SDO_CS.FROM_USNG

Format

SDO_CS.FROM_USNG(

     usng IN VARCHAR2,

     srid IN NUMBER,

     datum IN VARCHAR2 DEFAULT 'NAD83') RETURN SDO_GEOMETRY;

Description

Converts a point represented in U.S. National Grid format to a spatial point geometry object.

Parameters

usng

Well-known text string.

srid

The SRID of the coordinate system to be used for the conversion (that is, the SRID to be used in the returned geometry). Must be a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

datum

The name of the datum on which the U.S. National Grid coordinate for the point is based. Must be either a value in the DATUM_NAME column of the SDO_DATUMS table (described in Section 6.6.22) or null. The default value is NAD83.

Usage Notes

For information about Oracle Spatial support for the U.S. National Grid, see Section 6.10.

To convert a spatial point geometry to a point represented in U.S. National Grid format, use the SDO_CS.TO_USNG function.

Examples

The following example converts a a point represented in U.S. National Grid format to a spatial geometry point object with longitude/latitude coordinates.

-- Convert US National Grid point to SDO_GEMETRY point using SRID 4326
-- (WGS 84, longitude/latitude).
SELECT SDO_CS.FROM_USNG(
  '18SUJ2348316806479498',
  4326) FROM DUAL;
 
WGS84(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-77.03524, 38.8894673, NULL), NULL, NULL)

SDO_CS.MAP_EPSG_SRID_TO_ORACLE

Format

SDO_CS.MAP_EPSG_SRID_TO_ORACLE(

     epsg_srid IN NUMBER) RETURN NUMBER;

Description

Returns the Oracle Spatial SRID value corresponding to the specified EPSG SRID value.

Parameters

epsg_srid

The SRID of the EPSG coordinate reference system, as indicated in the COORD_REF_SYS_CODE field in the EPSG Coordinate Reference System table.

Usage Notes

This function returns a value that matches a value in the SRID column of the SDO_COORD_REF_SYS table (see Section 6.6.9).

To return the EPSG SRID value corresponding to the specified Oracle Spatial SRID value, use the SDO_CS.MAP_ORACLE_SRID_TO_EPSG function.

Examples

The following example returns the Oracle Spatial SRID value corresponding to EPSG SRID 23038.

SELECT SDO_CS.MAP_EPSG_SRID_TO_ORACLE(23038) FROM DUAL;
 
SDO_CS.MAP_EPSG_SRID_TO_ORACLE(23038)                                           
-------------------------------------                                           
                                82361

SDO_CS.MAP_ORACLE_SRID_TO_EPSG

Format

SDO_CS.MAP_ORACLE_SRID_TO_EPSG(

     legacy_srid IN NUMBER) RETURN NUMBER;

Description

Returns the EPSG SRID value corresponding to the specified Oracle Spatial SRID value.

Parameters

legacy_srid

Oracle Spatial SRID value. Must match a value in the LEGACY_CODE column of the SDO_COORD_REF_SYS table (see Section 6.6.9).

Usage Notes

This function returns the SRID of an EPSG coordinate reference system. The EPSG SRID value for a coordinate reference system is indicated in the COORD_REF_SYS_CODE field in the EPSG Coordinate Reference System table.

To return the Oracle Spatial SRID value corresponding to a specified EPSG SRID value, use the SDO_CS.MAP_EPSG_SRID_TO_ORACLE function.

Examples

The following example returns the EPSG SRID value corresponding to Oracle Spatial SRID 82361.

SELECT SDO_CS.MAP_ORACLE_SRID_TO_EPSG(82361) FROM DUAL;
 
SDO_CS.MAP_ORACLE_SRID_TO_EPSG(82361)                                           
-------------------------------------                                           
                                23038

SDO_CS.REVOKE_PREFERENCE_FOR_OP

Format

SDO_CS.REVOKE_PREFERENCE_FOR_OP(

     op_id IN NUMBER,

     source_crs IN NUMBER DEFAULT NULL,

     target_crs IN NUMBER DEFAULT NULL,

     use_case IN VARCHAR2 DEFAULT NULL);

Description

Revokes a preference for an operation between a source coordinate system and a target coordinate system.

Parameters

op_id

ID number of the operation. Must match an op_id value that was specified in a call to the SDO_CS.ADD_PREFERENCE_FOR_OP procedure.

source_crs

The SRID of the source coordinate reference system. Must match the source_crs value in a source_crs, target_crs, and use_case combination that was specified in a call to the SDO_CS.ADD_PREFERENCE_FOR_OP procedure.

target_crs

The SRID of the target coordinate reference system. Must match the target_crs value in a source_crs, target_crs, and use_case combination that was specified in a call to the SDO_CS.ADD_PREFERENCE_FOR_OP procedure.

use_case

Name of the use case associated with the preference. Must match the use_case value in a source_crs, target_crs, and use_case combination that was specified in a call to the SDO_CS.ADD_PREFERENCE_FOR_OP procedure.

Usage Notes

This procedure reverses the effect of the SDO_CS.ADD_PREFERENCE_FOR_OP procedure.

If use_case is null, this procedure deletes one or more rows from the SDO_PREFERRED_OPS_SYSTEM table (described in Section 6.6.24). If use_case is not null, this procedure deletes one or more rows from the SDO_PREFERRED_OPS_USER table (described in Section 6.6.25).

Examples

The following example revokes a preference for operation ID 19777 to be used in transformations from SRID 4301 to SRID 4326 when use case use_case_B is specified for the transformation.

EXECUTE SDO_CS.REVOKE_PREFERENCE_FOR_OP(19977, 4301, 4326, 'use_case_B');

SDO_CS.TO_OGC_SIMPLEFEATURE_SRS

Format

SDO_CS.TO_OGC_SIMPLEFEATURE_SRS(

     wkt IN VARCHAR2) RETURN VARCHAR2;

Description

Converts a well-known text string from the Open Geospatial Consortium simple feature format that includes the TOWGS84 keyword to the format without the TOWGS84 keyword.

Parameters

wkt

Well-known text string.

Usage Notes

To convert a well-known text string from the Open Geospatial Consortium simple feature format without the TOWGS84 keyword to the format that includes the TOWGS84 keyword, use the SDO_CS.FROM_OGC_SIMPLEFEATURE_SRS procedure.

Examples

The following example converts a well-known text string from the Open Geospatial Consortium simple feature format that includes the TOWGS84 keyword to the format without the TOWGS84 keyword.

SELECT sdo_cs.to_OGC_SimpleFeature_SRS('GEOGCS [ "Longitude / Latitude (DHDN)", 
  DATUM ["", SPHEROID ["Bessel 1841", 6377397.155, 299.1528128], 
  TOWGS84 [582.000000, 105.000000, 414.000000, -1.040000, -0.350000, 3.080000,
    8.300000] ],
  PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree", 0.01745329251994330]]')
FROM DUAL;
 
MDSYS.SDO_CS.TO_OGC_SIMPLEFEATURE_SRS('GEOGCS["LONGITUDE/LATITUDE(DHDN)",DATUM["
--------------------------------------------------------------------------------
GEOGCS [ "Longitude / Latitude (DHDN)", DATUM ["", SPHEROID ["Bessel 1841", 6377
397.155, 299.1528128], 582.000000, 105.000000, 414.000000, -1.040000, -0.350000,
3.080000, 8.300000 ], PRIMEM [ "Greenwich", 0.000000 ], UNIT ["Decimal Degree",
0.01745329251994330]]

SDO_CS.TO_USNG

Format

SDO_CS.TO_USNG(

     geom IN SDO_GEOMETRY,

     accuracy_in_meters IN NUMBER,

     datum IN VARCHAR2 DEFAULT 'NAD83') RETURN VARCHAR2;

Description

Converts a spatial point geometry object to a point represented in U.S. National Grid format.

Parameters

geom

Point geometry whose representation is to be converted to a point represented in U.S. National Grid format. The input geometry must have a valid non-null SRID, that is, a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

accuracy_in_meters

Accuracy of the point location in meters. Should be 1 raised to a negative or positive power of 10 (for example, 0.001, 0.01, 0.1, 1, 10, 100, or 1000). Any other specified values are adjusted internally by Spatial, and the result might not be what you expect.

datum

The name of the datum on which the U.S. National Grid coordinate for the point is to be based. Must be either NAD83 or NAD27. The default value is NAD83.

Usage Notes

For information about Oracle Spatial support for the U.S. National Grid, see Section 6.10.

The accuracy_in_meters value affects the number of digits used to represent the accuracy in the returned U.S. National Grid string. For example, if you specify 0.000001, the string will contain many digits; however, depending on the source of the data, the digits might not accurately reflect geographical reality. Consider the following scenarios. If you create a U.S. National Grid string from a UTM geometry, you can get perfect accuracy, because no inherently inaccurate transformation is involved. However, transforming from a Lambert projection to the U.S. National Grid format involves an inverse Lambert projection and a forward UTM projection, each of which has some inherent inaccuracy. If you request the resulting U.S. National Grid string with 1 millimeter (0.001) accuracy, the string will contain all the digits, but the millimeter-level digit will probably be geographically inaccurate.

To convert a a point represented in U.S. National Grid format to a spatial point geometry, use the SDO_CS.FROM_USNG function.

Examples

The following example converts a spatial geometry point object with longitude/latitude coordinates to a point represented in U.S. National Grid format using an accuracy of 0.001 meter (1 millimeter).

-- Convert longitude/latitude (WGS 84) point to US National Grid.
SELECT SDO_CS.TO_USNG(
  SDO_GEOMETRY(2001, 4326,
    SDO_POINT_TYPE(-77.0352402158258, 38.8894673086544, NULL),
    NULL, NULL),
  0.001) FROM DUAL;
 
SDO_CS.TO_USNG(SDO_GEOMETRY(2001,4326,SDO_POINT_TYPE(-77.0352402158258,38.889467
--------------------------------------------------------------------------------
18SUJ2348316806479498

SDO_CS.TRANSFORM

Format

SDO_CS.TRANSFORM(

     geom IN SDO_GEOMETRY,

     to_srid IN NUMBER

     ) RETURN SDO_GEOMETRY;

or

SDO_CS.TRANSFORM(

     geom IN SDO_GEOMETRY,

     tolerance IN NUMBER,

     to_srid IN NUMBER

     ) RETURN SDO_GEOMETRY;

or

SDO_CS.TRANSFORM(

     geom IN SDO_GEOMETRY,

     dim IN SDO_DIM_ARRAY,

     to_srid IN NUMBER

     ) RETURN SDO_GEOMETRY;

or

SDO_CS.TRANSFORM(

     geom IN SDO_GEOMETRY,

     to_srname IN VARCHAR2

     ) RETURN SDO_GEOMETRY;

or

SDO_CS.TRANSFORM(

     geom IN SDO_GEOMETRY,

     tolerance IN NUMBER,

     to_srname IN VARCHAR2

     ) RETURN SDO_GEOMETRY;

or

SDO_CS.TRANSFORM(

     geom IN SDO_GEOMETRY,

     dim IN SDO_DIM_ARRAY,

     to_srname IN VARCHAR2

     ) RETURN SDO_GEOMETRY;

Description

Transforms a geometry representation using a coordinate system (specified by SRID or name).

Parameters

geom

Geometry whose representation is to be transformed using another coordinate system. The input geometry must have a valid non-null SRID, that is, a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

tolerance

Tolerance value (see Section 1.5.5).

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.6).

to_srid

The SRID of the coordinate system to be used for the transformation. It must be a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

to_srname

The name of the coordinate system to be used for the transformation. It must be a value (specified exactly) in the COORD_REF_SYS_NAME column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

Usage Notes

Transformation can be done only between two different georeferenced coordinate systems or between two different local coordinate systems.

An exception is raised if geom, to_srid, or to_srname is invalid. For geom to be valid for this function, its definition must include an SRID value matching a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

Examples

The following example transforms the cola_c geometry to a representation that uses SRID value 8199. (This example uses the definitions from the example in Section 6.11.)

-- Return the transformation of cola_c using to_srid 8199 
-- ('Longitude / Latitude (Arc 1950)')
SELECT c.name, SDO_CS.TRANSFORM(c.shape, m.diminfo, 8199) 
  FROM cola_markets_cs c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS_CS' AND m.column_name = 'SHAPE' 
  AND c.name = 'cola_c';

NAME                                                                            
--------------------------------                                                
SDO_CS.TRANSFORM(C.SHAPE,M.DIMINFO,8199)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z)
--------------------------------------------------------------------------------
cola_c                                                                          
SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(3.00074114, 3.00291482, 6.00067068, 3.00291287, 6.0006723, 5.00307625, 4.0007
1961, 5.00307838, 3.00074114, 3.00291482))     

-- Same as preceding, but using to_srname parameter.
SELECT c.name, SDO_CS.TRANSFORM(c.shape, m.diminfo, 
      'Longitude / Latitude (Arc 1950)')
  FROM cola_markets_cs c, user_sdo_geom_metadata m
  WHERE m.table_name = 'COLA_MARKETS_CS' AND m.column_name = 'SHAPE'
  AND c.name = 'cola_c';

NAME                                                                            
--------------------------------                                                
SDO_CS.TRANSFORM(C.SHAPE,M.DIMINFO,'LONGITUDE/LATITUDE(ARC1950)')(SDO_GTYPE, SDO
--------------------------------------------------------------------------------
cola_c                                                                          
SDO_GEOMETRY(2003, 8199, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(3.00074114, 3.00291482, 6.00067068, 3.00291287, 6.0006723, 5.00307625, 4.0007
1961, 5.00307838, 3.00074114, 3.00291482))

SDO_CS.TRANSFORM_LAYER

Format

SDO_CS.TRANSFORM_LAYER(

     table_in IN VARCHAR2,

     column_in IN VARCHAR2,

     table_out IN VARCHAR2,

     to_srid IN NUMBER);

or

SDO_CS.TRANSFORM_LAYER(

     table_in IN VARCHAR2,

     column_in IN VARCHAR2,

     table_out IN VARCHAR2,

     use_plan IN TFM_PLAN);

or

SDO_CS.TRANSFORM_LAYER(

     table_in IN VARCHAR2,

     column_in IN VARCHAR2,

     table_out IN VARCHAR2,

     use_case IN VARCHAR2,

     to_srid IN NUMBER);

Description

Transforms an entire layer of geometries (that is, all geometries in a specified column in a table).

Parameters

table_in

Table containing the layer (column_in) whose geometries are to be transformed.

column_in

Column in table_in that contains the geometries to be transformed.

table_out

Table that will be created and that will contain the results of the transformation. See the Usage Notes for information about the format of this table.

to_srid

The SRID of the coordinate system to be used for the transformation. to_srid must be a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9).

use_plan

Transformation plan. The TFM_PLAN object type is explained in Section 6.5.

use_case

Name of the use case whose transformation rules are to be applied in performing the transformation. Use cases are explained in Section 6.4.

Usage Notes

Transformation can be done only between two different georeferenced coordinate systems or between two different local coordinate systems.

An exception is raised if any of the following occurs:

The table_out table is created by the procedure and is filled with one row for each transformed geometry. This table has the columns shown in Table 13-2.

Table 13-2 Table to Hold Transformed Layer

Column Name Data Type Description

SDO_ROWID

ROWID

Oracle ROWID (row address identifier). For more information about the ROWID data type, see Oracle Database SQL Reference.

GEOMETRY

SDO_GEOMETRY

Geometry object with coordinate values in the specified (to_srid parameter) coordinate system.


Examples

The following example transforms the geometries in the shape column in the COLA_MARKETS_CS table to a representation that uses SRID value 8199. The transformed geometries are stored in the newly created table named COLA_MARKETS_CS_8199. (This example uses the definitions from the example in Section 6.11.)

-- Transform the entire SHAPE layer and put results in the table
-- named cola_markets_cs_8199, which the procedure will create.
CALL SDO_CS.TRANSFORM_LAYER('COLA_MARKETS_CS','SHAPE','COLA_MARKETS_CS_8199',8199);

Example 6-8 in Section 6.11 includes a display of the geometry object coordinates in both tables (COLA_MARKETS_CS and COLA_MARKETS_CS_8199).


SDO_CS.UPDATE_WKTS_FOR_ALL_EPSG_CRS

Format

SDO_CS.UPDATE_WKTS_FOR_ALL_EPSG_CRS();

Description

Updates the well-known text (WKT) description for all EPSG coordinate reference systems.

Parameters

None.

Usage Notes

For information about using procedures to update well-known text (WKT) description, see Section 6.7.1.2.

Examples

The following example updates the WKT description for all EPSG coordinate reference systems.

EXECUTE SDO_CS.UPDATE_WKTS_FOR_ALL_EPSG_CRS;
Updating SRID 4001...                                                           
Updating SRID 4002...                                                           
Updating SRID 4003...                                                           
. . .                                                      
Updating SRID 69036405...                                                       
Updating SRID 69046405...

SDO_CS.UPDATE_WKTS_FOR_EPSG_CRS

Format

SDO_CS.UPDATE_WKTS_FOR_EPSG_CRS(

     srid IN NUMBER);

Description

Updates the well-known text (WKT) description for the EPSG coordinate reference system associated with a specified SRID.

Parameters

srid

The SRID of the coordinate system whose well-known text (WKT) description is to be updated. An entry for the specified value must exist in the SDO_COORD_REF_SYS table (described in Section 6.6.9).

Usage Notes

For information about using procedures to update well-known text (WKT) description, see Section 6.7.1.2.

Examples

The following example updates the WKT description for the EPSG coordinate reference system associated with SRID 8307.

EXECUTE SDO_CS.UPDATE_WKTS_FOR_EPSG_CRS(8307);

SDO_CS.UPDATE_WKTS_FOR_EPSG_DATUM

Format

SDO_CS.UPDATE_WKTS_FOR_EPSG_DATUM(

     datum_id IN NUMBER);

Description

Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified datum.

Parameters

datum_id

The ID of the datum. Must match a value in the DATUM_ID column of the SDO_DATUMS table (described in Section 6.6.22).

Usage Notes

For information about using procedures to update well-known text (WKT) description, see Section 6.7.1.2.

Examples

The following example updates the WKT description for all EPSG coordinate reference systems associated with datum 5100.

EXECUTE SDO_CS.UPDATE_WKTS_FOR_EPSG_DATUM(5100);
Updating SRID 5714...                                                           
Updating SRID 5715...

SDO_CS.UPDATE_WKTS_FOR_EPSG_ELLIPS

Format

SDO_CS.UPDATE_WKTS_FOR_EPSG_ELLIPS(

     ellipsoid_id IN NUMBER);

Description

Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified ellipsoid.

Parameters

ellipsoid_id

The ID of the ellipsoid. Must match a value in the ELLIPSOID_ID column of the SDO_ELLIPSOIDS table (described in Section 6.6.23).

Usage Notes

For information about using procedures to update well-known text (WKT) description, see Section 6.7.1.2.

Examples

The following example updates the WKT description for all EPSG coordinate reference systems associated with ellipsoid 7100.

EXECUTE SDO_CS.UPDATE_WKTS_FOR_EPSG_ELLIPS(7001);
Updating SRID 4001...                                                           
Updating SRID 4188...                                                           
Updating SRID 29901...                                                          
Updating SRID 61886405...                                                       
Updating SRID 4277...                                                           
Updating SRID 27700...                                                          
Updating SRID 62776405...                                                       
Updating SRID 4278...                                                           
Updating SRID 62786405...                                                       
Updating SRID 4279...                                                           
Updating SRID 62796405...

SDO_CS.UPDATE_WKTS_FOR_EPSG_OP

Format

SDO_CS.UPDATE_WKTS_FOR_EPSG_OP(

     coord_op_id IN NUMBER);

Description

Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified coordinate transformation operation.

Parameters

coord_op_id

The ID of the SRID of the coordinate transformation operation. Must match a value in the COORD_OP_ID column of the SDO_COORD_OP_PARAM_VALS table (described in Section 6.6.5).

Usage Notes

For information about using procedures to update well-known text (WKT) description, see Section 6.7.1.2.

Examples

The following example updates the WKT description for all EPSG coordinate reference systems associated with coordinate transformation operation 2000067.

EXECUTE SDO_CS.UPDATE_WKTS_FOR_EPSG_OP(2000067);
Updating SRID 20000671...

SDO_CS.UPDATE_WKTS_FOR_EPSG_PARAM

Format

SDO_CS.UPDATE_WKTS_FOR_EPSG_PARAM(

     coord_op_id IN NUMBER,

     parameter_id IN NUMBER);

Description

Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified coordinate transformation operation and parameter for transformation operations.

Parameters

coord_op_id

The ID of the SRID of the coordinate transformation operation. Must match a value in the COORD_OP_ID column of the SDO_COORD_OP_PARAM_VALS table (described in Section 6.6.5).

parameter_id

The ID of the SRID of the parameter for transformation operations. Must match a value in the PARAMETER_ID column of the SDO_COORD_OP_PARAM_VALS table (described in Section 6.6.5) where the COORD_OP_ID column value is equal to the coord_op_id parameter value.

Usage Notes

For information about using procedures to update well-known text (WKT) description, see Section 6.7.1.2.

Examples

The following example updates the WKT description for all EPSG coordinate reference systems associated with coordinate transformation operation 9601 and parameter 8602.

EXECUTE SDO_CS.UPDATE_WKTS_FOR_EPSG_PARAM(9601, 8602);

SDO_CS.UPDATE_WKTS_FOR_EPSG_PM

Format

SDO_CS.UPDATE_WKTS_FOR_EPSG_PM(

     prime_meridian_id IN NUMBER);

Description

Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified prime meridian.

Parameters

prime_meridian_id

The ID of the prime meridian. Must match a value in the PRIME_MERIDIAN_ID column in the SDO_PRIME_MERIDIANS table (described in Section 6.6.26).

Usage Notes

For information about using procedures to update well-known text (WKT) description, see Section 6.7.1.2.

Examples

The following example updates the WKT description for all EPSG coordinate reference systems associated with prime meridian 8902.

EXECUTE SDO_CS.UPDATE_WKTS_FOR_EPSG_PM(8902);
Updating SRID 4803...                                                           
Updating SRID 20790...                                                          
Updating SRID 20791...                                                          
Updating SRID 68036405...                                                       
Updating SRID 4904...                                                           
Updating SRID 2963...                                                           
Updating SRID 69046405...

SDO_CS.VALIDATE_WKT

Format

SDO_CS.VALIDATE_WKT(

     srid IN NUMBER

     ) RETURN VARCHAR2;

Description

Validates the well-known text (WKT) description associated with a specified SRID.

Parameters

srid

The SRID of the coordinate system whose well-known text (WKT) description is to be validated. An entry for the specified value must exist in the SDO_COORD_REF_SYS table (described in Section 6.6.9).

Usage Notes

This function returns the string 'TRUE' if the WKT description is valid. If the WKT description is invalid, this function returns a string in the format 'FALSE (<position-number>)', where <position-number> is the number of the character position in the WKT description where the first error occurs.

The WKT description is checked to see if it satisfies the requirements described in Section 6.7.1.1.

Examples

The following example validates the WKT description of the coordinate system associated with SRID 81989000. The results show that the cause of the invalidity (or the first cause of the invalidity) starts at character position 181 in the WKT description. (SRID 81989000 is not associated with any established coordinate system. Rather, it is for a deliberately invalid coordinate system that was inserted into a test version of the MDSYS.CS_SRS table, and it is not included in the MDSYS.CS_SRS table that is shipped with Oracle Spatial.)

SELECT SDO_CS.VALIDATE_WKT(81989000) FROM DUAL;

SDO_CS.VALIDATE_WKT(81989000)                                                   
--------------------------------------------------------------------------------
FALSE (181) 

SDO_CS.VIEWPORT_TRANSFORM

Format

SDO_CS.VIEWPORT_TRANSFORM(

     geom IN SDO_GEOMETRY,

     to_srid IN NUMBER

     ) RETURN SDO_GEOMETRY;

Description

Transforms an optimized rectangle into a valid polygon for use with Spatial operators and functions.

Note:

This function is deprecated, and will not be supported in future releases of Spatial. Instead, use a geodetic MBR to specify the query window, as explained in Section 6.2.3.

Parameters

geom

Geometry whose representation is to be transformed from an optimized rectangle to a valid polygon. The input geometry must have an SRID value of 0 (zero), as explained in the Usage Notes.

to_srid

The SRID of the coordinate system to be used for the transformation (that is, the SRID to be used in the returned geometry). to_srid must be either a value in the SRID column of the SDO_COORD_REF_SYS table (described in Section 6.6.9) or NULL.

Usage Notes

The geometry passed in must be an optimized rectangle.

If to_srid is a geodetic SRID, a geometry (not an optimized rectangle) is returned that conforms to the Oracle Spatial requirements for a geodetic geometry (for example, each polygon element's area must be less than one-half the surface area of the Earth).

If to_srid is not a geodetic SRID, an optimized rectangle is returned in which the SRID is set to to_srid.

Visualizer applications that work on geodetic data usually treat the longitude and latitude space as a regular Cartesian coordinate system. Fetching the data corresponding to a viewport is usually done with the help of an SDO_FILTER or SDO_GEOM.RELATE operation where the viewport (with an optimized rectangle representation) is sent as the window query. Before release 10.1, this optimized rectangle type could not be used in geodetic space, and therefore this type of viewport query could not be sent to the database. The VIEWPORT_TRANSFORM function was created to provide a workaround to this previous restriction.

The viewport rectangles should be constructed with the SRID value as 0 and input to the function to generate a corresponding valid geodetic polygon. This geodetic polygon can then be used in the SDO_FILTER or SDO_GEOM.RELATE call as the window object.

An SRID value of 0 should only be specified when calling the VIEWPORT_TRANSFORM function. It is not valid in any other context in Spatial.

This function should be used only when the display space is equirectangular (a rectangle), and the data displayed is geodetic.

Examples

The following example specifies the viewport as the whole Earth represented by an optimized rectangle. It returns the names of all four cola markets. (This example uses the definitions from the example in Section 6.11.)

SELECT c.name FROM cola_markets_cs c WHERE
   SDO_FILTER(c.shape, SDO_CS.VIEWPORT_TRANSFORM(
       SDO_GEOMETRY(
           2003,
           0,    -- SRID = 0 (special case)
           NULL,
           SDO_ELEM_INFO_ARRAY(1,1003,3),
           SDO_ORDINATE_ARRAY(-180,-90,180,90)),
       8307)) = 'TRUE';

NAME                                                                            
--------------------------------                                                
cola_a                                                                          
cola_c                                                                          
cola_b                                                                          
cola_d   

If the optimizer does not generate an optimal plan and performance is not as you expect, you can try the following alternative version of the query.

SELECT c.name FROM cola_markets_cs c,
   (SELECT
   SDO_CS.VIEWPORT_TRANSFORM(
      SDO_GEOMETRY(2003, 0, NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,3),
      SDO_ORDINATE_ARRAY(-180,-90,180,90)), 8307)
   window_geom FROM DUAL)
WHERE SDO_FILTER(c.shape, window_geom) = 'TRUE';

NAME
--------------------------------
cola_a
cola_c
cola_b
cola_d