DB2 Version 10.1 for Linux, UNIX, and Windows

ST_Transform function

The ST_Transform function takes a geometry and a spatial reference system identifier as input parameters and transforms the geometry to be represented in the given spatial reference system. Projections and conversions between different coordinate systems are performed and the coordinates of the geometries are adjusted accordingly.

The geometry can be converted to the specified spatial reference system only if the geometry's current spatial reference system is based in the same geographic coordinate system as the specified spatial spatial reference system. If either the geometry's current spatial reference system or the specified spatial reference system is based on a projected coordinate system, a reverse projection is performed to determine the geographic coordinate system that underlies the projected one.

If the given geometry is null, then null will be returned.

This function can also be called as a method.

Syntax


>>-db2gse.ST_Transform--(--geometry--,--srs_id--)--------------><

Parameters

geometry

A value of type ST_Geometry or one of its subtypes that represents the geometry that is transformed to the spatial reference system identified by srs_id.

srs_id

A value of type INTEGER that identifies the spatial reference system for the resulting geometry.

If the transformation to the specified spatial reference system cannot be performed because the current spatial reference system of geometry is not compatible with the spatial reference system identified by srs_id, then an exception condition is raised (SQLSTATE 38SUC).

If srs_id does not identify a spatial reference system listed in the catalog view DB2GSE.ST_SPATIAL_REFERENCE_SYSTEMS, then an exception condition is raised (SQLSTATE 38SU1).

Return type

db2gse.ST_Geometry

Examples

Example 1

The following example illustrates the use of ST_Transform to convert a geometry from one spatial reference system to another.

First, the state plane spatial reference system with an ID of 3 is created using a call to db2se.

db2se create_srs SAMP_DB
   -srsId 3 -srsName z3101a -xOffset 0 -yOffset 0 -xScale 1 -yScale 1
   - coordsysName NAD_1983_StatePlane_New_York_East_FIPS_3101_Feet

Then, points are added to:

The SAMPLE_POINTS_SP table in state plane coordinates using that spatial reference system.
The SAMPLE_POINTS_LL table using coordinates specified in latitude and longitude.

SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse
CREATE TABLE sample_points_sp (id INTEGER, geometry ST_Point)
CREATE TABLE sample_points_ll (id INTEGER, geometry ST_Point)

INSERT INTO sample_points_sp
  VALUES (12457, ST_Point('point ( 567176.0 1166411.0)', 3) )

INSERT INTO sample_points_sp
  VALUES (12477, ST_Point('point ( 637948.0 1177640.0)', 3) )

INSERT INTO sample_points_ll
  VALUES (12457, ST_Point('point ( -74.22371600 42.03498700)', 1) )

INSERT INTO sample_points_ll
  VALUES (12477, ST_Point('point ( -73.96293200 42.06487900)', 1) )

Then the ST_Transform function is used to convert the geometries.

Example 2

This example converts points that are in latitude and longitude coordinates to state plane coordinates.

SELECT id, CAST( ST_AsText( ST_Transform( geometry, 3) )
    AS VARCHAR(100) ) STATE_PLANE
  FROM sample_points_ll

Results:

ID          STATE_PLANE
----------- -----------------------------------------
      12457 POINT ( 567176.00000000 1166411.00000000)
      12477 POINT ( 637948.00000000 1177640.00000000)

Example 3

This example converts points that are in state plane coordinates to latitude and longitude coordinates.

SELECT id, CAST( ST_AsText( ST_Transform( geometry, 1) )
    AS VARCHAR(100) ) LAT_LONG
  FROM sample_points_sp

Results:

ID          LAT_LONG
----------- ----------------------------------
      12457 POINT ( -74.22371500 42.03498800)
      12477 POINT ( -73.96293100 42.06488000)