# Use MS SQL Server to convert MGRS to Lat Long

Below is the SQL for converting Military Grid Reference System (MGRS) to decimal Latitude and Longitude. It is very accurate, but not perfect. If you can make it work 100%, it is yours, but PLEASE share the answer. We used various spreadsheets from the internet to help us get this far and thought we had it solved. It works perfectly for many MGRS coordinates, but some not.

The gauntlet is thrown and we have taken it a long way. I hope one of you can see our error and get it working.

Currently, we are using an open source dll for the conversion, but I really want to get it into SQL Server.

``````ALTER FUNCTION [dbo]. [getMGRSLat]
(
@latzone decimal(25,16),
script Date: 04/30/2012
@longzone decimal(25,16), @northing decimal(25,16), @easting decimal(25,16), @diRight decimal(25,16), @diLeft decimal(25,16)
)
RETURNS decimal (38,16)
AS
BEGIN

declare @c17 decimal(38,16), @c29 decimal(38,16), @c26 decimal (38,16) declare @latzone decimal(25,16), @longzone decimal(25,16), @northing decimal(25,16), @easting decimal(25,16), @diRight decimal(25,16), @diLeft decimal(25,16)

SET @latzone = 24
set @longzone = 42
set @northing = 56275
set @easting = 01445
set @diLeft = 19
set @diRight = 1

set @c29 =
(61+90.0*0.242541767+298.0*0.00542396+45.0*0.242541767*0.242541767-252.0*0.006739497
-3.0*0.00542396*0.00542396)*(power(-0.044123322,6.0)/720.0)
declare @c12 decimal(38,38)
set @c12 =
(l-power((1-0.081819191*0.081819191),(1.0/2.0)))/(1+power((1-0.081819191*0.081819191
), (1.0/2.0)))
declare @cl0 decimal(38,16), @cll decimal(38,16), @c16 decimal(38,16), @c15 decimal(38,38), @c14 decimal(38,16), @c13 decimal(38,16) declare @G20 int, @g25 int SET @G20 = (2000000*cast((@latzone*40000000/360)/2000000 as integer))+((@diRight-((case when 1 + @longzone % 2 = 1 then 6 else 1 end)))*100000) set @g25 CASE WHEN @G20 > 0 then @G20 else 10000000 + @G20 end set @cl0 (@g25+@northing)/0.9996 set @cll =
@c10/(6378137*(1.0-((power(0.081819191,2.0))/4.0)-((3.0*(power(0.081819191,4.0)))/64
.0)-((5.0*(power(0.081819191,6)))/256.0)))
set @c16 =
(1097.0*power(cast((1-power((1-0.081819191*0.081819191),(1.0/2.0)))/(1+power((1-0.08
1819191*0.081819191),(1.0/2.0))) as decimal(38,38)),4.0))/512.0 set @c15 = (151. 0*power(cast((1-power((1-0. 081819191*0. 081819191) , (1.0/2.0)))/(1+power((1-0.081
819191*0.081819191),(1.0/2.0))) as decimal(38,38)),3.0))/96.0 set @c14 = ((21.0*power(@c12,2))/16.0)-((55.0*power(@c12,4.0))/32.0)
set @c13 = 3.0*@c12/2-((27*(power(@c12,3.0)))/32.0)
set @c17 =
@c11+@c13*SIN(2.0*@c11)+@c14*SIN(4.0*@c11)+@c15*SIN(6.0*@c11)+@c16*SIN(8.0*@c11)
set @c26 =
(6378137.0/(power((1-power((0.081819191*SIN(@c17)),2.0)),(1.0/2.0))))*TAN(@c17)/6347
877 . 706
declare @c24 decimal(38,16), @c27 decimal(38,16), @c28 decimal(38,16), @c22
decimal(25,18)
DECLARE @G5 INT
set @c22 =
6378137.0/(power((1-power((0.081819191*SIN(@c17)),2.0)),(1.0/2.0)))
SET @G5 = 1 + @longzone % 3
set @c24 = (500000-(100000.0*(@diLeft-(CASE WHEN @G5 = 1 THEN 16 WHEN @G5
2 THEN 0 ELSE 8 END))+@easting))/(@c22*0.9996) set @c27 = (@c24*@c24/2.0) declare @c20 decimal(38,16), @c21 decimal(38,16) set @c20 = 0.006739497*power(cos(@c17),2.0) SET @c21 = power(TAN(@c17),2.0) set @c28 =
(5+3.0*@c21+10.0*@c20-4.0*@c20*@c20-9.0*0.006739497)*(power(@c24,4.0)/24.0)
select @c29, @c12,@c22
select @G20, @g25
select @c10, @c11 ,@c16 , @c15, @c14, @c13 select @g5, @c24, @c20, @c21 select @c12, @c10, @G20, @g25

return 180*(@c17-@c26*(@c27+@c28+@c29))/PI()

END

ALTER FUNCTION [dbo] . [getMGRSLong]
(
@latzone decimal(25,16),
script Date: 04/30/2012
@longzone decimal(25,16), @northing decimal(25,16), @easting decimal(25,16), @diRight decimal(25,16), @diLeft decimal(25,16)
)
RETURNS decimal(38,16)
AS
BEGIN

declare @c17 decimal(38,16), @c29 decimal(38,16), @c26 decimal (38,16) declare @latzone decimal(25,16), @longzone decimal(25,16), @northing decimal(25,16), @easting decimal(25,16), @diRight decimal(25,16), @diLeft decimal(25,16)

SET @latzone = 32
set @longzone = 42
set @northing = 56275
set @easting = 01445
set @diLeft = 19
set @diRight = 1

declare @c12 decimal(38,38), @c22 decimal(38,16) set @c12 = (1-power((1-0.081819191*0.081819191),(1.0/2.0)))/(1+power((1-0. 081819191*0. 081819191 ), (1.0/2.0)))

declare @g2 decimal(38,16), @h12 decimal(38,16), @k10 decimal(38,38) declare @c10 decimal(38,16), @c11 decimal(38,16), @c16 decimal (38,16), @c15 decimal(38,38), @c14 decimal(38,16), @c13 decimal(38,16) declare @G20 int, @g25 int SET @G20 = (2000000.0*cast((@latzone*40000000.0/360.0)/2000000.0 as integer))+((@diRight-((case when 1 + @longzone % 2 = 1 then 6 else 1 end)))*100000) set @g25 = CASE WHEN @G20 > 0 then @G20 else 10000000 + @G20 end set @c10 = (@g25+@northing)/0.9996 set @c11 =
@c10/(6378137.0*(1-((power(0.081819191,2.0))/4.0)-((3.0*(power(0.081819191,4.0)))/64
.0)-((5.0*(power(0.081819191,6.0)))/256.0)))
set @c16 =
(1097.0*power(cast((1-power((1-0.081819191*0.081819191),(1.0/2.0)))/(1+power((1-0.08
1819191*0.081819191),(1.0/2.0))) as decimal(38,38)),4.0))/512.0 set @c15 =
(151.0*power(cast((1-power((1-0.081819191*0.081819191),(1.0/2.0)))/(1+power((1-0.081
819191*0.081819191),(1.0/2.0))) as decimal(38,38)),3.0))/96.0 set @c14 = ((21.0*power(@c12,2.0))/16.0)-((55.0*power(@c12,4.0))/32.0)
set @c13 3.0*@c12/2.0-((27.0*(power(@c12,3.0)))/32.0)
set @c17
@c11+@c13*SIN(2.0*@c11)+@c14*SIN(4.0*@c11)+@c15*SIN(6.0*@c11)+@c16*SIN(8.0*@c11)

set @c22 = 6378137.0/power((1-power((0.081819191*SIN(@c17)),2.0)),(1.0/2.0))
declare @c20 decimal(38,16), @c21 decimal (38,16) set @c20 = 0.006739497*power(COS(@c17),2.0) SET @c21 = power(TAN(@c17),2.0) declare @g5 int, @c31 decimal(38,16),@c32 decimal(38,16),@c33 decimal(38,38), @c24 decimal (38,16) SET @G5 = 1 + @longzone % 3 set @c24 = (500000-(100000*(@diLeft-(CASE WHEN @G5 = 1 THEN 16 WHEN @G5 2 THEN 0 ELSE 8 END))+@easting))/(@c22*0.9996) set @c31 = @c24 set @c32 = (1+2*@c21+@c20)*power(@c24,3.0)/6.0
set @c33 =
(5.0-2.0*@c20+28*@c21-3.0*power(@c20,2.0)+8.0*0.006739497+24.0*power(@c21,2.0))*powe
r(@c24,5.0)/120.0
set @k10 = (@c31-@c32+@c33)/cos(@c17)
set @g2 = -183+6*@longzone
set @h12 = @k10*180.0/PI()

return @g2 - @h12
END
``````
-
Check out these two – Mike T May 3 '12 at 21:43