Noodles’ Blog

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

thanks for your efforts, very useful

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

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.

Fantastic! Thanks for this, saved tonnes of time!

Brilliant!
You are a legend

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

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

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

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.

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

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

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

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

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.

Thanks for all your hard work.

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

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

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

Awesome. Thanks so much for your dedication.

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.

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

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

Cheers

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

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

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

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!

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

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

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

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!

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

Cheers
Chris T

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!

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

– Rachel

Hi Noodle

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

cheers

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

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

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.

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.

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.

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

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?

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?

All great work. NZ post don’t make it easy to extract the data. The PDFs ( from NZ post) can be by Region but you have not added it as a field. This would be the gold, as most reporting is done by region. Are you able to add this field to the data?

This is fantastic, and I really appreciate your efforts. The postcodes are one peice of the puzzle for a project I am working on, the other being SNZ mesh blocks. Are you able, or do you know anywhere, where these two have been aligned?

Hey, how current is this postcode list?

Just dropping a note of thanks, awesome to see helpful projects like this, keep up the good work.

Thanks for your efforts, appreciate it

This is just what I was hoping NZPost would provide when they introduced the new system. Since then I have been putting off the evil day when I would have to do it myself.
My interest is I’ve set up databases for a number of not-for-profit groups to which I belong, with a button to look up the Postal code and insert it after they have typed in the address of a member.
To use this data in the same way, would I need to pay and to whom and what about NZ Post copyright. I don’t want any suprises down the track. So I’m asking before starting what will still be quite a lot of work

Thanks very much for the DB. It really helped a lot!!!!!!!!!!!!
You are a legend mate!

Is there a way to determine the region by postcode e.g starting with a particular digit?