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 specified 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 region covered by the specified spatial reference system covers the location of the geometry. 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.

Syntax

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 SYSGEO.ST_SPATIAL_REFERENCE_SYSTEMS, then an exception condition is raised (SQLSTATE 38SU1).

Return type

ST_Geometry

Examples

Prepare Example Data

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 33 is created.


call st_create_srs('Z3101A', 33, 0, 0, 1, 0, 1, 0, 1, 
      '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.


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)', 33) )

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

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 1

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


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

Results:


ID          STATE_PLANE
----------- -----------------------------------------
      12457 POINT (567176 1166411)                                                
      12477 POINT (637948 1177640)

Example 2

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.223715 42.034988)                                          
      12477 POINT (-73.962931 42.064880)