topic Re: Return Lat, Long with SQL query (Oracle) in Data Management Questions

Return Lat, Long with SQL query (Oracle)

BlakeTerhune — Tue, 31 Mar 2015 21:07:44 GMT

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?

Re: Return Lat, Long with SQL query (Oracle)

BlakeTerhune — Sun, 12 Dec 2021 02:07:28 GMT

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

Re: Return Lat, Long with SQL query (Oracle)

JoshuaBixby — Wed, 01 Apr 2015 16:25:41 GMT

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)

Re: Return Lat, Long with SQL query (Oracle)

BlakeTerhune — Wed, 01 Apr 2015 16:37:53 GMT

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.

Re: Return Lat, Long with SQL query (Oracle)

JoshuaBixby — Wed, 01 Apr 2015 16:46:05 GMT

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.

Re: Return Lat, Long with SQL query (Oracle)

BlakeTerhune — Wed, 01 Apr 2015 17:03:56 GMT

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?

Re: Return Lat, Long with SQL query (Oracle)

BlakeTerhune — Wed, 01 Apr 2015 18:42:54 GMT

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

Re: Return Lat, Long with SQL query (Oracle)

JoshuaBixby — Wed, 01 Apr 2015 20:55:31 GMT

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.

Re: Return Lat, Long with SQL query (Oracle)

BlakeTerhune — Wed, 01 Apr 2015 21:03:19 GMT

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:

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

Re: Return Lat, Long with SQL query (Oracle)

JoshuaBixby — Wed, 01 Apr 2015 21:49:05 GMT

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?

Re: Return Lat, Long with SQL query (Oracle)

BlakeTerhune — Sun, 12 Dec 2021 02:07:31 GMT

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!

Re: Return Lat, Long with SQL query (Oracle)

BlakeTerhune — Sun, 12 Dec 2021 02:07:34 GMT

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
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.
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
Check ST_SPATIAL_REFERENCE Table, you will see a new record show up in there.
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;