StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL function to convert UK OS coordinates from easting/northing to longitude and latitude
text
Body
copied!<p>Please can someone post a SQL function to convert easting/northing to longitude/latitude. I know it's incredibly complicated but I haven't found anyone who has documented it in T-SQL. </p> <p>This <a href="http://www.movable-type.co.uk/scripts/latlong-gridref.html" rel="nofollow">javascript code</a> works but I'm having trouble converting it to SQL. </p> <p>I have 16,000 coordinates and need them all converted to lat/long.</p> <p>This is what I have so far but it's not getting past the while loop.</p> <pre><code>DECLARE @east real = 482353, @north real = 213371 DECLARE @a real = 6377563.396, @b real = 6356256.910, @F0 real = 0.9996012717, @lat0 real = 49*PI()/180, @lon0 real = -2*PI()/180 DECLARE @N0 real = -100000, @E0 real = 400000, @e2 real = 1 - (@b*@b)/(@a*@a), @n real = (@a-@b)/(@a+@b) DECLARE @n2 real = @n*@n, @n3 real = @n*@n*@n DECLARE @lat real = @lat0, @M real = 0 WHILE (@north-@N0-@M >= 0.00001) BEGIN SET @lat = ((@north-@N0-@M)/(@a*@F0)) + @lat DECLARE @Ma real = (1 + @n + (5/4)*@n2 + (5/4)*@n3) * (@lat-@lat0), @Mb real = (3*@n + 3*@n*@n + (21/8)*@n3) * SIN(@lat-@lat0) * COS(@lat+@lat0), @Mc real = ((15/8)*@n2 + (15/8)*@n3) * SIN(2*(@lat-@lat0)) * COS(2*(@lat+@lat0)), @Md real = (35/24)*@n3 * SIN(3*(@lat-@lat0)) * COS(3*(@lat+@lat0)) SET @M = @b * @F0 * (@Ma - @Mb + @Mc - @Md) END DECLARE @cosLat real = COS(@lat), @sinLat real = SIN(@lat) DECLARE @nu real = @a*@F0/sqrt(1-@e2*@sinLat*@sinLat) DECLARE @rho real = @a*@F0*(1-@e2)/POWER(1-@e2*@sinLat*@sinLat, 1.5) DECLARE @eta2 real = @nu/@rho-1 DECLARE @tanLat real = tan(@lat) DECLARE @tan2lat real = @tanLat*@tanLat DECLARE @tan4lat real = @tan2lat*@tan2lat DECLARE @tan6lat real = @tan4lat*@tan2lat DECLARE @secLat real = 1/@cosLat DECLARE @nu3 real = @nu*@nu*@nu DECLARE @nu5 real = @nu3*@nu*@nu DECLARE @nu7 real = @nu5*@nu*@nu DECLARE @VII real = @tanLat/(2*@rho*@nu) DECLARE @VIII real = @tanLat/(24*@rho*@nu3)*(5+3*@tan2lat+@eta2-9*@tan2lat*@eta2) DECLARE @IX real = @tanLat/(720*@rho*@nu5)*(61+90*@tan2lat+45*@tan4lat) DECLARE @X real = @secLat/@nu DECLARE @XI real = @secLat/(6*@nu3)*(@nu/@rho+2*@tan2lat) DECLARE @XII real = @secLat/(120*@nu5)*(5+28*@tan2lat+24*@tan4lat) DECLARE @XIIA real = @secLat/(5040*@nu7)*(61+662*@tan2lat+1320*@tan4lat+720*@tan6lat) DECLARE @dE real = (@east-@E0) DECLARE @dE2 real = @dE*@dE DECLARE @dE3 real = @dE2*@dE DECLARE @dE4 real = @dE2*@dE2, @dE5 real = @dE3*@dE2 DECLARE @dE6 real = @dE4*@dE2, @dE7 real = @dE5*@dE2 SET @lat = @lat - @VII*@dE2 + @VIII*@dE4 - @IX*@dE6 DECLARE @lon real = @lon0 + @X*@dE - @XI*@dE3 + @XII*@dE5 - @XIIA*@dE7 SELECT @lon, @lat </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload