Home > Postcodes > New Zealand Postcode Database (V2)

New Zealand Postcode Database (V2)

September 14th, 2007 Leave a comment Go to comments

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.

Categories: Postcodes Tags:
  1. Adam
    September 15th, 2007 at 13:59 | #1

    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

  2. September 15th, 2007 at 15:46 | #2

    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

  3. stef
    September 24th, 2007 at 03:36 | #3

    thanks for your efforts, very useful

  4. Eor
    October 5th, 2007 at 14:13 | #4

    Is there any reason why the suburb column in the streets table is empty?

  5. October 5th, 2007 at 14:45 | #5

    There’s a few without suburbs, I.e. they didn’t exist on any data source that I could find.

  6. Rahul
    October 6th, 2007 at 18:22 | #6

    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 [email protected] would appreciate the help.

  7. InTheSand
    October 11th, 2007 at 12:59 | #7

    Fantastic! Thanks for this, saved tonnes of time!

  8. Oz
    October 16th, 2007 at 18:05 | #8

    Brilliant!
    You are a legend

  9. Reubs
    October 21st, 2007 at 21:11 | #9

    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

  10. Richard
    October 22nd, 2007 at 09:46 | #10

    Awesome thank you. Saves a lot of time extracting the pdfs!

  11. JayJay
    October 31st, 2007 at 08:50 | #11

    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

  12. October 31st, 2007 at 09:32 | #12

    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.

  13. November 9th, 2007 at 16:59 | #13

    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.

  14. November 20th, 2007 at 03:33 | #14

    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

  15. November 20th, 2007 at 06:58 | #15

    The database can be used anywhere by anyone with no limitations.

  16. TimR
    November 22nd, 2007 at 11:28 | #16

    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

  17. November 27th, 2007 at 09:02 | #17

    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

  18. Richard
    December 4th, 2007 at 11:12 | #18

    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

  19. January 15th, 2008 at 10:43 | #19

    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.

  20. January 18th, 2008 at 15:03 | #20

    Thanks for all your hard work.

  21. January 21st, 2008 at 13:47 | #21

    noodles i cant see the donate button which will be happily pushed if you can do above. cheers.

  22. January 29th, 2008 at 14:19 | #22

    Excellent Work!
    Thanks for the time you have spent on this.

  23. February 1st, 2008 at 15:19 | #23

    Hi, I don’t understand why the official postcode count is somewhere around 1800, but this database only contains 359 of them? Thanks.

  24. Paul Walker
    February 6th, 2008 at 12:23 | #24

    Awesome. Thanks so much for your dedication.

  25. Owen
    February 11th, 2008 at 19:57 | #25

    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.

  26. Mano Jayasinghe
    February 29th, 2008 at 10:04 | #26

    Great effort, and thank you for making it available for one and for all. Guys
    like you make the internet really TICK! Well-done.

  27. Linkin
    March 6th, 2008 at 17:10 | #27

    hey noodles,
    thanks for the amazing job, im just wondering, is this DB under NZ new postcode system??

    Cheers

  28. April 14th, 2008 at 14:27 | #28

    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

  29. Mark C
    April 20th, 2008 at 14:32 | #29

    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

  30. Mark C
    April 21st, 2008 at 12:15 | #30

    I’ve found and fixed some errors and changes. Should I post the corrections here?

  31. Mark C
    April 21st, 2008 at 16:47 | #31

    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!

  32. April 24th, 2008 at 11:40 | #32

    I’ll do some more work on it this weekend, you can email me any errors/changes at noodles [at] planetslackers [dot] com

  33. May 2nd, 2008 at 01:49 | #33

    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

  34. Anand Kumar
    May 12th, 2008 at 00:32 | #34

    I can’t thank you enough. The mess created by NZ Post has been sorted by you. You are a Legend!!

  35. Anand Kumar
    May 12th, 2008 at 00:54 | #35

    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 [email protected]

  36. May 12th, 2008 at 16:46 | #36

    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!

  37. Chris T
    May 14th, 2008 at 22:12 | #37

    You are a legend! What a huge job you’ve done, and boy, am I SOOOO thankful.

    Cheers
    Chris T

  38. Nick
    June 18th, 2008 at 20:40 | #38

    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!

  39. Rachel
    July 4th, 2008 at 14:55 | #39

    Noodle, you’re awesome. Thanks so much. You saved me many many hours trying to do a web scrape.

    – Rachel

  40. Ian
    July 8th, 2008 at 15:49 | #40

    Hi Noodle

    How do I use your software? Run it against a csv file or ???

    cheers

  41. Brett
    July 10th, 2008 at 11:23 | #41

    To Nick: I haven’t been able to replicate this. Can you paste your query?

  42. Brett
    July 10th, 2008 at 14:23 | #42

    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

  43. Peter
    July 15th, 2008 at 17:47 | #43

    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.

  44. Chris
    August 1st, 2008 at 15:20 | #44

    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.

  45. Richard
    August 1st, 2008 at 22:51 | #45

    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.

  46. alex
    October 28th, 2008 at 22:01 | #46

    Not having the regions in there makes it a bit annoying 🙁 Do you plan on adding the regions back in for the future?

  47. Ozgur
    November 14th, 2008 at 17:24 | #47

    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?

  48. Victor
    December 5th, 2008 at 13:40 | #48

    I have just download the mobileZIPodes for my Iphone. I find however that my 4410 code for Palmerston North is lasted as being somewhere in Masterton Clearly your dta base does not match the Post Office Data base can this be fixed?

  49. December 5th, 2008 at 13:47 | #49

    I have no idea what mobileZIPodes is, but I see 4410 being listed for Palmerston North in the database

Comment pages
1 2 3 17
  1. September 14th, 2007 at 22:50 | #1