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) + ''' ', |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.