Is there a way in SQL to change the values in a geography column with latitude and longitude values inverted?

Question

In a column of a table where geography type data is kept, data is entered in (Latitude, Longitude) format instead of (Longitude, Latitude). Is there any way I can fix this easily?

Answer 1

..max 10 points per geography instance...

declare @t table (_geography geography);

insert into @t(_geography)
values
(geography::STMPolyFromText('MULTIPOLYGON(((-72.358 47.653, -72.348 47.649, -72.358 47.658, -72.358 47.653)), ((-72.341 47.656, -72.341 47.661, -72.351 47.661, -72.341 47.656)))', 4326)),
(geography::STMPolyFromText('MULTIPOLYGON(((-72.358 47.653, -72.348 47.649, -72.358 47.658, -72.358 47.653)))', 4326)),
(geography::STGeomFromText('LINESTRING(-40.360 47.656, -45.343 47.656, -46.355 44.680, -44.355 42.680 )', 4326)),
(geography::STGeomFromText('MULTIPOINT(-50.360 47.656, -50.343 47.656)', 4326)),
(geography::STGeomFromText('CIRCULARSTRING(-72.358 47.653, -72.348 47.649, -72.348 47.658, -72.358 47.658, -72.358 47.653)', 4326))  
;  

select 
    _geography.STIsValid() as oldgeographyValid,
    _geography.ToString() as oldgeographyToString,
    newgeographystring as newgeographyToString
    --geography::STGeomFromText(newgeographystring, _geography.STSrid).STIsValid()  as newgeography
from
(
select t.*, 

    formatmessage(
    --old points become placeholders
    replace(replace(replace(replace(replace( --max:10points, 10 replaces
    replace(replace(replace(replace(replace(t._geography.ToString(), 
    isnull(p.xp.value('(p[@r=1]/@o)[1]', 'varchar(100)'), ''), '%s'), --1st replace
    isnull(p.xp.value('(p[@r=2]/@o)[1]', 'varchar(100)'), ''), '%s'),
    isnull(p.xp.value('(p[@r=3]/@o)[1]', 'varchar(100)'), ''), '%s'),
    isnull(p.xp.value('(p[@r=4]/@o)[1]', 'varchar(100)'), ''), '%s'),
    isnull(p.xp.value('(p[@r=5]/@o)[1]', 'varchar(100)'), ''), '%s'), --5th replace
    isnull(p.xp.value('(p[@r=6]/@o)[1]', 'varchar(100)'), ''), '%s'),
    isnull(p.xp.value('(p[@r=7]/@o)[1]', 'varchar(100)'), ''), '%s'),
    isnull(p.xp.value('(p[@r=8]/@o)[1]', 'varchar(100)'), ''), '%s'),
    isnull(p.xp.value('(p[@r=9]/@o)[1]', 'varchar(100)'), ''), '%s'),
    isnull(p.xp.value('(p[@r=10]/@o)[1]', 'varchar(100)'), ''), '%s'), --10th replace

    --format message parameters := new points
    p.xp.value('(p[@r=1]/@n)[1]', 'varchar(100)'),
    p.xp.value('(p[@r=2]/@n)[1]', 'varchar(100)'),
    p.xp.value('(p[@r=3]/@n)[1]', 'varchar(100)'),
    p.xp.value('(p[@r=4]/@n)[1]', 'varchar(100)'),
    p.xp.value('(p[@r=5]/@n)[1]', 'varchar(100)'),
    p.xp.value('(p[@r=6]/@n)[1]', 'varchar(100)'),
    p.xp.value('(p[@r=7]/@n)[1]', 'varchar(100)'),
    p.xp.value('(p[@r=8]/@n)[1]', 'varchar(100)'),
    p.xp.value('(p[@r=9]/@n)[1]', 'varchar(100)'),
    p.xp.value('(p[@r=10]/@n)[1]', 'varchar(100)')
    ) --formatmessage end
    as newgeographystring
from @t as t
cross apply
(
    select
    (
    select 
        rownum as '@r',
        concat(t._geography.STPointN(n.rownum).Long, ' ', t._geography.STPointN(n.rownum).Lat) as '@o', --old point
        concat(t._geography.STPointN(n.rownum).Lat, ' ', t._geography.STPointN(n.rownum).Long) as '@n' --new point
    from
    (
        select top (t._geography.STNumPoints()) row_number() over(order by @@spid) as rownum
        from sys.all_objects
    ) as n
    order by rownum
    for xml path('p'), type
    ) as xp
) as p
) as src;