So, I've been a bit annoyed because the publicly available database that I can get my hands on isn't full of all of the information that I want. Luckily, that information is available online, so I can use a technique called screen scraping to push the information into the relevant parcel database by knowing the location of the information I want on the web page that talks about it. Thus, I will have a database will all of the new information included.
The code is below and pushes the name of a property owner into the parcels database that I have setup
ini_set('display_errors', 1);
error_reporting(E_ALL);
// Opens a connection to a MySQL server.
$username="user";
$password="pass";
$database="db";
$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());
}
$numPts = 100;
$startpt = 300;
$totalpts = $startpt + $numPts;
for ($i = $startpt; $i <= $totalpts- 1; $i++) {
// Pull out RP_ACCT_ID for a given ID key value: INDEX RP_ACCT_ID
$count = $i;
$query1 = "SELECT RP_ACCT_ID from parcels where parcels.ID='$count'";
$result = mysql_query($query1);
//echo $query1;
//echo $result;
while ($row = mysql_fetch_array($result)){
$accounta= $row['RP_ACCT_ID'];
}
// processes the valid RP_ACCT_ID to get the owner from the website:
$entry=$accounta;
$address="http://146.218.200.12/pls/ilisw/lis.online_lis_info_pkg.acct_info?p_in_rp_acct_id=";
$address=$address.$entry;
$file=file_get_contents($address);
$webpage=addslashes($file);
$start = strstr($file, "Name:");
$startlen = strlen($start);
$end = strpos($start, "Mailing");
$end = $end - 154;
$name = substr($start, 92, $end);
$start=$start+74;
$end = strpos($file, "Mailing Address:");
// insert owner back into database (combine these updates)
$query2 = "UPDATE `db_kitsap`.`parcels` SET `OWNER` = '$name' where parcels.RP_ACCT_ID='$accounta'";
$result = mysql_query($query2);
//insert web page back into database
$query3 = "UPDATE `db_kitsap`.`parcels` SET `WebPage` = '$webpage' where parcels.RP_ACCT_ID='$accounta'";
$result = mysql_query($query3);
}
Thursday, August 26, 2010
Screen Scraping to fill in database fields.
Sunday, August 8, 2010
Include FTP commands in Shell Scripts.
The first part of getting a working cron updater for GIS data is downloading the neccessary data in a form that can be executed from a linux shell (I'm using cron). So, retrieving the file will require using FTP to go out and get the file from a foreign server. Here is the text of the shell script that I use to do this - just executing this script goes out, retrieves the file and places it in a known directory so that I can work with it.
Here was my working script (ftp.sh):
#!/bin/bash
HOST='kcwppub3.co.kitsap.wa.us' # change the ipaddress accordingly
USER='anonymous' # username also change
PASSWD='email_address' # password also change
ftp -n $HOST <
quote PASS $PASSWD
bin
prompt off
cd /gis/datacd/arcview/layers/parcel/ # this folder contains files...
lcd /home/dom/Downloads/ # this location is local directory
mget parcels.zip
bye
END_SCRIPT
INformation adapted from this source.
Automatically updating base layers from Shp Files in external sources.
So, I need to develop an automated process for going from a file on someone else's server to a working file that I can deal with. Unfortunately, this is going to take a few more steps than I am used to.
The file is here:
ftp://kcwppub3.co.kitsap.wa.us/gis/datacd/arcview/layers/parcel/footprints.zip
Step zed is to delete the existing data in the database, to allow for the inclusion of new stuff.
Step one is to retrieve the file and download it to a known location
Step two is to unzip the file in the known location
Step three is to convert that .shp file to data in a MYSQL database using shp2mysql utility into the existing database.
This will all be run through Cron as a shell script, with a new version of this data sync'ed every night.
This should work – and getting it working should be a pretty big achievement. Also, the code, once created, will serve to allow the importation of any similar data by only changing the filenames, directories, and database names. This procedure relies upon having a file with the same filename present on a server, and that it is that file which is continually updated, without the filename changing.
Ta-Da
Now I just have to get it to work, and to do so in an automated way.
Dom
Friday, August 6, 2010
New Menu Creation Options.
So, some exciting developments in GIS lately. After taking almost a yearlong break from working on GIS I feel like the past few weeks I've really been able to come back – and with a bang. I feel like all of the work I did previously is really helping me build toward something very powerful, and the new skills that I continue to acquire become more and more helpful as time goes on, and in unexpected ways.
The new developments lately have been in the ability to present high-quality maps easily. I think the first map I did with google took me about three months to create, and was pretty much holy hell to figure out. A lot of that has to do with my unfamiliarity with Javascript, but a lot of it is the lack of a good presentation framework for building a menus system to deal with the functionality of the Googlemaps API (which is phenomenal).
The discovery of a system that allows me to build feature rich menus while continueing to use the Google Maps API as my framework AND allow integration into that framework of data from ESRI layers published by Map Servers was a huge one. The Open Source software that does this is GeoEXT, which is based on the functionality of OpenLayers combined with the user-friendly and intuitive menu functionality of EXT Js (now Sencha). The result is full featured maps that can do just about anything you might request of them. And, its all javascript, as near as I can tell: which means you could overlay a processing layer using PHP or your other favorite programming language and you really can do anything.
http://www.geoext.org/
I felt stuck in a rut for a while, but with the advent of this new software and the wealth of possibilities is allows for I feel reenvigorated, and that is a wonderful thing for a project that I have been looking forward to continuing to work on for such a long time.
Dom