Difference between revisions of "Downloading PEI Civic Address Data"

Revision as of 20:58, 7 November 2005

About the Civic Address System

In recent years, Prince Edward Island has moved to a uniform system of civic addresses, where each property in the province is assigned a unique civic address. Originally intended to enable the 911 emergency system, the civic addressing system, because it ties civic address data to latitude and longitude geolocation information, has tremendous utility for other applications.

The province's Address Locator page links to several web-based applications for looking up civic addresses. In addition, raw civic data is available from a form-based data download page.

Documented here is a PHP script that enables the automatic creation of a MySQL database and download and import of current civic address data. The system was created to enable the PEI Civic Address Lookup with AJAX application, but can be used to drive any application that relies upon having access to digital Prince Edward Island address information.

What the Script Does

The PHP script connects to the civic address data download page and grabs the latest data for the three counties of Prince Edward Island as tab-delimited ASCII text files. These are then concatenated into one large text file and imported into a MySQL table. If the table already exists, it is emptied before import; if the table doesn't exist, then it is created, along with associated indices.

System Requirements

The script was created on a Linux system, and has only been tested under Linux; it may work, with modifications, under other operating systems.

• MySQL
• PHP (you don't need a webserver, just the command-line PHP will do)
• GNU Wget (used to download the data)

Download or Browse the Source Code

The source code is kept in a Subversion repository:

• Browse the Repository
• Via HTTP: http://svn.reinvented.net/PEICivicAddressData
• Via SVN: svn://svn.reinvented.net/PEICivicAddressData

Install and Run

1. Create a new database, or select an existing one, to hold the civic address data.
2. Edit the file settings/PEICivicAddressData.inc, adding your MySQL username and password, and selecting the database name and table name for the civic address data (these default to 'civicaddress', so the assumption is that you have a database called 'civicaddress').
3. Also check the settings for the location of 'wget' and 'cat' on your host (hint: whereis wget and whereis cut will help).
4. Set the $DEBUG variable to 1 to see copious debugging message the first time through (you can set it to 0 later, when you're sure everything is working okay).
5. php ./dataimport/PEICivicAddressDataImport.php

If everything worked okay, you should see debugging messages, finishing with Total of X addresses now in the table. where X is in the range of 65,000 addresses.

Testing

Fire up your favourite MySQL client, point it at the database you configured, and run some sample queries:

• Look up 95 Rochford St
  • select * from civicaddress where street_no = 95 and street_nm = 'ROCHFORD ST';
• Count the addresses in Montague
  • select count(*) from civicaddress where comm_nm = 'Montague';
• Count the addresses in each civic address community:
  • select comm_nm,count(*) from civicaddress group by comm_nm;
• Find the 10 closest addresses to 95 Rochford St using simple Pythagorean theorem calculation:
  • SELECT c1.street_no,c1.street_nm,c1.comm_nm,sqrt(power((c1.latitude - (c2.latitude)),2) + power((c1.longitude-(c2.longitude)),2)) as dist from civicaddress c1,civicaddress c2 where (c2.street_no = 95) and (c2.street_nm = 'Rochford St') order by dist limit 10;

Reference Materials

• Prince Edward Island Civic Address Standards and Guidelines