MS Excel Formula - Convert NZ Map Grid to WGS 1984?

Forums › Off topic › MS Excel Formula - Convert NZ Map Grid to WGS 1984?

CruciasNZ

879 posts

Ultimate Geek

Trusted

#145469 19-May-2014 10:28

Good day guys,

I'm wondering if anyone has made an Excel spreadsheet to convert between NZ Map Grid to WGS 1984 Co-Ordinate systems? I normally use the LINZ website but I have run into a situation where it would not work (namely some of my data rows have no Northings/Eastings). LINZ has the formulas on their website and it's been on the radar to make one of these sheets myself, however I do not have the time right now to do so.

Therefore I thought I would crowdsource - has anyone done it and be willing to share it?

Cheers,

Cru

Professional Forum Lurker

nate

6473 posts

Uber Geek

Retired Mod

Trusted

Lifetime subscriber

#1047903 19-May-2014 11:09

How many rows of data? I've had to do this in the past for a client and if you're happy with a one-off conversion, I could possibly translate these for you.

Killerkiwi2005

374 posts

Ultimate Geek

Trusted

#1047941 19-May-2014 12:16

Not easily have you seen the formula ;)

http://www.linz.govt.nz/geodetic/conversion-coordinates/projection-conversions/projection-to-geographic-nzmg-to-nzgd1949

There is a download of the C code for doing the conversion so I guess you could make a small app to do the conversion, I wouldnt even try that in vba
http://www.linz.govt.nz/geodetic/software-downloads

frankv

5680 posts

Uber Geek

Lifetime subscriber

#1047947 19-May-2014 12:26

I've converted the C into Java... happy to share that.

See also http://forums.gps.org.nz/viewtopic.php?f=6&t=3146

tchart

2380 posts

Uber Geek

ID Verified

Trusted

#1047958 19-May-2014 12:39

Yup not so easy to convert NZ Map Grid to WGS84 (or the other way around). The problem is that they are on different (underlying) datums and the shift between datums uses a complex formula.

NZ Transverse Mercator uses a datum which is basically the same as WGS84 so much simpler to convert.

Best bet is to use the LINZ web site or find someone with some GIS software.

If the data isn't commercially sensitive I can convert it for you if you want (I work for a GIS software vendor).

CruciasNZ

879 posts

Ultimate Geek

Trusted

#1047978 19-May-2014 13:10

Hey cheers for the replies.

I have seen the formula which is exactly why I don't have the time :D

The problem with using LINZ is it can't handle rows of blanks, so I'd need to feed it each 'chunk' of points sequentially. The data I am working on is road condition data for the State Highways and so is under confidentiality agreements unfortunately. Hence the desire to get a working Excel conversion.

Guess I'll just skip GPS (it's a nice to have, not a need to have for this project thank god).

Professional Forum Lurker

frankv

5680 posts

Uber Geek

Lifetime subscriber

#1048081 19-May-2014 14:50

Google for "Redfearn's formula XLS"....

This is a bit simpler than what LINZ offers, and it's what I used. Whilst the formula looks horrendous, it actually isn't that bad... just long.

It's a general purpose formula to convert IIRC any Transverse Mercator projection to degrees/minutes/seconds and vice versa. There are eight constants to plug into the formula to make it a NZTM projection, plus your (x,y) coordinates.

One Excel example is at www.icsm.gov.au/gda/gdatm/redfearn.xls