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
Tuesday, May 26, 2009
Creating Infowindow Boxes for Displayed Polygons
Surface type: ";
So, I wanted to construct an infobox for each of the polygons that come up in my impervious surfaces browser. Originally, the php script that I wrote was using the DOM to create a kml file, but I adapted the code into the following, which creates the HTML for a little text box that I pop up:
PhP:
$pound='#';
$parcelLink1='Kitsap County GIS';
$parcelLink=$parcelLink1.$parcelLink2.$parcelLink3;
$parcelLink11='Kitsap County GIS';
$parcelLink10=$parcelLink11.$parcelLink12.$parcelLink13;
$textbox0="
$textbox1=$row['Surface_Ty'];
$textbox2="
Surface Area (sqft): ";
$textbox3=intval($row['Area']);
$textbox3a=" Sqft";
$textbox4="
Impervious Surface Units: ";
$textbox5=((intval($row['ISU']*100))/100);
$textbox6="
Assigned Account: ";
$textbox7=$row['AccountNum'];
$textbox8="
Assigned Address: ";
$textbox9=$row['Address_fr'];
$textbox10="
Total Area for this account: ";
$textbox11=$TotalAreaNew;
$textbox12=" Sqft";
$textbox13="
Total ISUs for this account: ";
$textbox14=$TotalIsuNew;
$textbox15="
Parcel Information: ";
$textbox16=$parcelLink;
$textbox17="
Owner Information: ";
$textbox18=$parcelLink10;
$textbox19="
$textbox=$textbox0.$textbox1.$textbox2.$textbox3.$textbox3a.$textbox4.$textbox5.$textbox6.$textbox7.$textbox8.$textbox9.$textbox10.$textbox11.$textbox12.$textbox13.$textbox14.$textbox15.$textbox16.$textbox17.$textbox18.$textbox19;
Then, later, I construct a textbox when I plot the polygon and assign a listener that knows to open the new infowindow when any of the polygons I just drew are clicked on. Assigning the listener was actually the hardest part about making the new infowindow boxes – I was thinking that I needed to assign the listener to create the window on each polygon, when in actuality I just needed to assign the listener to the main window (because you can only have one infowindow at a time) and give the listener some coordinates at which to open the new infowindow (the coordinates, in case you are interested, are the coordinates at the center of each polygon).
So the Php script takes the encoded OGC geometry objects for the polygons that are selected from my user’s query. It parses those polygons out into the corrdinates and reassembles them into a form that is inputtable into new gpolygon() as an array of points. Then it assigns a listener to that polygon which tells the map that if that polygon is clicked on – display all of the information about the polygon (as parsed and created by the script earlier) in a data infowindow that is cenetered on the center coordinates of the polygon.
And it works.
Here’s the newest version:
http://freeforthepicking.com/imagery/kmlmap5.5.SITUS.php
If you want to try it out – enter account number 1101850 as an example or try a street address of 3027 olympus
Dominic
Friday, May 15, 2009
GPolygon() solves the ggeoxml() innaccuracy issue for house-sized polygons
Well, today I was able to get my real impervious surfaces to show up the way that I wanted.
Originally, I built a PhP utility that created a KML file for a requested property. But, using GgeoXML to display that KML caused the polygons to be simplified – and this simplification was enough (at the scale of buildings that I am working with) to significantly distort the reliability of the data.
So, I had to go back to the drawing board.
I ended up writing a very similar PhP utility that uses the Gpolygon overlay class. So now, each of the polygons I had originally created and stuck in a KML is just displayed directly in the API after I’ve used PhP to correctly format the Glatlong inputs into the Gpolygon() class.
But, the end result is that I have much more polygons being displayed – the polygons are exactly the same ones that I created in the original shp file that I was working with.
Building this map was actually much easier than learning DOM architecture to create the KML-based map that I made first – I can see why folks recommended I go this way originally; on the other hand, I learned a lot: and that’s worth the circuitous route for me: the scenery was more memorable.
Here’s an example polygon loaded from a KML file and displayed using Ggeoxml service class to retrieve the data:
http://freeforthepicking.com/imagery/kmlmap5.A.SITUS.php?q=3101600&Submit=Search&r=&s=
And here’s the same set of polygons using the Gpolygon() overlay class:
http://freeforthepicking.com/imagery/kmlmap5.3.SITUS.php?q=3101600&Submit=Search&r=&s=
As you can see, the true shape of the objects is reflected in the second, but not the first map.
Thanks for everyone’s help: next challenge is getting the info windows working for the polygons – but its always something! I feel empowered to do everything I can think of now, but I wouldn’t be able to do that without the help of the folks in this group, so thank you everyone.
Dr. Dominic Ebacher
Ebacherdom.blogspot.com
Wednesday, May 6, 2009
To transform Coordinates in a SHP file - use Project Tool in ESRI ArcView
I was having a great deal of trouble with projected coordinate systems. Our workplaces uses data that is created using a stateplane projection, but the google maps API wants data in the form of WGS1983 format. For the longest time I didn’t know how to get over this hurdle. I could convert the shapefiles using shp2mysql.exe but shp2mysql wouldn’t change the projection and the polygons wouldn’t work.
The answer was simple: a tool in ESRI’s ArcView called Project (management). I searched all over looking for transform or coordinates or something similar: but Project is the tool you’d like to use: it allows you transform any source data into another coordinate system or projection including datum and spheroids.
And now the data can fly back and forth every whichway.
Dom
Saturday, May 2, 2009
Selecting features in a Spatial way using MySQL
There is really no limit to what I can do with HTML, PhP, GIS and MySQL any longer. It was a sharp learning curve, and it remains a very brisk uphill walk against the grade: but it is possible, everything I possible. When I think of a problem now, or something I want to do and give more utility to the operation of the program – I have to spend some time thinking about how I want to approach it, but I CAN approach it.
I don’t know what I thought – but I always thought that programming would be harder than it is. Here I’m referring to PhP, but this could b talking about any kind of programming – I thought it would b monumentally difficult. When I was younger, I wrote program in Visual Basic to help my kid sister learn to multiply numbers together: it was a pretty great program, very simple but it did gie me the chance to learn programming. Somehow along the way I convinced myself that using any other language was going to be an insurmountable task. Well, now that I’m here, and I’m using PhP and the variables are flying and my arrays are being filled and unfilled I have come to the realization that it isn’t any harder than visual basic was: and in a lot of ways it was much, much easier. I don’t just mean that it was easier to learn because I already had that experience with visual basic as a kid either: that is certainly and obviously true as well. What I mean to say is that independent of what I knew before: learning a newer programming language and working with it in a production environment today is easier than it was back with the 8088 and other obsolete equipment I had to use back in the day. On the other hand, without that obsolete equipment, I wouldn’t have been able to do anything at all. I only wish I had tried to learn some other language sooner – I feel like the possibilities of what I can do are so limitless that I’m disappointed I didn’t know about their existence sooner so I could have been pursuing them for longer at this time in my life rather than just getting started with them.
Right now, I’m still adding some features and functionality to the script I wrote for Utility Billing to allow them to pull Imperious Surfaces up. What I am adding for them is the ability to see the parcel boundaries and not just the impervious surfaces in the area they are looking at. In addition, I want to add some functionality so that they can see the impervious surfaces for properties adjacent to the ones they searched for. I plan on having those two changes implemented by the end of this next week. I already have the geometries for all of those features stored in my geodatabase, which is always a fun challenge: now I just have to get them to display appropriately.
The larger challenge, and the more interesting one is that I want the parcel boundaries to display, but not all of them and not all the time. By not all of them, I mean that my users have searched for the imperious surfaces of a particular parcel: and they want to see the parcel boundaries, but they are zoomed in so far that they can’t see many properties: maybe 100 at zoom level 18. So, I need to figure out a functional way to select only those parcel boundaries that are within the present field of view. The reason this is interesting is because I am going to have to find a spatial function of MySQL, and I like figuring out the ways that that RDBMS is equipped to deal with spatial data. I think I should be able to find a way to make it happen, such that when a property shows up, all of the parcel boundaries that are within say ¼ mile are selected and made available to display as well. What I don’t know is how much processor power is going to be eaten up doing that SELECT. Perhaps if it eats up too much processor power, and therefore requires too much time, I’ll have to find another solution. But the cool thing here is that I’m not simply selecting all of the parcels – or even just the one parcel I’m searching for (I’ve done both of those things) but SELECTING a subset of parcels based on their location attributes using MySQL’s spatial engine and all of this Geo-relational magic is happening independent of ArcView or any other ESRI product.
I’ll keep you posted on how it turns out.
Dom
Monday, April 27, 2009
Tutorial: How to convert or import your MS access data into MySQL
Want to convert or import your MS access data into MySQL: here's my tutorial:
If you don’t already have it downloaded and installed, do that:
Download and install the program from intelligent converters:
http://www.convert-in.com/acc2sql.htm
Now, using that program (access to mysql 4.3) export the access database of your choice:
Screen 1: store to dump file
Screen 2: defaults are fine
Screen 3: all blank fine
Screen 4: enter your MS access database information, add location for the dump file to create with a .sql extension
Screen 5 select the table you want to export, and click next
Screen 6: click finish
Now, you’ve got your dump file. Zip it so that it takes not as long to transfer.
Okay, now load up your phpmyadmin.
Select the database you want to upload to in the left side of the screen
On the left screen, hit the sql button over the databases without
Hit import file:
Select the zipped file that you created, hit go.
Now, your table will be created, and all you have to do is fill it.
How to convert access data into MYSQL:
Open db in microsoft access:
Select table you want to export.
Click on export
Save file with extension .csv and type as “text”
When new window opens, choose delimited (it will be chosen normally)
Hit next
In this window: choose comma as the delimiter between your fields
Save the column titles NOT in the first row (unchecked).
When the file is saved, zip it to speed up transfer (don’t worry – the database can read zipped files)
Now, get that data into the database.
Reload phpmyadmin, select the database and table you’re working with (if you forget to select a table this wont work)
On the right screen, hit the import button.
On the filetype menu at the bottom right, choose CSV
Choose fields Delimited by as comma, to match what we exported the files as from access.
Select the zip file you created for the data export
And hit go.
If everything worked: you will see a screen that says “however many files added”
Go back and delete the 5 records that were created by the original table creating program and call it good – you’ve successfully transferred one table from MS access into MYSQL.
Friday, April 10, 2009
Creating KML from PHP and displaying in Google Maps API
The original solution I tried was simply displaying a KML overlay of the impervious surfaces on top of the google map aerial imagery. This doesn't work well for any number of reasons. First, the KMLs get too large for google to display with 1000's of entries. Google resents having such large datasets and doesn't allow you to load the pop-up windows that I programmed into the KML (within ARCview) consistently for these large datasets. In addition, having such a large dataset is an overload of information: the customers using this system don't want to know about EVERY impervious surface in the city, so why am I bothering to show them all? The answer is that the KML-based solution is limiting. Its a great first step for displaying datasets, especially points and regions; but when you get down to the nitty gritty of thousands of polygons it ceases being easy/useful and starts becoming a liability.
So, what to do?
Well, the first thing I did was ask the great people out there on the web what they thought I should do. Their answer was to get just the information that people want to them, without displaying everything else. That way, the loading will go quick: be much less intensive, and demanding as far as network resources go as well as help the customer arrive at the specific information they require MUCH quicker than if I made them search around the map themselves looking for their property (and likely learning a new interface at the same time).
Okay, so we're going to have a map that displays imagery (perhaps the custom tiled imagery that I put there and discussed using earlier) and we're going to display just the polygons that the customer wants to see. How do we know what the customer wants? We're going to ask them of course. So our page is going to have another visual component - and that is a couple of search boxes; the first box will be for entering address buttons and the second will be for entering billing account information. NExt to these text input boxes, we're going to have a couple of submit buttons so they can input their query into the system.
The behind the scenes funtion of the website in this case is much more sophisticated than the earlier maps that I created. Those were just javascript, html, and the KML data files. The javascript was in two forms - the javascript which loads and runs the google application itself and the javascript that I composed to toggle the image layers. The KML data files are just a geographic flavor of XML with a bunch of markup to help google display the polyons and telling it how to label them effectively.
The new page is going to have two additional elements - php scripts and a MYSQL database. Php is a scripting/programming language that will allow us to run a query and generate a subsequent XML document that is composed of the information we need to display the correct polygons that our user wanted to look at (based on the query they entered). So, rather than being a static file containing all of the information from ALL of the impervious surfaces - the new website will be displaying temporary, dynamically generated XML document that will be deleted as soon as the page is closed, and contained within that XML will be the polygon information that match the query the user submitted. Great right?
Well, not so fast.
It is that simple as far as function goes, but problems arise. First and foremost of these is the fact that in order to query the database for the polygons to display - the polygons have to be in the database to begin with. How can a shape be in a databse, you ask? Well, because of geometry field types within the MySQL database, of course.
when I am displaying the polygons from the KML files - the geometry of each polygon was entire unknown to me: which is to say I didn't have any idea about how it got there. I'd just export the map from ArcVIEW using their built in shp2kml utility and the kml would appear, complete and when I loaded it all of the polygons would be there. An examination of the components of the KML file show how the polygon information is stored; which is a series of points that begin and end on the same line. There are lots of other attributes for the polygons in there too - opacity, colors, border width, etc.
So, we'll build a database, and in the database will be a cell and in that cell will be the same text that, when placed into the XML document will be the same as that produced by the KML-generating utility. And basically, that's what happens. We have geometry fields within the database program that I use (MySQL), and these field types know how to store the text that I enter into them - furthermore, they know so much about those data types they can convert them effectively and store them appropriately so that when recalled the data comes out as a polygon, which is great. There are two main ways that polygon information is stored, the first is so-called Well-Known-Text formation (WKT), which is a text desription of the polygon something like polygon((1,1 2,2 1,1) polygon X). That's great, easy to understand and 1 and 2 are the apprpriate coordinate pairs as you travel along the outside of the polygon. The other way the data is stored is as Well-Known-Binary (WKB) which is a binary translation of the Well-known-text and gets the information one step closer to machine code. As an aside, MySQL stores the data internally within the database in a format that does not conform to WKT or WKB, but in a third manner entirely, but that is not germane to our processes, because when we call data out of the database, we call using an AsText(field) function that converts the internal information back into WKT, which we can then use for our purposes.
Okay; so spatial data (points, lines, polygons, multipolygons) are capable of being stored in MySQL, but how does it get in there. Well, we could go in and populate the fields manually; its possible. We could generate the polygons ourselves with a GUI (Google has examples of this) and then store them in a geometry field in the MySQL database. But, that doesn't work for us - we're not interested in generating NEW data, we already have all the polygons we need. How do we take the polygons we have, in this case 1000's of them inside a SHP file editable by arcview and get them stuck into the sql database? The shp file doesn't store information in WKT, its not like we can copy them out and ESRI doesn't provide a utility to export SHP file information into a form recognizable for input by MySQL. What to do?
The solution to that problem is to use the shp2mysql utility (available here) that was developed for just this purpose. This utility is itself a slight modification of a similar script shp2PostGreS which imports into another similar database system, Post-GReSSQL. That database system is actually better for storing geographic information, but it isn't offered by a lot of hosts so it isn't as popular, even though it is better and questionably easier to implement. The shp2mysql utility is a command line program that takes in paraments (shape file name, database name, table name) and outputs a SQL script. That SQL script, when loaded in a MySQL database (by copying it into the SQL window, or importing in the PhPMyAdmin GUI, produces a table that contains all of the polygon data from the shp file - including ALL of the feature attributes stored in the .dbf file that is part of the SHP package you were working with.
There you go - a MySQL database filled with all of the feature attributes you were working with in ArcView and their associated attributes from the attribute table you were working on.
YEAH right? Well, that's actually just the easy part.
----------------------------------------------------------------------
Now, we've installed the capability to utilize the polygon information from the shp files we were working with - storing that information instead in a queryable MySQL database that sits on our web server and is happy to spit out just the specific information our customers are looking for.
But we have to program a web page that will do that.
This web page will have all of the same elements from the map page I created earlier - we'll use the google maps API; maybe we'll modify the map to utilize the imagery we have here to display at the higher zoom levels. How do we actually perform the query?
Well, we'll take the text that the user entered in the search boxes and we'll use PHP to submit to the database, querying the appropriate query based on which search box the data was entered into. The data that was entered by the user will become the paramater upon which the query is performed. For accounts the query will look something like this.
qrysql = "Select *, astext(OGC_GEOm) from tblISUs where ISU.Accts = '$querytext'"
The OGC_Geom field is the geometry field where our polygon information resides with the MySQL database, and the Astext() function makes the database spit out the polygon information as WKT, which we can use rather than the MYSQL geometry encoding which we can't use, and which also doesn't look nearly as pretty when seen on the screen.
The database will spit out some WKT, along with a lot of other nonsense (the other attributes for the data). We'll have to process, sort, and display that information with PhP so that what we have at the end is a pretty little XML document which has pop-ups that look just like we want them. I'd speak more on this part, but I haven't done it yet - so my understanding of the process is actually a bit rudimentary at best; but that's bound to change soon enough.
-------------------------------------------------------------------------------
So you have a database, and you want to start pulling individual polygons out of it. I decided to use PhP for that step of this process. Others have used varying solutions. For my project, I wanted to be able to generate documents on-the-fly, rather than just run the script once and be done with it - and PhP is a great workhorse for processing on demand.
The first thing I did was find someone who was doing anything even remotely similar. AT the time, I didn't know about writing PhP and I just wanted an out of the box solution I could copy and be done with. In the end, I had to learn at least a rudimentary amount of PhP to fix things, but it worked out. I worked off of Pamela Fox's tutorial about encoding KML points and lines using PhP. That was all well and good, but I needed to modify it to encode Polygons. I asked around, and a nice fellow named Ralf in Germany gave me some modified code (modified from Pamela's Tutorial) that I started to work with. One problem was that Pamela's code was in PHP 5, and Ralf's was in PhP 4, so I had trouble deciphering that for a while. Eventually I started to realize what was happening in the code - something called the Document Object Model (DOM) was getting referenced a lot, and I started to get the jist of the object oriented programming of the PhP script. I also found a tutorial that did exactly what I wanted to do - namely the author took multipolygons and spit them out as a javascript array; BUT, the program was a one-time program (which he wrote using Ruby) rather than something that my server would allow me to run each time a new account was requested. So, I had to write the PHP by in large mostly myself, but it wasn't as hard as I first imagined.
I have had people question my approach. Why create KML's, they wonder - it seems like a wasted step. Well, for a couple of reasons. The first is that when I started this, I didn't know any other way of getting data displayed in google maps. I know other people build javascript overlays, but I don't know how to do that, and I don't know how they would be formatted. KMLs are a geographically flavored XML document, which means they are very orderly and structured perfectly and consistently - that means that creating them using DOM is a cinch - whereas creating the javascript overlay is not as easy (or at least it wasn't for me). Secondly, I should have an idea of how much the program is being used by how many KML's are getting generated, which is fun - and although there are other ways to record this information, its just one more confirmation that people are using the program like they are supposed to. The KML files which are created are not large - 4-20kb each and even if every polygon is made into a KML, the size will amount to only a trivial amount of storage space.
So, basically our PhP program (LINK) does several things:
1. Request information in the database meeting our search criteria (a well-formed SQL statement)
2. It takes one row of that data at a time and processes it.
2a. It takes the multipolygon object in the OGC_GEOM field, and chops out the beginning part and the end parenthesis
2b. It takes the remaining part (mostly coordinates) and explodes them into an array, separating at commas.
2c. It takes those exploded values, and further separates them into individual coordinates (lats and longs)
2d. It puts the exploded values back together again in the same format they will be displayed in the KML.
3. It formats a complete and well-formed KML document using information within the table
3a. Integrates the coordinates from step (2) into the KML document, along with other attributes from the table.
3b. Formats the text boxes that will pop up when the polygons are clicked in google maps or google earth.
3c. Formats the appearance of the polygons displayed, coloring the polygon and its lines and setting the line width.
4. Saves the formatted KML text as a KML file in its own right in the server.
5. Sends a variable to a google maps Page and integrates the new KML as a geoXML overlay
Here is an example of one of the KML files that is produced from the program:
for loading in google earth
for just the txt of the KML file
AS you can see, apart from being programmed correctly, the KML contains all of the information we had hoped to include about the impervious surface polygon in question, including its area, how many ISU's that area represented, which account it was assigned to and what the address of that account is as well as the TYPE of surface - be it a parking lot or a rooftop.
Getting the formatting to work perfectly was a challenge, but in the end it works perfectly. I will say so far that things look great: the only exception I make to that is that some of the polygons look rather simplified (fewer points than they should have) and I'm looking into why that would be the case.
Okay, so in gettting the information back OUT of mySQL you're going to find you have a bit of a problem. The problem is that shp2mysql encodes the data within the shp files as multipolygons. So, even after you get the PhP to successfully parse everything, there will still be some errors due to that. I solved this problem by simply deleting the holes: that is - when I had a polygon with holes in it, I just let the hole get filled in by deleting the secondary coordinates that dealt with the shape of the hole. My dataset didn't have many holes, so it didn't make much of a problem for me, but I am confident now that one could compose a solution to deal with that problem, but it would take a good deal of work more than I was prepared to devote to it.
The map portion could be just a simple map: I used the google maps API example code and added a toggle button to turn the polygon overlay On and OFF. I also tweaked the controls to look like the google makes default UI.
As soon as I loaded the polygons, I noticed a problem - namely that the polygons that I had so carefully drawn around every impervious surface in the city were in fact not lining up the way they should be. That is, I'd have a building outline drawn, but the building I had drawn it for would be somewhere nearby but not underneath my polygon: or it would be partly underneath, but the borders wouldn't line up. It was very frustrating, to tell you the truth. That was when I knew that I was going to have to have the imagery viewed in the browser window be the same as the imagery that I drew the polygons from. This was a whole separate digression that involved using custom tiles drawn from the georeferenced imagery I was working from using MAPTILER. That program worked like a champ - if you're working form MrSID files like I was, you should give it a try, the program was awesome.
So, in the end I used my custom map tiles as the aerial imagery background that the polygons are displayed over. The images I tiled are not only perfectly matched with te polygons I am working with - but the resolution of the imagery is much higher than what is currently offered by google earth. As a result, the clients using this program are able to see much more of what they want to see, and make the comparisons I wanted them to be able to make.
A working version of what I have is available if you'd like to see it. This page takes account numbers and spits out a google map with the relevant KML loaded in it and colored appropriately. The page also includes a link at the bottom to display another account number's impervious surfaces - that is just for ease of use so that the user doesn't have to leave the page to get to the next surface they want to evaluate.
After this was done, I proceeded to tweak it and add additional functionality. I also wanted to create this tutorial for folks who wanted to do something similar but didn't have the resources to complete the project (as I didn't) without some additional help.
I hope this has been useful. If it has, and you'd like to say "thanks," I'd love to recieve an email.
Tuesday, March 31, 2009
Using MapTiler to Display Aerial Imagery from MrSID files
The map tiler progam is a GUI for the GDAL2 command line utility. I am going to hopefully use it to construct a system of map tiles. Google uses map tiles to display imagery; each section of a picture you look at is one tile, and within that tile are smaller tiles. The power in this tiled architecture is that it allows you to zoom in on a portion of the image and not have to look at all the zoomed in portions of that image. This type of architecture is obviously well-suited for the web; but it takes some doing to get it working.
The problem that I encountered with imagery is that the google imagery that is available for Bremerton isn't sufficient for what I need: that is, the houses and parking lots and other impervious surfaces aren't really available at a viewing zoom that would allow our customers (using a version of the map that I'd be serving) the ability to see their surfaces in the detail that they will want to make an assessment of the correctness of the polygons that I've drawn for them. AT least, I wouldn't be satisfied if I was them. Further zooming doesn't produce fuzzy imagery, it produces no imagery and the minimum zoom level available is too far out for easy discernment of property borders and resolution of other salient features of physical geography.
So, what do we do?
Well, one nice thing about google is that they've got a good bead on a solution to just about any problem that folks have. The solution to this particular problem is custom map tiles. Custom tiles are images that are hosted from someone other than google - and are displayed in the basemap of the image just like the regular satelite imagery. So, for instance you could upload a high-resolution image of a particular rooftop and specify that this tile be displayed for zoom levels 14-16 (the greatest zoom levels, and the point at which the satelite imagery coverage for Bremerton breaks down). This way, when a customer zooms all the way down to their house, instead of seeing a message that no imagery is available they'll see the imagery from the custom tiles that I've put there for them to see. Simple enough huh?
Well, it might be.
The trick is that we have to create the imagery in a way that it is easy for Google to integrate it into their program. That's where this MapTiler program comes in - this program takes high-resolution aerial imagery from a number of different sources and file types and converts it into the same map tiles that google uses to add imagery to its site. EAch of the tiles has to be 256x256 pixels, and there are different tiles for the different zoom levels of imagery you want to display. In addition, the tiles are ordered (either by their attributes or by filename) so that they are easy to assemble - this involves a simple numbering scheme of #1,#2 where #1 is the the vertical row number for the tile, and #2 is the horizontal row number of the tile. A simple 9 tile schema would thus be attributed:
1,1 2,1 3,1
2,1 2,2 2,3
3,1 3,2 3,3
And because we know that each tile is 256 pixels - we know that this little example has the dimensions of 768 x 768 pixels. Now imagine that - but hundred of thousands of pixels down and across and you get the idea of the immensity of scale we're talking about. But, supposedly, maptiler can do it: can take our really big imagery files and chop them up and number them appropriately. ITs supposed to be as easy as running the program and then taking the folder with the files to your hosting server and they should load once you make the appropriate reference calls to load them.
We'll see.
The filetype we're working with in this instance is multiresolution seamless image database files (MrSID). This is a filetype that was developed for Nasa and other organizations to easily display large amounts of high-resolution imagery and is something of an industry standard when it comes to display, recall, and processing of high-resolution imagery for GIS and other applicaitons.
Mr. SID is great - but the MrSID files are single georeferenced orthoimages (or mosaics of single images). They are a picture, and you can hang them in 2D space in the correct place. But, they aren't the flavor, arrangement, or numbering the gooogle maps API needs imagery to be in for seamless display over the web - so we need to process and convert them to get them to look like something google maps will want to display for us.
Hopefully, we'll process the images and get them to be diplayed when we go to the appropriate zoom level in google maps. IF that's the case - we're in like Flynn. There's also another reason I didn't want to use google satellite imagery: our ortho-referenced imagery doesn't quite line up with theirs. The reasons for this are various: most of the difference has to do with the correction of orthagonal and isometric variance due to the reference point of the camera when it took the imagery. Some of the images were stretched just a little and others were zoomed in or out just a bit so they fit with the tiles adjacent to them perfectly. It isn't much - and when you view the complete city of Bremerton aerial mosaic it is presented as a single seamless photograph, with differences that are scarcely noticeable. But; the google imagery isn't corrected or massaged in this manner: the imagery on google is from Satellites, which means that the pictures were taken from straight up - not from an angle or off to hte side (however slight), and that means that all of those corrections that were made represent differences in the imagery between matched-coordinate site comparisons. It isn't something that is easily corrected for - it isn't as if all of the coordinates of our imagery are off to the left 6 pixels: that would be easy to fix. The end result of this is that if I am in ArcGIS and I digitize some Impervious Surface feature from our digital imagery and I take that feature over to google maps and display it in exactly the same place on the map, the feature polygon that I drew doesn't sit exactly on top of the structure I want it to. It overlaps with that structure, and you can tell that the polygon certainly represents that structure and not another one - BUT, the differences between the location of the structures (as well as their orientation and isometric conformation based on the location of the camera) make the polygon look wrong. The last thing I want to do is create a mapping condition that makes people think we measured their Impervious Surfaces incorrectly. I decided a while ago I wouldn't be able to display our polygons over google's imagery correctly: but if I can replace it with the imagery that I worked off of to create the map - the polygons will look correct, AND the imagery will be of a quality and zoomability that will make the map really useful. Its the best solution. The next best solution is to simply not display any imagery at all, and while that works it isn't a solution that's going to win me any accolades for inventiveness. The custom tiles solution is pretty sophisticated - but if it works I'll have built a map base that will serve our purposes wonderfully: and in addition, I'll have added another REALLY important skill to my GIS toolkit.
---------------------------------------
The interface for using MapTiler is pretty straightforward. The only bump I encountered was finding the correct number for the projection I use to display the SID files. The georeferencing for the shape files is actually a projection, and that means you ahve to get exactly the right one. We use a NAD83 lambert conical projection derived just for Washington State and to find that I had to go and look it up. It took a bit to find, but if one is using the more common projections it should be a lot easier.
After all of the settings are loaded, the maptiler program does its work. It took 3 whole days, day and night, to render the maps I needed from the shp files. When it was complete though, the finished product was pretty amazing: you have a directory filled with subdirectories with the images you need and in root: you've got a few files - one of them is a google map HTML page that loads the images. A second file does the same thing but for the openlayers maps API. I haven't used the latter, but the former is meeting my needs - this file can be easily augmented to include the bells and whistles you like to have in your map. For me, this meant adding in the geoXML overlay for the polygon KML's that I was creating from the impervious surfaces for the city.