In our Oracle 11g SDE (10.0), there's a point feature class with a Shape field of ST_Geometry type. It also has a Northing and Easting field that gets populated by GPS personnel. I need to be able to get GCS_WGS_1984 (SRID 0) latitude and longitude (decimal degrees) from the points that are in NAD_1983_HARN_StatePlane_Arizona_Central_FIPS_0202 (SRID 48). I'm sure there's a way to do it with ST_Transform and/or ST_AsText, but I haven't been able to figure it out; ST_AsText always comes through as just (HUGE CLOB). What's the proper Oracle SQL syntax to get this?
Solved! Go to Solution.
Doh! Yes, ST_X and ST_Y works.
ST_X(sde.st_transform(SHAPE, 4152)) as LONGITUDE,
ST_Y(sde.st_transform(SHAPE, 4152)) as LATITUDE
I'll see about getting our funked up spatial references table fixed. Thanks Joshua!
Well, I figured out the (HUGE CLOB) issue, but I still can't get it to convert from UTM to Latitude, Longitude. I get an error saying
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 2577
Here's the SQL
SELECT EID, SHAPE, ST_X(SHAPE) as X, ST_Y(SHAPE) as Y,
sde.st_astext(sde.st_transform(SHAPE, 0)) AS TEXTCOORD
I was just testing the ST_X and ST_Y to see if any ST_Geometry operations would work (and they did).
The SRID value for WGS84 isn't 0, it is 4326. For most systems, there is no spatial reference with an SRID value of 0, 0 is usually used by default to say there is no SRID value. If all datasets have SRID of 0, you can calculate against them but mixing SRID of 0 with other values usually causes issues.
Also, you appear to be running into this issue, which I think still applies to st_transform even though the KB doesn't state it: Error: ORA-20603: Spatial References are not compatible (from ST_Transform)
As always, thanks for the reply, Joshua. I get what you're saying, but I get this error when I try to use that number:
ORA-20005: srid 4326 does not exist in st_spatial_references table.
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 2556
When I look at the SDE.ST_Spatial_References table, the SRID column has smaller numbers and there are four records that have WGS1984 in the name. The CS_ID field has the 4326 number, but it doesn't appear that ST_Transform is looking at that field.
Your stateplane data doesn't have the same datum, i.e., NAD83 != WGS84, which is what I think is throwing the error. NAD83 and WGS84 may be very similar, and even treated the same in some transformations, but they aren't the same. St_transform appears to want the datums to be exactly the same.
Try SRIDs of 6152 and 4269. I am thinking the former might work.
Both 6152 and 4269 still give the same error: SRID does not exist. The SRID column in the ST_Spatial_References table go from 0 to 56 and many of the values in SR_NAME are the same. Of the 56 records, there are only 8 distinct:
Maybe our ST_Spatial_References table is incomplete?
I just checked in our development instance of SDE that we upgraded to 10.2.2 and the SDE.ST_Spatial_References table is different. It actually has SRIDs that match the expected numbers like 4269 and 4326, but it also has the other "off" numbers like 0 and 48. SRID 6152 is not in there at all.
I tried running the same query on the Dev 10.2.2 SDE database and I got it to work but the coordinates still seem wrong (there's no negative).
I checked the spatial reference table in one of our Oracle 11g2 10.2.2 instances. It has 5,269 entries with only 1 below SRID of 1000, and that is SRID = 0. Interestingly enough, the name for SRID 0 is the same as for SRID 4326, i.e., GCS_WGS_1984.
Regarding 6152, it looks like I fat-fingered it. Try 4152.
SRID 4152 was the ticket! However, it only works in 10.2.2 SDE. When I run the same query in 10.0 it errors because it can't find the SRID in the spatial references table.
Regarding the 1st question, it might be best to ask Esri Support since we are talking SDE system tables in a production geodatabases.
For the 2nd question, does using ST_X and ST_Y not work for you?