| Oracle® Spatial User's Guide and Reference 10g Release 1 (10.1) Part Number B10826-01 |
|
|
View PDF |
| Oracle® Spatial User's Guide and Reference 10g Release 1 (10.1) Part Number B10826-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 15-1 lists the coordinate system transformation subprograms.
Table 15-1 Subprograms for Coordinate System Transformation
| Subprogram | Description |
|---|---|
| SDO_CS.TRANSFORM |
Transforms a geometry representation using a coordinate system (specified by SRID or name). |
| SDO_CS.TRANSFORM_LAYER |
Transforms an entire layer of geometries (that is, all geometries in a specified column in a table). |
| SDO_CS.VALIDATE_WKT |
Validates the well-known text (WKT) description associated with a specified SRID. |
| SDO_CS.VIEWPORT_TRANSFORM (deprecated) | Transforms an optimized rectangle into a valid polygon for use with Spatial operators and functions. |
The rest of this chapter provides reference information on the subprograms, listed in alphabetical order.
Format
SDO_CS.TRANSFORM(
geom IN SDO_GEOMETRY,
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,
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 MDSYS.CS_SRS table (described in Section 6.4.1).
Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).
The SRID of the coordinate system to be used for the transformation. It must be a value in the SRID column of the MDSYS.CS_SRS table (described in Section 6.4.1).
The name of the coordinate system to be used for the transformation. It must be a value (specified exactly) in the CS_NAME column of the MDSYS.CS_SRS table (described in Section 6.4.1).
Usage Notes
Transformation can be done only between two different georeferenced coordinate systems or between two different local coordinate systems.
An exception is raised if geom, to_srid, or to_srname is invalid. For geom to be valid for this function, its definition must include an SRID value matching a value in the SRID column of the MDSYS.CS_SRS table (described in Section 6.4.1).
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.8.)
-- 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);
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 MDSYS.CS_SRS table (described in Section 6.4.1).
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 15-2.
Table 15-2 Table to Hold Transformed Layer
| Column Name | Data Type | Description |
|---|---|---|
| SDO_ROWID | ROWID | Oracle ROWID (row address identifier). For more information about the ROWID data type, see Oracle Database SQL Reference. |
| GEOMETRY | SDO_GEOMETRY | Geometry object with coordinate values in the specified (to_srid parameter) coordinate system. |
Examples
The following example transforms the geometries in the shape column in the COLA_MARKETS_CS table to a representation that uses SRID value 8199. The transformed geometries are stored in the newly created table named COLA_MARKETS_CS_8199. (This example uses the definitions from the example in Section 6.8.)
-- 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-5 in Section 6.8 includes a display of the geometry object coordinates in both tables (COLA_MARKETS_CS and COLA_MARKETS_CS_8199).
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 MDSYS.CS_SRS table (described in Section 6.4.1).
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.4.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 MDSYS.CS_SRS table (described in Section 6.4.1) 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.8.)
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
|
 Copyright © 1999, 2003 Oracle Corporation All Rights Reserved. |
|