Friday, July 13, 2012

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?...