8
Coordinate System Functions

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

Table 8-1 lists the coordinate systems functions and procedures.

Table 8-1 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).

The rest of this chapter provides reference information on the functions and procedures, listed in alphabetical order.

Note: Error messages for coordinate system functions are documented in Section D.7. (They are not included in the Oracle8i Error Messages manual for release 8.1.7.)

---

SDO_CS.TRANSFORM

Format

SDO_CS.TRANSFORM(

geom IN MDSYS.SDO_GEOMETRY,

dim_array IN MDSYS.SDO_DIM_ARRAY,

to_srid IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

or

SDO_CS.TRANSFORM(

geom IN MDSYS.SDO_GEOMETRY,

dim_array IN MDSYS.SDO_DIM_ARRAY,

to_srname IN VARCHAR2

) RETURN MDSYS.SDO_GEOMETRY;

or

SDO_CS.TRANSFORM(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER,

to_srid IN NUMBER

) RETURN MDSYS.SDO_GEOMETRY;

or

SDO_CS.TRANSFORM(

geom IN MDSYS.SDO_GEOMETRY,

tolerance IN NUMBER,

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

dim_array

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

tolerance

Tolerance value (see Section 1.5.4).

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

to_srname

The name of the coordinate system to be used for the transformation. to_srname must be a value (specified exactly) in the CS_NAME column of the MDSYS.CS_SRS table (described in Section D.3.1).

Usage Notes

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

-- Return the transformation of cola_c using to_srid 8199 
SELECT c.name, SDO_CS.TRANSFORM(c.shape, m.diminfo, 8199) 
  FROM cola_markets c, user_sdo_geom_metadata m 
  WHERE m.table_name = 'COLA_MARKETS' 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.00074116, 3.00289624, 6.0006707, 3.00289431, 6.00067234, 5.00305745, 4.0007
1964, 5.00305956, 3.00074116, 3.00289624)) 

---

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

Usage Notes

An exception is raised if any of the following occurs:

• table_in does not exist, or column_in does not exist in the table.

• 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 8-2.

Table 8-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 Oracle8i SQL Reference manual.
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 table to a representation that uses SRID value 8199. The transformed geometries are stored in the newly created table named COLA_MARKETS_8199. (This example uses the definitions from the example in Section D.6.)

-- Transform the entire SHAPE layer and put results in the table
-- named cola_markets_8199, which the procedure will create.
EXECUTE SDO_CS.TRANSFORM_LAYER('COLA_MARKETS','SHAPE','COLA_MARKETS_8199',8199);

Example D-2 in Section D.6 includes a display of the geometry object coordinates in both tables (COLA_MARKETS and COLA_MARKETS_8199).