Parsing street address REDUX

Well, I got a chance to use the program that I created for Stuart over that the Kitsap Health district again. He received another set of data, addresses again that he needed account information for. This time the data he received was from WestSound, a utilities provider outside of the city of Port Orchard.



The table I received had two columns, house numbers and street names (without any directionals this time, though).

Parsing the street names was easy enough using the following PHP code that I’ve included at the end of this note. Then, I took the data into ACCESS (after exporting it from MySQL) and matched it back up with the original table (which didn’t have key values or anything) and sent it back to Stuart. So, from a short list of incomplete addresses I was able to match 97% of them to a parcel number: which is pretty good.

Enjoy the PhP script below. Basically it just takes the data from the original table in, parses the street column into two new columns and spits the result back into a new table. Its pretty simple, but perhaps useful for someone.

Dominic



//php code written by Dominic Ebacher, May 2009
// Opens a connection to a MySQL server.
$username="ENTERUSER";
$password="ENTERPW";
$database="ENTERDB";
$connection = mysql_connect ('localhost', $username, $password);
if (!$connection) {
die('Not connected : ' . mysql_error());
}
// Sets the active MySQL database. You can't select a database unless the connection is open, can you?
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
die('Can\'t use db : ' . mysql_error());
}

$query0 ="drop TABLE tbl_WestSound_Parsed";
$result = mysql_query($query0);

$query1 ="CREATE TABLE tbl_WestSound_Parsed
(
ST_NO integer,
ST_NAME text,
INDICATOR text
)";
$result = mysql_query($query1);

$query = "SELECT * FROM tbl_Westsound";
$result = mysql_query($query);
if (!$result){ die('Invalid query: ' . mysql_error()); }
while ($row = @mysql_fetch_array($result)){

$FullAddress= $row['ST_NAME'];
$AddressArray=explode(" ", $FullAddress);
$numparts=sizeof($AddressArray);

$numlast=$numparts-1;
$st_no=$row['ST_NO'];
//echo $indicator;
if ($numlast<>'0'){
$indicator=$AddressArray[$numlast];
unset ($AddressArray[$numlast]);
}


$streetname=implode(" ",$AddressArray);
//echo $streetname;
$o=" ";
//echo "st no:";
//echo $number;
//echo "st Direction:";
//echo $directional;
//echo "St Name:";
//echo $streetname;
//echo "Indicator:";
//echo $indicator;
$foo=$st_no.$o.$streetname.$o.$indicator;
//echo $foo;

$numbera[]=$st_no;
$streetnamea[]=$streetname;
$indicatora[]=$indicator;

unset($indicator);
unset($st_no);
unset($streetname);
//$query2 ="Insert into tbl_WestSound_Parsed (ST_NO,ST_NAME,DIRECTION,INDICATOR) VALUES ('$number','$streetname','$directional','$indicator')";
//$result = mysql_query($query2);

echo "
";

}
echo "end";
$numEnt=sizeof($streetnamea);

for ($p = 0; $p <= $numEnt - 1; $p++) {
$query ="Insert into tbl_WestSound_Parsed (ST_NO,ST_NAME,INDICATOR) VALUES ('$numbera[$p]','$streetnamea[$p]','$indicatora[$p]')";
$result = mysql_query($query);
}
?>