Return Lat, Long with SQL query (Oracle)

[BlakeTerhune]

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?

[BlakeTerhune]

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
FROM LIS.ADDRESS_PNT

I was just testing the ST_X and ST_Y to see if any ST_Geometry operations would work (and they did).

[JoshuaBixby]

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)

[BlakeTerhune]

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.

[JoshuaBixby]

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.

[BlakeTerhune]

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:

• GCS_WGS_1984
• NAD_1927_StatePlane_Arizona_Central_FIPS_0202
• NAD_1983_HARN_StatePlane_Arizona_Central_FIPS_0202
• NAD_1983_HARN_StatePlane_Arizona_Central_FIPS_0202_Feet_Intl
• NAD_1983_HARN_StatePlane_Arizona_Central_FIPS_0202_IntlFeet
• NAD_1983_HARN_UTM_Zone_12N
• NAD_1983_UTM_Zone_12N
• Unknown_CoordSys

Maybe our ST_Spatial_References table is incomplete?

[BlakeTerhune]

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

[JoshuaBixby]

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.

[BlakeTerhune]

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.

Follow-up questions:

1. Any idea on the best way to update/refresh the spatial references table in SDE so it has the right SRIDs?
2. Do you know if there is a way to calculate latitude and longitude as separate fields instead of the CLOB returned from ST_AsText?

[JoshuaBixby]

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?