Roll Your Own Province

Our work at Reinvented with Elections PEI and, before that, the provincial government has afforded me an intimate relationship with the province’s system of civic addressing.

In a unusual show of openness, the province makes its raw civic address data freely downloadable. You can always grab the latest copy of the 65,000 addresses in the province, along with the latitude and longitude of each one (there’s no personally identifying information in the data — it’s just street number, street name, community, county and coordinates).

As you might imagine, this is not only a Good Thing, but it has the power to enable all sorts of interesting applications based around the data.

To help push this sort of application development along, I’m releasing some open source scripts that make using the data easier.

The first of these is a PHP script that automates the process of downloading the civic address data into a MySQL database. If you’ve got access to a machine with PHP and MySQL, you can grab the script and have your own copy of the civic address database running in a few minutes.

The second script uses the excellent JavaScript code to enable a PHP-based AJAX civic address lookup that looks like this:

Civic Address Lookup Screen Snip

The idea is that, like Google Suggest, as you type the address possible matches for what you’ve typed are displayed, and you can select one of these with your mouse or with arrow keys and the TAB key.

The script doesn’t actually do anything with the data once you’ve selected an address — there’s where you fill in your own magic.

Both scripts are have been only lightly tested. There’s complete information in the Rukapedia (data import \| AJAX lookup) about how to install and use them. Both are released under the GNU Public License.

Comments and suggestions welcome.


Gordon Pierce's picture
Gordon Pierce on November 9, 2005 - 02:47 Permalink

Peter, your code never ceases to amaze me for its clarity and usefulness. I adapted your example to a similar system running under Windows… a bit of SQL query got the PEI Gov data into MySQL (no easy equivalent for the “cat” command) and after that it all worked as you had documented. I’m making babysteps with PHP, so your site is worth its weight in gold.

Ron's picture
Ron on November 24, 2005 - 22:10 Permalink

Nice project, one tweak here.
In the file PEICivicAddressLookupAJAX.php it would be a good idea to use the $mysql_table variable already defined, so you can use any table this snipet:

$query = “SELECT street_no,street_nm,comm_nm from “.$mysql_table.” where

- Ron

Ron's picture
Ron on November 24, 2005 - 23:35 Permalink

Here’s a little script to just print out the lookup.
Copy it as lookup.php
- Ron

<title>Civic Address Lookup Output Test</title>
<link rel=”stylesheet” type=”text/css” href=”PEICivicAddressLookup.css”>

<h1>PEI Civic Address Lookup</h1>

<h2>Here is the address you selected:</h2>

<font face=”Arial”>Back to Main Page</font>