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 · Accepted solution]

Doh! Yes, ST_X and ST_Y works.

SELECT EID,
    ST_X(sde.st_transform(SHAPE, 4152)) as LONGITUDE,
    ST_Y(sde.st_transform(SHAPE, 4152)) as LATITUDE
FROM LIS.ADDRESS_PNT;

I'll see about getting our funked up spatial references table fixed. Thanks Joshua!

[BlakeTerhune]

After working with Esri support (thanks Jing!) I have some more information to share.

To add a new entry to ST_SPATIAL_REFERENCE Table, you can create or import a feature class with the spatial reference you wanted to the geodatabase. Make sure you use ST_Geometry as the Configuration Keyword. By default, if you choose Default as the Configuration Keyword, it will create a ST_Geometry type feature class.

If you know a feature class that is using the desired SRID, you can import this feature class to your 10.0 geodatabase, there will be a new record account for this SRID in the ST_SPATIAL_REFERENCE Table.

If you want to create a feature class to generate the desired SRID, you can following the following steps

1. For example the SRID you wanted is 4152, you can run "select * from ST_COORDINATE_SYSTEMS where id=4152;" in Oracle. The returned Type field is Geographic, so when we select the coordinate system for our new feature class, you will go to Geographic Coordinate Systems. The Description Colum will give us a clue on where to find this coordinate system.
2. In ArcCatalog, right click on the database connection > New > Feature class > Provide Name, then click Next > Expand Geographic Coordinate Sytems, Then North America, select NAD 1983 HARN, then click Next > Next > Select Default as the Configuration Keyword, click Next > Finish
3. Check ST_SPATIAL_REFERENCE Table, you will see a new record show up in there.
4. Feel free to delete the feature class. Delete the feature class will not delete the record in the ST_SPATIAL_REFERENCE Table

This worked in ArcSDE 10.0 but I haven't tested anything else. I'm assuming from looking at the documentation that it works the same. The big thing to keep in mind here is that SRID is just an arbitrary number for that row in the ST_Spatial_References table. It means nothing outside of that table. The SRID doesn't have to be the same as the CS_ID. So when we upgrade to 10.2.2, we will have to remember to check the SRIDs in the table to see if they changed and alter our SQL views accordingly.

I followed the steps to create a new feature class in SDE with the coordinate system I wanted (GCS_North_American_1983_HARN - 4152). Our final SQL for 10.0 uses 57 for SRID, which only applies to our particular SDE database.

SELECT EID,  
    ST_X(sde.st_transform(SHAPE, 57)) as LONGITUDE,  
    ST_Y(sde.st_transform(SHAPE, 57)) as LATITUDE  
FROM LIS.ADDRESS_PNT;