15 Coordinate System Transformation Subprograms

SDO_CS.TRANSFORM

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

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

dim

Dimensional information array corresponding to geom, usually selected from one of the xxx_SDO_GEOM_METADATA views (described in Section 2.4).

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

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

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

SDO_CS.VALIDATE_WKT

Format

SDO_CS.VALIDATE_WKT(

     srid IN NUMBER

     ) RETURN VARCHAR2;

Description

Validates the well-known text (WKT) description associated with a specified SRID.

Parameters

srid

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) 

SDO_CS.VIEWPORT_TRANSFORM

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.

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