I have a table with latitude longitude (NAD27) columns in it. I compute two other columns, X and Y, representing Web Mercator (WGS84) location.

Currently I'm using a Arcmap to do this, by applying the recommended geotransformation for the study area - the 3 parameter (geocentric) geotransformation - to go from NAD27 to WGS84.

I would like to do this entirely within Sql Server 2012. From what I can tell, Sql Server does not support datum transformations out of the box. Does anyone know of a Sql library that supports this geotransformation? I would like to simply use the same coefficients in Sql that I'm currently using in Arcmap.

I also need to project from WGS84 lat/long into web mercator. I see this formula implemented in javascript, but if someone has a Sql stored procedure that does this, it would be great.

  • To my knowledge there is no working OO solution at moment for datum transformations. Easiest way to build it in database would be use sharpmap.codeplex.com lib- Or take existing code and convert it to T-SQL which i tried... Apr 5, 2013 at 16:36
  • @simplexio Thanks, any luck with the T-SQL conversion? Apr 5, 2013 at 17:30
  • How accurate do you want your converted coordinates to be? Or does accuracy matter all that much?
    – Mintx
    Apr 5, 2013 at 22:48
  • @Mintx I'd like to reproduce the same results that I currently getting using Arcmap. Apr 6, 2013 at 3:25
  • 1
    Offcourse. If you can change db to PostGIS, it has re-tranformation support. MS SQL server might be good db and has good support, but i looses to postgresq when we are talking pre made tools Apr 10, 2013 at 10:40

2 Answers 2


Regarding the javascript to SQL, this is probably how you would handle that:

        CASE WHEN FromX > 180 THEN NULL ELSE FromX * 0.017453292519943295 * 6378137.0 END AS mercatorX_lon2,
        CASE WHEN FromY > 90 THEN NULL ELSE 3189068.5 * LOG((1.0 + SIN(FromY * 0.017453292519943295)) / (1.0 - SIN(FromY * 0.017453292519943295))) END AS mercatorY_lat2

I think the following will answer your first question. It will require quite a bit of error checking. To assist, you can find the original equation here: http://www.colorado.edu/geography/gcraft/notes/datum/gif/molodens.gif

--fromTheta :column --radians
--fromLamda :column --radians
--fromH     :column --meters

DECLARE @fromA float = 6378206.4        --radius of earth, meters
DECLARE @fromF float =1.0/294.9786982   --Flattening
DECLARE @toA float =6378137.0           --radius of earth, meters
DECLARE @toF float = 1.0/298.257223563  --Flattening
DECLARE @dA float = @toA - @fromA       --change in equatorial radius
DECLARE @dX float = -8.0                --change in X, meters
DECLARE @dY float = 160.0               --change in Y, meters
DECLARE @dZ float = 176.0               --change in Z, meters
DECLARE @dF float = @toF-@fromF         --change in flattening
DECLARE @fromES float = 2.0*@fromF - @fromF*@fromF --first eccentricity squared
DECLARE @bda float = 1.0-@fromF         --polar radius divided by equatorial radius

--RM = (@fromA*(1-@fromES)/POWER(1-@fromES*sin(fromTheta)*sin(fromTheta), 1.5))

--RN = (@fromA/SQRT(1.00-@fromES*sin(fromTheta)*sin(fromTheta)))


((((-@dX*sin(fromTheta)*cos(fromLamda)-@dY*sin(fromTheta)*sin(fromLamda))+@dZ*cos(fromTheta))+@dA*(@fromA/SQRT(1.00-@fromES*sin(fromTheta)*sin(fromTheta)))*@fromES*sin(fromTheta)*cos(fromTheta)/@fromA)+@df*((@fromA*(1-@fromES)/POWER(1-@fromES*sin(fromTheta)*sin(fromTheta), 1.5))/@bda+(@fromA/SQRT(1.00-@fromES*sin(fromTheta)*sin(fromTheta)))*@bda)*sin(fromTheta)*cos(fromTheta))/((@fromA*(1-@fromES)/POWER(1-@fromES*sin(fromTheta)*sin(fromTheta), 1.5)) + fromH) AS deltaTheta,
(-@dX*sin(fromLamda)+@dY*cos(fromLamda))/((((@fromA/SQRT(1.00-@fromES*sin(fromTheta)*sin(fromTheta))) +fromH) * cos(fromTheta)) AS deltaLamda,
@dX*cos(fromTheta)*cos(fromLamda)+@dY*cos(fromTheta)*sin(fromLamda)+@dZ*sin(fromTheta)-@da*@fromA/(@fromA/SQRT(1.00-@fromES*sin(fromTheta)*sin(fromTheta)))+@dF*@bda*(@fromA/SQRT(1.00-@fromES*sin(fromTheta)*sin(fromTheta)))*sin(fromTheta)*sin(fromTheta) AS deltaH


Edit: a couple variables that should have been column names, and a missing comma and parenthesis.

Edit: one more parenthesis.

I've tested this formula and it works using random points against ArcGISs transform. Remember that your units may be in feet/degrees. Also remember these results are deltas, so you'll have to add them against your values to obtain your final results.

  • 1
    Thanks, I think the XYZ deltas need to be applied after converting from lat,long into XYZ space where XY and Z axis origin is at the center of the earth. Apr 15, 2013 at 16:20
  • Im going to print that gif, and frame it in the wall in front of my desk.
    – nickves
    Apr 16, 2013 at 20:28
  • @KirkKuykendall This method is the abridged Molodensky, where the deltas you get back are actually in arc-seconds and can be applied to your initial lat/longs to get the translation to your target datum. I don't know your AOI, but geocentric is usually the least accurate (but easiest!) way to get from NAD27->WGS84.
    – Mintx
    Apr 16, 2013 at 21:57
  • Also note ike's @dX @dY @dZ values which may be different depending on which NAD_1927_To_WGS_1984 geocentric method you've chosen.
    – Mintx
    Apr 16, 2013 at 22:09

This is a link to a similar question:


I think that the first answer can be useful to understand what you can and what you can't do in SQL Server and to know some methods to resolve your problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.