Help

ST_Transform

Definition

ST_Transform converts two-dimensional ST_Geometry data into the spatial reference specified by the spatial reference ID (SRID).

Using ST_Transform with PostgreSQL

In PostgreSQL, the destination spatial reference you specify for the transformation must have the same geographic coordinate system as the source spatial reference on the ST_Geometry column.

If the data is stored in a database (rather than a geodatabase), do the following to change the spatial reference of the ST_Geometry data:

  1. Create a backup copy of the table.
  2. Create a second (destination) ST_Geometry column on the table.
  3. Register the destination ST_Geometry column, specifying the new SRID.

    This specifies the spatial reference of the column by placing a record in the sde_geometry_columns system table.

  4. Execute ST_Transform. Specify that the transformed data be output to the destination ST_Geometry column.
  5. Unregister the first (source) ST_Geometry column.

If the data is stored in a geodatabase, you should use ArcGIS tools to reproject the data to a new feature class. Executing ST_Transform on a geodatabase feature class bypasses functionality to update geodatabase system tables with the new SRID.

Using ST_Transform with Oracle

In Oracle, you can convert between spatial references that have the same geographic coordinate system or different geographic coordinate systems. When the geographic coordinate systems are different, a geographic transformation is done. A geographic transformation converts between two geographic coordinate systems. A geographic transformation is defined in a particular direction, for example, from NAD 1927 to NAD 1983, but the ST_Transform function will correctly apply the transformation no matter what the source and destination coordinate systems are.

Geographic transformation methods can be divided into two types: equation based and file based. Equation-based methods are self-contained and do not need any external information. File-based methods use on-disk files to calculate offset values. They are usually more accurate than equation-based methods. File-based methods are commonly used in Australia, Canada, Germany, New Zealand, Spain, and the United States. The files (except for the Canadian ones) can be obtained from an ArcGIS for Desktop installation or directly from the various national mapping agencies.

To support file-based transformations in Oracle, the files must be placed on the Oracle server in the same relative folder structure as the pedata folder in the ArcGIS for Desktopinstallation directory.

For example, in the ArcGIS for Desktop installation directory, there is a folder called pedata. This folder contains several subfolders, but the three folders that contain supported file-based methods are harn, nadcon, and ntv2. Either copy the pedata folder and its contents from the ArcGIS for Desktop installation directory to the Oracle server, or create a directory on the Oracle server that contains the supported file-based transformation method subdirectories and files. Once the files are on the Oracle server, set an operating system environment variable called PEDATAHOME on the server. Set the PEDATAHOME variable to the location of the directory that contains the subdirectories and files; for example, if the pedata folder is copied to C:\pedata on a Microsoft Windows server, set the PEDATAHOME environment variable to C:\pedata.

Refer to your operating system documentation for information on how to set an environment variable.

After setting PEDATAHOME, you must start a new SQL session before you can use the ST_Transform function; however, the server does not need to be restarted.

If the data is stored in a database (rather than a geodatabase) and no spatial index has been defined on the spatial column, you can add a second ST_Geometry column and output the transformed data to it. You can keep both the original (source) ST_Geometry column and the destination ST_Geometry column in the table, though you can only display one column at a time in ArcGIS using a view or altering the query layer definition for the table.

If the data is stored in a database (rather than a geodatabase) and the spatial column has a spatial index defined on it, you cannot preserve the original ST_Geometry column. Once a spatial index has been defined on an ST_Geometry column, the SRID is written to the st_geometry_columns metadata table. ST_Transform does not update that table.

  1. Create a backup copy of the table.
  2. Create a second (destination) ST_Geometry column on the table.
  3. Execute ST_Transform. Specify that the transformed data be output to the destination ST_Geometry column.
  4. Drop the spatial index from the source ST_Geometry column.
  5. Drop the source ST_Geometry column.
  6. Create a spatial index on the destination ST_Geometry column.

If the data is stored in a geodatabase, you should use ArcGIS tools to reproject the data to a new feature class. Executing ST_Transform on a geodatabase feature class bypasses functionality to update geodatabase system tables with the new SRID.

Using ST_Transform with SQLite

In SQLite, you can convert between spatial references that have the same geographic coordinate system or different geographic coordinate systems. When the geographic coordinate systems are different, a geographic transformation is done. A geographic transformation converts between two geographic coordinate systems. A geographic transformation is defined in a particular direction, for example, from NAD 1927 to NAD 1983, but the ST_Transform function will correctly apply the transformation no matter what the source and destination coordinate systems are.

Geographic transformation methods can be divided into two types: equation based and file based. Equation-based methods are self-contained and do not need any external information. File-based methods use on-disk files to calculate offset values. They are usually more accurate than equation-based methods. File-based methods are commonly used in Australia, Canada, Germany, New Zealand, Spain, and the United States. The files (except for the Canadian ones) can be obtained from an ArcGIS for Desktop installation or directly from the various national mapping agencies.

Syntax

Source and destination spatial references have the same geographic coordinate system

Oracle and PostgreSQL

sde.st_transform (geometry1 sde.st_geometry, srid integer)

SQLite

st_transform (geometry1 geometryblob, srid in32)

Source and destination spatial references do not have the same geographic coordinate system

Oracle

sde.st_transform (g1 sde.st_geometry, srid integer, geogtrans_id integer)

SQLite

st_transform (geometry1 geometryblob, srid int32, geogtrans_id int32)

Return type

Oracle and PostgreSQL

ST_Geometry

SQLite

Geometryblob

Examples

Transforming data when the source and destination spatial references have the same geographic coordinate system

The following example creates a table, transform_test, which has two linestring columns: ln1 and ln2. A line is inserted into ln1 with SRID 4326. The ST_Transform function is then used in an UPDATE statement to take the linestring in ln1, convert it from the coordinate reference assigned to SRID 4326 to the coordinate reference assigned to SRID 3857, and place it in column ln2.

Oracle

CREATE TABLE transform_test (
 ln1 sde.st_geometry,
 ln2 sde.st_geometry);

INSERT INTO transform_test (ln1) VALUES (
 sde.st_geometry ('linestring (10.01 40.03, 92.32 29.39)', 4326)
);

UPDATE transform_test
 SET ln2 = sde.st_transform (ln1, 3857);

PostgreSQL

CREATE TABLE transform_test (
 ln1 sde.st_geometry,
 ln2 sde.st_geometry);

INSERT INTO transform_test (ln1) VALUES (
 sde.st_geometry ('linestring (10.01 40.03, 92.32 29.39)', 4326)
);

UPDATE transform_test
 SET ln2 = sde.st_transform (ln1, 3857);

SQLite

CREATE TABLE transform_test (id integer);

SELECT AddGeometryColumn(
 NULL,
 'transform_test',
 'ln1',
 4326,
 'linestring',
 'xy',
 'null'
);

INSERT INTO transform_test (ln1) VALUES (
 st_geometry ('linestring (10.01 40.03, 92.32 29.39)', 4326)
);

UPDATE transform_test
 SET ln1 = st_transform (ln1, 3857);

Transforming data when the source and destination spatial references do not have the same geographic coordinate system (Oracle and SQLite only)

In the following example, table n27 is created, which contains an ID column and a geometry column. A point is inserted to table n27 with an SRID of 4267. The 4267 SRID uses the NAD 1927 geographic coordinate system.

Next, table n83 is created and the ST_Transform function is used to insert the geometry from table n27 into table n83, but with an SRID of 4269 and geographic transformation ID 1241. SRID 4269 uses the NAD 1983 geographic coordinate system and 1241 is the well-known ID for the NAD_1927_To_NAD_1983_NADCON transformation. This transformation is file based and can be used for the lower 48 states of the United States.

Oracle

--Create table.
CREATE TABLE n27 (
 id integer,
 geometry sde.st_geometry
);

--Insert point with SRID 4267.
INSERT INTO N27 (id, geometry) VALUES (
 1, 
 sde.st_geometry ('point (-123.0 49.0)', 4267)
);

--Create the n83 table as the destination table of the transformation.
CREATE TABLE n83 (
 id integer,
 geometry sde.st_geometry
);

--Run the transformation.
INSERT INTO N83 (id, geometry)(
 select c.id, sde.st_transform (c.geometry, 4269, 1241)
 from N27 c
);

If PEDATAHOME is defined correctly, a SELECT statement run against the n83 table will return the following:

SELECT id, sde.st_astext (geometry) description 
 FROM N83;

ID	DESCRIPTION
1 | POINT((-123.00130569 48.999828199))

SQLite

--Create source table.
CREATE TABLE n27 (id integer);

SELECT AddGeometryColumn(
 NULL,
 'n27',
 'geometry',
 4267,
 'point',
 'xy',
 'null'
);

--Insert point with SRID 4267.
INSERT INTO n27 (id, geometry) VALUES (
 1, 
 st_geometry ('point (-123.0 49.0)', 4267)
);

--Create the n83 table as the destination table of the transformation.
CREATE TABLE n83 (id integer);

SELECT AddGeometryColumn(
 NULL,
 'n83',
 'geometry',
 4269,
 'point',
 'xy',
 'null'
);

--Run the transformation.
INSERT INTO n83 (id, geometry) VALUES (
 1, 
 st_transform ((select geometry from n27 where id=1), 4269, 1241)
);

Related topics