super-cooper

This article was first published in the February 2008 issue of Python Magazine . It is being reproduced here with the permission of Marco Tabini and Associates .

Requirements

Python 2.4+
pyExcelerator module (http://sourceforge.net/projects/pyexcelerator/ )
Anthony Tuininga’s ceODBC module (http://sourceforge.net/html/ceODBC.html )
John Machin’s xlrd module (http://www.lexicon.net/sjmachin/xlrd.htm )
Michael Foord’s cgiutils module (http://www.voidspace.org.uk/python/cgiutils.html )

Lets face it, using Python, we can pull data from virtually anywhere in the corporate enterprise - databases, text files, or other documents. But how can we easily visualize data that has place associated with it? Well, we turn to Python and the API of a popular web-mapping application, that’s how.

I was recently tasked with finding a way to routinely (read daily) present a dataset of point locations on a onscreen map to our team of 40 or so people. I needed a way to easily disseminate some sort of map product that would display the data, something that required no special software setup on PCs - and most importantly - something that would be easy and intuitive for our team members to use. I thought about providing a PDF map product - I would have to harvest the data, process it, plot it on a map using mapping software, and email the final product to my team members as an attachment. Doesn’t sound like much fun, does it? Oh, and did I mention that those would all be manual processes to be completed once per day? While pondering this not-so-desirable methodology, I ran across a clever method used by our Information Technology team that passed locational data in a URL to Microsoft Live Maps. Their process takes point locations (in the form of latitude and longitude) from SQL Server, and using VB.Net and the Microsoft Live Search Maps URL API, builds a URL that plots well locations on a Live Search Maps web map. This was it, I would harvest the data, process it, build my URL using the Live Search Maps URL API, and send out the URL to my team nightly in a email. And I would do all of this using Python.

Example dataset

The dataset I needed to map is a gas well drilling schedule stored in a Microsoft Office Excel 2003 workbook. Rows in the workbook represent natural gas wells on the schedule and each has attributes associated with it such as well name, latitude, longitude, and the date drilling commenced; or spud date, as it is called in the oil and gas industry. The schedule gets updated at least daily, and in this line of business, plans can change every hour - literally. However, it was decided that a process that mapped the locations of the wells on a daily basis would suffice. Also note that, as will be demonstrated later, you don’t necessarily need point locations with latitude and longitude to benefit from this process; address locations can be used also.

Choosing a web mapping application

When first exploring the option of using a existing web mapping service to plot my well locations, I looked into most of the popular services out there: Google Maps (http://maps.google.com/, my personal favorite for the up-to-date aerial photography and ability to save your own maps, among other things), Yahoo! Maps (http://maps.yahoo.com/), and Microsoft Live Search Maps (http://maps.live.com/ - Figure 1). As stated earlier, our IT team had chosen Live Search Maps for an earlier application, and I soon learned the reason in doing so is that at the time, Live Search Maps was the only service out there which provided the ability to pass in multiple point locations to plot via latitude and longitude through the URL string. This was very important to the process, as I want to simply pass in all of my well locations through the URL, and then provide this URL to my team members.

Features of Microsoft Live Search Maps

Aside from the already-discussed feature of passing in multiple locations through the URL string, another great feature of Live Search Maps is the Scratch Pad - think of it as a "table of contents" for your map (Figure 2). The URL API (which will be discussed shortly) provides us the ability to create a scratch pad which lists each item and a description in a list on the right side of our map. Users can then see a scrollable list of all items that are currently plotted on the map. Also included in the Scratch Pad is the ability to zoom to street, city, or regional levels, send a location in an email, or obtain driving directions to or from a location. No other mapping service was able to provide these capabilities. So using Live Search Maps, my team members would be provided with a somewhat "traditional" web mapping interface that would be easy and intuitive to use, and with the Scratch Pad, they would be given a list of the mapped locations as well as the point locations plotted on the map.

Microsoft Live Search Maps URL API

The Live Search Maps Online Help (http://help.live.com/Help. aspx?market=en-US&project=WL_Local&querytype=topic&query=WL_ LOCAL_PROC_BuildURL.htm ) provides us with full documentation to the Live Search Maps URL API. I won’t go over everything, but I will cover what parts this process uses to build a URL - you can read the rest at your leisure. The API is fairly simple and straightforward. The first step is to build your standard Live Search Maps URL base - it’s always the same:

http://maps.live.com/default.aspx?

Next, you start to add your parameters. For us, that means set the map style. There are four options to choose from: aerial photo (a), road map (r), hybrid - aerial photo with labels and some roads (h), and oblique - commonly referred to as "bird’s eye view" (o). For this application I decided to use the road map view, since it would provide towns, cities, and major roads - all labeled - for users to orient themselves by:

http://maps.live.com/default.aspx?style=r

Next, we start to define and add our custom annotations to pass in and add to the Scratch Pad. For annotations, the form is:

sp=an.latitude_longitude_title_description

Latitude and longitude are obviously the locational information for the well, and you are given the option of adding a title and description (they end up on different lines in the scratch pad). So our URL now becomes something like so for the USA 1-18 well set to spud on 11/12/2007 and drilled by rig Smith #2 (note the encoding values used for the dash in the well name and the pound symbol in the rig name - don’t forget to encode all of your special characters in your URL string):

http://maps.live.com/default.aspx?style=r&sp=an.35.265638147_-93.5877380077_USA%201-18_11/12/2007%20-%20Smith%202

Basically, thats it! Copy and paste the URL above into your web browser and you will get a Live Search Maps map with the USA 1-18 well on the map and in the Scratch Pad. To add additional point locations to the URL, simply add a tilde and the next custom annotation(s):

http://maps.live.com/default.aspx?style=r&sp=an.35.265638147_-93.5877380077_USA%201-18_11/12/2007%20-%20Smith
%202~an.35.0878763351_-93.5096775211_Jones%2013-13_11/01/2007%20-%20Smith%2035

Harvesting and processing the Excel data

Before we can build our map, we have to get the data out of our Excel workbook. As Figure 3 shows, the worksheet layout is fairly simple. The columns represent, from left to right, well name, spud date (date the well started drilling), total depth, or TD date (the date the well finished drilling and reached total depth), latitude, longitude, and drilling rig number. To harvest our data from the workbook, we will enlist the help of the PyExcelerator module (http://sourceforge.net/projects/pyexcelerator ) and a function that comes with the docs - which worked right out of the box! Essentially we read the Excel workbook and return a list of lists, where each nested list represents a row in our worksheet. The entire function for parsing the Excel workbook can be found in Listing 1.

1. import pyExcelerator, os, time, string, calendar, datetime
2. import smtplib, cgiutils, xldate, DateTime, EmailRecips
3.  
4. def ExcelToList(fname, worksheet=0, encoding=‘cp1251′):
5. data = pyExcelerator.parse_xls(fname,encoding)
6. sheet_name=data[worksheet][0].encode(encoding)
7. values = data[worksheet][1]
8. for each in sorted(data[0][1].items()):
9. print str(each)[1:-1]
10. vdict = {}
11. row_idx_max = 0
12. col_idx_max = 0
13. for row_idx, col_idx in sorted(values.keys()):
14. row_idx_max = max(row_idx,row_idx_max)
15. col_idx_max = max(col_idx,col_idx_max)
16. v = values[(row_idx, col_idx)]
17. if isinstance(v, unicode):
18. v = v.encode(encoding, ‘backslashreplace’)
19. vdict[(row_idx,col_idx)] = v
20. vlist = []
21. for row in range(row_idx_max+1):
22. vlist.append([])
23. for col in range(col_idx_max+1):
24. if (row,col) not in vdict:
25. vdict[(row,col)]=None
26. vlist[row].append(vdict[(row,col)])
27. return vlist
28. Download this code: listing1.txt

The first step in ExcelToList utilizes the parse_xls method of pyExcelerator, which returns a list of two-tuples; one tuple for each worksheet in the workbook where each tuple consists of the worksheet name and worksheet data. The worksheet data is a dictionary mapping each non-blank cell to its cell value:

import pyExcelerator, os, time, string, calendar, datetime,
import smtplib, cgiutils, xldate, DateTime
data = pyExcelerator.parse_xls(filename, encoding)

Which returns:

[('Sheet1', {(0, 1): 39412.0,
(1, 2): 39437.0,
(2, 4): -93.600387566799995,
(0, 0): 'Smith #2-3',
(2, 0): 'Coop #4-17',
(2, 2): 39452.0,
(1, 4): -94.856870378300002,
(1, 1): 39421.0,
(1, 5): 'Rig 4',
(1, 3): 34.831162240499999,
(0, 5): 'Rig 67',
(2, 1): 39431.0,
(2, 5): 'Rig 23',
(0, 4): -97.340309266600002,
(1, 0): 'Jones #1-19',
(2, 3): 35.232594439099998,
(0, 3): 35.276017337600003,
(0, 2): 39430.0}),
('Sheet2', {}),
('Sheet3', {})]

Note that we returned a three-item list, but the dictionaries for Sheets 2 and 3 are empty, as there is no data in those worksheets. From this we get the dictionary for the worksheet we want out of the list data (where worksheet=0 ):

values = data[worksheet][1]

Which returns:

{(0, 1): 39412.0,
(1, 2): 39437.0,
(2, 4): -93.600387566799995,
(0, 0): 'Smith #2-3',
(2, 0): 'Coop #4-17',
(2, 2): 39452.0,
(1, 4): -94.856870378300002,
(1, 1): 39421.0,
(1, 5): 'Rig 4',
(1, 3): 34.831162240499999,
(0, 5): 'Rig 67',
(2, 1): 39431.0,
(2, 5): 'Rig 23',
(0, 4): -97.340309266600002,
(1, 0): 'Jones #1-19',
(2, 3): 35.232594439099998,
(0, 3): 35.276017337600003,
(0, 2): 39430.0}

Sorted, it’s much easier to see what’s going on:

for each in sorted(data[0][1].items()):
print str(each)[1:-1]

(0, 0), 'Smith #2-3'
(0, 1), 39412.0
(0, 2), 39430.0
(0, 3), 35.276017337600003
(0, 4), -97.340309266600002
(0, 5), 'Rig 67'
(1, 0), 'Jones #1-19'
(1, 1), 39421.0
(1, 2), 39437.0
(1, 3), 34.831162240499999
(1, 4), -94.856870378300002
(1, 5), 'Rig 4'
(2, 0), 'Coop #4-17'
(2, 1), 39431.0
(2, 2), 39452.0
(2, 3), 35.232594439099998
(2, 4), -93.600387566799995
(2, 5), 'Rig 23'

Here again, keys represent the cell mappings (Excel cell A1 = (0,0) ) and values represent the data within the cell. Next, we iterate through our values object, putting our key/value cell reference/cell value pairs into the vdict object and getting the maximum number of rows and colums (lines 10-19, Listing 1):

Here, our max is 2 rows and 5 columns, since it is zero- based. Finally, we iterate through our vdict object, converting it to a two-dimensional list with each list item representing a row in our Excel worksheet. Blank cells are represented by None :

vlist = []
for row in range(row_idx_max+1):
vlist.append([])
for col in range(col_idx_max+1):
if (row,col) not in vdict:
vdict[(row,col)]=None
vlist[row].append(vdict[(row,col)])

Our end product returned from ExcelToList looks like so:

[['Smith #2-3', 39412.0, 39430.0, 35.276017337600003, -97.340309266600002, 'Rig 67'],
['Jones #1-19', 39421.0, 39437.0, 34.831162240499999, -94.856870378300002, 'Rig 4'],
['Coop #4-17', 39431.0, 39452.0, 35.232594439099998, -93.600387566799995, 'Rig 23']]

Now we have a nice list that we can simply iterate through and process later.

But Wait a Minute! My Data is in <insert your database engine here> ! How do I get it out to use for something like this?

Never fear, if your data is in a RDBMS, you’re actually better off. If you don’t already have it, head on over to SourceForge and pick up Anthony Tuininga’s great ODBC module ceODBC (http://ceodbc.sourceforge.net/html/ceODBC.html ) that will allow you to connect to your database. I tested it on SqlServer and DB2 with great success. Listing 2 demostrates how to use ceODBC with SqlServer.

1. >>> import ceODBC
2. >>> db=ceODBC.connect(‘DSN=Wells’)
3. >>> c=db.cursor()
4. >>> c.execute(’select wellname, latitudedecimal,
5. ... longitudedecimal from dbo.wells where wellid in
6. ... (11587,11194,11157)‘)
7. >>> for each in c.fetchall():
8. ...     print each
9. ...
10. (‘GILL #1-18′, 33.095599, -92.38563)
11. (‘HOW #2-7′, 35.10155, -91.48824)
12. (‘JKK #11-13′, 34.09130, -93.45256)
13. Download this code: listing2.txt

This method employs a User DSN I created in the Windows ODBC Administator which uses my Windows credentials to log on to SqlServer. Of course, there are a plethora of database connectivity modules out there, so use whichever you want.

Building the Live Search Maps URL

Now that we have all of our data from the Excel workbook (or RDBMS), we can use it to build Scratch Pad annotations and eventually our URL. Based on the URL API, this looks easy enough, but let’s perform a few other litle tricks that will make things display much nicer in the Scratch Pad. First, we need to set our URL base:

urlBase = 'http://maps.live.com/default.aspx?style=r'

Now we need to iterate through our well data list from our Excel workbook, place the list items into namespaces and perform some processing on some of them, then build a annotation string from each one. There are two functions we should discuss first. You may have noticed earlier that after we slurp our data out of our Excel workbook, we are left with date values that look like 39430.0 and 39452.0 . Huh? Many of you will recognize these as Excel dates. Excel stores dates as floating point numbers; the number of days (or fraction thereof) since 12/31/1899, to be exact. For example, 9/20/2006 is actually 38980 to Excel; Excel just formats it to look like a date for simplicity of viewing. To deal with Excel dates and turn them into something I could actually use, I turned to the xlrd module (http://www.lexicon.net/sjmachin/xlrd.htm ) and its function xldate_as_tuple. Also, to get the well status, we assume that anytime today’s date falls within the window greater than or equal to spud date (beginning of drilling) and less than or equal to td date (end of drilling), that well is currently drilling. We’d like to know this and denote it in the Scratch Pad, so we determine each wells status. And if indeed the well is drilling, we note it in our annotation (Listing 3).

1. def GetWellStatus(inSpudDt, inPrjTdDt):
2. if len(inSpudDt) > 0 and len(inPrjTdDt) > 0:
3. today1 = time.strftime(‘%m/%d/%Y’,time.localtime())
4. today = (datetime.date(int(today1.split(‘/’)[2]),
5. int(today1.split(‘/’)[0]),
6. int(today1.split(‘/’)[1])))
7. spudDt = (datetime.date(inSpudDt[0],
8. inSpudDt[1],inSpudDt[2]))
9. prjTdDt = datetime.date(inPrjTdDt[0],inPrjTdDt[1],
10. inPrjTdDt[2])
11. if today > prjTdDt:
12. status = ‘Drilled’
13. elif today >= spudDt and today <= prjTdDt:
14. status = ‘Drilling’
15. elif today < spudDt:
16. status = ‘Proposed’
17. else:
18. status = ‘Unknown’
19. else:
20. status = ‘Undeterminable’
21. return status
22.  
23. def KillObject(toBeKilled):
24. if os.path.exists(toBeKilled):
25. os.remove(toBeKilled)
26.  
27. def WriteOutput(file, text):
28. writer = open(file, ‘a’)
29. writer.write(text)
30.  
31. def BuildLiveMapsUrl(wells, ds):
32. urlBase = ‘http://maps.live.com/default.aspx?style=r’
33. wholeList = []
34. dict = {}
35. for well in wells:
36. eachList = []
37. name = (well[0].replace(‘ ‘,‘%20′)).replace(‘#’,‘%23′)
38. spudDateTuple = xldate.xldate_as_tuple(well[1],0)
39. spudDate = str(spudDateTuple[0])+‘/’+str(spudDateTuple[1])+‘/’+str(spudDateTuple[2])
40. tdDateTuple = xldate.xldate_as_tuple(well[2],0)
41. tdDate = str(tdDateTuple[0])+‘/’+str(tdDateTuple[1])+‘/’+str(tdDateTuple[2])
42. lat = well[3]
43. long = well[4]
44. rig = well[5].replace(‘ ‘,‘%20′)
45. status = GetWellStatus(spudDateTuple, tdDateTuple)
46. if status == ‘Drilling’:
47. eachList.append(‘an.’ + str(lat) + ‘_’ + str(long) + ‘_’ + name + ‘_’ + spudDate +
48. ‘%20-%20′ + rig + ‘%20-%20′ + status.upper)
49. else:
50. eachList.append(‘an.’ + str(lat) + ‘_’ + str(long) + ‘_’ + name + ‘_’ + spudDate +
51. ‘%20-%20′ + rig)
52. eachList.append(spudDate)
53. wholeList.append(eachList)
54. wholeList.sort(lambda x,y:cmp(x[1],y[1]))
55. for whole in wholeList:
56. print whole
57. urlList = []
58. for each in wholeList:
59. del each[1]
60. wellString = str(each)[2:len(each)-3]
61. urlList.append(wellString)
62. urlStringWells = string.join(urlList,‘~’)
63. masterUrl = urlBase + ‘&sp=’ + urlStringWells + ‘~an.36.1164211465469_-94.143074863021_SEECO%
64. 20Fayetteville%20Office~an.35.513123_-93.834189_SWN%20Ozark%20Office~an.35.0685432008022_
65. -92.4189197874021_SWN%20Conway%20Office~an.‘
66. f = ‘C:/temp/DS_Html_Email.html’
67. t = ‘C:/temp/DS_Text_Email.txt’
68. dsUpdated = str(time.strftime(‘%A, %m/%d/%Y, %I:%M %p’, time.localtime(os.path.getmtime(ds))))
69. KillObject(f)
70. KillObject(t)
71. WriteOutput(f,‘<html>\n<head>\n<style type="text/css">body,table {font-family:Verdana;\
72. font-size:10pt;empty-cells: show}\n</style>\n</head>\n<body>\n‘)
73. WriteOutput(f,‘<table style="width: 700px">\n<tr>\n<td><h3>AAT Drill Schedule Mapper for ‘\
74. + time.strftime(‘%A, %m/%d/%Y’,time.localtime()) + ‘</h3></td>\n</tr>’)
75. WriteOutput(f,‘\n<tr>\n<td>Drill schedule last updated: ‘ + dsUpdated + ‘</td>\n</tr>’)\
76. WriteOutput(f,‘\n<tr>\n<td>&nbsp;</td>\n</tr>’)  # empty row
77. WriteOutput(f,‘\n<tr>\n<td><a href="’ + masterUrl + ‘">Click here for a map of the latest\
78. AAT drill schedule</a></td>\n</tr>‘)
79. WriteOutput(f,‘\n<tr>\n<td>&nbsp;</td>\n</tr>’)  # empty row
80. WriteOutput(f,‘\n<tr>\n<td>*** THIS MESSAGE AUTO-GENERATED ***</td>\n</tr>\n<tr>\n<td>\
81. *** PLEASE DO NOT REPLY TO THIS EMAIL ***</td>\n</tr>‘)
82. WriteOutput(f,‘\n</table>\n</body>\n</html>’)
83. WriteOutput(t,‘AAT Drill Schedule Mapper for ‘ + str(time.strftime(‘%A, %m/%d/%Y’\
84. ,time.localtime())))
85. WriteOutput(t,‘\n\nDrill schedule last updated: ‘ + dsUpdated)
86. WriteOutput(t,‘\n\nCopy and paste this address into your web broswer:\n\n‘ + masterUrl)
87. WriteOutput(t,‘\n\n** This message auto-generated - please do not respond to this email ***’)
88.  
89. def SendEmail(message, recipients):
90. sender = ‘AAT_Drill_Schedule_Mapper’
91. server = smtplib.SMTP(ip_address)
92. server.sendmail(sender, recipients, message)
93. server.quit()
94.  
95. if __name__ == ‘__main__’:
96.  
97. wells = ExcelToList(‘C:/temp/DS.xls’)
98. BuildLiveMapsUrl(wells, ‘C:/temp/DS.xls’)
99. # Build our email, using both HTML and plain-text inputs
100. fhtml = open(‘C:/temp/DS_Html_Email.html’,‘r’)
101. ftxt = open(‘C:/temp/DS_Text_Email.txt’,‘r’)
102. html = fhtml.read()
103. text = ftxt.read()
104. subject = ‘AAT Drill Schedule Mapper - ‘ + str((DateTime.now() + DateTime.\
105. RelativeDateTime(days=+1)).strftime(‘%A, %m/%d/%Y’))
106. # Create the message with both HTML and text formats
107. message = cgiutils.createhtmlmail(subject, html, text)
108. # Send the email out
109. recips = [‘chad_cooper@swn.com’]  # for testing
110. #recips = Dictionaries.aatEmails
111. SendEmail(message,recips)
112. Download this code: listing3.txt

In order to have the wells listed in the Scratch Pad in descending order by spud date (we want the ones drilling the soonest at the top of the list), we will create a list, eachList , and add the annotation string and the spud date to this list as items. As we iterate through the wells list, eachList gets appended to another list, wholeList , which ends up looking like so:

['an.35.2760173376_-97.3403092666_Smith%20%232-3_2007/11/21%20-%20Rig%2067%20-%20DRILLING', '2007/11/21'],
['an.34.8311622405_-94.8568703783_Jones #1-19_12/05/2007%20-%20Rig 4', '12/05/2007'],
['an.35.2325944391_-93.6003875668_Coop #4-17_12/15/2007%20-%20Rig 23', '12/15/2007']]

Next, we delete the date items from the list, since we no longer need them after the sort. We then build a string from our list elements and add that to our URL base. Finally, we add some of the Southwestern Energy office locations to the end of the URL, so users can get driving directions from any of our offices directly to a well location:

urlList = []
for each in wholeList:
del each[1]
wellString = str(each)[3:len(each)-3]
urlList.append(wellString)
urlStringWells = string.join(urlList,'~')
masterUrl=urlBase+'&sp='+urlStringWells+'~an.36.1164211465469_-94.143074863021_SEECO%20Fayetteville%20Office
~an.35.513123_-93.834189_SWN%20Ozark%20Office~an.35.0685432008022_-92.4189197874021_SWN%20Conway%20Office~an.'

And our final URL is:

http://maps.live.com/default.aspx?style=r&sp=an.35.2760173376_-97.3403092666_Smith%20%232-3_11/21/2007
%20-%20Rig%2067%20-%20DRILLING~an.34.8311622405_-94.8568703783_Jones%20%231-19_12/05/2007%20-
%20Rig%204~an.35.2325944391_-93.6003875668_Coop%20%234-17_12/15/2007%20-%20Rig%2023~
an.36.1164211465469_-94.143074863021_SEECO%20Fayetteville%20Office~an.35.513123_-93.834189_SWN%
20Ozark%20Office~an.35.0685432008022_-92.4189197874021_SWN%20Conway%20Office~an.

Our next step is to compose our email to send out to the team. In doing this we will write two versions, one for HTML-enabled clients that is styled and contains a clickable link to our map; and one version for text-only enabled email clients. This version is unstyled and provides the entire map URL that can be copy-pasted into a web browser. For our HTML email, we will create a simple HTML file, and write out code to it (Listing 3). Listing 4 shows the HTML output of Listing 3.

1. <html>
2. <head>
3. <style type="text/css">body,table {font-family:Verdana;font-size:10pt;empty-cells: show}
4. </style>
5. </head>
6. <body>
7. <table style="width: 700px">
8. <tr>
9. <td><h3>AAT Drill Schedule Mapper for Friday, 11/23/2007</h3></td>
10. </tr>
11. <tr>
12. <td>Drill schedule last updated: Friday, 11/23/2007, 10:08 PM</td>
13. </tr>
14. <tr>
15. <td>&nbsp;</td>
16. </tr>
17. <tr>
18. <td><a href="http://maps.live.com/default.aspx?style=r&sp=an.35.2760173376_-97.3403092666_
19. Smith%20%232-3_2007/11/21%20-%20Rig%2067%20-%20DRILLING~an.35.2325944391_-93.6003875668_
20. Coop%20%234-17_2007/12/15%20-%20Rig%2023~an.34.8311622405_-94.8568703783_Jones%20%231-19_
21. 2007/12/5%20-%20Rig%204~an.36.1164211465469_-94.143074863021_SEECO%20Fayetteville%20Office
22. ~an.35.513123_-93.834189_SWN%20Ozark%20Office~an.35.0685432008022_-92.4189197874021_SWN%20
23. Conway%20Office~an.">Click here for a map of the latest AAT drill schedule</a></td>
24. </tr>
25. <tr>
26. <td>&nbsp;</td>
27. </tr>
28. <tr>
29. <td>*** THIS MESSAGE AUTO-GENERATED ***</td>
30. </tr>
31. <tr>
32. <td>*** PLEASE DO NOT REPLY TO THIS EMAIL ***</td>
33. </tr>
34. </table>
35. </body>
36. </html>
37. Download this code: listing4.txt

Our text version is much simpler:

WriteOutput(t,'AAT Drill Schedule Mapper for ' + str(time.strftime('%A, %m/%d/%Y',time.localtime())))
WriteOutput(t,'\n\nDrill schedule last updated: ' + dsUpdated)
WriteOutput(t,'\n\nCopy and paste this address into your web broswer:\n\n' + masterUrl)
WriteOutput(t,'\n\n** This message auto-generated - please do not respond to this email ***')

Which provides us with text output of:

AAT Drill Schedule Mapper for Friday, 11/23/2007

Drill schedule last updated: Friday, 11/23/2007, 10:08 PM

Copy and paste this address into your web broswer:

http://maps.live.com/default.aspx?style=r&sp=an.35.2760173376_-97.3403092666_Smith%20%232-3_2007/11/21%20-
%20Rig%2067%20-%20DRILLING~an.35.2325944391_-93.6003875668_Coop%20%234-17_2007/12/15%20-%20Rig%
2023~an.34.8311622405_-94.8568703783_Jones%20%231-19_2007/12/5%20-%20Rig%204~an.36.1164211465469_
-94.143074863021_SEECO%20Fayetteville%20Office~an.35.513123_-93.834189_SWN%20Ozark%20Office~an.
35.0685432008022_-92.4189197874021_SWN%20Conway%20Office~an.

*** This message auto-generated - please do not respond to this email ***

Disseminating the map to our team members

Our final step in this process is to send out to the team the email containing the URL to the day’s drill schedule. To do this I enlisted the help of Michael Foord’s cgiutils module (http://www.voidspace.org.uk/python/cgiutils.html ) and the createhtmlmail function (Listing 3). createhtmlmail takes our html and text files we just created and uses them as inputs to create a mime-message that will render as html or text, depending on the receiving email client. If no html is supplied, then the htmllib module is used to guess a text rendering. createhtmlmail returns us a message object which is passed into the SendEmail function; which also takes a list of email addresses of our team members as input. To send our email, we simply create a connection to our Outlook Exchange Server and pass in the sender, our list of recipients, and our html/text message to the smtplib.sendmail object. Finally, we terminate the SMTP session and close out the connection.

def SendEmail(message, recipients):
sender = 'AAT_Drill_Schedule_Mapper'
server = smtplib.SMTP(ip_address)
server.sendmail(sender, recipients, message)
server.quit()

And that’s it! I run this script as a Scheduled Task on Windows XP every night at 9PM, and a few minutes later, the email goes out to each of our team members (Figure 4). Listing 5 shows the process in its entirety.

1. import pyExcelerator, os, time, string, calendar, datetime
2. import smtplib, cgiutils, xldate, DateTime, EmailRecips
3.  
4. def ExcelToList(fname, worksheet=0, encoding=‘cp1251′):
5. data = pyExcelerator.parse_xls(fname,encoding)
6. sheet_name=data[worksheet][0].encode(encoding)
7. values = data[worksheet][1]
8. for each in sorted(data[0][1].items()):
9. print str(each)[1:-1]
10. vdict = {}
11. row_idx_max = 0
12. col_idx_max = 0
13. for row_idx, col_idx in sorted(values.keys()):
14. row_idx_max = max(row_idx,row_idx_max)
15. col_idx_max = max(col_idx,col_idx_max)
16. v = values[(row_idx, col_idx)]
17. if isinstance(v, unicode):
18. v = v.encode(encoding, ‘backslashreplace’)
19. vdict[(row_idx,col_idx)] = v
20. vlist = []
21. for row in range(row_idx_max+1):
22. vlist.append([])
23. for col in range(col_idx_max+1):
24. if (row,col) not in vdict:
25. vdict[(row,col)]=None
26. vlist[row].append(vdict[(row,col)])
27. return vlist
28.  
29. def GetWellStatus(inSpudDt, inPrjTdDt):
30. if len(inSpudDt) > 0 and len(inPrjTdDt) > 0:
31. today1 = time.strftime(‘%m/%d/%Y’,time.localtime())
32. today = (datetime.date(int(today1.split(‘/’)[2]),
33. int(today1.split(‘/’)[0]),
34. int(today1.split(‘/’)[1])))
35. spudDt = (datetime.date(inSpudDt[0],
36. inSpudDt[1],inSpudDt[2]))
37. prjTdDt = datetime.date(inPrjTdDt[0],inPrjTdDt[1],
38. inPrjTdDt[2])
39. if today > prjTdDt:
40. status = ‘Drilled’
41. elif today >= spudDt and today <= prjTdDt:
42. status = ‘Drilling’
43. elif today < spudDt:
44. status = ‘Proposed’
45. else:
46. status = ‘Unknown’
47. else:
48. status = ‘Undeterminable’
49. return status
50.  
51. def KillObject(toBeKilled):
52. if os.path.exists(toBeKilled):
53. os.remove(toBeKilled)
54.  
55. def WriteOutput(file, text):
56. writer = open(file, ‘a’)
57. writer.write(text)
58.  
59. def BuildLiveMapsUrl(wells, ds):
60. urlBase = ‘http://maps.live.com/default.aspx?style=r’
61. wholeList = []
62. dict = {}
63. for well in wells:
64. eachList = []
65. name = (well[0].replace(‘ ‘,‘%20′)).replace(‘#’,‘%23′)
66. spudDateTuple = xldate.xldate_as_tuple(well[1],0)
67. spudDate = str(spudDateTuple[0])+‘/’+str(spudDateTuple[1])+‘/’+str(spudDateTuple[2])
68. tdDateTuple = xldate.xldate_as_tuple(well[2],0)
69. tdDate = str(tdDateTuple[0])+‘/’+str(tdDateTuple[1])+‘/’+str(tdDateTuple[2])
70. lat = well[3]
71. long = well[4]
72. rig = well[5].replace(‘ ‘,‘%20′)
73. status = GetWellStatus(spudDateTuple, tdDateTuple)
74. if status == ‘Drilling’:
75. eachList.append(‘an.’ + str(lat) + ‘_’ + str(long) + ‘_’ + name + ‘_’ + spudDate +
76. ‘%20-%20′ + rig + ‘%20-%20′ + status.upper)
77. else:
78. eachList.append(‘an.’ + str(lat) + ‘_’ + str(long) + ‘_’ + name + ‘_’ + spudDate +
79. ‘%20-%20′ + rig)
80. eachList.append(spudDate)
81. wholeList.append(eachList)
82. wholeList.sort(lambda x,y:cmp(x[1],y[1]))
83. for whole in wholeList:
84. print whole
85. urlList = []
86. for each in wholeList:
87. del each[1]
88. wellString = str(each)[2:len(each)-3]
89. urlList.append(wellString)
90. urlStringWells = string.join(urlList,‘~’)
91. masterUrl = urlBase + ‘&sp=’ + urlStringWells + ‘~an.36.1164211465469_-94.143074863021_SEECO%
92. 20Fayetteville%20Office~an.35.513123_-93.834189_SWN%20Ozark%20Office~an.35.0685432008022_
93. -92.4189197874021_SWN%20Conway%20Office~an.‘
94. f = ‘C:/temp/DS_Html_Email.html’
95. t = ‘C:/temp/DS_Text_Email.txt’
96. dsUpdated = str(time.strftime(‘%A, %m/%d/%Y, %I:%M %p’, time.localtime(os.path.getmtime(ds))))
97. KillObject(f)
98. KillObject(t)
99. WriteOutput(f,‘<html>\n<head>\n<style type="text/css">body,table {font-family:Verdana;\
100. font-size:10pt;empty-cells: show}\n</style>\n</head>\n<body>\n‘)
101. WriteOutput(f,‘<table style="width: 700px">\n<tr>\n<td><h3>AAT Drill Schedule Mapper for ‘\
102. + time.strftime(‘%A, %m/%d/%Y’,time.localtime()) + ‘</h3></td>\n</tr>’)
103. WriteOutput(f,‘\n<tr>\n<td>Drill schedule last updated: ‘ + dsUpdated + ‘</td>\n</tr>’)\
104. WriteOutput(f,‘\n<tr>\n<td>&nbsp;</td>\n</tr>’)  # empty row
105. WriteOutput(f,‘\n<tr>\n<td><a href="’ + masterUrl + ‘">Click here for a map of the latest\
106. AAT drill schedule</a></td>\n</tr>‘)
107. WriteOutput(f,‘\n<tr>\n<td>&nbsp;</td>\n</tr>’)  # empty row
108. WriteOutput(f,‘\n<tr>\n<td>*** THIS MESSAGE AUTO-GENERATED ***</td>\n</tr>\n<tr>\n<td>\
109. *** PLEASE DO NOT REPLY TO THIS EMAIL ***</td>\n</tr>‘)
110. WriteOutput(f,‘\n</table>\n</body>\n</html>’)
111. WriteOutput(t,‘AAT Drill Schedule Mapper for ‘ + str(time.strftime(‘%A, %m/%d/%Y’\
112. ,time.localtime())))
113. WriteOutput(t,‘\n\nDrill schedule last updated: ‘ + dsUpdated)
114. WriteOutput(t,‘\n\nCopy and paste this address into your web broswer:\n\n‘ + masterUrl)
115. WriteOutput(t,‘\n\n** This message auto-generated - please do not respond to this email ***’)
116.  
117. def SendEmail(message, recipients):
118. sender = ‘AAT_Drill_Schedule_Mapper’
119. server = smtplib.SMTP(ip_address)
120. server.sendmail(sender, recipients, message)
121. server.quit()
122.  
123. if __name__ == ‘__main__’:
124.  
125. wells = ExcelToList(‘C:/temp/DS.xls’)
126. BuildLiveMapsUrl(wells, ‘C:/temp/DS.xls’)
127. # Build our email, using both HTML and plain-text inputs
128. fhtml = open(‘C:/temp/DS_Html_Email.html’,‘r’)
129. ftxt = open(‘C:/temp/DS_Text_Email.txt’,‘r’)
130. html = fhtml.read()
131. text = ftxt.read()
132. subject = ‘AAT Drill Schedule Mapper - ‘ + str((DateTime.now() + DateTime.\
133. RelativeDateTime(days=+1)).strftime(‘%A, %m/%d/%Y’))
134. # Create the message with both HTML and text formats
135. message = cgiutils.createhtmlmail(subject, html, text)
136. # Send the email out
137. recips = [‘chad_cooper@swn.com’]  # for testing
138. #recips = Dictionaries.aatEmails
139. SendEmail(message,recips)
140. Download this code: listing5.txt

Limitations and other Live Search Maps applications

Internet Explorer imposes a strict limitation of 2,048 characters in the path portion of a URL. I have yet to run into this problem, as most of my URLs generated from this process are on the order of 400-600 characters long. All other modern web browsers (Firefox, Opera, or Safari) will give you no problems in this department.

The Live Search Maps URL API also provides several other ways to plot locations on its maps. Let’s say for my application I need to supply a map of driving directions to each well location from our office in Fayetteville, Arkansas. No problem, the URL API provides us with the tools to do so. All we have to do is build a route string with the beginning waypoint ("A") as the office in Fayetteville, and the end waypoint(s) ("B") as the well locations:

http://maps.live.com/default.aspx?style=r&rtp=adr."A"~pos."B"

Here, rtp defines the route, adr defines the start waypoint as an address, and pos defines the end waypoint as a specific point on the map. Our office location ("A") remains static, but the dynamic end waypoints ("B") can easily be added to the URL through a slight change in our BuildLiveMapsUrl function that adds them in the form of "pos.latitude_longitude_name" to the URL to create a end product such as:

http://maps.live.com/default.aspx?style=r&rtp=adr.1083%20East%20Sain%20Street%20Fayetteville,%20AR%2072703~
pos.35.2760173376_-97.3403092666_Smith%20%232-3

We could easily loop through the original dataset and create a series of URLs similar to above and include them as clickable links in our email.

Conclusion

The Microsoft Live Search Maps URL API is a fairly simple, straightforward, and end-user friendly way to display point locations on a map. When combined with a minimal amount of Python code to simply create a URL to use in a existing cross-browser application, Live Search Maps becomes an indispensible tool that allows us to disseminate, view, understand, and interpret data that was once simply sitting around. I hope this article demonstrates how Python can be used to take once lifeless enterprise data and turn it into a dynamic, meaningful, and sexy end product.

About the author

Chad Cooper has been in the Geographic Information Systems field for 5 years, the last 3 of which have been with Southwestern Energy Company . Chad has been hacking up Python code for the past 2 years. He lives in Arkansas with his wife and two incredibly energetic young sons.

Tags: publications, pymag, python