topic Re: Formula For State Plane to Lat/Lon Conversion in Coordinate Reference Systems Questions

Formula For State Plane to Lat/Lon Conversion

GeorgePorto — Thu, 07 Aug 2014 17:20:58 GMT

Does anyone know where I can find the equation to put into Excel that will convert CT State Plane coordinates to Lat/Lon? I know ways to programmatically do this using ArcGIS but have a scenario/workflow where we may need to do this from within Excel. Thanks!

Re: Formula For State Plane to Lat/Lon Conversion

ScottDean — Thu, 07 Aug 2014 18:13:00 GMT

I haven't personally used it but there is an add-in for Excel that does it.

http://www.dimensionengine.com/excel/geogtools/

Re: Formula For State Plane to Lat/Lon Conversion

GeorgePorto — Thu, 07 Aug 2014 18:16:56 GMT

Thanks Scott. I did see that but was hesitant in installing as I thought that there had to be some formula that Excel could execute to do the conversion. I may end up trying this extension if all else fails.

Thanks again.

Re: Formula For State Plane to Lat/Lon Conversion

MelitaKennedy — Thu, 07 Aug 2014 21:02:20 GMT

Equations are available in John P. Snyder's Map Projections: A Working Manual, which is available as a PDF here. Other places include OGP Guidance Note 7-2 at http://www.epsg.org.

Melita

Disclosure: I'm on the subcommittee that maintains the EPSG Geodetic Parameter Registry and this guidance note.

Re: Formula For State Plane to Lat/Lon Conversion

GeorgePorto — Fri, 08 Aug 2014 12:43:15 GMT

Thank you for your response Melita. I will be looking into your references.

Re: Formula For State Plane to Lat/Lon Conversion

mpboyle — Mon, 13 Feb 2017 22:50:45 GMT

Melita Kennedy‌,

I stumbled across this thread when searching for a way to convert state plane coordinates to lat,lon values on the fly in Sql Server. I have found the guidance note 7.2 document you noted and am overwhelmed by the math

Before I dive too deep into trying to understand it, I first wanted to check and make sure that we can/should be able to convert our data to lat,lon values using the formulas provided.

Below is a screenshot of the coordinate system properties most of our data is within (Iowa State Plane NAD 1983 2011, wkid: 6463).

If I had an x,y coordinate pair (below), can we use the formulas in note 7.2 to conver to lat,lon?...if yes, what formula exactly would we need to use?

Any help you can provide is much appreciated!

x: 5402473.342

y: 3421155.895

Re: Formula For State Plane to Lat/Lon Conversion

MelitaKennedy — Tue, 14 Feb 2017 18:05:05 GMT

Yes, you should be able to use the 7-2 Guidance note, or Snyder's Map Projections: A Working Manual.

Parts that might not be so apparent (more if using Snyder than the guidance note) is that you subtract off the false easting and northing values first, then convert the new values to meters. This is because the ellipsoid's semimajor axis (in your case, GRS80) is usually defined in meters. Angular parameter values also need converted to radians.

Lambert conformal conic isn't one of the really hard projections to implement, but it's not one of the easy ones either!

Do you not have the Spatial tools? It looks like it's already implemented there...

Melita

Re: Formula For State Plane to Lat/Lon Conversion

mpboyle — Tue, 14 Feb 2017 19:42:35 GMT

Melita Kennedy‌,

I'm working through the Lambert Conic Conformal (2sp) example and I'm a little confused about how to find the 't' value since I'm not starting with a latitude...I'm trying to convert from State Plane to lat/lon. Can I use any pair of lat/lon values I want to do the forward calculations?

I'm also confused about the 'r' value. What's the difference between the 'r' and 'r_F' values?

Below is what I've got so far and feel pretty confident these values are correct based on the example provided in the guidance notes.

Any advice to finish this off would be appreciated!

Re: Formula For State Plane to Lat/Lon Conversion

MelitaKennedy — Tue, 14 Feb 2017 20:04:07 GMT

The F subscript represents the "false origin" aka the latitude of origin parameter. No subscript means you should use the current point's latitude, but that would only be needed on the forward case (from lat/lon to xy), not on the inverse case.

You might want to try repro-ing the example in the guidance note, as that will clearly show if the calculations are correct or not.

Re: Formula For State Plane to Lat/Lon Conversion

mpboyle — Thu, 16 Feb 2017 21:17:40 GMT

Melita Kennedy‌,

Ended up created 2 Sql functions to calculate longitude and latitude values given a pair of State Plane x,y coordinates. I then use these functions inside a Sql trigger to auto-calculate lat/lon fields within my feature classes.

I appreciate the help, works great!

Should anyone want the actual code behind the functions, I can provide it, however it is based on the formulas found within the references above.

Re: Formula For State Plane to Lat/Lon Conversion

ThomasMarshall — Fri, 24 Feb 2017 20:52:59 GMT

I would very much like a copy of your SQL formulas. I once tried to do this using SQL, but due to the large numbers of formulas involved,and the number of steps, I found that rounding errors became significant, and my results were not as accurate as I needed.

If you have found a better way, I would be very interested.

Re: Formula For State Plane to Lat/Lon Conversion

ThomasMarshall — Fri, 24 Feb 2017 21:51:03 GMT

I'll give it a try. Thanks!

Re: Formula For State Plane to Lat/Lon Conversion

JohnHansen — Fri, 29 Sep 2017 22:41:44 GMT

This is awesome - thanks so much.Works well, and very fast.

Re: Formula For State Plane to Lat/Lon Conversion

MichaelMorgan4 — Fri, 18 May 2018 22:06:07 GMT

Very helpful, thanks a million. I have to keep all my geographically projected feature classes in SQL Server for easy retrieval with other applications, and the ESRI Python script I thought I had to use just for reprojection kept crashing and burning on me.

Re: Formula For State Plane to Lat/Lon Conversion

SeanO_Halloran — Thu, 20 Dec 2018 21:20:15 GMT

I'm using a state plane zone that does not have values for Standard Parallel 1 & 2. Do you use a value of 0 in these cases for the functions for calculating latitude and longitude?

Re: Formula For State Plane to Lat/Lon Conversion

MelitaKennedy — Thu, 20 Dec 2018 22:16:41 GMT

Hi Sean,

The posted code is for Lambert conformal conic. It sounds like your zone (which one?) is using either transverse Mercator or Hotine oblique Mercator. Either of those projections would need to be implemented.

Melita

Re: Formula For State Plane to Lat/Lon Conversion

SeanO_Halloran — Thu, 20 Dec 2018 22:45:24 GMT

Thanks Melita. I'm looking at Idaho East NAD 83 US foot zone 1100 which is Transverse Mercator.

Re: Formula For State Plane to Lat/Lon Conversion

mpboyle — Sun, 12 Dec 2021 10:51:28 GMT

Thomas Marshall‌

Since I can't attach through a comment, below are the Sql functions, and some notes to be aware of...

These functions calculate longitude and latitude for Lambert Conformal Conic 2-parallel projections (as this is what our production features are stored in (wkid: 6463 --- NAD 1983 (2011) Iowa State Plane North))

Note lines 59 through 76 --- you will need to provide the values for your coordinate system
- You can find these values within the XY Coordinate System properties of a feature class
- I do this by storing the values in a table and call them through a SELECT statement, that way the values are stored in one place (example below)

Longitude Calculation

USE [**DatabaseName**]
GO
/****** Object:  UserDefinedFunction [dbo].[get_longitude] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:     Your Name
-- Create date: Date
-- Description:     Returns longitude given x,y coordinates in Lambert Conformal Conic projection
-- =============================================
CREATE FUNCTION [dbo].[get_longitude] 
(
     -- Add the parameters for the function here
     @x numeric(24,12),  --state plane x coordinate
     @y numeric(24,12)   --state plane y coordinate
)
RETURNS numeric(24,12)
AS
BEGIN
     -- Declare the return variable here
   DECLARE
   @pi numeric(24,12),     --pi
   @fx numeric(24,12),     --false easting
   @fy numeric(24,12),     --false northing
   @cmD numeric(24,12),    --central meridian in degrees
   @cmR numeric(24,12),    --central meridian in radians
   @sp1D numeric(24,12),   --standard parallel 1 in degrees
   @sp1R numeric(24,12),   --standard parallel 1 in radians
   @sp2D numeric(24,12),   --standard parallel 2 in degrees
   @sp2R numeric(24,12),   --standard parallel 2 in radians
   @loD numeric(24,12),    --latitude of origin in degrees
   @loR numeric(24,12),    --latitude of origin in radians
   @smjrM numeric(24,12),  --semimajor axis in meters
   @smjrF numeric(24,12),  --semimajor axis in feet
   @smnrM numeric(24,12),  --semiminor axis in meters
   @smnrF numeric(24,12),  --semiminor axis in feet
   @flat numeric(24,12),   --flattenging
   @iflat numeric(24,12),  --inverse flattening
   @e numeric(24,12),      --eccentricity
   @m1 numeric(24,12),     --calculation value
   @m2 numeric(24,12),     --calculation value
   @t1 numeric(24,12),     --calculation value
   @t2 numeric(24,12),     --calculation value
   @tf numeric(24,12),     --calculation value
   @n numeric(24,12),      --calculation value
   @F numeric(24,12),      --calculation value
   @rf numeric(24,12),     --calculation value
   @rz numeric(24,12),     --calculation value
   @tz numeric(24,12),     --calculation value
   @zz numeric(24,12),     --calculation value
   @lonR numeric(24,12),   --longitude in radians
   @lonD numeric(24,12)    --longitude in degrees

   -- Add the T-SQL statements to compute the return value here
   SET @pi = pi()
   -- false origin values
   SET @fx = /* Enter false easting value */
   SET @fy = /* Enter false northing value */
   -- latitude of origin values
   SET @loD = /* Enter latitude of origin value */
   SET @loR = @loD * @pi / 180
   -- central meridian values
   SET @cmD = /* Enter central meridian value */
   SET @cmR = @cmD * @pi / 180
   -- standard parallel values
   SET @sp1D = /* Enter standard parallel 1 value */
   SET @sp1R = @sp1D * @pi / 180
   SET @sp2D = /* Enter standard parallel 2 value */
   SET @sp2R = @sp2D * @pi / 180
   -- semimajor axis values
   SET @smjrM = /* Enter semimajor axis value in meters */
   SET @smjrF = @smjrM * 3.2808333
   -- semiminor axis values
   SET @smnrM = /* Enter semiminor axis value in meters */
   SET @smnrF = @smnrM * 3.2808333
   -- inverse flattening value
   SET @iflat = @smjrM / (@smjrM - @smnrM)
   -- flattenging value
   SET @flat = 1 / @iflat
   -- eccentricity value
   SET @e = SQRT(2 * @flat - SQUARE(@flat))
   -- m values
   SET @m1 = COS(@sp1R) / POWER((1 - SQUARE(@e) * SQUARE(SIN(@sp1R))), 0.5)
   SET @m2 = COS(@sp2R) / POWER((1 - SQUARE(@e) * SQUARE(SIN(@sp2R))), 0.5)
   -- t values
   SET @t1 = TAN((@pi / 4) - (@sp1R / 2)) / POWER((1 - (@e * SIN(@sp1R))) / (1 + (@e * SIN(@sp1R))), (@e / 2))
   SET @t2 = TAN((@pi / 4) - (@sp2R / 2)) / POWER((1 - (@e * SIN(@sp2R))) / (1 + (@e * SIN(@sp2R))), (@e / 2))
   SET @tf = TAN((@pi / 4) - (@loR / 2)) / POWER((1 - (@e * SIN(@loR))) / (1 + (@e * SIN(@loR))), (@e / 2))
   -- n value
   SET @n = (LOG(@m1) - LOG(@m2)) / (LOG(@t1) - LOG(@t2))
   -- f value
   SET @F = @m1 / (@n * POWER(@t1, @n))
   -- r value
   SET @rf = @smjrF * @F * POWER(@tf, @n)
   -- r' value
   SET @rz = POWER(POWER((@x - @fx), 2) + POWER((@rf - (@y - @fy)), 2), 0.5)
   -- t' value
   SET @tz = POWER((@rz / (@smjrF * @F)), (1 / @n))
   -- thetha' value
   SET @zz = ATAN((@x - @fx) / (@rf - (@y - @fy)))
   -- longitude value in radians
   SET @lonR = ((@zz / @n) + @cmR)
   -- longitude value in degrees
   SET @lonD = (@lonR * 180) / @pi

     -- Return the result of the function
     RETURN @lonD;

END
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Latitude Calculation

USE [**DatabaseName**]
GO
/****** Object:  UserDefinedFunction [dbo].[get_latitude] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:     Your Name
-- Create date: Date
-- Description:     Returns latitude given x,y coordinates in Lambert Conformal Conic projection
-- =============================================
CREATE FUNCTION [dbo].[get_latitude] 
(
     -- Add the parameters for the function here
     @x numeric(24,12),  --state plane x coordinate
     @y numeric(24,12)   --state plane y coordinate
)
RETURNS numeric(24,12)
AS
BEGIN
     -- Declare the return variable here
   DECLARE
   @pi numeric(24,12),     --pi
   @fx numeric(24,12),     --false easting
   @fy numeric(24,12),     --false northing
   @cmD numeric(24,12),    --central meridian in degrees
   @cmR numeric(24,12),    --central meridian in radians
   @sp1D numeric(24,12),   --standard parallel 1 in degrees
   @sp1R numeric(24,12),   --standard parallel 1 in radians
   @sp2D numeric(24,12),   --standard parallel 2 in degrees
   @sp2R numeric(24,12),   --standard parallel 2 in radians
   @loD numeric(24,12),    --latitude of origin in degrees
   @loR numeric(24,12),    --latitude of origin in radians
   @smjrM numeric(24,12),  --semimajor axis in meters
   @smjrF numeric(24,12),  --semimajor axis in feet
   @smnrM numeric(24,12),  --semiminor axis in meters
   @smnrF numeric(24,12),  --semiminor axis in feet
   @flat numeric(24,12),   --flattenging
   @iflat numeric(24,12),  --inverse flattening
   @e numeric(24,12),      --eccentricity
   @m1 numeric(24,12),     --calculation value
   @m2 numeric(24,12),     --calculation value
   @t1 numeric(24,12),     --calculation value
   @t2 numeric(24,12),     --calculation value
   @tf numeric(24,12),     --calculation value
   @n numeric(24,12),      --calculation value
   @F numeric(24,12),      --calculation value
   @rf numeric(24,12),     --calculation value
   @rz numeric(24,12),     --calculation value
   @tz numeric(24,12),     --calculation value
   @zz numeric(24,12),     --calculation value
   @latTR numeric(24,12),  --latitude trial in radians
   @lat1R numeric(24,12),  --latitude iteration 1 in radians
   @lat2R numeric(24,12),  --latitude iteration 2 in radians
   @lat3R numeric(24,12),  --latitude iteration 3 in radians
   @lat4R numeric(24,12),  --latitude iteration 4 in radians
   @latFR numeric(24,12),  --latitude final iteration in radians
   @latTD numeric(24,12),  --latitude trial in degrees
   @lat1D numeric(24,12),  --latitude iteration 1 in degrees
   @lat2D numeric(24,12),  --latitude iteration 2 in degrees
   @lat3D numeric(24,12),  --latitude iteration 3 in degrees
   @lat4D numeric(24,12),  --latitude iteration 4 in degrees
   @latFD numeric(24,12)   --latitude final iteration in degrees


   -- Add the T-SQL statements to compute the return value here
   SET @pi = pi()
   -- false origin values
   SET @fx = /* Enter false easting value */
   SET @fy = /* Enter false northing value */
   -- latitude of origin values
   SET @loD = /* Enter latitude of origin value */
   SET @loR = @loD * @pi / 180
   -- central meridian values
   SET @cmD = /* Enter central meridian value */
   SET @cmR = @cmD * @pi / 180
   -- standard parallel values
   SET @sp1D = /* Enter standard parallel 1 value */
   SET @sp1R = @sp1D * @pi / 180
   SET @sp2D = /* Enter standard parallel 2 value */
   SET @sp2R = @sp2D * @pi / 180
   -- semimajor axis values
   SET @smjrM = /* Enter semimajor axis value in meters */
   SET @smjrF = @smjrM * 3.2808333
   -- semiminor axis values
   SET @smnrM = /* Enter semiminor axis value in meters */
   SET @smnrF = @smnrM * 3.2808333
   -- inverse flattening value
   SET @iflat = @smjrM / (@smjrM - @smnrM)
   -- flattenging value
   SET @flat = 1 / @iflat
   -- eccentricity value
   SET @e = SQRT(2 * @flat - SQUARE(@flat))
   -- m values
   SET @m1 = COS(@sp1R) / POWER((1 - SQUARE(@e) * SQUARE(SIN(@sp1R))), 0.5)
   SET @m2 = COS(@sp2R) / POWER((1 - SQUARE(@e) * SQUARE(SIN(@sp2R))), 0.5)
   -- t values
   SET @t1 = TAN((@pi / 4) - (@sp1R / 2)) / POWER((1 - (@e * SIN(@sp1R))) / (1 + (@e * SIN(@sp1R))), (@e / 2))
   SET @t2 = TAN((@pi / 4) - (@sp2R / 2)) / POWER((1 - (@e * SIN(@sp2R))) / (1 + (@e * SIN(@sp2R))), (@e / 2))
   SET @tf = TAN((@pi / 4) - (@loR / 2)) / POWER((1 - (@e * SIN(@loR))) / (1 + (@e * SIN(@loR))), (@e / 2))
   -- n value
   SET @n = (LOG(@m1) - LOG(@m2)) / (LOG(@t1) - LOG(@t2))
   -- f value
   SET @F = @m1 / (@n * POWER(@t1, @n))
   -- r value
   SET @rf = @smjrF * @F * POWER(@tf, @n)
   -- r' value
   SET @rz = POWER(POWER((@x - @fx), 2) + POWER((@rf - (@y - @fy)), 2), 0.5)
   -- t' value
   SET @tz = POWER((@rz / (@smjrF * @F)), (1 / @n))
   -- thetha' value
   SET @zz = ATAN((@x - @fx) / (@rf - (@y - @fy)))
   -- latitude trial value in radians
   SET @latTR = (@pi / 2) - (2 * ATAN(@tz))
   -- latitude iteration 1 value in radians
   SET @lat1R = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@latTR))) / (1 + (@e * SIN(@latTR))), (@e / 2)))))
   -- latitude iteration 2 value in radians
   SET @lat2R = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@lat1R))) / (1 + (@e * SIN(@lat1R))), (@e / 2)))))
   -- latitude iteration 3 value in radians
   SET @lat3R = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@lat2R))) / (1 + (@e * SIN(@lat2R))), (@e / 2)))))
   -- latitude iteration 4 value in radians
   SET @lat4R = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@lat3R))) / (1 + (@e * SIN(@lat3R))), (@e / 2)))))
   -- latitude final iteration value in radians
   SET @latFR = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@lat4R))) / (1 + (@e * SIN(@lat4R))), (@e / 2)))))
   -- latitude trial value in degrees
   SET @latTD = (@latTR * 180) / @pi
   -- latitude iteration 1 value in degrees
   SET @lat1D = (@lat1R * 180) / @pi
   -- latitude iteration 2 value in degrees
   SET @lat2D = (@lat2R * 180) / @pi
   -- latitude iteration 3 value in degrees
   SET @lat3D = (@lat3R * 180) / @pi
   -- latitude iteration 4 value in degrees
   SET @lat4D = (@lat4R * 180) / @pi
   -- latitude final iteration value in degrees
   SET @latFD = (@latFR * 180) / @pi


     -- Return the latitude in degrees
     RETURN @latFD;

END
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Formula For State Plane to Lat/Lon Conversion

DrewDowling — Wed, 10 Mar 2021 17:09:36 GMT

It looks like the last forum migration messed up the formatting of your code. I've reformatted it to maybe help others. Hope you don't mind.

USE yourDB GO /****** Object: UserDefinedFunction [dbo].[get_longitude] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Your Name -- Create date: Date -- Description: Returns longitude given x,y coordinates in Lambert Conformal Conic projection -- =============================================C CREATE FUNCTION [dbo].[get_longitude] ( -- Add the parameters for the function here @x numeric(24,12), --state plane x coordinate @y numeric(24,12) --state plane y coordinate ) RETURNS numeric(24,12) AS BEGIN -- Declare the return variable here DECLARE @pi numeric(24,12), --pi @fx numeric(24,12), --false easting @fy numeric(24,12), --false northing @cmD numeric(24,12), --central meridian in degrees @cmR numeric(24,12), --central meridian in radians @sp1D numeric(24,12), --standard parallel 1 in degrees @sp1R numeric(24,12), --standard parallel 1 in radians @sp2D numeric(24,12), --standard parallel 2 in degrees @sp2R numeric(24,12), --standard parallel 2 in radians @loD numeric(24,12), --latitude of origin in degrees @loR numeric(24,12), --latitude of origin in radians @smjrM numeric(24,12), --semimajor axis in meters @smjrF numeric(24,12), --semimajor axis in feet @smnrM numeric(24,12), --semiminor axis in meters @smnrF numeric(24,12), --semiminor axis in feet @flat numeric(24,12), --flattenging @iflat numeric(24,12), --inverse flattening @e numeric(24,12), --eccentricity @m1 numeric(24,12), --calculation value @m2 numeric(24,12), --calculation value @t1 numeric(24,12), --calculation value @t2 numeric(24,12), --calculation value @TF numeric(24,12), --calculation value @n numeric(24,12), --calculation value @F numeric(24,12), --calculation value @RF numeric(24,12), --calculation value @RZ numeric(24,12), --calculation value @tz numeric(24,12), --calculation value @zz numeric(24,12), --calculation value @lonR numeric(24,12), --longitude in radians @lonD numeric(24,12) --longitude in degrees -- Add the T-SQL statements to compute the return value here SET @pi = pi() -- false origin values SET @fx = /* Enter false easting value */ SET @fy = /* Enter false northing value */ -- latitude of origin values SET @loD = /* Enter latitude of origin value */ SET @loR = @loD * @pi / 180 -- central meridian values SET @cmD = /* Enter central meridian value */ SET @cmR = @cmD * @pi / 180 -- standard parallel values SET @sp1D = /* Enter standard parallel 1 value */ SET @sp1R = @sp1D * @pi / 180 SET @sp2D = /* Enter standard parallel 2 value */ SET @sp2R = @sp2D * @pi / 180 -- semimajor axis values SET @smjrM = /* Enter semimajor axis value in meters */ SET @smjrF = @smjrM * 3.2808333 -- semiminor axis values SET @smnrM = /* Enter semiminor axis value in meters */ SET @smnrF = @smnrM * 3.2808333 -- inverse flattening value SET @iflat = @smjrM / (@smjrM - @smnrM) -- flattenging value SET @flat = 1 / @iflat -- eccentricity value SET @e = SQRT(2 * @flat - SQUARE(@flat)) -- m values SET @m1 = COS(@sp1R) / POWER((1 - SQUARE(@e) * SQUARE(SIN(@sp1R))), 0.5) SET @m2 = COS(@sp2R) / POWER((1 - SQUARE(@e) * SQUARE(SIN(@sp2R))), 0.5) -- t values SET @t1 = TAN((@pi / 4) - (@sp1R / 2)) / POWER((1 - (@e * SIN(@sp1R))) / (1 + (@e * SIN(@sp1R))), (@e / 2)) SET @t2 = TAN((@pi / 4) - (@sp2R / 2)) / POWER((1 - (@e * SIN(@sp2R))) / (1 + (@e * SIN(@sp2R))), (@e / 2)) SET @TF = TAN((@pi / 4) - (@loR / 2)) / POWER((1 - (@e * SIN(@loR))) / (1 + (@e * SIN(@loR))), (@e / 2)) -- n value SET @n = (LOG(@m1) - LOG(@m2)) / (LOG(@t1) - LOG(@t2)) -- f value SET @F = @m1 / (@n * POWER(@t1, @n)) -- r value SET @RF = @smjrF * @F * POWER(@tf, @n) -- r' value SET @RZ = POWER(POWER((@x - @fx), 2) + POWER((@rf - (@y - @fy)), 2), 0.5) -- t' value SET @tz = POWER((@rz / (@smjrF * @F)), (1 / @n)) -- thetha' value SET @zz = ATAN((@x - @fx) / (@rf - (@y - @fy))) -- longitude value in radians SET @lonR = ((@zz / @n) + @cmR) -- longitude value in degrees SET @lonD = (@lonR * 180) / @pi -- Return the result of the function RETURN @lonD; END

USE yourDB GO /****** Object: UserDefinedFunction [dbo].[get_latitude] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[get_latitude] ( -- Add the parameters for the function here @x numeric(24,12), --state plane x coordinate @y numeric(24,12) --state plane y coordinate ) RETURNS numeric(24,12) AS BEGIN -- Declare the return variable here DECLARE @pi numeric(24,12), --pi @fx numeric(24,12), --false easting @fy numeric(24,12), --false northing @cmD numeric(24,12), --central meridian in degrees @cmR numeric(24,12), --central meridian in radians @sp1D numeric(24,12), --standard parallel 1 in degrees @sp1R numeric(24,12), --standard parallel 1 in radians @sp2D numeric(24,12), --standard parallel 2 in degrees @sp2R numeric(24,12), --standard parallel 2 in radians @loD numeric(24,12), --latitude of origin in degrees @loR numeric(24,12), --latitude of origin in radians @smjrM numeric(24,12), --semimajor axis in meters @smjrF numeric(24,12), --semimajor axis in feet @smnrM numeric(24,12), --semiminor axis in meters @smnrF numeric(24,12), --semiminor axis in feet @flat numeric(24,12), --flattenging @iflat numeric(24,12), --inverse flattening @e numeric(24,12), --eccentricity @m1 numeric(24,12), --calculation value @m2 numeric(24,12), --calculation value @t1 numeric(24,12), --calculation value @t2 numeric(24,12), --calculation value @TF numeric(24,12), --calculation value @n numeric(24,12), --calculation value @F numeric(24,12), --calculation value @RF numeric(24,12), --calculation value @RZ numeric(24,12), --calculation value @tz numeric(24,12), --calculation value @zz numeric(24,12), --calculation value @latTR numeric(24,12), --latitude trial in radians @lat1R numeric(24,12), --latitude iteration 1 in radians @lat2R numeric(24,12), --latitude iteration 2 in radians @lat3R numeric(24,12), --latitude iteration 3 in radians @lat4R numeric(24,12), --latitude iteration 4 in radians @latFR numeric(24,12), --latitude final iteration in radians @latTD numeric(24,12), --latitude trial in degrees @lat1D numeric(24,12), --latitude iteration 1 in degrees @lat2D numeric(24,12), --latitude iteration 2 in degrees @lat3D numeric(24,12), --latitude iteration 3 in degrees @lat4D numeric(24,12), --latitude iteration 4 in degrees @latFD numeric(24,12) --latitude final iteration in degrees -- Add the T-SQL statements to compute the return value here SET @pi = pi() -- false origin values SET @fx = /* Enter false easting value */ SET @fy = /* Enter false northing value */ -- latitude of origin values SET @loD = /* Enter latitude of origin value */ SET @loR = @loD * @pi / 180 -- central meridian values SET @cmD = /* Enter central meridian value */ SET @cmR = @cmD * @pi / 180 -- standard parallel values SET @sp1D = /* Enter standard parallel 1 value */ SET @sp1R = @sp1D * @pi / 180 SET @sp2D = /* Enter standard parallel 2 value */ SET @sp2R = @sp2D * @pi / 180 -- semimajor axis values SET @smjrM = /* Enter semimajor axis value in meters */ SET @smjrF = @smjrM * 3.2808333 -- semiminor axis values SET @smnrM = /* Enter semiminor axis value in meters */ SET @smnrF = @smnrM * 3.2808333 -- inverse flattening value SET @iflat = @smjrM / (@smjrM - @smnrM) -- flattenging value SET @flat = 1 / @iflat -- eccentricity value SET @e = SQRT(2 * @flat - SQUARE(@flat)) -- m values SET @m1 = COS(@sp1R) / POWER((1 - SQUARE(@e) * SQUARE(SIN(@sp1R))), 0.5) SET @m2 = COS(@sp2R) / POWER((1 - SQUARE(@e) * SQUARE(SIN(@sp2R))), 0.5) -- t values SET @t1 = TAN((@pi / 4) - (@sp1R / 2)) / POWER((1 - (@e * SIN(@sp1R))) / (1 + (@e * SIN(@sp1R))), (@e / 2)) SET @t2 = TAN((@pi / 4) - (@sp2R / 2)) / POWER((1 - (@e * SIN(@sp2R))) / (1 + (@e * SIN(@sp2R))), (@e / 2)) SET @TF = TAN((@pi / 4) - (@loR / 2)) / POWER((1 - (@e * SIN(@loR))) / (1 + (@e * SIN(@loR))), (@e / 2)) -- n value SET @n = (LOG(@m1) - LOG(@m2)) / (LOG(@t1) - LOG(@t2)) -- f value SET @F = @m1 / (@n * POWER(@t1, @n)) -- r value SET @RF = @smjrF * @F * POWER(@tf, @n) -- r\' value SET @RZ = POWER(POWER((@x - @fx), 2) + POWER((@rf - (@y - @fy)), 2), 0.5) -- t\' value SET @tz = POWER((@rz / (@smjrF * @F)), (1 / @n)) -- thetha\'' value SET @zz = ATAN((@x - @fx) / (@rf - (@y - @fy))) -- latitude trial value in radians SET @latTR = (@pi / 2) - (2 * ATAN(@tz)) -- latitude iteration 1 value in radians SET @lat1R = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@latTR))) / (1 + (@e * SIN(@latTR))), (@e / 2))))) -- latitude iteration 2 value in radians SET @lat2R = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@lat1R))) / (1 + (@e * SIN(@lat1R))), (@e / 2))))) -- latitude iteration 3 value in radians SET @lat3R = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@lat2R))) / (1 + (@e * SIN(@lat2R))), (@e / 2))))) -- latitude iteration 4 value in radians SET @lat4R = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@lat3R))) / (1 + (@e * SIN(@lat3R))), (@e / 2))))) -- latitude final iteration value in radians SET @latFR = (@pi / 2) - (2 * ATAN((@tz * POWER((1 - (@e * SIN(@lat4R))) / (1 + (@e * SIN(@lat4R))), (@e / 2))))) -- latitude trial value in degrees SET @latTD = (@latTR * 180) / @pi -- latitude iteration 1 value in degrees SET @lat1D = (@lat1R * 180) / @pi -- latitude iteration 2 value in degrees SET @lat2D = (@lat2R * 180) / @pi -- latitude iteration 3 value in degrees SET @lat3D = (@lat3R * 180) / @pi -- latitude iteration 4 value in degrees SET @lat4D = (@lat4R * 180) / @pi -- latitude final iteration value in degrees SET @latFD = (@latFR * 180) / @pi -- Return the latitude in degrees RETURN @latFD; END

Re: Formula For State Plane to Lat/Lon Conversion

WillDeLany — Fri, 19 Mar 2021 15:43:27 GMT

Hi @mpboyle ,

If you could provide those calculations (if you still have access to them), it would be much appreciated!