asked on Insert GEOM field converted to latitude and longitude into a table.
I am running the following on a table to insert a GEOM field:
--insert geometry into XX_XX_INFO table
ALTER TABLE XX_XX_INFO
ADD GEOM GEOMETRY
MERGE INTO XX_XX_INFO
USING XX1.DBO.EL_METER
ON Replace(LTrim(Replace(XX_X
WHEN MATCHED THEN
UPDATE SET GEOM = EL_METER.GEOM;
Which works great. But now I need to split that GEOM field into Latitudes and Longitudes and update the table instead of putting the GEOM field into it. I can query the GEOM field to get that info with this:
select
Geom.Lat as [Latitude],
Geom.Long as [Longitude]
from XX_XX_INFO
I just do not know how to get it put the lat longs into the table instead of the geometry.
Any help is appreciated.
--insert geometry into XX_XX_INFO table
ALTER TABLE XX_XX_INFO
ADD GEOM GEOMETRY
MERGE INTO XX_XX_INFO
USING XX1.DBO.EL_METER
ON Replace(LTrim(Replace(XX_X
WHEN MATCHED THEN
UPDATE SET GEOM = EL_METER.GEOM;
Which works great. But now I need to split that GEOM field into Latitudes and Longitudes and update the table instead of putting the GEOM field into it. I can query the GEOM field to get that info with this:
select
Geom.Lat as [Latitude],
Geom.Long as [Longitude]
from XX_XX_INFO
I just do not know how to get it put the lat longs into the table instead of the geometry.
Any help is appreciated.
Microsoft SQL Server
Last Comment
Zberteoc
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
What do you mean standard? That IS standard.
Ig you want degrees, minutes and seconds then use this:
select
Geom.STX as [Latitude],
Geom.STY as [Longitude],
CASE WHEN Geom.STX < 0 then '-' ELSE '' END +
CAST(FLOOR(ABS(Geom.STX)) as varchar) + ' ' +
CAST(CAST(FLOOR(ABS(Geom.STX) * 60) AS INT) % 60 as varchar) + ''' ' +
CAST(CAST(FLOOR(ABS(Geom.STX) * 3600) AS INT) % 60 AS VARCHAR) + '"' as Lat_Dgr,
CASE WHEN Geom.STY < 0 then '-' ELSE '' END +
CAST(FLOOR(ABS(Geom.STY)) as varchar) + ' ' +
CAST(CAST(FLOOR(ABS(Geom.STY) * 60) AS INT) % 60 as varchar) + ''' ' +
CAST(CAST(FLOOR(ABS(Geom.STY) * 3600) AS INT) % 60 AS VARCHAR) + '"' as Long_Dgr
from
XX_XX_INFO
ASKER
I get the following error:
Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 9237998671.000000.
Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 9237998671.000000.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
That worked, but it is out putting as state plane coordinates x and y. Is their a way to convert to standard lat longs?