13
Coordinate System Transformation Functions

SDO_CS.TRANSFORM

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

geom

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

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views.

to_srid

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

to_srname

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))

SDO_CS.TRANSFORM_LAYER

Format

SDO_CS.TRANSFORM_LAYER(

     table_in IN VARCHAR2,

     column_in IN VARCHAR2,

     table_out IN VARCHAR2,

     to_srid IN NUMBER);

Description

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

Parameters

table_in

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

column_in

Column in table_in that contains the geometries to be transformed.

table_out

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

to_srid

The SRID of the coordinate system to be used for the transformation. to_srid must be a value in the SRID column of the 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 13-2.

Table 13-2 Table to Hold Transformed Layer

Column Name	Data Type	Description
SDO_ROWID	ROWID	Oracle ROWID (row address identifier). For more information about the ROWID data type, see 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 5-4 in Section 5.8 includes a display of the geometry object coordinates in both tables (COLA_MARKETS_CS and COLA_MARKETS_CS_8199).

SDO_CS.VIEWPORT_TRANSFORM

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

geom

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

to_srid

The SRID of the coordinate system to be used for the transformation (that is, the SRID to be used in the returned geometry). to_srid must be either a value in the SRID column of the 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 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. 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.

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