Home > Coding, Postcodes > New Zealand Postcode Database

New Zealand Postcode Database

August 9th, 2006 Leave a comment Go to comments

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)

Categories: Coding, Postcodes Tags:
  1. September 6th, 2006 at 15:58 | #1

    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’

  2. September 23rd, 2006 at 21:30 | #2

    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 ๐Ÿ™‚

  3. September 24th, 2006 at 13:33 | #3

    Aaron: No, I had a need for it ๐Ÿ™‚

    Duane: Thanks

  4. Rodney
    October 18th, 2006 at 14:33 | #4

    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

  5. Marc
    January 1st, 2007 at 21:19 | #5

    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

  6. January 4th, 2007 at 21:15 | #6

    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

  7. January 16th, 2007 at 14:44 | #7

    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 ๐Ÿ™‚

  8. February 6th, 2007 at 11:03 | #8

    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

  9. February 6th, 2007 at 11:14 | #9

    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.

  10. February 6th, 2007 at 11:26 | #10

    Thats cool Noodles.. maybe ill give it a shot as well..
    was just trying to save the work!!

    thanks

    Scotty

  11. February 7th, 2007 at 14:25 | #11

    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

  12. Jonnie
    March 1st, 2007 at 16:40 | #12

    Have it in excel spreadsheet if required, mail me
    [email protected]

  13. Ryan
    March 9th, 2007 at 17:06 | #13

    Hi there,

    Thanks very much for this!
    I was going to do the exact same thing but you saved a heap of time ๐Ÿ˜€

    Ryan

  14. Charles Finn
    May 25th, 2007 at 09:37 | #14

    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.

  15. May 25th, 2007 at 09:52 | #15

    I didn’t use the adobe reader to export to text, it was another program. I forget which one though.

  16. Owen
    June 4th, 2007 at 21:04 | #16

    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!

  17. Owen
    June 4th, 2007 at 23:04 | #17

    Cheeky request: have you done the PO Boxes?

  18. Donovan
    June 11th, 2007 at 16:18 | #18

    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.

  19. June 11th, 2007 at 16:21 | #19

    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.

  20. Grant
    June 14th, 2007 at 11:53 | #20

    Thanks, You don’t have a copy of this in CSV or excel format do you?

  21. Owen
    June 16th, 2007 at 19:29 | #21

    I may have a go with the RDs and PO Boxes to complete the data set: are you interested in it?

  22. Andrew
    June 22nd, 2007 at 14:58 | #22

    This is great, but is there a reason for not including the cities and towns?

  23. June 22nd, 2007 at 15:04 | #23

    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.

  24. June 22nd, 2007 at 15:05 | #24

    Grant: I’ll chuck a copy of it up in csv

  25. Iain
    June 28th, 2007 at 12:22 | #25

    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)?

  26. Baek
    July 2nd, 2007 at 13:36 | #26

    Great Job! thank u for ur effort! Where could I get the update postcode file include city/town?

  27. Joe
    July 3rd, 2007 at 10:00 | #27

    Noodles,
    Can you put up NZ postcode with city,town,province/state in .csv format?

  28. Gary
    July 3rd, 2007 at 11:24 | #28

    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?

  29. Owen
    July 3rd, 2007 at 21:14 | #29

    Have a look at the Google Maps API: passing it an address sends back possibilities for postcodes & lat/long; see

  30. Owen
  31. Peter
    July 26th, 2007 at 18:03 | #31

    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!

  32. AndrewH
    July 31st, 2007 at 09:54 | #32

    Great One!!
    Have you got an Excel or CSV version?
    If you have can you send it through.
    Thanks

  33. August 8th, 2007 at 21:18 | #33

    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

  34. August 15th, 2007 at 21:39 | #34

    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

  35. August 18th, 2007 at 13:12 | #35

    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

  36. Andrew
    August 25th, 2007 at 13:44 | #36

    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

  37. September 11th, 2007 at 19:31 | #37

    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

  38. AlanMc
    September 12th, 2007 at 17:58 | #38

    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

  39. Louise
    September 21st, 2007 at 13:33 | #39

    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

  40. September 29th, 2007 at 06:12 | #40

    Hi, nice site! Please tell me how I can get the region associated with a suburb, street, or town.
    Thank You

  41. Wayne
    October 8th, 2007 at 16:19 | #41

    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

  42. David Wharekura
    October 9th, 2007 at 12:12 | #42

    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.

  43. JayJay
    October 31st, 2007 at 08:40 | #43

    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….

  44. Drew
    February 8th, 2008 at 07:40 | #44

    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??

  45. Badcop666
    April 30th, 2008 at 22:59 | #45

    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

  46. May 25th, 2008 at 18:07 | #46

    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

  47. June 19th, 2008 at 00:58 | #47

    Hi there
    Where would I get this data? I am interested in suburb, postcode, latitude and longitude.

    Thanks so much – great thread!
    Vivien

  48. July 1st, 2008 at 11:17 | #48

    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.

  49. August 12th, 2008 at 10:25 | #49

    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?

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