59 thoughts on “New Zealand Postcode Database”

  2. Duane

    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 🙂

  4. Rodney

    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

    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

  7. Tom

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

    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. noodles Post author

    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.

  13. Ryan

    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

    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.

  16. Owen

    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!

  18. Donovan

    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. noodles Post author

    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.

  21. Owen

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

  23. noodles Post author

    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.

  25. Iain

    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

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

  28. Gary

    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

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

  31. Peter

    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

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

  33. Blurb

    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

  35. Jake

    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

    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. David Harrop, New Plymouth

    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

    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. Pingback: New Zealand Postcode Database (V2)

  40. Louise

    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

  42. Wayne

    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

  43. David Wharekura

    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.

  44. JayJay

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

  45. Drew

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

  46. Badcop666

    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

  47. Walter Scheer

    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

  48. Vivienne

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

    Thanks so much – great thread!
    Vivien

  50. gerard

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *