ST_Transform matching datums.... ?

[RobRader]

I am using ST_GEOMETRY to create points for old GPS data. The machines used NAD27 state plane VI (0406).

The dataset for all of the rest of the data in the geodatabase is NAD83 state plane VI (0406). In comparing the strings for these two it looks like the datums do not match. So there is no way to translate as a 1:1 from one to the other. However... is there some magical projection I can use as an interim step?

example of what I am trying to do:
SQL> column SR_NAME format A50

SQL> select srid,sr_name from sde.st_spatial_references;

      SRID SR_NAME
---------- --------------------------------------------------
         0 GCS_WGS_1984
         3 NAD_1983_UTM_Zone_11N
         5 WGS_1984_UTM_Zone_11N
         2 NAD_1927_StatePlane_California_VI_FIPS_0406
         4 NAD_1983_StatePlane_California_VI_FIPS_0406_Feet

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',0), 5) ) from dual ;

SDE.ST_ASTEXT(SDE.ST_TRANSFORM(SDE.ST_POINT('POINT(11)',0),5))
--------------------------------------------------------------------------------
EMPTY

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',5), 0) ) from dual ;

SDE.ST_ASTEXT(SDE.ST_TRANSFORM(SDE.ST_POINT('POINT(11)',5),0))
--------------------------------------------------------------------------------
POINT  ( -121.48874115 0.00000275)

now to use the projections I need:

SQL> select sde.st_astext(sde.st_transform(sde.st_point('point(1 1)',2), 4) ) from dual ;
ERROR:
ORA-20603: Spatial References are not compatible.
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 788
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2600

Any input on how to know when a projection datum matches might help.

[CARLOSSARMIENTO]

Hello.

Could solve this problem?

I have the same problem too.

Thanks

[RobRader]

State planes are extremely hard to work with. The limitation of ST_Transform to only be able to work with the same exact datum, meant it would not do what was needed in my case. I had to write an external tool for it.

Here is an example of what will work:
create one projection and a feature class with a single point in it.
create a second projection, exact same but shift the easting and northing by a couple of thousand.
ST_TRANSFORM will then be able to shift the data for you to the new projection accounting for the new easting and northing.

It appears to be extremely limited to this simple use case. If the datum is the same and the work to be completed by the tranform is more in line with a "data shift" you should work. They might want to rename it.
-- just noticed the esri icon next to my name. I used to be an employee, but now work for Telvent. So please do not take my comments as an official statement after 2009. All old posts were as an employee, but no longer.