ArcGIS Help 10.1 - ST

Definition

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

Caution:

If you registered the spatial column with the PostgreSQL database using the st_register_spatial_column function, the SRID at the time of registration is written to the sde_geometry_columns table. If you created a spatial index on the spatial column in an Oracle database, the SRID at the time the spatial index was created is written to the st_geometry_columns table. Using ST_Transform to alter the SRID of the ST_Geometry data does not update the SRID in the sde_geometry_columns or st_geometry_columns table.

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:

Create a backup copy of the table.
Create a second (destination) ST_Geometry column on the table.
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.
Execute ST_Transform. Specify that the transformed data be output to the destination ST_Geometry column.
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 Desktop installation 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.

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

Syntax

Source and destination spatial references have the same geographic coordinate system (PostgreSQL and Oracle)

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

Source and destination spatial references do not have the same geographic coordinate system (Oracle only)

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

Return type

ST_Geometry

Examples

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

The following CREATE TABLE statement creates the transform_test table, which has two linestring columns: ln1 and ln2.

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

The following INSERT statement inserts an ST_LineString into ln1 with an SRID of 4326.

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

In the following UPDATE statement, the ST_Transform function takes the linestring in ln1, converts it from the coordinate reference assigned to SRID 4326 to the coordinate reference assigned to SRID 3857, and places it in column ln2.

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

Note:

SRIDs 4326 and 3857 have the same geographic datum.

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

The following CREATE TABLE statement creates the n27 table, which contains an ID column and an ST_Geometry column:

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

The following INSERT statement inserts an ID value and an ST_Point with SRID 4267:

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

The 4267 SRID uses the NAD 1927 geographic coordinate system.

Create the n83 table as the destination table of the transformation then run the transformation:

CREATE TABLE n83 (id integer, geometry sde.st_geometry);

INSERT INTO N83 (id, geometry) VALUES (
1, 
sde.st_transform (N27.geometry, 4269, 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. 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))

Tip:

For lists of supported geographic transformations, see this technical article and its related lists: http://support.esri.com/en/knowledgebase/techarticles/detail/21327.

6/19/2015