What's the point of text values? GPS Coordinates for the masses...

[ThomasColson]

In Using SQL to Auto-Populate XY Values I described how to use SQL Geography in SDE to automate the population of Lat and Lon values. IN some applications, my users are also looking for Degrees, Minutes, and Seconds, depending on the application, mission, and the end-user. In my line of business, we deal with a lot of folks whom are still looking at GPS read-outs on their mobile or GPS device in that format.

With the automation of data production, I'd like for end users to be able to "click" on a feature (in Portal or AGOL) and be able to see coordinate pairs in numerous formats, without having to "push more buttons". Real handy when a helo pilot whom learned how to fly in 'Nam with a topo map strapped to his knee radios in for the location of the drop point....

By adding some text attributes to a feature class and spinning some SQL magic on the back-end, you can automate the population of Degrees, Minutes, and Seconds, with proper annotation, on data creation. Now downstream users can get coordinate pairs in whatever format they need!

Here's the business end:

SELECT
[SHAPE].[Lat] as [DecimalLatitude],
floor(ABS([SHAPE].[Lat]))*(CASE WHEN [SHAPE].[Lat] < 0 then -1 ELSE 1 END) as [LatDegrees],
convert(int,[SHAPE].[Lat]*60) % 60 as [LatMinutes],
convert(decimal(4,2),convert(decimal(17,10),[SHAPE].[Lat]*3600) % 60) as [LatSeconds],

[SHAPE].[Long] as [DecimalLongitude],
floor(ABS([SHAPE].[Long]))*(CASE WHEN [SHAPE].[Long] < 0 then -1 ELSE 1 END) as [LongDegrees],
convert(int,ABS([SHAPE].[Long])*60) % 60 as [LongMinutes],
convert(decimal(4,2),convert(decimal(17,10),ABS([SHAPE].[Long])*3600) % 60) as [LongSeconds]

FROM [SOME TABLE IN GEOGRAPHY FORMAT]

which returns a bunch of columns:

DecimalLatitude	LatDegrees	LatMinutes	LatSeconds	DecimalLongitude	LongDegrees	LongMinutes	LongSeconds
35.6855385005474	35	41	7.94	-83.5370928999037	-83	32	13.53
35.6871696002781	35	41	13.81	-83.5376158999279	-83	32	15.42
35.6880251001567	35	41	16.89	-83.5350660998374	-83	32	6.24

Mixing things up we can:

SELECT
cast(convert(int,(floor(ABS([SHAPE].[Lat]))))as varchar) +NCHAR(176)+' '+ cast( convert(int,[SHAPE].[Lat]*60) % 60 as varchar) + ''' '+
cast( convert(decimal(17,3),[SHAPE].[Lat]*3600) % 60 as varchar) + '"',


'-'+cast(convert(int,(floor(ABS([SHAPE].[Long]))))as varchar) +NCHAR(176)+' '+ cast( convert(int,ABS([SHAPE].[Long])*60) % 60 as varchar) + ''' '+
cast( convert(decimal(17,3),ABS([SHAPE].[Long])*3600) % 60 as varchar) + '"'
FROM [SOME TABLE IN GEOGRAPHY FORMAT]

Note the addition of some ABS to keep negative symbols where they belong, and NCHAR(176) which gives us the Degree Symbol.

(No column name)	(No column name)
35° 41' 7.939"	-83° 32' 13.534"
35° 41' 13.811"	-83° 32' 15.417"
35° 41' 16.890"	-83° 32' 6.238"
35° 41' 16.673"	-83° 32' 17.458"

And here's how it works in a trigger:

CREATE TRIGGER [dbo].[YAY_GEOMETRY]
ON [dbo].[YAY]
after INSERT,UPDATE NOT FOR REPLICATION
AS   
BEGIN    
  SET NOCOUNT ON;  

  UPDATE p SET  
         SHAPE = CASE WHEN i.SHAPE IS NOT NULL   
        THEN p.SHAPE ELSE Geography::STPointFromText('POINT('  
          + CAST(p.LON AS VARCHAR(20)) + ' '   
          + CAST(p.LAT AS VARCHAR(20)) + ')', 4269) END,  
      LON = CASE WHEN p.SHAPE IS NULL THEN p.LON ELSE p.SHAPE.Long END,  
      LAT = CASE WHEN p.SHAPE IS NULL THEN p.LAT ELSE p.SHAPE.Lat END,
  DMSLAT = cast(convert(int,(floor(ABS(p.SHAPE.Lat))))as varchar) +NCHAR(176)+' '+ cast( convert(int,p.SHAPE.Lat*60) % 60 as varchar) + ''' '+
 cast( convert(decimal(17,3),p.SHAPE.Lat*3600) % 60 as varchar) + '"' ,
  DMSLON = '-'+cast(convert(int,(floor(ABS(p.SHAPE.Long))))as varchar) +NCHAR(176)+' '+ cast( convert(int,ABS(p.SHAPE.Long)*60) % 60 as varchar) + ''' '+
 cast( convert(decimal(17,3),ABS(p.SHAPE.Long)*3600) % 60 as varchar) + '"'
  FROM  YAY
     AS p 
;
END

For Degrees Decimal-minutes you could use:

DDMLAT = cast(convert(int,(floor(ABS(p.SHAPE.Lat))))as varchar) +NCHAR(176)+' '+cast( floor(convert(decimal(17,5),p.SHAPE.Lat*60) % 60) as varchar) + '.'+cast( parsename(convert(decimal(17,7),p.SHAPE.Lat*60) % 60,1) as varchar) + ''' ',
DDMLON =   '-'+cast(convert(int,(floor(ABS(p.SHAPE.Long))))as varchar) +NCHAR(176)+' '+cast( floor(convert(decimal(17,5),abs(p.SHAPE.Long*60)) % 60) as varchar) + '.'+cast( parsename(convert(decimal(17,7),p.SHAPE.Long*60) % 60,1) as varchar) + ''' ',