Supercharge your GIS reporting with SQL Reporting, ST_Geometry and ReportLab for python

I had the great pleasure of attending the Washington GIS Conference – an annual event put on by WA URISA. It was a busy three days being a workshop presenter, a paper presenter, on the conference committee and president of the organization.

We had a really surprising turn out at our paper presentation. I thought we were presenting on a somewhat obscure topic and would have been happy with an audience of 10 people. Turns out about 35 or 40 people showed up and stayed for the whole thing! I know, I can’t believe it either. And people are specifically mentioning this in the feedback survey. I think there is some untapped need here.

Here’s a copy of the presentation in PDF format. Let us know if you have any questions.

https://gisstudio.files.wordpress.com/2013/05/reporting-using-gis-data-in-sql-server-final.pdf *

*note that some links to live reports will not work as they are on an internal server.

Advertisements

Zipping files in python

Every so often I run an automated process that copies all of our clients data to file geodatabases and then zips them up to post to the web. Previously I used PKZip as a command line zipper, calling it from my python script in a batch file. I decided to explore the possibility of zipping from within python. Turns out there is a module called zipfile that does just what I need. A fellow blogger got me started on the right path with a very helpful blog posting (here it is).

# Description: 
# run this after creating the gis data for the web script
#---------------------------------------------------------------------------
import sys, string, os, zipfile
#if available add compression
try:
    import zlib
    compressType = zipfile.ZIP_DEFLATED
except:
    compressType = zipfile.ZIP_STORED
#Variables...
shpPath = "N:/yourpath/datafiles"
#***********************************************************************
# Read the list of shapefiles and zip'em
# shapefiles have a prefix that determines what zip file the go in
# reads the files alphabetically, or this script wouldn't work
# if the zip file already exists, it will overwrite
#***********************************************************************
x = 0
currentZip = ''
try:
  itemList = os.listdir(shpPath)
  for i in itemList:
    fullpath = os.path.join(shpPath, i)
    shpName = i.split("_",1)  # the data naming convention for the data includes an underscore.  This reads the prefix and name into a list
    thisZip = shpName[0] # reads the prefix
    shp = shpName[1] #reads the name of the file
    zipname = 'ClientName_' + thisZip +'_shps.zip'  #add a new prefix (ClientName_) and groups all the like prefixes into one zip.  So... all the transportation data is in one zip file.
    if i <> zipname:
      if currentZip == thisZip:
        #add to zip archive
        zf = zipfile.ZipFile(shpPath + '/zips/'+ zipname, mode='a')
        try:
            print 'adding', i
            zf.write(fullpath, arcname='ClientName_'+ i, compress_type=compressType)   #this writes the zip file
            x+=1
            #arcname sets the name and strips the folder location from the saved path internal to the zip file
        finally:
            zf.close()
      else:
        #make a new archive and set the new currentZip variable
        currentZip = thisZip
        print '\ncreating new archive'
        zf = zipfile.ZipFile(shpPath + '/zips/'+ zipname, mode='w')
        try:
          print 'adding', i
          zf.write(fullpath, arcname='ClientName_'+i, compress_type=compressType)
          x+=1
          zf.close()
        except:
          print "Unexpected error:", sys.exc_info()[1]
    else:
      print 'cannot zip a zip file'
      pass
except:
  print "Unexpected error:", sys.exc_info()[1]

Email yourself the results of your post script

Wouldn’t it be great to get an email once a script has finished letting you know if it worked or failed?  Well it’s actually really easy to pull off.  Here’s how I did it:

Note for this example you would replace ‘smtp.example.com’ with your email host.

def SendEmail(sender, receivers, message):
    import sys, smtplib   
    try:
     smtpObj = smtplib.SMTP('smtp.example.com')
     smtpObj.sendmail(sender, receivers, message)
     print 'email sent'
    except:
     print 'error sending'
     sys.exit
then in your script you use the code like this:
—script did some stuff—
–script made some output—
message = "my output message" +  output  #for example.  use whatever you want here to convey the info you need
receivers = "myemail@myaddress.com"
sender = "myemail@myaddress.com"

SendEmail(sender, receivers, message)

Tracking time in a script – how I do it

Looking for an easy way to track time in a script? Here’s what I do.
 
import time
from time import strftime

#*******START***TIME***********
starttime = time.time()
print '\n--> Starting script at ', strftime("%a, %d %b %Y %H:%M:%S")

#<your script here>
#*******END***TIME***********
endtime = time.time() 
elapsed = endtime - starttime 
print '\n\n--> Finished script... ' , strftime("%a, %d %b %Y %H:%M:%S"), '\n    ..Elapsed time: ', round(elapsed/60,1), ' minutes.'

results in….

--> Starting script at Mon, 16 Jan 2012 08:16:50
--> Finished script... Mon, 16 Jan 2012 08:17:20
 ..Elapsed time: 0.5 minutes.

Favorite Code Editor

In my progression from utter novice programmer to whatever stage you might say I’m at now, I’ve tried several editors. So far my favorite editor is Komodo. Once I started using this software, those pesky newbie mistakes dropped away. Never again did I forget I closing parantheses or bracket. Every if statement ends in a colon and every try is matched with an except. Komodo keeps track of all of these and makes it impossible to miss when you’ve left them out. And the tab guides are indispensible when you are more than a few tabs deep in a loop inside a loop inside a try/except statement. The keyboard shortcuts for indent/outdent and comment/uncomment are life savers too. It’s also possible to wire up Komodo to read and help with predictive text with the Arcpy modules.

One of the very nice things I appreciate about Komodo is the ability to Continue reading

Delay your script so large files can finish saving

A few seconds is all you need

I ran into an interesting issue the other day while updating some old ArcMap MXD files. The ArcPy script I wrote worked great except for the last file it processed would get corrupted and not open. I pondered for a bit and spoke with my friend who suggested that the script was ending while the last file was saving and thus corrupting the output since the process did not get to finish properly. I added a delay  at the end of the process allowing everything to finish up before the script ends. And…it worked!

Getting a script to sit and do nothing may seem counter-intuitive, but sometimes it’s exactly what needs to happen.  Here’s how to make a python script wait 6 seconds.

import time
time.sleep(6)