New Zealand Postcode Database (V2)
I’ve done a bit of work on the database, added a bit of information and corrected mistakes.
Thanks to Owen and others for help with the data.
If you find this data useful, please donate.
What’s new in V2
- More accurate
- Added GPS co-ordinates
- Added PO Boxes and Private Bags
- Added Rural postcodes
- Removed regions (as some datasources labeled a city/town as one region and others differed.)
I’ve also taken the opportunity to include CSV and MSSQL formats, as well as MySQL.
History
I originally created the database as I needed street and suburb data for a project I was doing. No one else was stupid enough to spend that much time on it, so I took it on. NZ Post still has their PAF data, but are unlikely to release a free database of the postcodes.
Original Post
More info on NZ Addresses
Downloads
MySQL
nzpostcodes_v2.sql.gz (655KB)
nzpostcodes_v2.sql.bz2 (445KB)
CSV
nzpostcodes_v2.csv.zip (1.07MB)
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 20/04/08]
As a few people have pointed out, there was no suburb data in the MySQL database version of the postcode database. This has now been fixed. Sorry about the problems and how long it’s taken me to get around to fixing it.
September 14th, 2007 at 10:50 pm
[...] Update: New Zealand Postcode Database version 2 [...]
September 15th, 2007 at 1:59 pm
Brilliant, really appreicate your efforts on this.
By the way , I tried runing the MS SQL script but recieved the following errors:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword ‘IF’.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘boxes’.
Msg 102, Level 15, State 1, Line 974
Incorrect syntax near ‘rural’.
Msg 102, Level 15, State 1, Line 1743
Incorrect syntax near ’streets’.
Msg 102, Level 15, State 1, Line 45281
Incorrect syntax near ’suburbs’.
I’m no MS SQL expert but it looks like it may require a few changes before it will execute. If we end up using this data i will have one of my guys update the script and I can send you the updated version.
Cheers
Adam
September 15th, 2007 at 3:46 pm
Thanks, I don’t have a copy of MSSQL here to try it out on, so it was just exported from phpMyAdmin in MSSQL compliance mode, but obviously it wasn’t that compliant :p
September 24th, 2007 at 3:36 am
thanks for your efforts, very useful
October 5th, 2007 at 2:13 pm
Is there any reason why the suburb column in the streets table is empty?
October 5th, 2007 at 2:45 pm
There’s a few without suburbs, I.e. they didn’t exist on any data source that I could find.
October 6th, 2007 at 6:22 pm
Hi,
In need of the database for what u just developed here. Great effort man. Is possible for u to get me the complete and updated reference file. I can be reached at dope.smugglaz@gmail.com would appreciate the help.
October 11th, 2007 at 12:59 pm
Fantastic! Thanks for this, saved tonnes of time!
October 16th, 2007 at 6:05 pm
Brilliant!
You are a legend
October 21st, 2007 at 9:11 pm
This is great but are you meant to be able to link the suburbs table with the streets table? Cause all the values in the suburb field of the streets table are set to ‘0′. Thanks for ya help on this
October 22nd, 2007 at 9:46 am
Awesome thank you. Saves a lot of time extracting the pdfs!
October 31st, 2007 at 8:50 am
Hey noodles,
Tried running the script on our server but got the folowing message:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘ENGINE=MyISAM DEFAULT CHARSET=latin1′ at line 15
Just wondering what version of MySQL does this work on?
I’ll download the CSV version and create a SQL file that is compatible.
Thought that you might wanna know though its not compatible with MySQL 4.0.8.
Cheers mate,
J
October 31st, 2007 at 9:32 am
The file was created from MySQL 5.x. I highly recommend you upgrade your MySQL as they don’t even support 4.0 anymore.
I assume it works on 4.1 and above, but I only run v5.x here
Thanks for letting me know though.
November 9th, 2007 at 4:59 pm
Noodles you’re a champ! i got to the site via the reply you made to my post on whirlpool. Do you happen to have any other postcode/address databases? I’m currently trying to find one for singapore and hong kong but haven’t had much luck.
November 20th, 2007 at 3:33 am
Hello,
What terms can your database be used under? I ask because I have a commercial website where users can register and I may want to use the database to check for errors in the postcodes and addresses which users submit. I don’t want to use your database commercially like this without your permission.
Also, I don’t see a donation button on the right.
Thanks,
- Tim
November 20th, 2007 at 6:58 am
The database can be used anywhere by anyone with no limitations.
November 22nd, 2007 at 11:28 am
Noodles,
greatly appreciated.
I have the Oz version (but not updated recently) - and it includes the Lat/Long of the postcode. Using this I enable a client to post/email all their customers (Vets) that are within 200 (or xx) klms of the target postcode, whenever they are doing a country presentation.
I may make a start on your data.
Many thanks - TimR
November 27th, 2007 at 9:02 am
Noodles,
Fantastic - thanks. It turns out that I may not need the database just yet (maybe later), but it’s good to know it’s there.
- Tim
December 4th, 2007 at 11:12 am
Hey,
I don’t know if I’m missing something but the oddeven and suburb columns for the street table contain 0 for every record…
Thanks, Richard
January 15th, 2008 at 10:43 am
Hi Noodles and co excellent. Just one thing. I am also after the Provinces that the data came from, ie the pdf name such as Hawkes Bay etc. Anything that can be done incorporating that province into the data? Cheers, Mark.
January 18th, 2008 at 3:03 pm
Thanks for all your hard work.
January 21st, 2008 at 1:47 pm
noodles i cant see the donate button which will be happily pushed if you can do above. cheers.
January 29th, 2008 at 2:19 pm
Excellent Work!
Thanks for the time you have spent on this.
February 1st, 2008 at 3:19 pm
Hi, I don’t understand why the official postcode count is somewhere around 1800, but this database only contains 359 of them? Thanks.
February 6th, 2008 at 12:23 pm
Awesome. Thanks so much for your dedication.
February 11th, 2008 at 7:57 pm
Another way of doing it (apart from POs and RDs) is to query Google Maps (not the NZ version); in the html that is returned you can find the postcode.
February 29th, 2008 at 10:04 am
Great effort, and thank you for making it available for one and for all. Guys
like you make the internet really TICK! Well-done.
March 6th, 2008 at 5:10 pm
hey noodles,
thanks for the amazing job, im just wondering, is this DB under NZ new postcode system??
Cheers
April 14th, 2008 at 2:27 pm
I needed a web service of this data for an application we’re writing at the moment. I’ve created one based on Google’s new Appengine product. You’ll find it here; http://nzpostcodes.appspot.com/
Cheers,
hads
April 20th, 2008 at 2:32 pm
Good stuff.
Noodles, if you’re busy then fine, but THREE people (four including me) have stated that ALL rows in the streets table have 0 (zero) in the suburb column.
Not just some rows, but ALL rows.
Given your description of: “…as I needed street and *suburb* data…” this seems like an oversight.
Thanks,
Mark
April 21st, 2008 at 12:15 pm
I’ve found and fixed some errors and changes. Should I post the corrections here?
April 21st, 2008 at 4:47 pm
Ah! Many of my corrections were to the suburbs table.
Looking at the CSV, I see it is has more suburbs, more postcodes, and fixes some errors compared to the suburbs table.
Can we please have a new suburbs table?
Also, the “oddeven” column in the streets table has NO odd values; only even(2) and none(0). There are 502 odd, 536 even and 42419 (0) in the CSV file, but no odd, 536 even and 42921 (0) in the streets table.
Thanks!
April 24th, 2008 at 11:40 am
I’ll do some more work on it this weekend, you can email me any errors/changes at noodles [at] planetslackers [dot] com
May 2nd, 2008 at 1:49 am
Hey noodles,
Great work on the PDF decoding and compiling - I did it earlier this year before finding your site and it’s not fun!
I found the suburb count was significantly lower than expected when using the NZ Post PDF (±1300), so I compiled my own suburb list (4,637 entries) for my client’s use… it’s now available for purchase if you find a client that needs it: http://www.dreamscapemedia.com.au/products/nz_suburb_database/index.php
-Shannon Murdoch
May 12th, 2008 at 12:32 am
I can’t thank you enough. The mess created by NZ Post has been sorted by you. You are a Legend!!
May 12th, 2008 at 12:54 am
Thanks. I needed a list of Rural delivery suburbs or postcodes. The CSV list you created is commendable but how do I identify which one of them are RD ? My email is anku6@hotmail.com
May 12th, 2008 at 4:46 pm
Hi Everybody, thanks for the useful data. It’s been a real help. Hey does anybody know what the official postal code count is supposed to be? Thanks!
May 14th, 2008 at 10:12 pm
You are a legend! What a huge job you’ve done, and boy, am I SOOOO thankful.
Cheers
Chris T
June 18th, 2008 at 8:40 pm
Hey noodles,
Many thanks for putting in the work to provide this - I started scraping PDFs and it’s nasty work.
I’ve found a problem with the suburb table though. If you look at the ‘Vernon Terrace’ street field, join the suburb table onto the postcode column and join another suburb table onto the suburb column. Basically the suburb column and postcode column contradict eachother: suburb reads Hillsborough in Auckland and postcode reads Christchurch.
I hope this is of help to you, and again many thanks for doing this!
July 4th, 2008 at 2:55 pm
Noodle, you’re awesome. Thanks so much. You saved me many many hours trying to do a web scrape.
- Rachel
July 8th, 2008 at 3:49 pm
Hi Noodle
How do I use your software? Run it against a csv file or ???
cheers
July 10th, 2008 at 11:23 am
To Nick: I haven’t been able to replicate this. Can you paste your query?
July 10th, 2008 at 2:23 pm
Be wary where a suburb name appears twice.IE a suburb name appears in two different towns(there are 59 of them). It is possible these have the wrong id assigned.
Roslyn are suburbs in Palmerston North & Dunedin.
EXAMPLE:
SELECT *
FROM `suburbs`
WHERE `postcode` = 4414
Returns(3 rows I’ve removed 2 for brevity):
907 Roslyn Palmerston North 4414
So the suburb id is 907
BUT…
if we do:
SELECT *
FROM `streets`
WHERE `postcode` = 4414
and we find Tremaine Ave in the result set we see:
39456 Tremaine Avenue 647-1057 4414 0 906 175.602 -40.343 1
Edit Delete 39457 Tremaine Avenue 666-1050 4414 2 906 175.602 -40.343 1
we see the suburb id is 906 ( tremaine ave, 4412 is in Palmerston North)
…
SELECT *
FROM `suburbs`
WHERE `suburbid` =906
returns:
906 Roslyn Dunedin 9010
Regards Brett
July 15th, 2008 at 5:47 pm
I am using your info via the Zip Codes application on my iPod Touch. There seems to be large gaps in the information. I have noticed that all postal codes between 3225 and 3300 are not there - ours is 3288. They are missing from the CSV file on your site.
Will be very useful.
August 1st, 2008 at 3:20 pm
I notice that there are roughly 360 unique postcodes in your CSV file. Having spoken to NZ Post, I understand that NZ actually has over 1600 unique postcodes. Am I doing something stupid?
Thx.
August 1st, 2008 at 10:51 pm
I haven’t yet downloaded the database as ive maxed out my data for the month and doing so will be painfully slow. But if the accuracy of this database is slightly out i just want to suggest how easy it would be to extract the info from publicly available pdfs using basic text parsing.
For instance from post code directory pdfs simply select all text and dump to plain text file, then parse.
Its very easy as the “junk” text such as “please visit our website..” is repeated in large blocks throughout the document and can be easily stripped out in a matter of seconds.
That will leave u with some nice blocks of text starting with “Lobby Name Box/Bag Range Postcode”, “Postcode Listings – Urban Areas ” and “To check that an address is serviced by Rural Delivery” that you can use to change parsers for a specific post code type.
If you look at the plain text dump you’ll see that RD parsing is a piece of cake as are boxs and bags (lobby/start/end/post code). Its a very basic 3 columns format. Very simple.
Urban addresses look complicated but they are not if u consider that all New Zealand address fall into about 12 categories (streets/roads/lanes/etc). So when parsing the text for urban addresses just check and compare against these. If the word your on is one of these categories then you know uve just hit then end of a street, from there u should be expecting a suburb/town, so if u hit a numeric digit instead of a letter then you know you got a street range, then after the suburb/town the next series of digits is the post code.
So the logic to parse the documents from NZ Post is very very simple, although given the copyright probably illegal. I estimate maybe 2-3 hours of coding and you’d have the whole database in 100% accurate form straight from the horses mouth so to speak.
October 28th, 2008 at 10:01 pm
Not having the regions in there makes it a bit annoying
Do you plan on adding the regions back in for the future?
November 14th, 2008 at 5:24 pm
Am I right in saying that the combination of Postcode, Suburb and Town/City is unique.
i.e. For Australian postcodes, we look at the suburb and postcode combination. Matching these two will give us the state as the particular suburb and postcode combination can only exist in one state.
However when looking at the data, I believe for New Zealand postcodes we can’t do this. To the point that a combination of Suburb and Postcode is not unique to a particular town.
Am I correct in my understanding of this?
Does anyone have any suggestions regarding how we could handle New Zealand postcodes in a similar way to Australia?