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. |
|
|
Creates a basic set of EPSG rules to be applied in certain transformations. |
|
|
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. |
|
|
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. |
|
|
Finds an SRID value for a coordinate system that matches information that you specify. |
|
|
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. |
|
|
Returns the Oracle Spatial SRID values corresponding to the specified EPSG SRID value. |
|
|
Returns the EPSG SRID value corresponding to the specified Oracle Spatial SRID value. |
|
|
Revokes a preference for an operation between a source coordinate system and a target coordinate system. |
|
|
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). |
|
|
Updates the well-known text (WKT) description for all EPSG coordinate reference systems. |
|
|
Updates the well-known text (WKT) description for the EPSG coordinate reference system associated with a specified SRID. |
|
|
Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified datum. |
|
|
Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified ellipsoid. |
|
|
Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified coordinate transformation operation. |
|
|
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. |
|
|
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. |
The rest of this chapter provides reference information on the subprograms, listed in alphabetical order.
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);
Adds a preference for an operation between a source coordinate system and a target coordinate system.
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).
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.
Converts a NADCON (North American Datum Conversion) grid in ASCII format to an Oracle Spatial XML representation.
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.
To convert an Oracle Spatial XML representation to a NADCON grid, use the SDO_CS.CONVERT_XML_TO_NADCON procedure.
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
. . .
Converts an NTv2 (National Transformation Version 2) grid in ASCII format to an Oracle Spatial XML representation.
NTv2 grid values in a CLOB object.
Output XML document containing the Oracle Spatial XML representation of the NTv2 grid.
To convert an Oracle Spatial XML representation to an NTv2 grid, use the SDO_CS.CONVERT_XML_TO_NTV2 procedure.
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
. . .
Converts an Oracle Spatial XML representation of a NADCON (North American Datum Conversion) grid to NADCON ASCII format.
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.
To convert a NADCON grid in ASCII format to an Oracle Spatial XML representation, use the SDO_CS.CONVERT_NADCON_TO_XML procedure.
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
. . .
Converts an Oracle Spatial XML representation of an NTv2 (National Transformation Version 2) grid to NTv2 ASCII format.
XML document containing the Oracle Spatial XML representation of the NTv2 grid.
Output CLOB object containing the values for the NTv2 grid.
To convert an NTv2 grid in ASCII format to an Oracle Spatial XML representation, use the SDO_CS.CONVERT_NTV2_TO_XML procedure.
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
. . .
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.
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.
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).
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.
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);
Creates a concatenated operation, associating it with a transformation plan and making it preferred either systemwide or for a specified use case.
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).
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.
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; /
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.
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).
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.
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;
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.
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).
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);
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(
source_srid IN NUMBER,
target_srid IN NUMBER) RETURN SDO_SRID_CHAIN;
Returns the default chain of SRID values in transformations from one coordinate reference system to another coordinate reference system.
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).
This function returns an object of type SDO_SRID_CHAIN, which is defined as VARRAY(1048576) OF NUMBER.
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(
reference_srid IN NUMBER,
is_legacy IN VARCHAR2,
max_rel_num_difference IN NUMBER DEFAULT 0.000001) RETURN SDO_SRID_LIST;
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.
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.
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.
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(
reference_srid IN NUMBER,
is_legacy IN VARCHAR2,
max_rel_num_difference IN NUMBER DEFAULT 0.000001) RETURN SDO_SRID_LIST;
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.
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.
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.
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.FIND_SRID(
srid OUT NUMBER,
epsg_srid_geog IN NUMBER DEFAULT NULL,
epsg_srid_proj IN NUMBER DEFAULT NULL,
datum_id IN NUMBER DEFAULT NULL,
pm_id IN NUMBER DEFAULT NULL,
proj_method_id IN NUMBER DEFAULT NULL,
coord_ref_sys_kind IN VARCHAR2 DEFAULT NULL,
semi_major_axis IN NUMBER DEFAULT NULL,
semi_minor_axis IN NUMBER DEFAULT NULL,
inv_flattening IN NUMBER DEFAULT NULL,
params IN EPSG_PARAMS DEFAULT NULL);
or
SDO_CS.FIND_SRID(
srid OUT NUMBER,
epsg_srid_geog IN NUMBER DEFAULT NULL,
epsg_srid_proj IN NUMBER DEFAULT NULL,
datum_id IN NUMBER DEFAULT NULL,
pm_id IN NUMBER DEFAULT NULL,
proj_method_id IN NUMBER DEFAULT NULL,
proj_op_id IN NUMBER DEFAULT NULL,
coord_ref_sys_kind IN VARCHAR2 DEFAULT NULL,
semi_major_axis IN NUMBER DEFAULT NULL,
semi_minor_axis IN NUMBER DEFAULT NULL,
inv_flattening IN NUMBER DEFAULT NULL,
params IN EPSG_PARAMS DEFAULT NULL,
max_rel_num_difference IN NUMBER DEFAULT 0.000001);
Output parameter; will contain either a numeric SRID value or a null value, as explained in the Usage Notes.
EPGS SRID value of a geographic coordinate system. Depending on the value of the coord_ref_sys_kind parameter, this procedure will either verify the existence of a coordinate system with this geographic SRID value, or will find an SRID value of a projected coordinate system based on a coordinate system with this SRID value.
EPGS SRID value of a projected coordinate system.
Datum ID value. Depending on the value of the coord_ref_sys_kind parameter, this procedure will look for the SRID of a geographic or projected coordinate system based on this datum.
Ellipsoid ID value. Depending on the value of the coord_ref_sys_kind parameter, this procedure will look for the SRID of a geographic or projected coordinate system based on this ellipsoid.
Prime meridian ID value. Depending on the value of the coord_ref_sys_kind parameter, this procedure will look for the SRID of a geographic or projected coordinate system based on this prime meridian.
Projection method ID value. This procedure will look for the SRID of a projected coordinate system based on this projection method.
Projection operation ID value. This procedure will look for the SRID of a projected coordinate system based on this projection operation. A projection operation is a projection method combined with specific projection parameters.
The kind or category of coordinate system. Must be a string value in the COORD_REF_SYS_KIND column of the SDO_COORD_REF_SYS table (described in Section 6.7.9). Examples: GEOGRAPHIC2D and PROJECTED
Semi-major axis ID value. Depending on the value of the coord_ref_sys_kind parameter, this procedure will loo for the SRID of a geographic or projected coordinate system based on this semi-major axis.
Semi-minor axis ID value. Depending on the value of the coord_ref_sys_kind parameter, this procedure will look for the SRID of a geographic or projected coordinate system based on this semi-minor axis.
Inverse flattening (unit "unity"). Depending on the value of the coord_ref_sys_kind parameter, this procedure will look for the SRID of a geographic or projected coordinate system based on this inverse flattening.
Projection parameters. The parameters depend on the projection method. The EPSG_PARAMS type is defined as VARRAY(1048576) OF EPSG_PARAM, and the EPSG_PARAM type is defined as (id NUMBER, val NUMBER, uom NUMBER). The format includes attributes for the parameter ID, value, and unit of measure ID, as shown in the following example:
epsg_params( epsg_param(8801, 0.0, 9102), epsg_param(8802, 9.0, 9102), epsg_param(8805, 0.9996, 9201), epsg_param(8806, 500000.0, 9001), epsg_param(8807, 0.0, 9001));
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 procedure; 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.
This procedure places the result of its operation in the srid output parameter. The result is either a numeric SRID value or a null value.
This procedure has the following major uses:
To check if a coordinate system with a specific SRID value exists. In this case, you specify a value for epsg_srid_geog or epsg_srid_proj (depending on whether the coordinate system is geographic or projected) and enough parameters for a valid PL/SQL statement. If the resulting srid parameter value is the same number as the value that you specified, the coordinate system with that SRID value exists; however, if the resulting srid parameter value is null, no coordinate system with that SRID value exists.
To find the SRID value of a coordinate system based on information that you specify about it.
If multiple coordinate systems match the criteria specified in the input parameters, only one SRID value is returned in the srid parameter. This could be any one of the potential matching SRID values, and it is not guaranteed to be the same value in subsequent executions of this procedure with the same input parameters.
The following example finds an SRID value for a projected coordinate system that uses datum ID 6267 in its definition.
DECLARE
returned_srid NUMBER;
BEGIN
SDO_CS.FIND_SRID (
srid => returned_srid,
epsg_srid_geog => null,
epsg_srid_proj => null,
datum_id => 6267,
ellips_id => null,
pm_id => null,
proj_method_id => null,
proj_op_id => null,
coord_ref_sys_kind => 'PROJECTED');
DBMS_OUTPUT.PUT_LINE('SRID = ' || returned_srid);
END;
/
SRID = 4267
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.
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.
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(
usng IN VARCHAR2,
srid IN NUMBER,
datum IN VARCHAR2 DEFAULT 'NAD83') RETURN SDO_GEOMETRY;
Converts a point represented in U.S. National Grid format to a spatial point geometry object.
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.
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.
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)
The EPSG dataset is available from the European Petroleum Survey Group, and is distributed in a Microsoft Access 97 database and as SQL scripts.
SDO_CS.MAKE_2D(
geom3d IN SDO_GEOMETRY,
target_srid IN NUMBER DEFAULT NULL) RETURN SDO_GEOMETRY;
Three-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).
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.
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))
SDO_CS.MAKE_3D(
geom2d IN SDO_GEOMETRY,
height IN NUMBER DEFAULT 0,
target_srid IN NUMBER DEFAULT NULL) RETURN SDO_GEOMETRY;
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).
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.
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.13.).
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))
The SRID of the EPSG coordinate reference system, as indicated in the COORD_REF_SYS_CODE field in the EPSG Coordinate Reference System table.
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.
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).
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.
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);
Revokes a preference for an operation between a source coordinate system and a target coordinate system.
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.
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).
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.
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.
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(
geom IN SDO_GEOMETRY,
accuracy_in_meters IN NUMBER,
datum IN VARCHAR2 DEFAULT 'NAD83') RETURN VARCHAR2;
Converts a spatial point geometry object to a point represented in U.S. National Grid format.
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.
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.
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(
geom IN SDO_GEOMETRY,
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,
use_case IN VARCHAR2,
to_srid IN NUMBER
) RETURN SDO_GEOMETRY;
or
SDO_CS.TRANSFORM(
geom IN SDO_GEOMETRY,
use_plan IN TFM_PLAN
) RETURN SDO_GEOMETRY;
Transforms a geometry representation using a coordinate system (specified by SRID or name).
You can also associate a use case or a transformation plan with the transformation.
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).
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).
The name of the use case to be associated with the transformation. If you specify the string USE_SPHERICAL, the transformation uses spherical math instead of ellipsoidal math, thereby accommodating Google Maps and some other third-party tools that use projections based on spherical math. Use cases are explained in Section 6.4. For considerations related to Google Maps, see Section 6.12.
Transformation plan. The TFM_PLAN object type is explained in Section 6.6.
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).
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.13.)
-- Return the transformation of cola_c using to_srid 8199
-- ('Longitude / Latitude (Arc 1950)')
SELECT c.name, SDO_CS.TRANSFORM(c.shape, 8199)
FROM cola_markets_cs c WHERE c.name = 'cola_c';
NAME
--------------------------------
SDO_CS.TRANSFORM(C.SHAPE,8199)(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM
--------------------------------------------------------------------------------
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, 'Longitude / Latitude (Arc 1950)')
FROM cola_markets_cs c WHERE c.name = 'cola_c';
NAME
--------------------------------
SDO_CS.TRANSFORM(C.SHAPE,'LONGITUDE/LATITUDE(ARC1950)')(SDO_GTYPE, SDO_SRID, 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(
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);
Transforms an entire layer of geometries (that is, all geometries in a specified column in a table).
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.
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 ( |
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.13.)
-- 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-18 in Section 6.13 includes a display of the geometry object coordinates in both tables (COLA_MARKETS_CS and COLA_MARKETS_CS_8199).
Updates the well-known text (WKT) description for all EPSG coordinate reference systems.
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
Updates the well-known text (WKT) description for the EPSG coordinate reference system associated with a specified 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.7.9).
This procedure updates the WKT descriptions for the specified SRID and all dependent SRIDs. For example, for SRID 4326 (WGS84 geodetic system), all EPSG coordinate systems that use this geodetic system will also be updated.
For information about using procedures to update well-known text (WKT) descriptions, see Section 6.8.1.3.
Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified datum.
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).
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified ellipsoid.
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).
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
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...
Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified coordinate transformation operation.
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).
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
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.
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.
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
Updates the well-known text (WKT) description for all EPSG coordinate reference systems associated with a specified prime meridian.
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).
For information about using procedures to update well-known text (WKT) description, see Section 6.8.1.3.
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...
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).
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.
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)