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!

[DavidFrame]

Hey, I'm trying to do something similar. I'm srid 2237. NAD 83 state plane Florida west zone. survey ft. My projection doesn't have standard parallels. But I do have a "Scale_Factor". So, I'm not sure if I can use your stored procs without some changes?

[AnnaritaMacri]

@MelitaKennedy  These posts are pretty old.  Did converting state plane to lat/long in SQL become any easier?  Also, the document link for the guidelines is no longer available. Is there a new link for guidance notes on this?

[MelitaKennedy]

Hello, 

This is going to hit a bunch of different questions that have come up over the years that I missed. Sorry!

I don't know about any possible changes at the SQL level because I haven't worked with RDBMS for years. If your RDBMS has a 'spatial' component, there's likely a function like 'project' or 'projection' that should do the equivalent. It might even take WKID numbers. Ah, check out the Microsoft SQL Server Spatial Tools. Actually, from that topic there's a link in the Tip that takes you to a GitHub opensource set of Spatial Tools. 

The IOGP (absorbed EPSG years ago; EPSG name was kept for recognition purposes) Guidance Notes related to coordinate reference systems, geodesy, coordinate transformations/operations are available or linked from this page. The one you want if you're going to implement the math of a map projection is 7-2. Currently, it's available from the IOGP bookstore, but it's free. You just need to fill out your email etc. You might get some emails from IOGP; but you can unsubscribe. 

re: NAD27. NAD27 uses Clarke 1866 versus GRS80, so you would need to change the semimajor axis and the semiminor axis OR the flattening value, depending on what is being used. Unfortunately the conversion between NAD83 and NAD27 in the US uses a grid file and would be quite a bit to implement. I've never done it myself. 

There are some 'equation-based' datum transformation methods but the ones between NAD27 and WGS84 (rather than NAD83) are not very accurate. 

Melita

[SeanO_Halloran]

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?

[MelitaKennedy]

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

[SeanO_Halloran]

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

[ScottFedak2085]

It would be awesome if coordinate transformations were built into SQL Server as they are in the PostGIS extension for Postgresql, but we can't be so lucky and this is awesome. Math always takes me a very long time to work through and I can't even imagine trying to tackle this. Someone in my office has utilized these formulas, so I can confirm that they do work!

I do have one question regarding these formulas; can it be assumed that they do not perform a datum transformation? In other words, you can use these formulas to transform coordinates in a Lambert conformal conic projections to it's CRS equivalent based on the GRS 80 Ellipsoid; but you would have to perform an additional transformation to get NAD27 (Clarke ellipsoid of 1866) or WGS 84 coordinates.

From what I've gathered, those datum transformations include even more complex math, but I could be interpreting things incorrectly. Thanks all!