3

I have a point feature class in an Oracle 18c GDB that is projected as NAD83 UTM 17N (SRID = 26917).

I would like to extract the X&Y coordinates from the points as WGS84 latitude & longitude decimal degrees (using SQL).

  • The coordinates would be used to generate a Google Street View URL (hence the lat/long).

I think this can be done by using SDE.ST_GEOMETRY's ST_TRANSFORM function:

ST_Transform takes two-dimensional ST_Geometry data as input and returns values converted into the spatial reference specified by the spatial reference ID (SRID) you provide.

However, I'm stuck on the SRID part:

sde.st_x(sde.st_transform (shape, ??????)) as longitudex

Is there a specific SRID I should be using to convert the UTM points to WGS84 lat/long?

Improve this question
6
  • This depends on which geographic coordinate system you want to target. NAD83? WGS84?
    – Vince
    Commented Feb 4, 2020 at 23:23
  • @Vince Ah, right. NAD83. I've updated the question.
    – User1974
    Commented Feb 4, 2020 at 23:51
  • 1
    Doesn't Google use WGS84? And what is the exact SRID of your UTM (there are thousands, not including the infinite number of custom UTMs possible), because you may need a transformation as well.
    – Vince
    Commented Feb 5, 2020 at 0:00
  • 2
    4269 for NAD83, 4326 for WGS84. epsg-registry.org
    – mkennedy
    Commented Feb 5, 2020 at 0:28
  • @Vince The SRID of the original UTM 17N feature class is 26917. I've updated the question.
    – User1974
    Commented Feb 5, 2020 at 1:26

1 Answer 1

Reset to default
0

As @Vince suggested, Google Maps uses WGS84.

And as @mkennedy pointed out, the SRID of WGS84 is 4326 (epsg-registry.org).

Therefore, the SQL to generate the hyperlink/URL would be:

select
    'http://maps.google.com/maps?q=' || chr(38) || 'layer=c' || chr(38) || 'cbll=' 
    || latitudey_dd || ',' || longitudex_dd as street_view_url
from
    (
    select 
        sde.st_x(sde.st_transform (shape, 4326)) as longitudex_dd,
        sde.st_y(sde.st_transform (shape, 4326)) as latitudey_dd 
    from assets
    )

Output: http://maps.google.com/maps?q=&layer=c&cbll=44.6790448,-75.8618236

Street View URL info here: Google street view URL

Hint:

In Toad, the ampersand was being interpreted as a variable. I had to escape/replace the ampersand with ||chr(38)||. Escaping ampersand character in SQL string

Improve this answer
1
  • 1
    Unfortunately, there's going to be some datum transformation shift, because Oracle doesn't support the three parameter form of ST_Transform and NAD83 isn't quite WGS84. Melita can probably give you the error off the top of her head, but you might want to try adding a column or creating a join table with the meat of the URL string, so you can populate it more accurately using GIS tools (and you wouldn't pay the reprojection cost with each access, either).
    – Vince
    Commented Feb 5, 2020 at 2:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.