Just a test drive for postgis:

SELECT esn_id from PSAP as "geospatial-data" WHERE ST_Contains("geospatial-data".geom,ST_GeomFromText('POINT(-90.56210 38.60369)'));

Yields:

"E29189002"

Total query runtime: 15 ms.

1 row retrieved.

Would you like to know more?...

Summary only...https://howtogis.blogspot.com/2015/05/just-test-drive-for-postgis-select.html

Trouble with shp2mysql.exe

So, I had a bit of trouble picking up the ball with processing shp files. I had a shell script that automatically loaded a dataset from an external source into my mysql database - I find these scripts very useful for automatic updates (can be processed using cron jobs) but also for loading data without having to put much thought into things when systems get upgraded (which happens to me quite frequently).
When I was running this shell script, it failed out when it would try to access shp2mysql.exe. That was strange. 

 I actually had to recompile the executable from source. It seems I had done that before - but still given the file the .exe extension on the back, that is what confused me when I went back to try to use the file again; I thought I was using a windows binary but really I was using a recompiled linux binary with a .exe extension - that is not the same thing. I didn't add the extension this time, and I marked up my shell script with notes in case this confuses me again. 

Here's the updated shell script that I use to load my SITUS file from an external source:

#!/bin/bash
# immediately set the shell to exit if simple command exit exists.
# set -e
# have to include the path in case this is the first time running so we know where the sql2mysql executable is, if this isn't the right path: add the one that is.
#PATH=$PATH:/usr/bin
NOW=$(date +"%b-%d-%y")
DIR=~/Documents/GISTEMP/
DIR2=~/Documents/GISTEMP/situs/
FILE=~/Documents/GISTEMP/situs/situs_$NOW.sql
FILE2=/usr/bin/shp2mysql_linux
echo "Today is $NOW."
# DatePart=`date ‘+%m%d%Y`
# The following just creates the directories if we need to.
# /GISTEMP
#
if [ -f $FILE2 ];
then
echo "File $FILE2 exists in path (/usr/bin), proceeding"
else
echo "File $FILE2 does not exist, you will likely have to recompile shp2mysql from source and run this script afterwards, sorry."
exit
fi

if [ -d $DIR ];
then
   echo "Directory $DIR exists"
else
   echo "Directory $DIR does not exist, creating"
   mkdir ~/Documents/GISTEMP/
fi
# /parcels
if [ -d $DIR2 ];
then
   echo "Directory $DIR2 exists"
else
   echo "Directory $DIR2 does not exist, creating"
   mkdir ~/Documents/GISTEMP/situs/

fi
   cp /home/dom/Downloads/shp2mysql/src/shp2mysql /home/dom/Documents/GISTEMP/situs/shp2mysql_linux

# /checks to see if we've done this before.
if [ -f $FILE ];
then
   echo "File $FILE exists, please update later"
  exit
else
   echo "File $FILE does not exist"
echo 'getting file via FTP from Kitsap County'
HOST='kcwppub3.co.kitsap.wa.us' # change the ipaddress accordingly
USER='anonymous' # username also change
PASSWD='islandhermit@gmail.com' # password also change
ftp -n $HOST
quote USER $USER
quote PASS $PASSWD
bin
prompt off
cd /gis/datacd/arcview/layers/parcel/ # this folder contains
lcd /home/dom/Documents/GISTEMP/
mget siteaddr.zip
bye
END_SCRIPT
fi
rm /home/dom/Documents/GISTEMP/situs/*
echo 'file acquired, unzipping'
# need to run in silent mode /q should help./
# copy the new filename to the directory for archival purposes
mv /home/dom/Documents/GISTEMP/siteaddr.zip /home/dom/Documents/GISTEMP/situs_$NOW.zip
unzip /home/dom/Documents/GISTEMP/situs_$NOW.zip -d /home/dom/Documents/GISTEMP/situs/
cd /home/dom/Documents/GISTEMP/situs/
echo 'converting the .shp file to .sql script'
shp2mysql_linux -s 4326 siteaddr situs db_kitsap > situs_$NOW.sql
# this dataset includes a field named "primary" which funks things up, to fix this we have to use a SED command to replace the 2nd instances of Primary in the first line to Primary2, the command below does this.
echo '.sql file created, now fixing a known flaw in the architecture of the table.'
sed '1 s/PRIMARY/PRIMARY2/2' situs_$NOW_fixed.sql
echo '.sql file fixed, uploading to mysql database'
# need to add an if exists for dropping and adding
mysql -uuser -ppassword db_kitsap -e "drop table if exists situs"
mysql -uuser -ppassword db_kitsap -e "source situs_$NOW_fixed.sql


Would you like to know more?...

Summary only...https://howtogis.blogspot.com/2012/07/trouble-with-shp2mysqlexe.html

ogr2ogr alternative, and online too.

A good online resource for shp file conversion if you want a different projection.

http://mygeodata.eu/apps/converter/main_EN.html?dataType=vector

Would you like to know more?...

Summary only...https://howtogis.blogspot.com/2011/01/ogr2ogr-alternative-and-online-too.html

Screen Scraping to fill in database fields.

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

Would you like to know more?...

Summary only...https://howtogis.blogspot.com/2010/08/screen-scraping-to-fill-in-database.html

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 USER $USER
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.

Would you like to know more?...

Summary only...https://howtogis.blogspot.com/2010/08/include-ftp-commands-in-shell-scripts.html

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

Would you like to know more?...

Summary only...https://howtogis.blogspot.com/2010/08/automatically-updating-base-layers-from.html

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

Would you like to know more?...

Summary only...https://howtogis.blogspot.com/2010/08/new-menu-creation-options.html