New Zealand Postcode Database
Due to the lack of data from NZ Post, I’ve created this mysql database from the PDF on the NZ Post website.
All efforts have been made to ensure that there are no errors, but there are no guarantees, if you need 100% accurate data please buy NZ Post’s PAF data.
Update: New Zealand Postcode Database version 2
Version 1:
nzpostcodes.sql.gz (467KB)
nzpostcodes.sql.bz2 (313KB)
September 6th, 2006 at 3:58 pm
Mate, great that youve done it. But please tell me you had a need for it and not just ‘I had nothing else to do’
September 23rd, 2006 at 9:30 pm
not sure if you are interested or not, but you can save almost a meg in SQL space, and make the index smaller just by changing your data types a little. Side effect of course is that everything speeds up
eg
Before:
Data 2,362 KB
Index 448,512 Bytes
Total 2,800 KB
After:
Data 1,581 KB
Index 359,424 Bytes
Total 1,932 KB
CREATE TABLE `postcodes` (
`streetid` smallint(11) unsigned NOT NULL auto_increment,
`street` varchar(255) NOT NULL,
`addrinfo` varchar(255) default NULL,
`suburb` varchar(255) NOT NULL,
`postcode` smallint(4) unsigned zerofill default NULL,
`oddeven` tinyint(1) default NULL,
`region` tinyint(2) default NULL,
PRIMARY KEY (`streetid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `nzregions` (
`regionid` tinyint(2) unsigned NOT NULL auto_increment,
`region` varchar(255) NOT NULL,
PRIMARY KEY (`regionid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Also with varchars, you don’t gain or loose anything by setting the length, the amount of data is always string length + 1 byte, so I tend to set them all to 255 to save myself headaches with truncation and the unknown. Well unless you want a field truncated, and then it’s ok I guess
September 24th, 2006 at 1:33 pm
Aaron: No, I had a need for it
Duane: Thanks
October 18th, 2006 at 2:33 pm
Mate! Well done - can’t believe that NZ Post has locked away such useful data that would help them. What would it take just to release the info you have worked hard at! Well done.
R
January 1st, 2007 at 9:19 pm
I don’t know if you have Lattitude and Longitude information in your database, but I imagine that many would find it useful.
You can screenscrape the information from the NZ Post online postcode finder and Multimap.
Eg. looking up postcode 7011 [Stoke] from NZ Post produces a map like this:
http://www.multimap.com/clients/browse.cgi?client=nzpost_01&g_postcode=7011
Looking at the HTML source code, we find the Lattitude and Longitude for some point inside the 7011 region:
Similarly, a search for a street address produces a map page where the image link looks, in part, like this:
[Eg an entire street: Arapiki Road, Stoke]
http://www.multimap.com/clients/browse.cgi?client=nzpost_01&lllist=173.24149,-41.31082,173.2495,-41.305317
[Eg a single address: 21 Arapiki Road, Stoke]
http://www.multimap.com/clients/browse.cgi?
client=nzpost_01&lat=-41.306399&lon=173.243622
Multimap uses the WGS84 datum.
HTH
January 4th, 2007 at 9:15 pm
I’ll probably run it over google’s mapping api: http://www.google.com/apis/maps/documentation/#Geocoding_HTTP_Request
Now I just need a list of addresses
January 16th, 2007 at 2:44 pm
Cheers mate very useful. Seems crazy that NZ Post want to charge so much for an equivalent. This is much easier than writing some scrape code
February 6th, 2007 at 11:03 am
Hey Noodles,
Great listing!! ive been trying to find something like this for a while now..
Did you manage to extract the Lat and Long using the google API??
If so, would you like to share the data?
Thanks
Scotty
February 6th, 2007 at 11:14 am
I started trying to do long/lat, but data differs from source to source, like google might say queen st is in one suburb, while another will say it’s in a completely different suburb.
So I didn’t get very far. I may revisit it later on though.
February 6th, 2007 at 11:26 am
Thats cool Noodles.. maybe ill give it a shot as well..
was just trying to save the work!!
thanks
Scotty
February 7th, 2007 at 2:25 pm
Hey Noodles,
Done a bit of ferreting around and have found listsing of geographical infomation for countries around the work. Ive just downloaded the NZ one, and i am currently doing a match on town name..
This is the link, just scroll down to New Zealand.
http://earth-info.nga.mil/gns/html/cntry_files.html
Hope this helps
March 1st, 2007 at 4:40 pm
Have it in excel spreadsheet if required, mail me
jonnie.deboer@gmail.com
March 9th, 2007 at 5:06 pm
Hi there,
Thanks very much for this!
I was going to do the exact same thing but you saved a heap of time
Ryan
May 25th, 2007 at 9:37 am
Thanks for doing this.
Did you have problems with digits that were skipped from the PDF? I found that the with Adobe Reader 6 or 8, the export to text option would just translate some digits (usually the last digit of the post code) into a space. E.g every reference to Broadwood 0496 would come across as 049.
May 25th, 2007 at 9:52 am
I didn’t use the adobe reader to export to text, it was another program. I forget which one though.
June 4th, 2007 at 9:04 pm
Thanks for doing this; our NZ Post rep claimed they were working on a web service to do this but I’m not holding my breath. The new charging regime is a shocker.
Hope that someday you have a need for lat/long data to be added; I’ll be first in line to grab that!
June 4th, 2007 at 11:04 pm
Cheeky request: have you done the PO Boxes?
June 11th, 2007 at 4:18 pm
Thanks so much for doing this. Being in New Zealand is almost third world as it is and then the bloody postal service does something stupid like giving out new post codes and not sharing the data with anyone. Assholes.
June 11th, 2007 at 4:21 pm
Owen: No I haven’t. They didn’t fit well into the schema I was aiming for. They’re freely available on the PDF from the nzpost website though.
June 14th, 2007 at 11:53 am
Thanks, You don’t have a copy of this in CSV or excel format do you?
June 16th, 2007 at 7:29 pm
I may have a go with the RDs and PO Boxes to complete the data set: are you interested in it?
June 22nd, 2007 at 2:58 pm
This is great, but is there a reason for not including the cities and towns?
June 22nd, 2007 at 3:04 pm
Owen: Sure, send it through (noodles [at] planetslackers.com) or link off the comments.
Andrew: There was no town/city information in the PDF that NZ Post supplied. I looked at refining it a bit more, but there are some disputes when it comes to where a town starts/ends and what roads are in that town.
June 22nd, 2007 at 3:05 pm
Grant: I’ll chuck a copy of it up in csv
June 28th, 2007 at 12:22 pm
Mate great effort with this. I too am totally pissed of at NZ Post for this - they should be encouraging businesses to migrate to the new postcodes by making it simple … not discouraging them by charging a ridiculous amount of cash and providing no simple form of their data … anyway now that my rant is over
Does anyone have this in MSSQL form (or know how to convert)?
July 2nd, 2007 at 1:36 pm
Great Job! thank u for ur effort! Where could I get the update postcode file include city/town?
July 3rd, 2007 at 10:00 am
Noodles,
Can you put up NZ postcode with city,town,province/state in .csv format?
July 3rd, 2007 at 11:24 am
Great information. We are looking for the post code information including
City
State / Provence (or locality)
Latitude
Longitude
We want to use this on a web site for calculating the distance between members and geographical spread of same.
Does anyone know how much this information would cost to get?
July 3rd, 2007 at 9:14 pm
Have a look at the Google Maps API: passing it an address sends back possibilities for postcodes & lat/long; see
July 3rd, 2007 at 9:15 pm
er. . .
http://www.google.com/apis/maps/documentation/#Geocoding_Structured
July 26th, 2007 at 6:03 pm
Noodles - you’re brilliant. I can’t figure out why NZ Post won’t publish the list in excel format just like Australia Post. - but then we might be able to accuse them of being helpful!
July 31st, 2007 at 9:54 am
Great One!!
Have you got an Excel or CSV version?
If you have can you send it through.
Thanks
August 8th, 2007 at 9:18 pm
Hey Noodles and other fantastic people here on your blog.
I’ve been looking for postcodes for a while (to use in my getting back into work project) and another member on the ACCFORUM.org website pointed me here. Great stuff. Yes, I’m a disabled person (from work injury) who is fighting the ACC. Is anyone here able to help with the tech stuff on my project? Once up and running it is open for other acc claimants to jump in and possibly create some sort of job for themselves. Anyway, bthanks.
Fran
August 15th, 2007 at 9:39 pm
For anyone interested, I’ve launched a product written in PHP that allows you to search for nearby postcodes (based on a kilometer radius) and calculate the approximate distance between postcodes using an accurate NZ postcode database. An Australian version is also available.
See http://www.dreamscapemedia.com.au/products/nz_postcode_locator/
-Shannon
August 18th, 2007 at 1:12 pm
Hi there, is it possible for us to obtain a copy of the data in a csv file. We also need this to get geographical distances between member. Has anyone done this yet. Also if it hasnt been done, is there somewhere we can send members to look up their latitudes and longitudes which maybe we can get them to enter at the time of registration?
City
State / Province
Latitude
Longitude
August 25th, 2007 at 1:44 pm
Thanks for the database, very much appreciated. There are a couple of errors where long names from other rows have overrun into the one listed. These are at:
[streetid] [street]
11151 Pasteur Street
11393 Argyll Street
15196 McCarthy Crescent
15715 Tamaki Street
18446 Matai Street
22451 Stout Street
23295 Banks Place
23300 Baring Terrace
23322 Blackwell St
Andrew
September 11th, 2007 at 7:31 pm
Have been referred to this site by NZ Post!
Our voluntary organisation has some 1500 post codes to alter, and I asked them for help.
I am not very computer literate, what program should I use to open your data?
Thanks
David
September 12th, 2007 at 5:58 pm
Hi, can you send me a copy of the database in CSV file format please? Did you ever get a chance to include PO Boxes?
Cheers
-Alan
September 14th, 2007 at 10:48 pm
[…] Original Post More info on NZ Addresses […]
September 21st, 2007 at 1:33 pm
First of all - Thanks for all your trouble!
Second - I’m not sure exactly how to interpret/use the ‘matchedaddress’ column. It looks to me as if it can be used as an extra validation if a match on just columns ’street’;’suburb’;'postcode’ failed?
Cheers
Louise
September 29th, 2007 at 6:12 am
Hi, nice site! Please tell me how I can get the region associated with a suburb, street, or town.
Thank You
October 8th, 2007 at 4:19 pm
Hi Noodles,
First, congrats for all your work on the postcodes. I downloaded your version 1 a few weeks ago (just before version 2) and also noticed the odd error and gap, so decided to see if I might produce the same data directly from NZ Post’s own PDF file. After a combination of “Perl and Perspiration”, I’m not too far off, although I still notice more errors every time I have another look. There are some *really* odd street names and synonyms around!
Your Version 2, with the lats and longs, is obviously even more handy but I see you’ve omitted the suburbs and regions on the grounds that NZ Post’s suburb/district/region tables are different from everyone else’s! Even so, I think I’ll use the above to put them back.
Not sure what I really need all this for but it annoys me that the government could easily make these SQL scripts freely available from its own databases which public money has already paid for.
Thanks again, keep up the good work and I’ll keep you posted (pun intended)!
Wayne
October 9th, 2007 at 12:12 pm
I had been doing the same thing by extracting the post office’s PDF via an OCR program which then dumped it into a Microsoft SQL Server database in which I then attempted to create a matching process (via MS SQL Server Stored Procs) to update addresses with the new post codes.
However, this was some time ago in my spare time. If people are interested I can re kinder the light and might revisit my work.
October 31st, 2007 at 8:40 am
YOU R THE MAN Noodles…..Thank you so much. This will help with our intranet project.
Thanks again….and again….and again….and again….and….
February 8th, 2008 at 7:40 am
Ok… that’s weird. My inquiry was deleted. It was an honest question because I don’t know much about the postcode system in New Zealand and wanted to make sure whatever data set I use is accurate. I suppose I have to assume that something sneaky and underhanded is going on here since my question was simply censored and deleted with no response whatsoever. Though I find this really odd and strange that somebody would want to do that??
April 30th, 2008 at 10:59 pm
Hi Noodles,
this has been well received, thanks for your efforts, endlessly massaging data is a time honoured and poorly paid activity.
I’ll be using this for an Ajax postcode lookup tool in Zen Cart during signup and/or general address entry.
If you’ve any working php/javascript code you’ré happy to share that would be appreciated, I’ll certainly flick you a copy of anything I come up with.
thanks again
Andy
May 25th, 2008 at 6:07 pm
Looking to do bulk mail outs …do you know how i can do this price effective…what changes are at hand in that department of NZ Post ?
Give me a low down please, there must be a better way…Walter
June 19th, 2008 at 12:58 am
Hi there
Where would I get this data? I am interested in suburb, postcode, latitude and longitude.
Thanks so much - great thread!
Vivien
July 1st, 2008 at 11:17 am
We’ve identified some inaccuracies in the NZ Post web lookup tool.
Here’s hoping their PDF was a better data source than their online version!
See http://tinyurl.com/46dsav OR http://glob.bushi.net.nz/glob/2006/09/25/seaview-road-piha-or-paihia-but-not-waitakere-thanks-nz-post/
Moral: some addresses as supplied by locals just won’t work, even if 100% accurate - take care with user supplied data, I guess.
August 12th, 2008 at 10:25 am
Our company, hscr, is a survey research company which specialises in surveying hospital and GP patients about the quality of care they have recently received. Each fortnight we send out 500+ letters to Auckland patients and in addition when we have special projects we may need to send thousands of questionnaires to people all over NZ.
The new postcode system is great except that even when we have an Excel file of all the postcodes (Thanks a million noodles!!! I am happy to pay a small contribution to your efforts!) we still need a macro that looks at the columns of addresses that we have and assigns the correct postcode - of course our addresses have all kinds of different formats and abbreviations (”ave”,”rd”, etc.) Is there such a macro available somewhere?