ArcMap

ST_Transform

Definition

ST_Transform takes two-dimensional ST_Geometry data as input and returns values converted into the spatial reference specified by the spatial reference ID (SRID) you provide.

When the geographic coordinate systems are different, ST_Transform performs a geographic transformation. 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 Desktop installation or directly from the various national mapping agencies.

To support file-based transformations, you must place the files on the database server in the same relative folder structure as the pedata folder in the ArcGIS Desktopor ArcGIS Pro installation directory.

For example, there is a folder called pedata in the ArcMap installation directory and in the Resources folder of the ArcGIS Pro installation directory. The pedata 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 installation directory to the database server, or create a directory on the database server that contains the supported file-based transformation method subdirectories and files. Once the files are on the database server, set an operating system environment variable called PEDATAHOME on the same 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 Windowsserver, 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.

Using ST_Transform with PostgreSQL

In PostgreSQL, you can convert between spatial references that have the same geographic coordinate system or different geographic coordinate systems.

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 when geographic coordinate systems are the same:

  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.

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.

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)

PostgreSQL

Option 1: sde.st_transform (g1 sde.st_geometry, srid int)

Option 2: sde.st_transform (g1 sde.st_geometry, srid int, [geogtrans_id int])

Option 3: sde.st_transform (g1 sde.st_geometry, srid int, [extent double] [prime meridian double] [unit conversion factor double])

In option 3, you can optionally specify the extent as a comma separated list of coordinates in the following order: lower left x-coordinate, lower left y-coordinate, upper right x-coordinate, upper right y-coordinate. If you do not specify an extent, ST_Transform uses a larger, more general extent.

When specifying an extent, the prime meridian and unit conversion factor parameters are optional. You only need to provide this information if the extent values you specify do not use the Greenwich prime meridian or decimal degrees.

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

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))

PostgreSQL

--Option 1
--Gets geographic transformation from ST_Geometry libraries.
--Does not require you to provide a GTid. 
--Performs an equation-based transformation between two geographic coordinate systems 
--with different datums. (SRID 4267/DATUM NAD27 to SRID 4269/DATUM NAD 83)

--Provide point to transform.
SELECT sde.ST_AsText(sde.ST_Transform(
  sde.ST_Geometry('point (-155.7029 63.6096)',4267), 4269));

--Returns output in SRID 4269.
"POINT ( -155.70290000 63.60960000)"

--Option 2 
--Example uses input point in SRID 3857(DATUM: WGS 1984) 
--and geographic transformation ID (GTid) 1251.
--Transforms point to SRID 102008 (DATUM: NAD 83)

--Provide point to transform.
SELECT sde.ST_AsText(sde.ST_Transform(
  sde.ST_Geometry('point (-13244252.9404 4224702.5198)', 3857), 102008, 1251));

--Returns output in SRID 102008.
"POINT (-1957193.14740000 -297059.19680000)"

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