| Oracle® Spatial Developer's Guide 11g Release 1 (11.1) Part Number B28400-01 |
|
|
View PDF |
| Oracle® Spatial Developer's Guide 11g Release 1 (11.1) Part Number B28400-01 |
|
|
View PDF |
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 21-1 lists the coordinate system transformation subprograms.
Table 21-1 Subprograms for Coordinate System Transformation
| Subprogram | Description |
|---|---|
|
|
Adds a preference for an operation between a source coordinate system and a target coordinate system. |
|
|
Converts a NADCON (North American Datum Conversion) grid in ASCII format to an Oracle Spatial XML representation. |
|
|
Converts an NTv2 (National Transformation Version 2) grid in ASCII format to an Oracle Spatial XML representation. |
|
|
Converts an Oracle Spatial XML representation of a NADCON (North American Datum Conversion) grid to NADCON ASCII format. |
|
|
Converts an Oracle Spatial XML representation of an NTv2 (National Transformation Version 2) grid to NTv2 ASCII format. |
|
|
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. |
|
|
Deletes the basic set of EPSG rules to be applied in certain transformations. |
|
|
Deletes a concatenated operation. |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
Converts a point represented in U.S. National Grid format to a spatial point geometry object. |
|
|
Gets the version number of the EPSG dataset used by Spatial. |
|
|
Converts a three-dimensional geometry into a two-dimensional geometry. |
|
|
Converts a two-dimensional geometry into a three-dimensional geometry. |
|
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 |
|
|
Transforms a geometry representation using a coordinate system (specified by SRID or name). |
|
|
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. |
|
|
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.
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
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.7.8).
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.7.9).
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.7.9).
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.7.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.7.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.7.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');
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
Latitude values of the NADCON grid in a CLOB object.
Longitude values of the NADCON grid in a CLOB object.
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
. . .
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 grid values in a CLOB object.
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
. . .
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 document containing the Oracle Spatial XML representation of the NADCON grid.
Output CLOB object containing the latitude values of the NADCON grid.
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
. . .
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 document containing the Oracle Spatial XML representation of the NTv2 grid.
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
. . .
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
ID number of the concatenated operation.
Name to be associated with the concatenated operation.
Transformation plan. The TFM_PLAN object type is explained in Section 6.6.
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; /
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
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.7.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.7.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;
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
ID number of the concatenated operation to be created.
Name to be associated with the concatenated operation.
Transformation plan. The TFM_PLAN object type is explained in Section 6.6.
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.7.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.7.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.7.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; /
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
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.7.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.7.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;
Format
SDO_CS.DELETE_OP(
op_id IN NUMBER);
Description
Deletes a concatenated operation.
Parameters
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);
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
Rule set to be used for the transformation. If you specify a null value, the Oracle system rule set is used.
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.7.25).
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.7.9).
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.7.9).
Usage Notes
This function returns an object of type TFM_PLAN, which is explained in Section 6.6.
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))
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
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.7.9).
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.7.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)
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
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.7.9).
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.7.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.
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.8.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)
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
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.7.9).
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.7.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.
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.8.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)
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
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]]
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
Well-known text string.
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.7.9).
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.7.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.11.
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 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)
Format
SDO_CS.GET_EPSG_DATA_VERSION() RETURN VARCHAR2;
Description
Gets the version number of the EPSG dataset used by Spatial.
Parameters
None.
Usage Notes
The EPSG dataset is available from the European Petroleum Survey Group, and is distributed in a Microsoft Access 97 database and as SQL scripts.
Examples
The following example gets the version number of the EPSG dataset used by Spatial.
SELECT SDO_CS.GET_EPSG_DATA_VERSION FROM DUAL; GET_EPSG_DATA_VERSION --------------------------------------------------------------------------------6.5
Format
SDO_CS.MAKE_2D(
geom3d IN SDO_GEOMETRY,
target_srid IN NUMBER DEFAULT NULL) RETURN SDO_GEOMETRY;
Description
Converts a three-dimensional geometry into a two-dimensional geometry.
Parameters
Two-dimensional geometry object.
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.7.9).
Usage Notes
This function returns a two-dimensional geometry object that removes the third (height) dimension value from each vertex in the input geometry.
For information about three-dimensional coordinate reference system support, see Section 6.5.
Examples
The following example converts a three-dimensional geometry to a two-dimensional geometry by removing all the third (height) dimension values. (It uses as its input geometry the output geometry from the example for the SDO_CS.MAKE_3D function.)
SELECT SDO_CS.MAKE_2D(SDO_GEOMETRY(3003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(1, 1, 10, 5, 1, 10, 5, 7, 10, 1, 7, 10, 1, 1, 10))) FROM DUAL; SDO_CS.MAKE_2D(SDO_GEOMETRY(3003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORD -------------------------------------------------------------------------------- SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(1, 1, 5, 1, 5, 7, 1, 7, 1, 1))
Format
SDO_CS.MAKE_3D(
geom2d IN SDO_GEOMETRY,
height IN NUMBER DEFAULT 0,
target_srid IN NUMBER DEFAULT NULL) RETURN SDO_GEOMETRY;
Description
Converts a two-dimensional geometry into a three-dimensional geometry.
Parameters
Two-dimensional geometry object.
Height value to be used in the third dimension for all vertices in the returned geometry. If this parameter is null or not specified, a height of 0 (zero) is used for all vertices.
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.7.9).
Usage Notes
For information about using this function to simulate a cross-dimensionality transformation, see Section 6.5.4.
For information about three-dimensional coordinate reference system support, see Section 6.5.
Examples
The following example converts the cola_a two-dimensional geometry to a three-dimensional geometry. (This example uses the definitions from the example in Section 6.12.).
SELECT SDO_CS.MAKE_3D(c.shape, 10, 8307) FROM cola_markets_cs c WHERE c.name = 'cola_a'; SDO_CS.MAKE_3D(C.SHAPE,10,8307)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELE -------------------------------------------------------------------------------- SDO_GEOMETRY(3003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(1, 1, 10, 5, 1, 10, 5, 7, 10, 1, 7, 10, 1, 1, 10))
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
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.7.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
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
Oracle Spatial SRID value. Must match a value in the LEGACY_CODE column of the SDO_COORD_REF_SYS table (see Section 6.7.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
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
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.
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.
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.
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.7.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.7.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');
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
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]]
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
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.7.9).
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.
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.11.
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 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
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
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.7.9).
Tolerance value (see Section 1.5.5).
Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.8).
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.7.9).
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.7.9).
Usage Notes
Transformation can be done only between two different georeferenced coordinate systems or between two different local coordinate systems.
Transformation of circles and arcs is not supported, regardless of the type of coordinate systems involved.
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.7.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.12.)
-- 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))
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 containing the layer (column_in) whose geometries are to be transformed.
Column in table_in that contains the geometries to be transformed.
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.
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.7.9).
Transformation plan. The TFM_PLAN object type is explained in Section 6.6.
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:
table_in does not exist, or column_in does not exist in the table.
The geometries in column_in have a null or invalid SDO_SRID value.
table_out already exists.
to_srid is invalid.
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 21-2.
Table 21-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 Language Reference. |
|
GEOMETRY |
SDO_GEOMETRY |
Geometry object with coordinate values in the specified ( |
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.12.)
-- 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-17 in Section 6.12 includes a display of the geometry object coordinates in both tables (COLA_MARKETS_CS and COLA_MARKETS_CS_8199).
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.8.1.3.
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...
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
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.7.9).
Usage Notes
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
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);
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
The ID of the datum. Must match a value in the DATUM_ID column of the SDO_DATUMS table (described in Section 6.7.22).
Usage Notes
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
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...
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
The ID of the ellipsoid. Must match a value in the ELLIPSOID_ID column of the SDO_ELLIPSOIDS table (described in Section 6.7.23).
Usage Notes
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
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...
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
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.7.5).
Usage Notes
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
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...
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
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.7.5).
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.7.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.8.1.3.
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);
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
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.7.26).
Usage Notes
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
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...
Format
SDO_CS.VALIDATE_WKT(
srid IN NUMBER
) RETURN VARCHAR2;
Description
Validates the well-known text (WKT) description associated with a specified SRID.
Parameters
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.7.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.8.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)
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
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.
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.7.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.12.)
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
