Oracle^{®} Spatial User's Guide and Reference Release 9.2 Part No. A9663001 

The MDSYS.SDO_CS package contains functions and procedures 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 functions and procedures in this chapter, you must understand the conceptual information about coordinate systems in Section 1.5.4 and Chapter 5.
Table 131 lists the coordinate systems functions and procedures.
Table 131 Functions and Procedures for Coordinate Systems
Function  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.VIEWPORT_TRANSFORM  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 functions and procedures, listed in alphabetical order.
Format
SDO_CS.TRANSFORM(
geom IN MDSYS.SDO_GEOMETRY,
to_srid IN NUMBER
) RETURN MDSYS.SDO_GEOMETRY;
or
SDO_CS.TRANSFORM(
geom IN MDSYS.SDO_GEOMETRY,
dim IN MDSYS.SDO_DIM_ARRAY,
to_srid IN NUMBER
) RETURN MDSYS.SDO_GEOMETRY;
or
SDO_CS.TRANSFORM(
geom IN MDSYS.SDO_GEOMETRY,
to_srname IN VARCHAR2
) RETURN MDSYS.SDO_GEOMETRY;
or
SDO_CS.TRANSFORM(
geom IN MDSYS.SDO_GEOMETRY,
dim IN MDSYS.SDO_DIM_ARRAY,
to_srname IN VARCHAR2
) RETURN MDSYS.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 nonnull SRID, that is, a value in the SRID column of the MDSYS.CS_SRS table (described in Section 5.4.1).
Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views.
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 5.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 5.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 5.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 5.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 5.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 132.
Table 132 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 the Oracle9i SQL Reference. 
GEOMETRY  MDSYS.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 5.8.)
 Transform the entire SHAPE layer and put results in the table  named cola_markets_cs_8199, which the procedure will create. EXECUTE SDO_CS.TRANSFORM_LAYER('COLA_MARKETS_CS','SHAPE','COLA_MARKETS_CS_8199',8199);
Example 54 in Section 5.8 includes a display of the geometry object coordinates in both tables (COLA_MARKETS_CS and COLA_MARKETS_CS_8199).
Format
SDO_CS.VIEWPORT_TRANSFORM(
geom IN MDSYS.SDO_GEOMETRY,
to_srid IN NUMBER
) RETURN MDSYS.SDO_GEOMETRY;
Description
Transforms an optimized rectangle into a valid polygon for use with Spatial operators and functions.
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 5.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 onehalf 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. With the current restriction of not allowing this optimized rectangle type in geodetic space, this type of viewport queries cannot be sent to the database.
The VIEWPORT_TRANSFORM function provides a workaround. 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.
Note that 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.
Note: The function format with the to_srname parameter, which was supported in the previous release, is no longer supported. You must use the format with the to_srid parameter. 
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 5.8.)
SELECT c.name FROM cola_markets_cs c WHERE SDO_FILTER(c.shape, SDO_CS.VIEWPORT_TRANSFORM( MDSYS.SDO_GEOMETRY( 2003, 0,  SRID = 0 (special case) NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), MDSYS.SDO_ORDINATE_ARRAY(180,90,180,90)), 8307), 'querytype=window') = '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( MDSYS.SDO_GEOMETRY(2003, 0, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), MDSYS.SDO_ORDINATE_ARRAY(180,90,180,90)), 8307) window_geom FROM DUAL) WHERE SDO_FILTER(c.shape, window_geom, 'querytype=window') = 'TRUE'; NAME  cola_a cola_c cola_b cola_d

Copyright © 1999, 2002 Oracle Corporation All rights reserved 
