I am trying to write a SQL trigger that will automatically update latitude and longitude values. I have gotten it to populate the fields with the x and y coordinate values for the projection that the data is in, but I want to convert those into latitude and longitude values. I have found that ST_Transform should complete the task, but I keep getting errors indicating that ST_Transform is not a recognized command. SQL indicates the expression is valid, but when I try to add a new point in ArcGIS, I get the error. The code that I used to get the coordinates in the projection the data is in is below.
set long = shape.STX,
lat = shape.STY
where long is null
The data is in EPSG 3078, but I want the coordinates to show in EPSG 4326.
I have tried using sde.ST_Transform as well, and I get the same error with that. I have also tried using st_x and st_y, but those aren't working either. ArcGIS keeps giving me errors about ambiguous user-defined functions.
I am using ArcGIS 10.2 and SQL 2008 R2 Management Studio Express.
Does anyone have any ideas of how I can take the coordinate values from shape.STX and shape.STY and transform those into latitude and longitude values for the fields in my table?
Any assistance is greatly appreciated.
Thank you in advance.