The ST_Transform() function

The ST_Transform() function transforms an ST_Geometry into the specified spatial reference system.

The following transformations are valid:

Between two UNKNOWN coordinate systems (that is, the srtext column in the spatial_references table for both SRIDs contains UNKNOWN)
Between a projected coordinate system and an unprojected coordinate system
Between two projected coordinate systems
Between two coordinate systems that have different false origins or system units

Between two UNKNOWN coordinate systems (that is, the srtext column in the spatial_references table for both SRIDs contains UNKNOWN)
Between a projected coordinate system and an unprojected coordinate system, in which the underlying geographic coordinate systems are the same
Between two projected coordinate systems, in which the underlying geographic coordinate systems are the same
Between two coordinate systems with the same geographic coordinate system that have different false origins or system units

The geographical coordinate systems of the source and target spatial reference systems do not need to be the same. A spatial reference system in one geographical coordinate system can be transformed into a spatial reference system in a different geographical coordinate system if the transform is supported by the ESRI libraries.

The geographical coordinate systems of the source and target spatial reference systems must be the same. Datum conversion is not supported.

Syntax

ST_Transform(g ST_Geometry, SRID integer)

Return type

ST_Geometry

Example: Change the false origin of a spatial reference system

Suppose you create a spatial_references table entry suitable for Australia with the SE_CreateSrid() function:

EXECUTE FUNCTION SE_CreateSrid (110, -45, 156, -10,
                                "Australia: lat/lon coords");

(expression) 

        1002

Now load all of your data for Australia:

CREATE TABLE aus_locns (name varchar(128), locn ST_Point);

INSERT INTO aus_locns VALUES ("Adelaide",    '1002 point(139.14 -34.87)');
INSERT INTO aus_locns VALUES ("Brisbane",    '1002 point(153.36 -27.86)');
INSERT INTO aus_locns VALUES ("Canberra",    '1002 point(148.84 -35.56)');
INSERT INTO aus_locns VALUES ("Melbourne",   '1002 point(145.01 -37.94)');
INSERT INTO aus_locns VALUES ("Perth",       '1002 point(116.04 -32.12)');
INSERT INTO aus_locns VALUES ("Sydney",      '1002 point(151.37 -33.77)');

After you load all of your data for the Australian mainland, you realize you must include data for a few outlying islands, such as Norfolk Island and the Cocos Islands. However, the false origin and scale factor that you chose for SRID 1002 does not work for these islands as well:

INSERT INTO aus_locns VALUES ("Norfolk Is.", '1002 point(167.83 -29.24)');
(USE19) - Coordinates out of bounds in ST_PointIn.

INSERT INTO aus_locns VALUES ("Cocos Is.",   '1002 point( 96.52 -12.08)');
(USE19) - Coordinates out of bounds in ST_PointIn.

The solution is to create a spatial_references table entry with a false origin and scale factor that accommodates both the old data and new data, and then update the old data:

EXECUTE FUNCTION SE_CreateSrid (95, -55, 170, -10,
                                "Australia + outer islands: lat/lon coords");

(expression) 

        1003


INSERT INTO aus_locns VALUES ("Norfolk Is.", '1003 point(167.83 -29.24)');
INSERT INTO aus_locns VALUES ("Cocos Is.",   '1003 point( 96.52 -12.08)');

UPDATE aus_locns
   SET locn = ST_Transform(locn, 1003)::ST_Point
   WHERE ST_Srid(locn) = 1002;

Example: Project data dynamically

In a typical application, spatial data is stored in unprojected latitude and longitude format. Then, when you draw a map, you retrieve the data in a particular projection.

First, create a spatial_references table entry that is suitable for your unprojected data. For this example, use the 1983 North American datum. Because this datum is a well-known, standard datum you can use the SE_CreateSrtext() function to create the srtext field:

INSERT INTO spatial_references 
  (srid, description, falsex, falsey, xyunits,
   falsez, zunits, falsem, munits, srtext)
VALUES (1004, "Unprojected lat/lon, NAD 83 datum",
        -180, -90, 5000000, 0, 1000, 0, 1000, 
        SE_CreateSrtext(4269));

Now create a table and load your data:

CREATE TABLE airports (id    char(4),
                       name  varchar(128),
                       locn  ST_Point);

INSERT INTO airports VALUES(
   'BTM', 'Bert Mooney',          '1004 point(-112.4975 45.9548)');
INSERT INTO airports VALUES(
   'BZN', 'Gallatin Field',       '1004 point(-111.1530 45.7769)');
INSERT INTO airports VALUES(
   'COD', 'Yellowstone Regional', '1004 point(-109.0238 44.5202)');
INSERT INTO airports VALUES(
   'JAC', 'Jackson Hole',         '1004 point(-110.7377 43.6073)');
INSERT INTO airports VALUES(
   'IDA', 'Fanning Field',        '1004 point(-112.0702 43.5146)');

Create one or more spatial_references table entries for any projections that you need. Be sure that the underlying geographic coordinate system (in this case, NAD 83) is the same:

INSERT INTO spatial_references 
  (srid, description, falsex, falsey, xyunits,
   falsez, zunits, falsem, munits, srtext)
VALUES (1005, "UTM zone 12N, NAD 83 datum",
        336000, 4760000, 1000, 0, 1000, 0, 1000, 
        SE_CreateSrtext(26912));

Transform the data to a projected coordinate system on an as needed basis:

SELECT id, ST_Transform(locn, 1005) as utm FROM airports;


id   BTM
utm  1005 POINT (383951.152 5090115.666) 

id   BZN
utm  1005 POINT (488105.331 5069271.419) 

id   COD
utm  1005 POINT (657049.762 4931552.365) 

id   JAC
utm  1005 POINT (521167.881 4828291.447) 

id   IDA
utm  1005 POINT (413500.979 4818519.081)

Example: Compare geometries that have different SRIDs

You can use the ST_Transform() function to transform a geometry when you compare two geometries that have different SRIDs:

SELECT * FROM tab1 a, tab2 b WHERE
   ST_Intersects(a.shape, ST_Transform(b.shape, ST_SRID(a.shape)));

Example: Transform between geographic spatial reference systems

The following statements create a table and insert data for the geographic spatial reference system 4326:

CREATE TABLE geogcs_to_geogs_xform (pid smallint, geom ST_Geometry) ;

INSERT INTO geogcs_to_geogs_xform 
       VALUES (5, ST_GeomFromText ('point (10.05 10.28)', 4326)) ;

INSERT INTO geogcs_to_geogs_xform 
       VALUES (6, ST_GeomFromText ('point z (10.05 10.28 2.51)', 4326)) ;

INSERT INTO geogcs_to_geogs_xform 
       VALUES (7, ST_GeomFromText ('point m (10.05 10.28 4.72)', 4326)) ;

INSERT INTO geogcs_to_geogs_xform 
       VALUES (8, ST_GeomFromText ('point zm (10.05 10.28 2.51 4.72)', 4326)) ;

The following query transforms the rows from the geographic spatial reference system 4326 to the geographic spatial reference system 4269:

SELECT pid, ST_Transform (geom, 4269) FROM geogcs_to_geogs_xform;

pid           5
(expression)  4269 POINT (10.0499794612 10.2799956451)

pid           6
(expression)  4269 POINT Z (10.0499794612 10.2799956451 2.51)

pid           7
(expression)  4269 POINT M (10.0499794612 10.2799956451 4.72)

pid           8
(expression)  4269 POINT ZM (10.0499794612 10.2799956451 2.51 4.72)

4 row(s) retrieved.

Example: Transform between projected spatial reference systems

This example transforms data between projected spatial reference systems that are in different geographic coordinate systems.

The following statements create a table and insert data for the projected spatial reference system 2153:

CREATE TABLE projcs_to_projcs_xform (pid smallint, geom ST_Geometry) ;

INSERT INTO projcs_to_projcs_xform 
       VALUES (11, ST_GeomFromText ('point(573900 9350)', 2153)) ;

INSERT INTO projcs_to_projcs_xform 
       VALUES (12, ST_GeomFromText ('multipoint(573900 9350, 573900 9351, 
               573901 9351, 573901 9350, 573900 9350)', 2153)) ;

INSERT INTO projcs_to_projcs_xform 
       VALUES (13, ST_GeomFromText ('linestring(573900 9350, 573901 9350)', 
               2153)) ;

INSERT INTO projcs_to_projcs_xform 
       VALUES (14, ST_GeomFromText ('linestring(573900 9350, 573900 9351, 
               573901 9351, 573901 9350, 573900 9350)', 2153)) ;

INSERT INTO projcs_to_projcs_xform 
       VALUES (15, ST_GeomFromText ('multilinestring((573900 9350, 573900 9351, 
               573901 9351, 573901 9350, 573900 9350),(573902 2, 573902 3, 
               573903 3, 573903 2, 573902 2))', 2153)) ;

INSERT INTO projcs_to_projcs_xform 
       VALUES (16, ST_GeomFromText ('polygon((573900 9350, 573900 9351, 
               573901 9351, 573901 9350, 573900 9350))', 2153)) ;

INSERT INTO projcs_to_projcs_xform 
       VALUES (17, ST_GeomFromText ('multipolygon(((573900 9350, 573900 9351, 
               573901 9351, 573901 9350, 573900 9350)),((573902 2, 573902 3, 
               573903 3, 573903 2, 573902 2)))', 2153)) ;

The following query transforms the rows from the projected spatial reference system 2153 to the projected spatial reference system 32611:

SELECT pid, ST_Transform (geom, 32611) FROM projcs_to_projcs_xform;

pid           11
(expression)  32611 POINT (573898.627678 9349.9324469)

pid           12
(expression)  32611 MULTIPOINT (573898.627678 9349.9324469, 573898.627678 9350.
              9324471, 573899.627679 9350.93244701, 573899.627679 9349.93244681
              , 573898.627678 9349.9324469)

pid           13
(expression)  32611 LINESTRING (573898.627678 9349.9324469, 573899.627679 9349.
              93244681)

pid           14
(expression)  32611 LINESTRING (573898.627678 9349.9324469, 573898.627678 9350.
              9324471, 573899.627679 9350.93244701, 573899.627679 9349.93244681
              , 573898.627678 9349.9324469)

pid           15
(expression)  32611 MULTILINESTRING ((573898.627678 9349.9324469, 573898.627678
               9350.9324471, 573899.627679 9350.93244701, 573899.627679 9349.93
              244681, 573898.627678 9349.9324469),(573900.626767 1.93059742451,
               573900.626768 2.93059762195, 573901.626768 2.93059752136, 573901
              .626768 1.93059733883, 573900.626767 1.93059742451))

pid           16
(expression)  32611 POLYGON ((573898.627678 9349.9324469, 573899.627679 9349.93
              244681, 573899.627679 9350.93244701, 573898.627678 9350.9324471,
              573898.627678 9349.9324469))

pid           17
(expression)  32611 MULTIPOLYGON (((573898.627678 9349.9324469, 573899.627679 9
              349.93244681, 573899.627679 9350.93244701, 573898.627678 9350.932
              4471, 573898.627678 9349.9324469)),((573900.626767 1.93059742451,
               573901.626768 1.93059733883, 573901.626768 2.93059752136, 573900
              .626768 2.93059762195, 573900.626767 1.93059742451)))

7 row(s) retrieved.