Australian Postcode database

In a previous posting, I demonstrated some code which used with the auto completion “app” on the Australia Post website to verify or validate a postcode “locality” and “state”. I also outlined why this approach should not be used for anything which requires a reliable solution.

In this posting I’m going to show a more reliable postcode verification system using MySQL, Php and the data publically available postcode data, which at the time of writing, which can be downloaded from Australia Post’s website, here

Note.My application of this process is for use with VirtueMart, a ecommerce system for Joomla, hence the database names used. Bu these can be changed as long as all references in PHP code are also changed.

Using PhpMyAdmin to administer the database, the first task is to create a new table in the MySQL database to hold the postcode data.

DROP TABLE IF EXISTS `jos_aus_postcode_db`;
CREATE TABLE IF NOT EXISTS `jos_aus_postcode_db` (
  `Pcode` VARCHAR(4) NOT NULL,
  `Locality` VARCHAR(45) NOT NULL,
  `State` VARCHAR(4) NOT NULL,
  `Comments` VARCHAR(45) NOT NULL,
  `DeliveryOffice` VARCHAR(45) NOT NULL,
  `PresortIndicator` VARCHAR(45) NOT NULL,
  `ParcelZone` VARCHAR(45) NOT NULL,
  `BSPnumber` VARCHAR(45) NOT NULL,
  `BSPname` VARCHAR(45) NOT NULL,
  `Category` VARCHAR(45) NOT NULL,
  PRIMARY KEY  (`Pcode`,`Locality`,`State`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I’m using the table name “jos_aus_postcode_db” as this table is used by Joomla / VirtueMart.
The field names correspond to the column titles in the CSV file.
Field size is fairly arbitary. The maximum length of “Locality” in the June dataset appears to be 35 characters, most other fields seem to be exactly 30 characters.

Note. The primary key needs to be the combination of postcode, Locality and State as there are some localities with the same postcode which appear in 2 different states !

In PhpMyAdmin, select the database for the new table, then select SQL.
Paste the query below into the box and press Go!

You should now have an empty table into which you can import the data.

The next step is to import the data. In PhpMyAdmin, select the table that has just been created, and select import.
In the “Format of imported file” panel, select CSV.
In the options: –
Set
Fields terminated by to , (comma)
All other options can be left in thier default values.

Then in the “File to import” panel, browse for the CSV file that has been extracted from the zip file downloaded from AusPost, and import the file.
This process may take some time depending on the speed of your MySQL server.

You should now have a table populated with the postcode data.
Note. The first row contains column headers, which should be deleted in PhpMyAdmin. The first row can also be deleted from the CSV before upload. It doesn’t really make much difference as the table doesn’t have a separate ID field as a primary key.

I’ve written a simple PHP function to query the database and return true or false depending on whether the postcode, city and state are correct.
Note. You need to change the name of your MySQL server, the username and also the password to match those of your database and server.

public function validate_postcode_localDB($postcode,$city=null,$stateCode=null) 
	{
 
		$myDBServer='localhost';
		$myDatabase='YOUR DATABASE NAME GOES HERE';
		$myDBUsername='YOUR DATABASE USER NAME GOES HERE';
		$myDBPassword='YOUR DATABASE PASSWORD GOES HERE';
 
		$num=0; // Initialise Number of records found that match this pattern
 
		$sqlquery = "SELECT * FROM jos_aus_postcode_db WHERE Pcode = '$postcode'";
		if ($city!==null)
		{
			$sqlquery .= " AND Locality = '$city'";
		}
		if ($stateCode!==null)
		{
			$sqlquery .= " AND State = '$stateCode'";
		}
		// we connect to example.com and port 3307
		$link = mysql_connect($myDBServer, $myDBUsername, $myDBPassword);
		if (!$link) {
			die('Could not connect: ' . mysql_error());
		}
		$res = mysql_db_query($myDatabase,$sqlquery,$link);
		if ($res) 
		{
			$num = mysql_num_rows($res);
		}
		mysql_close($link);
 
		if ($num==0)
		{
			return false;
		}
 
		return true;
	}

To test the function use this code

<?php 
	$postcode = "3000";
	$city="Melbourne";
	$stateCode="VIC";
 
	$start = microtime(true);
	echo '<html><body>';
	echo validate_postcode_online($postcode,$city,$stateCode);
 
	$end = microtime(true);
	echo '<br/>In '. ($end - $start).' secs';
	echo '</body><html>';
?>

Note.
This will display “1” if the postcode, city and state are valid and correspond, because validate_postcode_online returns true or false, and PHP shows 1 if you echo true.
PHP doesn’ts display anything if you echo false;