Friday, June 12, 2009

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);
}
?>



Would you like to know more?...

Thursday, June 11, 2009

My Impervious Surface Unit mapping utility now selects other ISUs based on distance!


Well, I did manage to integrate that new search/distance function into the program. You can see the function I used in the previous post. This function allows you to compute the distance between two points with WGS 1984 coordinates (the kind you get from your GPS device, i.e. 47.854N -122.458E). What it outputs is the distance in miles.


So, for the program I just told it to do a search between two points, the first point being the center point for a particular parcel, and the second points being an array of data that contained all of the center points for all of the other properties in Bremerton. So now, when I perform a query, the computer not only plots the polygon that I asked it to show: but also the polygons for all properties within .05 miles (about 300ft). This is why you can see in the image below that the impervious surfaces displayed are in a rough circle around the selected property. I wanted to differentiate between the two, so I made the highlighted property colored and the rest of the properties grayish.

There is variance in the color of the grayed properties: some are darker than others. This is because some ISU’s have more than one match, so they get colored twice and are therefore darker. I need to go back and clean up the duplicates to correct for this, or make the matching direct instead of joined. The reason that some parcels that should be there around the edge are missing is because sometimes the center point of the ISU in question doesn’t lay within the search criteria, and so that ISU doesn’t get selected.

I used arcMAP to determine the center point of each parcel. To do this, I just added two fields to the ISU attribute table CenterX and CenterY. These had properties of double (15,10), which gave me lossless entry of all of the coordinate data. I then used the field geometry tool, and selected x-coordinate of centroid and y-coordinate of centroid and entered them into the appropriate fields, saved and reuploaded to the MySQL database for use.

Even with all this new programming, the whole utility still runs fast. Indexing the columns has improved the speed of the program immensely. Indexing doesn't help with the new function though, since the calculations have to be performed for each center point in the dataset each time a new ISU is selected. But, not only does the distance calculating formula work, but I was able to successfully use it to create a new function within the program I was creating. Highly recommended.

The newest version of the utility also includes links to the parcel owner / property information within the Kitsap County database. Also, each of the adjacent properties have the option of being selected to become the new center property (by recentering), which allows you to find details about adjacent accounts pretty easily. This added functionality is going to make this a real slam-dunk for Utility Billing if they can find a way to use it in time.

Dom

Here was the actual SQL statement I used to find the adjacent parcels:


$querycenter = "SELECT AccountNum,((ACOS(SIN($lat * PI() / 180) * SIN(tbl_ISU.Centery * PI() / 180) + COS($lat * PI() / 180) * COS(tbl_ISU.Centery * PI() / 180) * COS(($lon - tbl_ISU.Centerx) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM tbl_ISU having distance<= '.05' ORDER BY distance limit 0,500";
$resultcenter = mysql_query($querycenter);


The limit is just so that the thing doesn't timeout if it finds too many properties.

Would you like to know more?...

Wednesday, June 10, 2009

Calculate Distance In Mysql with Latitude and Longitude

So you have a whole table full of members or places with latitude and longitude's associated with them. Just replace the $lat and $lon with the center point you want to find distances from. You can also change the distance<=10 to a number you want to search from. This will limit your results to all results that are under 10 miles from the starting point


SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM members HAVING distance<='10' ORDER BY distance ASC


Copied from: HERE

Would you like to know more?...

Tuesday, June 9, 2009

Using OpenLaszlo to create dynamic, feature-rich GIS content

Open Laszlo is a program on the market that I’ve heard about for the last six months or so, but haven’t taken the time to really investigate. I must say though, nothing has piqued my curiosity as well, and I am eager to start using it to see if making interactive content is quite as easy as it appears on the demo at their site. (http://www.openlaszlo.org/)



I am interested in learning to use this new internet platform for some development work: it seems like something that has the ability to be taken anywhere, and there’s a lot of places that I want to go.

After watching the demo yesterday, learning this system got bumped to the top of the list: I’m excited to see what I can do, and how hard/easy it is to implement my ideas using OpenLaszlo.

Dom

Would you like to know more?...

Monday, June 8, 2009

Combine 2 tables in mySQL

I needed to combine two tables that were linked by the same primary key (I had added centerpoint data and needed to add it to the pre-existing table, but didn't want to drop/readd the table because it had been updated with new data as well) I used the following SQL query:


CREATE TABLE /*! IF NOT EXISTS */ tbl_ISU SELECT * FROM tbl_ISUs inner join Coords ON tbl_ISUs.FID_Imperv = Coords.FID;

It worked like a charm.

Pretty cool huh?

Would you like to know more?...

Monday, June 1, 2009

Using PhP to parse some addresses

I wrote a PhP script to parse addresses that were coming into me in a format that was less than manageable. The format below was what the addresses looked like:

Bay DR W, 364

The Php took that and parsed that into street name, street number, street indicator and direction. It created a new table and put the parsed values into that table, which I then exported and used to match up with the Kitsap County SITUS database to verify the addresses that I was getting out (the Kitsap County SITUS is similarly parsed to the new table that I created).



The script I wrote is below: The basic logic is this: take everything that is to the right of the comma and call it the street number. Then, take what is left, and see if any of it matches either a road indicator or a direction. If it does - put it in the proper field, and then take everything that is left over and call that the street name. It worked for 98% of the addresses I was using - and the atypical ones had been entered wrong, and were subsequently corrected.



//php code written by Dominic Ebacher, May 2009
// Opens a connection to a MySQL server.
$username="name";
$password="pw";
$database="db_POAddress";
$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_Addresses_Parsed";
$result = mysql_query($query0);

$query1 ="CREATE TABLE tbl_Addresses_Parsed
(
id MEDIUMINT(5) NOT NULL AUTO_INCREMENT,
ST_NO integer,
ST_NAME text,
DIRECTION text,
INDICATOR text,
TYPE text,
SERVICE text,
NO_UNITS varchar(5),
NOTES text,
PRIMARY KEY (id)
)";
$result = mysql_query($query1);

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

$FullAddress= $row['Address'];
$AddressArray=explode(",", $FullAddress);
$number =$AddressArray[1];
$street =$AddressArray[0];
$type=$row['Type'];
$service=$row['Service'];
$unit=$row['NO_Units'];
$notes=$row['Notes'];
$StreetArray=explode(" ", $street);

$numPts = sizeof($StreetArray);


for ($p = 0; $p <= $numPts - 1; $p++) {


if ($StreetArray[$p]=='ST') {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]=='WAY') {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'PL' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'DR' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'AVE' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'CT' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'LN' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'RD' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'PKWY' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'BLVD' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'TERRACE' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'HWY' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'RT' ) {$indicator=$StreetArray[$p]; unset($StreetArray[$p]);}


if ($StreetArray[$p]== 'N' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'S' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'E' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'W' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'NW' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'NE' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'SW' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'SE' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'n' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 's' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'e' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'w' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'nw' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'ne' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'sw' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}
if ( $StreetArray[$p]== 'se' ) {$directional=$StreetArray[$p]; unset($StreetArray[$p]);}

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

$numbera[]=$number;
$streetnamea[]=$streetname;
$directionala[]=$directional;
$indicatora[]=$indicator;
$servicea[]=$service;
$notesa[]=$notes;
$typea[]=$type;
$unita[]=$unit;
unset($directional);
unset($type);
unset($unit);
unset($notes);
unset($service);
unset($indicator);
unset($number);
unset($streetname);
//$query2 ="Insert into tbl_Addresses_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_Addresses_Parsed (ST_NO,ST_NAME,DIRECTION,INDICATOR,TYPE,SERVICE,NO_UNITS,NOTES) VALUES ('$numbera[$p]','$streetnamea[$p]','$directionala[$p]','$indicatora[$p]','$typea[$p]','$servicea[$p]','$unita[$p]','$notesa[$p]')";
$result = mysql_query($query);
}
?>

Would you like to know more?...