Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
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... –  simplexio Apr 5 '13 at 16:36
    
@simplexio Thanks, any luck with the T-SQL conversion? –  Kirk Kuykendall Apr 5 '13 at 17:30
    
How accurate do you want your converted coordinates to be? Or does accuracy matter all that much? –  Mintx Apr 5 '13 at 22:48
    
@Mintx I'd like to reproduce the same results that I currently getting using Arcmap. –  Kirk Kuykendall Apr 6 '13 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 –  simplexio Apr 10 '13 at 10:40

2 Answers 2

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

SELECT  FromX, 
        FromY, 
        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
FROM TABLENAME

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)))

SELECT 

((((-@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

FROM TABLENAME

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.

share|improve this answer
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. –  Kirk Kuykendall Apr 15 '13 at 16:20
    
Im going to print that gif, and frame it in the wall in front of my desk. –  nickves Apr 16 '13 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 '13 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 '13 at 22:09

This is a link to a similar question:

http://sqlspatialtools.codeplex.com/discussions/286893

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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