Formula For State Plane to Lat/Lon Conversion

[GeorgePorto]

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!

[ScottDean]

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

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

[GeorgePorto]

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.

[MelitaKennedy]

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.

[GeorgePorto]

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

[mpboyle]

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

[MelitaKennedy]

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

[mpboyle]

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!

[MelitaKennedy]

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.

[mpboyle]

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.