Return Lat, Long with SQL query (Oracle)

14466
11
Jump to solution
03-31-2015 02:07 PM
BlakeTerhune
MVP Regular Contributor

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?

0 Kudos
1 Solution

Accepted Solutions
BlakeTerhune
MVP Regular Contributor

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!

View solution in original post

0 Kudos
11 Replies
BlakeTerhune
MVP Regular Contributor

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
MVP Esteemed Contributor

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)

0 Kudos
BlakeTerhune
MVP Regular Contributor

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

0 Kudos
BlakeTerhune
MVP Regular Contributor

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?

0 Kudos
BlakeTerhune
MVP Regular Contributor

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

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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
MVP Regular Contributor

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?
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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?

0 Kudos