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);
}
?>
Friday, June 12, 2009
Parsing street address REDUX
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.
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
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
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?
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);
}
?>
Thursday, May 28, 2009
Fixing the address search glitch.
So, no sooner had I gotten the newest version of my ISU browser map published than Chance noticed a major flaw in the way that it was setup. It was a flaw I knew about – but I had hoped that he wouldn’t notice it before I had a chance to fix it at least, but of course he did.
In all previous versions of the map that had an address search function, I was pulling the address information for matching with user input from the County’s SITUS database table. The thought behind this is that SITUS is more complete, and sometimes has more addresses than the city does, so in using it I would have a higher probability of matching naive user input.
Because I was using the SITUS database, this had to be linked the existing key value (ACCOUNT NUMBER) somehow – and that somehow ends up being the Kitsap County parcel number (or the RPACCT_ID). And because there are flaws in going through the parcel number (which is much less specific) when very specific user data is already available (specific addresses) the approach is flawed.
What the map was doing was taking the first account it found that shared a parcel with the address that was entered. Now for residential parcels that’s fine – one address one parcel is generally the rule. But, for lots of commercial properties and a few residentials that have multiple addresses on a parcel, selection specificity was being lost, and sometimes the first account that was selected wasn’t the one that we wanted (and wasn’t the address that was enetered – each of our billing accounts have an address of their own too).
The solution was to create a new table that had the addresses (parsed into street number and street name) for each of the locations in the city. This table was something I created many months ago – and I used it here to great advantage, happy for every minute that I spent on that painstaking project. Then I linked that table with a left inner join to only have available in that table (addresses) the rows that shared an account number with the ISU table – that way we know the account we’re getting from the address table is THE right account – rather than the fireline account at the same location. The fireline account wouldn’t be selected now because it wouldn’t appear in the new addresses table because the left join removed all the entries for the fireline account because that account’s account number doesn’t appear in the ISU table (I didn’t assign that account any ISU polygons, essentially). So, now I have this table assigning account numbers to the php script when the address is entered, and as far as I can tell it works perfectly. My original concern – that we would miss addresses by using this table – was not incorrect (we will miss them) but it wasn’t pertinent precisely because if there is an address (and account) that WE don’t have, I wouldn’t have assigned it any ISU polygons, so the fact that I can assign it with SITUS isn’t useful because the accounts that were missed (comparing SITUS vs BILLING ADDRESSES) wouldn’t ever have polygons assigned to them by virtue of the fact that they were missed. Once I figured that out, I felt fine implementing this new address solution, and its working great.
Just in case though, I set the PHP script to first query the new address table, and if that table didn’t have any matches to then search the SITUS table. It didn’t hurt anything, and it might provide functionality at a later date if we do need to link something out of the SITUS table.
Here’s the newest version:
http://gisfbdev.ci.bremerton.wa.us/imagery/kmlmap6.1.SITUS.php
I’d show a screenshot, but these changes were all fixes in the innards of the program – not cosmetic changes so the screen shot wouldn’t look any better than the last time I showed what the program looked like.
You’ll also notice that the hosting address of this page is different. I’ve been developing this on the City’s webserver that has been set up for me for this purpose. It makes some thing a lot harder, but on the other hand, it keeps the peace around here – and it does make more sense for the city to be hosting its own solutions, so I am happy to comply as long as it doesn’t interfere with the development that I need to do.
Plus, I got to learn administration on a LAMP server, which is awesome (I want one!). I might have to set up a computer at home that can serve a new web page for me perhaps.
Peace,
Dom