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
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’
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 🙂
Data 2,362 KB
Index 448,512 Bytes
Total 2,800 KB
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 🙂
Aaron: No, I had a need for it 🙂
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.
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:
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]
[Eg a single address: 21 Arapiki Road, Stoke]
Multimap uses the WGS84 datum.
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
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 🙂
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?
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.
Thats cool Noodles.. maybe ill give it a shot as well..
was just trying to save the work!!
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.
Hope this helps
Have it in excel spreadsheet if required, mail me
Thanks very much for this!
I was going to do the exact same thing but you saved a heap of time 😀
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.
I didn’t use the adobe reader to export to text, it was another program. I forget which one though.
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!
Cheeky request: have you done the PO Boxes?
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.
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.
Thanks, You don’t have a copy of this in CSV or excel format do you?
I may have a go with the RDs and PO Boxes to complete the data set: are you interested in it?
This is great, but is there a reason for not including the cities and towns?
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.
Grant: I’ll chuck a copy of it up in csv
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)?
Great Job! thank u for ur effort! Where could I get the update postcode file include city/town?
Can you put up NZ postcode with city,town,province/state in .csv format?
Great information. We are looking for the post code information including
State / Provence (or locality)
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?
Have a look at the Google Maps API: passing it an address sends back possibilities for postcodes & lat/long; see
er. . .
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!
Have you got an Excel or CSV version?
If you have can you send it through.
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.
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.
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?
State / Province
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:
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
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?
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?
Pingback: New Zealand Postcode Database (V2)
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?
Hi, nice site! Please tell me how I can get the region associated with a suburb, street, or town.
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)!
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.
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….
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??
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.
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
Where would I get this data? I am interested in suburb, postcode, latitude and longitude.
Thanks so much – great thread!
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.
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?