2

Need to create a SQL view of calculated Lat/Lon values from an existing SQL view of County Coords.

Loaded up SQL Server Spatial Tools and registerd CLR types. Lambert generated a resource error.

-- Project point and linestring using Lambert Conformal Conic projection

declare @lambert Projection

set @lambert = Projection::LambertConformalConic(0, 0, 0, 0)

select @lambert.Project('POINT (45 30)').ToString()

select @lambert.Unproject(@lambert.Project('LINESTRING (10 0, 10 10)')).ToString()

select @lambert.ToString()

--

XY going in=(521387.001,204215.954) Need LatLon coming out.

Any ideas welcome.

0

Here is SQL function , depends on version of the Lambert :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[LambertToGPS] (@Xlambert FLOAT , @YLambert FLOAT , @LambertVersion INT )


RETURNS 
@Coordinates TABLE 
    (
        Longitude FLOAT,
        Latitude FLOAT
    )
AS

BEGIN
/*
' |---------------------------------------------------------------------------------------------------------------|
' | Const | 1 'Lambert I | 2 'Lambert II | 3 'Lambert III | 4 'Lambert IV | 5 'Lambert II Etendue | 6 'Lambert 93 |
' |-------|--------------|---------------|----------------|---------------|-----------------------|---------------|
' |    n  | 0.7604059656 |  0.7289686274 |   0.6959127966 | 0.6712679322  |    0.7289686274       |  0.7256077650 |
' |-------|--------------|---------------|----------------|---------------|-----------------------|---------------|
' |    c  | 11603796.98  |  11745793.39  |   11947992.52  | 12136281.99   |    11745793.39        |  11754255.426 |
' |-------|--------------|---------------|----------------|---------------|-----------------------|---------------|
' |    Xs |   600000.0   |    600000.0   |   600000.0     |      234.358  |    600000.0           |     700000.0  |
' |-------|--------------|---------------|----------------|---------------|-----------------------|---------------|
' |    Ys | 5657616.674  |  6199695.768  |   6791905.085  |  7239161.542  |    8199695.768        | 12655612.050  |
' |---------------------------------------------------------------------------------------------------------------|
*/




DECLARE  @a FLOAT, @f FLOAT, @b FLOAT,@e FLOAT, @he FLOAT,@n FLOAT,@c FLOAT,
    @Xs FLOAT , @Ys FloAT, @LambdaC FLOAT,@Lambda FLOAT, @Gamma FLOAT ,@Phi FLOAT,@NN FLOAT, @NHeCosPhi FLOAT,@X FLOAT, 
    @Y FLOAT, @Z FLOAT,@FX FLOAT, @FY FLOAT ,@FZ FLOAT,@D FLOAT,@Rx FLOAT, @Ry FLOAT , @Rz FLOAT, @Tx FLOAT, @Ty FLOAT,
    @Tz FLOAT,@Dp1 FLOAT,@R2 FLOAT, @R3 FLOAT , @ae2 FLOAT ,@Phi0 FLOAT, @Phi1 FLOAT, @R FLOAT,@L FLOAT,
    @ExpLi FLOAT,@Phiim1 FLOAT,@ESinP FLOAT,@Miaou FLOAT,@Phiip1 FLOAT


SET @a = 6378249.2     
SET @f = 1/293.466021
SET @b = @a*(1-@f)
SET @e = SQRT((@a*@a - @b*@b)/(@a*@a))

SET @he = 0.0

SET @n= CASE 
            WHEN @LambertVersion = 1 THEN 0.7604059656
            WHEN @LambertVersion = 2 THEN 0.7289686274
            WHEN @LambertVersion = 3 THEN 0.6959127966
            WHEN @LambertVersion = 4 THEN 0.6712679322
            WHEN @LambertVersion = 5 THEN 0.7289686274   
            ELSE 0.7256077650
        END

SET @c  = CASE 
            WHEN @LambertVersion = 1 THEN 11603796.98
            WHEN @LambertVersion = 2 THEN 11745793.39
            WHEN @LambertVersion = 3 THEN 11947992.52 
            WHEN @LambertVersion = 4 THEN 12136281.99
            WHEN @LambertVersion = 5 THEN 11745793.39    
            ELSE 11754255.426
        END

SET @Xs = CASE 
            WHEN @LambertVersion = 1 THEN 600000.0
            WHEN @LambertVersion = 2 THEN 600000.0
            WHEN @LambertVersion = 3 THEN 600000.0
            WHEN @LambertVersion = 4 THEN 234.358
            WHEN @LambertVersion = 5 THEN 600000.0   
            ELSE 700000.0
        END

SET @Ys = CASE 
            WHEN @LambertVersion = 1 THEN 5657616.674
            WHEN @LambertVersion = 2 THEN 6199695.768
            WHEN @LambertVersion = 3 THEN 6791905.085
            WHEN @LambertVersion = 4 THEN 7239161.542
            WHEN @LambertVersion = 5 THEN 8199695.768
            ELSE 12655612.050
        END

SET @LambdaC = 2.337229167 * PI()/180.00
SET @Gamma = ATAN ((@XLambert-@Xs)/(@Ys-@YLambert))
SET @Lambda = @LambdaC + @Gamma /@n

SET @R = SQRT((POWER (@XLambert-@Xs,2)  + POWER (@YLambert-@Ys,2)))
SET @L = (-1.00/@n)*LOG(ABS(@R/@c))

SET @ExpLi = EXP(@L)
SET @Phiim1 = 2.00*ATAN(@ExpLi)-PI()/2.00
SET @ESinP = @e * SIN(@Phiim1)
SET @Miaou = (1.00+@ESinP)/(1.00-@ESinP)
SET @Miaou = POWER (@Miaou, @e/2)
SET @Phi = 2.00*ATAN(@Miaou*@ExpLi)-PI()/2.00

 WHILE(ABS(@Phi-@Phiim1)>=0.00000000001)
 BEGIN
    SET @ESinP = @e * SIN(@Phi)
    SET @Miaou = (1.00+@ESinP)/(1.00-@ESinP)
    SET @Miaou = POWER(@Miaou, @e/2.00)
    SET @Phiip1 = 2.00*ATAN((@Miaou)*@ExpLi)-PI()/2.00
    SET @Phiim1 = @Phi
    SET @Phi = @Phiip1
 END


SET @NN= @a/SQRT(1.00-POWER(@e*SIN(@Phi),2.00))

SET @NHeCosPhi = (@NN+@he)*COS(@Phi)
SET @X = @NHeCosPhi*COS(@Lambda)
SET @Y = @NHeCosPhi*SIN(@Lambda)
SET @Z = (@NN*(1.00-@e*@e)+@he)*SIN(@Phi)


SET @D = 0.00
SET @Rx = 0.00
SET @Ry= 0.00
SET @Rz = 0.00
SET @Tx = -168.00
SET @Ty = -060.00
SET @Tz =  320.00


SET @Dp1 = 1.00 + @D
SET @FX = @Tx+@X*@Dp1+@Z*@Ry-@Y*@Rz
SET @FY =@Ty+@Y*@Dp1+@X*@Rz-@Z*@Rx
SET @FZ =@Tz+@Z*@Dp1+@Y*@Rx-@X*@Ry

SET @a = 6378137.0     
SET @f = 1.00/298.257223563
SET @b = @a*(1.00-@f)
SET @e = SQRT((@a*@a - @b*@b)/(@a*@a))


SET @Lambda = ATAN(@FY/@FX)
SET @R2 = SQRT((@FX*@FX)+(@FY*@FY))
SET @R3 = SQRT((@R2*@R2)+(@FZ*@FZ))
SET @ae2 = @a*@e*@e

SET @Phi0 = ATAN(@FZ/(@R2*(1.00-@ae2/@R3)))
SET @Phi1 = ATAN((@FZ/@R2)*1.00/(1.00-@ae2*COS(@Phi0)/(@R2*SQRT(1.00-(@e*SIN(@Phi0))*(@e*SIN(@Phi0))))))


WHILE(ABS(@Phi1-@Phi0)>0.00000000001)
    BEGIN
         SET @Phi1 = ATAN((@FZ/@R2)*1/(1-@ae2*COS(@Phi0)/(@R2*SQRT(1-(@e*SIN(@Phi0))*(@e*SIN(@Phi0))))))
         SET @Phi0 = @Phi1
    END
SET @Phi = @Phi1

INSERT INTO @Coordinates (Longitude,Latitude)
SELECT @Lambda * 180.00 / PI()  AS Longitude , @Phi * 180.00 / PI() AS Latitude


RETURN

END 
  • 1
    Welcome to GIS.SE. Please remember, that good code (and good answers) always contain some comments in order to help other users - and yourself 3 months from now - understand it easier. – Erik Jan 11 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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