Everett

Alberta’s Drilling History Visualized

The following animation is a map of all the Alberta Energy Regulator (AER) recorded wells drilled in Alberta between January 2010 and April of 2017:

The reduction in drilling since the latter half of 2014 due to the collapse in oil prices is clearly visible in the animation through both the histogram and the map itself. There is also a strong seasonal spike in drilling every winter. The seasonal spikes cause some challenges in comparing the true changes in the amount of drilling for different parts of the province as time goes on. I think an interesting next step will be to plot a heat map style plot of the year over year change in drilling and animate it as well. This is also just a short animation that maps only a tiny subset of Alberta’s total drilling history. Another interesting next step will be to make this code work more efficiently and to run it on a more powerful machine that can plot the entire drilling history of the province from 1883 to present.

For more detail on how this animation was created see the GitHub page for the project (https://evjrob.github.io/AB-Drilling-History-Visualized/) or clone the repository and experiment for yourself (https://github.com/evjrob/AB-Drilling-History-Visualized).

 

 

 

Posted by Everett in Data Science, Programming, 0 comments
Solving Sudokus and Making an App

Solving Sudokus and Making an App

My path to publishing my first app on the Google Play store has been a long one with odd beginnings. It all started when I first learned of simulated annealing algorithms from a post on hacker news months ago.  In that post someone was showing off their algorithm and how it could solve the Traveling Salesman Problem or some other tough NP-Complete problem. I can’t remember the specifics, but for whatever reason it inspired me to apply the technique to another NP-Complete problem: Sudoku puzzles!

I spent a couple weeks slowly working away at an implementation in Go and by the end had a configurable command line program that can reliably solve the easy puzzles, and sometimes also solve the harder ones. It doesn’t do that well on the “very hard” or “evil” Sudoku puzzles because they seem to branch early which creates excessively large peaks and troughs in the solution space that are insurmountable for my implementation. This could be because of my choice of cost function, or because I haven’t found the magic mix of parameters that maximize the likelihood of success. I incorporated some interesting tricks to the algorithm that I found while doing research; most notably the use of concurrent channels that run at exponentially increasing temperatures and thus have increasing likelihoods of escaping the deeper troughs in the search space. Each of these channels runs for a set number of iterations on their own, and then they return their current result and get compared to their neighbouring channels. Whenever a hotter channel has a better solution than a cooler channel they swap their current working solutions. The idea is that the hotter threads will keep the search from permanently getting stuck in a local minimum, while the cooler channels will inherit the best working solution and settle nicely into the minimum to find the solution if there is one.

This wasn’t the end of my Sudoku solving however. I started to wonder about deterministic algorithms for solving them and quickly stumbled onto Peter Norvig’s Constraint Propagation Search algorithm. I set to work implementing this algorithm in Go as well, and finished it not long after. The results were far more impressive than my simulated annealing algorithm: It always got the right solution, and typically in one tenth the time on even the hardest 9 x 9 puzzles I had in my test data.

At this point my obsession with Sudoku solving algorithms was starting to peak, and I wanted to implement something even faster and more efficient than Peter Norvig’s algorithm. Eventually I stumbled onto Donald Knuth’s Dancing Links algorithm. At first I struggled to understand exactly how the algorithm worked. How could it be so much more efficient than Peter Norvig’s algorithm? It was obvious that Dancing Links avoids the computationally costly steps of copying the entire puzzle when a branch in the search space occurs, but I was still having trouble understanding how exactly it managed to backtrack and reverse the changes it made without copying. And so I spent far too much time drawing up the example problem in his paper using a flow charting web app.

This slideshow requires JavaScript.

Finally I understood it. I saw the dance occur right before my eyes, and no longer felt unworthy of applying it to my sudokus. And so I created another Go package called sudokuDlx. It manages to solve my sudoku puzzles much faster than the Constraint Propagation Search algorithm; it solves the full set of about 150 test puzzles in about half a second on my laptop versus more than 16 seconds for sudokuCPS. This advantage of speed is most noticeable when we attempt to solve Peter Norvig’s impossible puzzle. Here sudokuDlx determines the puzzle is impossible almost as fast as it takes to solve any valid puzzle while sudokuCPS takes an agonizing 10 minutes and 52 seconds to do the same task.

Having finished the Dancing Links implementation, I put the whole business of solving sudokus on hold. I spent most of the ensuing time on Udacity learning how to develop an android app. I sort of imagined trying to find some application for my new found sudoku solving skills when I started learning android development, but a major problem existed: Most android programs are written in Java, while all of my sudoku solvers are written in Go. I knew it would be possible to re-write the algorithms in Java, but I wasn’t super enthused about having to do that. I was almost prepared to find some other project to make into an android app, but decided to investigate Gomobile a little bit before giving up. It has quite a few limitations about the types and data structures that can be passed from Java to go and vice versa, but nothing that a few wrapper functions in the GO package can’t handle.

I set to work making my sudoku solving android app and after a couple dozen hours of development spread over evenings and weekends I finally have an app I feel is worth publishing. The code for this app is up on GitHub and the app itself can be downloaded on the Google Play store.

Posted by Everett, 2 comments
My Life As A Quant: A Neopets Stock Market Bot.

My Life As A Quant: A Neopets Stock Market Bot.

This program definitely violates the Neopets Terms of Use. Use at your own risk.

Sometime in the year 2000 or so I was really into the website Neopets. I was about 10, and everyone my age was into Neopets. I don’t remember many details, but I do remember trying to get rich using the Neopets stock market. It didn’t really work because I didn’t have the patience to properly execute the tried and true techniques every one else had already figured out. I knew this, and the brief thought of making a robot to do it for me crossed my mind. The ten year old me had no clue how to go about creating such a robot though, and I’m pretty sure that I had it in my head to build a literal robot that would use the keyboard and mouse. Maybe out of lego or something. I’m not sure.

Some 12 years later (in 2012), something jogged my memory and drew my attention to this unrealized dream of long ago. More importantly, I realized I could make it work now. Sure Neopets isn’t much of a thing anymore, but that didn’t matter to me. I had to make this happen; it was a loose end! In pursuit of this goal I turned to Python as my language of choice, which conveniently has a library called Mechanize (named after the original Perl Library). Mechanize can programatically interact with webpages as though it is a user. It’s a great module that allowed me to finish the project in the span of a couple days between two busy weeks at university. Even today, about two years later, it’s still running on an almost daily basis (sometimes computer downtime prevents it from running), and has taken the 1.5 million neopoints of seed capital and turned it into more than 20 million neopoints.

Lets start with the code:

#
# stockbot.py - A webcrawling bot that can automatically play the Neopets stock market game.
#

import mechanize
from lxml import etree
import random
import datetime
import time
import math
import copy
import sys

# Ermagerd, global variables! Bad practice! Bad practice!
logFile = open('<PATH TO FILE>/log.txt', 'a')
errorHTMLdump = open('<PATH TO FILE>/errorHTML.txt', 'a')


##
# Main function controls browser session and logging into Neopets
##  
def main():
    
    #user credentials
    userName = 
    passWord = 

    br = mechanize.Browser(factory=mechanize.RobustFactory())
    
    # User-Agent
    br.addheaders = [('User-agent', 'Mozilla/5.0 (X11; Ubuntu; Linux i686; rv:17.0) Gecko/17.0 Firefox/17.0')]
    
    # Only necessary if you are using cron instead of anacron, and wish to hide your robotic behaviour.
    #humanizingDelay(300)
    
    # For potential issues connecting, and a URLError is raised. This sleeps for 30 seconds
    # then retries the connection up to 10 times before giving up and documenting the error.
    for attempt in range(10):
        try:
            br.open("http://www.neopets.com/login/index.phtml")
        except mechanize.URLError:
            time.sleep(30)
        else:
            break
    else:
        logFile.write(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+" - An error occured while trying to connect to the webpage.\n")
        sys.exit()

    br.select_form(nr=0)
    br.form['username'] = userName
    br.form['password'] = passWord

    # Login
    br.submit()
    
    bankWithdrawal(br)
    stockManager(br)
    bankDeposit(br)
    
    br.open("http://www.neopets.com/logout.phtml")

##
# Causes the script to pause for a random time to make it appear more 
# human by not always executing at the exact same time of day. maxLength
# is the maximum duration of the pause in seconds.
# minLength is optional and defaults to 0.
##
def humanizingDelay(maxLength, minLength=0):
    pauseDuration = random.uniform(minLength,maxLength)
    time.sleep(pauseDuration)

##
# Collects the daily bank interest and decides if it is necessary to withdraw
# neopoints for the day's stock purchase.
##  
def bankWithdrawal(browser):
    
    bankPage = "http://www.neopets.com/bank.phtml"
    bankHTML = browser.open(bankPage)
    
    browser.select_form(nr=3)
    browser.submit()
    
    humanizingDelay(5, minLength=2)
    
    bankHTMLString = bankHTML.read()
    if getNeopoints(bankHTMLString) < 17000:
        browser.select_form(nr=2)
        browser.form['amount'] = "17000"
        browser.submit()
        logFile.write(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+" - 17000 NP withdrawn from the bank.\n")


##
# Looks at the current neopoint value and deposits any excess
##  
def bankDeposit(browser):
    
    bankPage = "http://www.neopets.com/bank.phtml"
    bankHTML = browser.open(bankPage)
    
    humanizingDelay(3, minLength=1)
    
    bankHTMLString = bankHTML.read()
    if getNeopoints(bankHTMLString) > 32500:
        depositValue = getNeopoints(bankHTMLString) - 32500
        browser.select_form(nr=1)
        browser.form['amount'] = str(depositValue)
        browser.submit()
        logFile.write(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+" - "+str(depositValue)+" NP deposited to the bank.\n")
    
##
# Takes the current page's HTML and returns the neopoint value.
##     
def getNeopoints(pageHTML):
    startToken = "<a id='npanchor' href=\"/inventory.phtml\">"
    endToken = "</a>"
    startIndex = pageHTML.find(">",pageHTML.find(startToken))+1
    endIndex = pageHTML.find(endToken,startIndex)
    npString = pageHTML[startIndex:endIndex]
    npValue = int(npString.replace(",",""))
    
    return npValue


##
# A function performing the upper level stock market stuff.
## 
def stockManager(br):
    stockListHTML = br.open("http://www.neopets.com/stockmarket.phtml?type=list&full=true")
    portfolioHTML = br.open("http://www.neopets.com/stockmarket.phtml?type=portfolio")
   
    stockPrices = extractStockPrices(stockListHTML)
    stockHoldings = extractStockHoldings(portfolioHTML)
    
    todaysBuy = pickStockPurchase(stockPrices, stockHoldings)
    if todaysBuy != "No Stocks at 15-17NP":
        buyResult = buyStocks(todaysBuy, br)
        logFile.write(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+" - "+buyResult+"\n")
    else:
        logFile.write(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+" - No stocks purchased, none available at 15-17NP/share.\n")
        
    todaysSales = pickStockSales(stockPrices, stockHoldings)
    
    if len(todaysSales) >= 1:
        successfulSale = sellStocks(todaysSales, br)
        if successfulSale:
            for stock in todaysSales.keys():
                logFile.write(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+" - "+str(int(round(todaysSales.get(stock))))+" shares of ["+stock+"] sold at "+str(stockPrices.get(stock))+" NP each.\n")
        else:
            logFile.write(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+" - Error: Something went wrong when trying to sell stocks.\n")


##
# Extracts the Stock tickers and values into a dictionary
##  
def extractStockPrices(stockListHTML):
    
    #Put the HTML source into a string
    stockListHTMLString = stockListHTML.read()
    
    #Trim the source down to just the table we care about
    startToken = "<table cellpadding=3 cellspacing=0 border=1 align=center><tr><td align=center bgcolor='#8888ff'><font color=white><b>Logo"
    endToken = "<br clear="
    startIndex = stockListHTMLString.index(startToken)
    endIndex = stockListHTMLString.index(endToken)    
    stockTableHtml = stockListHTMLString[startIndex:endIndex]
    
    #Pick out all of the HTML stuff our xml parser will choke on
    removeSubStrings = [["<b>",""],
                        ["</b>",""],
                        ["<table cellpadding=3 cellspacing=0 border=1 align=center>","<table>"],
                        [" bgcolor='#eeeeff'",""],
                        [" bgcolor='#8888ff'",""],
                        [" align=center",""],
                        [" width=60",""],
                        [" height=60",""],
                        [" border=0",""],
                        ["<font color=",""],
                        ["'green'>",""],
                        ["'red'>",""],
                        ["'black'>",""],
                        ["white>",""],
                        ["</font>",""],
                        ["</a>",""]]
    

    for i in removeSubStrings:
        stockTableHtml = stockTableHtml.replace(i[0], i[1])

    while stockTableHtml.find("<img src=") != -1:
        tempStartIndex = stockTableHtml.find("<img src=")
        tempEndIndex = stockTableHtml.find("gif'>") + 5
        tempSubString = stockTableHtml[tempStartIndex:tempEndIndex]
        stockTableHtml = stockTableHtml.replace(tempSubString, "")
        
    while stockTableHtml.find("<a href=") != -1:
        tempStartIndex = stockTableHtml.find("<a href=")
        tempEndIndex = stockTableHtml.find("'>") + 2
        tempSubString = stockTableHtml[tempStartIndex:tempEndIndex]
        stockTableHtml = stockTableHtml.replace(tempSubString, "")       
        
    stockTableHtml = stockTableHtml.replace("<td>Logo</td>", "")
    stockTableHtml = stockTableHtml.replace("<td></td>", "")
           
    ##
    # Aggregate into dictionary
    ##  
    
    
    stockValues = {}

    table = etree.XML(stockTableHtml)
    rows = iter(table)
    next(rows)
    for row in rows:
        stockValues[row[0].text] = int(row[4].text)
        
    return stockValues
    
##
# Extracts the Stock tickers and current holdings into a dictionary
#       Only adds stocks which are present in portfolio
## 
def extractStockHoldings(portfolioHTML):
    
    #Put the HTML source into a string
    portfolioHTMLString = portfolioHTML.read()
    
    #Trim the source down to just the table we care about
    startToken = "<table align=center cellpadding=3 cellspacing=0 border=1>"
    endToken = "<span id='show_sell' style='display:none'><center><input type="
    startIndex = portfolioHTMLString.index(startToken)
    endIndex = portfolioHTMLString.index(endToken)   
    portfolioTableHtml = portfolioHTMLString[startIndex:endIndex]
    
    #Pick out all of the HTML stuff our xml parser will choke on
    while portfolioTableHtml.find("<tr id=") != -1:
        tempStartIndex = portfolioTableHtml.find("<tr id=")
        tempEndIndex = portfolioTableHtml.find("</table>\n</td>\n<tr>") + 21
        tempSubString = portfolioTableHtml[tempStartIndex:tempEndIndex]
        portfolioTableHtml = portfolioTableHtml.replace(tempSubString, "")    
        
    while portfolioTableHtml.find("<td align=\"center\" ><img") != -1:
        tempStartIndex = portfolioTableHtml.find("<td align=\"center\" ><img")
        tempEndIndex = portfolioTableHtml.find("\"></td>") + 7
        tempSubString = portfolioTableHtml[tempStartIndex:tempEndIndex]
        portfolioTableHtml = portfolioTableHtml.replace(tempSubString, "")    
        
    while portfolioTableHtml.find("<a href=") != -1:
        tempStartIndex = portfolioTableHtml.find("<a href=")
        tempEndIndex = portfolioTableHtml.find(">", tempStartIndex) + 1
        tempSubString = portfolioTableHtml[tempStartIndex:tempEndIndex]
        portfolioTableHtml = portfolioTableHtml.replace(tempSubString, "")
        
    tempStartIndex = portfolioTableHtml.find("<tr bgcolor=\"#BBBBBB\">")
    tempEndIndex = portfolioTableHtml.find("</tr>", tempStartIndex) + 5
    tempSubString = portfolioTableHtml[tempStartIndex:tempEndIndex]
    portfolioTableHtml = portfolioTableHtml.replace(tempSubString, "")  

    
    removeSubStrings2 = [["<td bgcolor='#ccccff' colspan=2>&nbsp;</td>",""],
                        ["<td bgcolor='#ccccff' align=center colspan=3><b>Today</b></td>",""],
                        ["<td bgcolor='#ccccff' align=center colspan=2><b>Holdings</b></td>",""],
                        ["<td bgcolor='#ccccff' align=center colspan=2><b>Overall</b></td>",""],
                        ["<td align=center bgcolor='#ccccff'><b>Icon</b></a></td>",""],
                        ["<tr>\n\n\n\n\n</tr>",""],
                        ["<tr><td align=\"right\" colspan=\"5\">Totals:</td><td\">4,000</td>",""],
                        ["<tr><td align=\"right\" colspan=\"5\">Totals:</td><td\">4,000</td>",""],
                        ["<tr><td align=\"right\" colspan=\"5\">Totals:</td><td\">4,000</td>",""],
                        ["<b>",""],
                        ["</b>",""],
                        ["<table align=center cellpadding=3 cellspacing=0 border=1>","<table>"],
                        [" bgcolor=\"#EEEEFF\"",""],
                        [" bgcolor=\"#FFFFFF\"",""],
                        [" bgcolor=\"#BBBBBB\"",""],
                        [" bgcolor='#ccccff'",""],
                        [" align=\"center\"",""],
                        [" align=center",""],
                        ["<font color=\"",""],
                        ["<font size=1>(profile)",""],
                        ["green\">",""],
                        ["red\">",""],
                        ["black\">",""],
                        ["</font>",""],
                        ["</a>",""],
                        ["<nobr>",""],
                        ["</nobr>",""],
                        ["<br>",""]]
    
    for i in removeSubStrings2:
        portfolioTableHtml = portfolioTableHtml.replace(i[0], i[1])
    
    
    ##
    # Aggregate into dictionary
    ##  
    
    stockHoldings = {}
    
    table = etree.XML(portfolioTableHtml)
    rows = iter(table)
    next(rows)
    for row in rows:
        stockHoldings[(row[0].text).strip()] = int(row[4].text.strip().replace(",",""))

    return stockHoldings
    
##
# Picks the stock to purchase for the day by checking which ones are at 15 NP
# If multiple stocks are at 15 NP, it buys the one we currently own the least
# amount of. If there is a tie, it decided between them randomly.
##     
def pickStockPurchase(prices, holdings): 
    
    #Make a list of the stocks at 15NP and a list of raw holdings for easy use of min later
    # If no stocks at 15NP / share then look for 16 and even 17 if necessary
    potentialPurchases = []
    holdingsValues = []
    stocksAtPrice = False
    targetPrice = 15
    while not(stocksAtPrice) and targetPrice <= 17:
        for ticker,price in  prices.iteritems():
            if price == targetPrice:
                stocksAtPrice = True
                if ticker in holdings:
                    potentialPurchases.append([ticker, holdings.get(ticker)])
                    holdingsValues.append(holdings.get(ticker))
                else:
                    potentialPurchases.append([ticker, 0])
                    holdingsValues.append(0)
        targetPrice = targetPrice + 1
                    
    
    #Go through the possible cases for potentialPurchases, act accordingly         
    if len(potentialPurchases) == 0:
        return "No Stocks at 15-17NP"
    elif len(potentialPurchases) == 1:
        return potentialPurchases[0][0]
    else:      #Most complicated possibility, pick one we own the least shares of,
                #randomly selected in event of tie.
        minHoldings = min(holdingsValues)
        shortList = []
        for stock in potentialPurchases:
            if stock[1] == minHoldings:
                shortList.append(stock[0])
        if len(shortList) == 1:
            return shortList[0]
        else:
            randomPick = random.choice(shortList)
            return randomPick
            

##
# Actually buys 1000 shares of the stock picked buy pickStockPurchase()
# Relies on the index of the form and inputs, hopefully this doesn't change.
##        
def buyStocks(ticker, browser):
    
    #The stock buying page URL
    buyPage = "http://www.neopets.com/stockmarket.phtml?type=buy"

    browser.open(buyPage)
    #The form we want isn't named, but it's the second one on the page
    browser.select_form(nr=1)
    #selecting the controls by name doesn't work, so we get them by index
    controls = browser.form.controls
    controls[2]._value = ticker # the ticker
    controls[3]._value = "1000"   # the number of shares
    
    humanizingDelay(5,minLength=1)
    response = browser.submit()
    
    #check that everything worked, return a string with the result
    if response.geturl() == "http://www.neopets.com/stockmarket.phtml?type=portfolio":
        return "Success: 1000 shares of ["+ticker+"] have been purchased."
    elif response.geturl() == "http://www.neopets.com/process_stockmarket.phtml":
        responseHTML = response.read()
        startToken = "<b>Error:"
        endToken = "</div>"
        startIndex = responseHTML.index(startToken)
        endIndex = responseHTML.index(endToken,startIndex)   
        errorString = responseHTML[startIndex:endIndex].replace("</b>","").replace("<b>","")
        return errorString
    else:
        return "Error: Unknown problem occured while buying stocks."

    
    


##
# Decides if any of the stocks in our portfolio are beyond the sale threshold.
# All of the current holdings are sold if they are beyond. Returns a Dictionary 
# of Ticker and number of stocks to sell.
##         
def pickStockSales(prices, holdings):

    sellThreshold = 50
    stocksToSell = {}
    
    # Go through the stocks we own, check if we should sell, then if so 
    # add to list at half the currently owned shares (rounded up)
    for ticker in holdings.keys():
        if prices.get(ticker) >= sellThreshold:
            stocksToSell[ticker] = holdings.get(ticker)
    return stocksToSell
      


##
# Implements the actual selling of the stocks from pickStockSales()
# Returns a boolean indicating success (for log file purposes)
##         
def sellStocks(salesList, browser):
    
    salesPage = "http://www.neopets.com/stockmarket.phtml?type=portfolio"
    browser.open(salesPage)
    #The form we want isn't named, but it's the second one on the page
    browser.select_form(nr=1)
    #We don't know the exact names of the inputs, they have a ref number, time to search:
    controls = browser.form.controls
    for control in controls:
        try:
            if len(control.name) > 10:
                if control.name[5:control.name.find("]")] in salesList:
                    control._value = "1000"
        except TypeError:
            pass
    
    humanizingDelay(5,minLength=1)
    response = browser.submit()
    
    #check that everything worked, return a string with the result
    if response.read().find("There were no successful transactions") == -1:
        return True
    else:
        errorHTMLdump.write("/n"+datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+"/n"+response.read())
        return False
    
    
main()

The script is broken down into a pretty logical sequence as it’s very procedural. It starts with the main function which deals with logging in, collecting bank interest and withdrawing neopoints for the stock market purchase if necessary, and then finally playing the stock market. Finally, it goes to deposit the neopoints gained from the stock market if any sales were made, then logs out. Pretty simple on the face of it, but the real interesting parts are the actual interactions with the webpages. In order for our bot to be able to do anything meaningful we need to first extract the information we care about from the page, then have the script make the right decisions using that information, and finally put the calculated input values into the right fields and submit the data. For the login, and bank portions of this bot, that’s fairly simple; it’s just a matter of going to the correct url selecting the right forms, and submitting a static or otherwise easy to calculate value that is pulled out of the HTML by finding some substrings that encapsulate the data we’re after.

Screenshot from 2014-09-27 14:46:58

The list of Neodaq stocks and their prices.

The stockManager() function is a little less straight forward. The Neopets stock market game is a simple html form based game, which makes most of this possible (building a bot to interact with flash content? No thank you!). This means that we use the same techniques as we did for the login and bank pages, we’re just after information that is a presented in a slightly more complex way as an HTML table. We’re interested in the structure of the table, and we need to maintain that, just without all of the HTML bits that don’t mean anything to us. This is done through the extractStockPrices() and extractStockHoldings() functions. They both work in pretty much the same way, by taking the entire HTML page as a string, isolating the table of interest, stripping out some of the problematic HTML bits, then putting it all into a dictionary that maintains the data structure using XML.

Screenshot from 2014-09-27 14:47:28

My portfolio of Neodaq stocks.

Finally, with the data isolated from the formatting, we can figure out what it is we actually want to do. This is accompolished using the pickStockPurchase(), buyStocks(), pickStockSales(), and sellStocks() functions. The pickStockPurchases() function goes through the dictionary of stocks with their prices we just made and looks for stocks in the 15-17 NP per share range. It then selects the lowest priced one that we currently own the least of. The buyStocks() function then simply purchases 1000 shares of this stock (which is the maximum number of shares any one user can purchase per day). The pickStockSales() function then looks at our portfolio of stocks and finds any that surpass the user defined sale threshold. It puts all stocks that need to be sold into a list and then the sellStocks() function takes care of actually selling them. The sellStocks() function sells off 100% of the holdings of any stock that meets this criteria which keeps things simpler, as the sales page is built in such a way that a single stock is broken down into each of the individual purchases that went into it rather than the total holdings. We originally purchased the stock in 1000 share units, which means the bot can simple put 1000 into all of the input fields that belong to that ticker to completely divest that stock.

Now you may have noticed that there is no means of ensuring that your bank account contains enough neopoints to facilitate the 17,000 NP withdrawal at the start. This means that it is necessary to ensure your seed capital is sufficient that you won’t burn through it before you start seeing returns. How long it takes before you start seeing returns is a matter of what your sales threshold is set at. For the most part, stocks on the Neopets stock market follow a pretty predictable pattern of swinging back and forth between about 6 and 60 NP per share. It’s less common, but stocks do periodically move to above this 60 NP range before they begin falling back to the 6 NP area. What this means for you is that the higher you set your sales threshold, the longer it will be before you see returns (although the returns will be larger). So if you are starting with a smaller pool of seed capital, it will make sense to use a lower threshold so that you won’t run into an empty bank account. Personally I started with the 1.5 million NP that were in my account from long ago, and used a sales threshold of 45 NP per share to start. If I recall correctly, the balance in my bank account never dropped below about 750,000 NP before I started earning it back. Since there is a strong element of randomness in all of this it is a very good idea to have more seed capital than you think you’ll need at your chosen sale threshold. A lot of people like to pick a return of 1,000,000 NP per month as their goal. To achieve this you need to earn a daily return of almost 33,000 NP per day (12 million divided by 365 days). You’re buying 1000 shares at up to 17,000 NP each day, so this means you should sell these shares at 33 NP per share above what you paid. This works out to a nice round number of 50 NP per share. Nice. This isn’t really a bad sales threshold to start at if you have the bank balance to support it.

For those lucky enough to have a very large sum of neopoints in their bank account, the burn rate will not even be an issue. This is because the bot collects bank interest. If your bank interest is in excess of 17,000 NP per day (the maximum this bot will ever spend on stocks) then you will never see a net decrease in your account balance. You’d need to have a bank balance of 49,640,000 NP before this would be the case though, so you’re probably not going to be in this privileged position to begin with. But that’s enough theory on burn rates and returns. If you want to read and learn more about this stuff then there are plenty of places to start.

Throughout this whole process the script also keeps detailed logs of what it is doing. This allows you to quickly check up on the progress of the bot without logging in to Neopets and then trying to figure out what has happened since you last looked.

Screenshot from 2014-09-27 15:06:59

The log file.

So now that we have the script, it’d be nice to have a way to make it run automatically each day too, right? If you’re a linux or mac user, then there’re two wonderful tools called cron and anacron that can accomplish this. I prefer anacron since it will work on systems that are not guaranteed to be on when the task is is scheduled; it’ll simply ensure the task is run once per day if the computer is turned on at all during that day. Cron also works well on server like systems with near 100% uptime. Windows users will have to use the windows task scheduler which has a fairly similar mechanic to cron and anacron.

But lets just go ahead and look at the process for setting up anacron:

To schedule a task with anacron, we use anacrontab:

sudo nano /etc/anacrontab

There will be a few lines in there already. At the bottom you simply want to add the following one:

1 6 cron.daily nice python /<PATH TO FILE>/stockbot.py >/dev/null

What this does is tells the system to run the task daily, with a delay of 6 minutes after start up. It assigns the task to cron.daily label (you can change this), and then executes the command “python /<directories>/stockbot.py” and sends any CLI output to null (basically computer oblivion).

You can learn more about cron and anacron here.

And that’s that! You now have a Neopets stock market bot running daily and earning you free neopoints. Congrats. It only took about 10 years.

Posted by Everett in Programming, 1 comment
Electric Longboards and Adrenaline Rushes

Electric Longboards and Adrenaline Rushes

  1. Why pursue this madness?
  2. The parts
  3. Putting it all together
  4. The results
The final board

A side shot of the final board.

 

Why pursue this madness?

Like many of my projects, it all started when I stumbled upon something interesting on the internet. In this case it was a video of the Board of Imagination. Now, a mind controlled skateboard is pretty cool, and arguably the main point of interest in that project. But it was the motorization of the board that really captivated me. Motorizing skateboards is really nothing new, in fact the use of a normal DC motor and gears in the Board of Imagination is pretty old school. It’s essentially all Brushless DC motors (BLDC) and timing belts now. There are also entire communities built around achieving and refining the goal of motorized anything. And it was that little bit of research that did me in. I no longer just wanted one; I needed one.

I spent a lot of time working out the logistics of motorizing a longboard, and explored all of the options. There was the expensive but very slick looking LaGrange L1 truck. The pretty venerable looking Alien Drive System and Alien Power System combo. Last of all, there was the very sturdy looking one piece Paris truck with welded on motor mount provided at http://diyelectricskateboard.com/. The DIY Electric Skateboard kit looked to be the newest kid on the block, and didn’t really have as much in the way of reviews. But it looked like it was the most approachable in terms of repairs and tinkering, and that’s what sold me on it. Or on the design at least.

Originally my plan had been to weld a similarly styled, but self designed and fabricated motor mount to a set of Paris Trucks. I knew the equipment at Protospace (the local makerspace) was capable of it, but I lacked the skill set to use any of those tools. It became obvious to me that I would need to simply buy the pre-made trucks if I wanted to have my board running before the end of summer. It was a good call, because with that part of the project taken care of, the rest of it quickly progressed, and I now have a functioning motorized longboard.

But enough talking, lets look at the process of building such a toy. Seriously though, this board has an insane amount of power. It can accelerate way too fast and reach a top speed well above my current comfort level of about 30km/h. Don’t treat it like a toy.    

 

The parts:

  1.  A longboard deck. I used the Earthwing Big Hoopty. It’s a super solid and awesome board at an easy $107 from a local board shop.
  2. The trucks and drive mechanism from DIYelectricskateboard.com.
  3. Some bearings, as they don’t come with the DIY Electric kit.
  4. A brushless DC motor.
  5. An Electronic Speed Controller to run the motor. I took a risk on this Boat ESC. The braking is a little soft, but it runs well, barely gets warm even without proper cooling, and doesn’t make excess noise.
  6. At least one battery pack. A 5000 mAh 6S (22.2V) provides plenty of speed and power. I used two 3S batteries in series to make a 6S battery with a lower profile. 
  7. A receiver and transmitter of some sort. I used the all too common Quanum (nope, not a typo) pistol grip combo.
  8. RC connectors. I used HXT4mm to be consistent with the existing connectors.
  9. Some way to attach the parts to the deck. I used industrial strength Velcro tape.
  10. Some way to shield the LiPo batteries from damage by debris. I tried using ABS, gave up, and resorted to a box made from 22 gauge galvanized steel sheet.

 

Putting it all together:

The process of putting together isn’t all that difficult. With the step requiring the welding of a motor mount to a truck eliminated, the most difficult part was definitely the construction of the battery shield. But we get ahead of ourselves. Lets start with the deck and trucks.
IMG_2460

The longboard deck with trucks and wheels attached.

The first step of mounting the trucks is very straight forward. If you have ever assembled a normal skateboard or longboard before, then you already know what to do. For those who haven’t put together a board, I’ll cover the basics. The easiest place to start is inserting the bearing into the ABEC 11 Flywheel clones. This is just a matter of putting one bearing onto the axle, then pressing the wheel down onto it. Repeat this process for each side of each wheel. Once the wheels are set up, you can fasten them to the trucks with the appropriate nut. Ensure the wheel with the plastic timing belt pulley goes on the correct side of the truck with the motor mount. Tighten the nuts just enough to remove any play along the axle. Finally, you can bolt the trucks to the long board deck. This is very straight forward, you just need to use provided bolts and nylon lock nuts, and ensure that the kingpin is facing outward.

IMG_2461

The motor pulley. Note the two holes for set screws.

With the basic hardware mounted, we can set up the motor too. Notice that the provided timing belt pulley for the motor has two bolts to tighten it to the motor shaft. It’s very helpful to have a couple  of flat spots on the motor shaft for these bolts to fasten against.

IMG_2462

The brushless DC motor shaft has been dremeled down to make flat spots for the set screws.

I achieved this by using a Dremel tool with a carbide bit. I recommend that you punch the motor shaft through a piece of paper towel before grinding the flat spots in, as the metal filings are going to be magnetized and get stuck to the shaft. The paper towel allows you to easily wipe them all away.

IMG_2464

The BLDC motor has been attached to the mounting bracket. It’s still loose enough to slide in the slots.

Attaching the motor pulley is pretty easy, but first we need to losely fit the motor to the motor mount. You want to attach the motor so that it’s flush with the motor mount, but still just lose enough to slide in the slots. We need it loose to tension the motor after we put the pulleys and belt on. The motor pulley is really easy. Just line the bolts up with the Dremelled down spots and tighten them using an Allen Key.  Once that is done, the timing belt can be added. If everything is still a little lose, then it will be as simple as just slipping it over the wheel and then the motor pulley.

IMG_2465

The belt is now in place and ready for tensioning.

Finally, we need to tension the belt. This is achieved by pulling the motor along the mount until the belt is firm, but not excessively tight, then tightening the four M4 socket head bolts that hold the motor to the mount. This is also not a bad time to adjust the tightness of the bushings on the trucks (the plasticy coloured parts on the kingpin). Just loosen them until you find it steers well, but without any contact between the wheels and the board.

IMG_2466

The belt has been pulled taught and the motor has been tightened onto the mounting bracket.

And that’s it for the Mechanical side of things. Next up is the electronics and the protective shield for the electronics.

IMG_2467

The ESC has had the connectors soldered on and is plugged into the motor.

The electronics are also pretty straight forward. For the most part it is all just plug and play, with the exception of the ESC, which requires one to solder on the connector of their choice. Since everything else I ordered is using the HXT4 mm bullet connectors, I simply stuck with those. I hacked three of them apart to create three stand alone female connectors for the motor, and used a full one for the power connection. It’s important to make sure you get the polarity right, so match up the wire colours with one of the batteries to be sure you solder it on the right way. The insulation on the wire is also too large for the red plastic housing, so I stripped it back a little bit to solder the connector on before putting it back in place and taping the insulation back up. I used tape because I forgot to put some heat shrink tubing on before the connector. Don’t make the same silly mistake, and remember the heat shrink tubing!

Now it’s just a matter of plugging everything together and attaching it to the bottom of the board. I hooked everything together and made sure the motor worked first, then I set to work planning the layout of the components on the underside of the board. Once I was satisfied I used the industrial strength adhesive Velcro tape to hold everything in place. It has a 2 inch width which is exactly the right width for the ESC and batteries. At this point the board is technically ridable, but LiPo batteries have a reputation for being a little “explodey” if they are damaged, so I wanted to play it safe and put a shield of sorts over them.

DSCF0230

The failed ABS plastic shield. ABS is a finicky material.

Building the shield was the hardest and most frustrating part. I had originally planned to construct it out of .25″ ABS sheet, but that failed spectacularly. Apparently ABS plastic is renowned for its tendency to warp as it cools (it’s why 3D printers using ABS need heated base plates), and may not be the ideal material to learn on the fly without prior experience. I tried to make it work with just a heat gun, but that wasn’t going to work, so I resorted to slump forming the sheet in an oven.That also didn’t work. ABS is a pain, it’s no wonder it is usually moulded to a form using a vacuum.

After the failure of the ABS shield, I decided to try my hand at making a box out of sheet metal. I was able to get 8 inch wide 22 gauge galvanized steel from the local Home Depot, and I figured that I could pick up the sheet metal skill set a lot more easily.

SCAN0002a

The front panel that faces the direction of motion.

SCAN0001a

The main body of the sheet metal shield.

I designed a pattern, and cut it out of the sheet using some straight snips. Unfortunately my 8 inch wide sheet wasn’t wide enough to construct the box as a single piece as I had hoped, but I figured I could compensate  by making the forward facing panel separately, and attaching it to the rest of the box with flat lock seams. I didn’t even bother soldering the pieces together.

IMG_2488

The front panel cut out of the sheet metal.

IMG_2485

Bending the main body of the box into shape.

For the most part it worked. The shield isn’t beautiful, but it is sturdy. The front panel rattles a little on rough roads, but I can’t see any circumstances in which it would fall off. In retrospect, a 22 gauge steel box is probably overkill as far as shielding goes, but there’s also some risk of the metal causing a short if it didn’t stand up to an impact, so over kill is probably good in this case.

IMG_2491

The finished box. It’s not going to win any competitions, but it will protect the batteries.


With the box complete, I drilled some holes in the base flanges, and then drilled some corresponding holes in the longboard deck when I had everything centred. Using 1.5″ long flat head bolts and some matching wing nuts I can easily replace and remove the shield as needed.

IMG_2493

The holes drilled in the flanges so the box can be attached to the longboard deck.

DSCF0229

The longboard deck has corresponding holes with bolts and wing nuts. Some foam has been added to the shield to cushion the batteries.

DSCF0228

The finished shield firmly bolted to the underside of the deck.

The results:

The final product of all this labour is an adrenaline rush. I’ve never taken a longboard downhill, so I really don’t have a taste for the high speed longboarding some people are accustomed to. But that’s okay because it means that I get a nice pleasant adrenaline rush when I manage to get this board above 30km/h .

Forgive the poor quality of the video, I lack a GoPro, so I had to film it with my phone. This also had the side effect that I didn’t really want to get up to speed, it’s nerve racking enough already without the additional filmography concerns.

In general, the acceleration has a ton of punch and the brakes are quite soft, especially at lower speeds. At higher speeds they work a lot better, and will certainly get you down to a speed at which you can toe drag to slow down. If you’re going down a really steep hill you may find that the brakes are too weak to prevent the acceleration due to gravity, so act accordingly. If that means toe dragging the whole way or walking the board down the hill, then do it. It also helps to reprogram the ESC to fit your riding style. You can find the instructions here: http://www.hobbyking.com/hobbyking/store/uploads/842133110X337002X22.pdf

I ended up keeping all the defaults except the startup acceleration, which I set to 9 levels. This makes the acceleration a lot smoother, so you’re less likely to over do it and fall over backwards as the board rockets forward.

Posted by Everett in Electronics, Kits, 4 comments
Mapping Oil and Gas Incidents in Alberta: Improvements

Mapping Oil and Gas Incidents in Alberta: Improvements

This post is a continuation of the original Mapping Oil and Gas Incidents in Alberta with Google Maps, JQuery, and PHP post. If you wish to know more about this project or find the code for version 1, it is suggested you start there.

The new map, with 100% less marker overlap and all relevant incidents displayed for a selected location.

If you just want a copy of all the files necessary (minus the database), then I have them both on github or in a zipped archive. Don’t forget to go in and change the values of config.inc.php to reflect your own MySQL database. If you would like the original ver. 1 source code, please check the original post.

Developing software is a never-ending process. There’s always a bug to fix, a feature that should have been included, and a better way of doing the same thing. The first version of this project had a number of issues that I had identified. There are still the issues related to the data; that only incidents falling under the jurisdiction of the ERCB/AER are included, and that plotted incident locations are only accurate to +/-200m in each axis. The related issue of multiple incidents being plotted on top of each other and difficult to select without extensive filter use is now fixed though. All incidents for a given ATS Legal Subdivision and the applied filters are now returned and listed in the Incident Details panel, along with a small indication of the number of incidents that have been selected.

The final issue; three points in the South Atlantic Ocean are still there, and the multiple incidents per map marker feature that has been implemented doesn’t work with them. This is because they are still being plotted on the map using latitude and longitude, but the incident info selection process works based on the Alberta Township System location. While these points all share a latitude and longitude of 0,0 their ATS locations are different, and so the problem of overlapping markers still exists here.

In addition to these issues identified for the original write-up, it also came to my attention that there was a problem with the way the page rendered on iPads. This was easily fixed by switching the width scale of the info-panel <div> element to be based upon the “em” unit instead of % as it originally was. By having the width of the feature be based on a fundamentally text oriented unit, the rendering has actually been made more consistent across all devices.

 *****

Lets take a closer look at the most major change; fixing the overlapping markers issue started with identifying the ATS location for each map marker as the preferred identifier over the Incident Number. Since we are now selecting database entries using the Location column in the database, it makes sense to add an index to this column:

ALTER TABLE `Spills` ADD INDEX( `Location` );

The getSpillInfo.php file also needs to be updated to reflect these changes. It now no longer just fetches the info for a specific unique Incident Number, it needs to fetch all the results for a given ATS location and the filter parameters set:

<?php
require('config.inc.php');

$atsLocation = $_POST['Location'];
$currentlicensee = $_POST['currentLicensee'];
$currentsubstance = $_POST['currentSubstance'];
$currentsource = $_POST['currentSource'];
$yearmin = $_POST['yearMin'];
$yearmax = $_POST['yearMax'];
$volumemin = $_POST['volumeMin'];
$volumemax = $_POST['volumeMax'];

// Fix the years to go from start of first year to end of the last.
$datemin = $yearmin."-01-01";
$datemax = $yearmax."-12-31";

//By using PDO and prepare, everything is automagically escaped
$db = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

//Start building the statement with the base of the query
$stmtString = "SELECT * FROM `Spills` WHERE (`Location` = :ATSLocation AND (`IncidentDate` BETWEEN :dateMin AND :dateMax) AND (`Volume Released` BETWEEN :volumeMin AND :volumeMax)";

//Add in the filters if they're set
if ($currentlicensee !== "All") {
 $stmtString .= " AND `LicenseeName` = :licensee";
}
if ($currentsubstance !== "All") {
 $stmtString .= " AND `Substance Released` = :substance";
}
if ($currentsource !== "All") {
 $stmtString .= " AND `Source` = :source";
}

//Finish the statement with the sorting part
$stmtString .= ") ORDER BY `IncidentDate` DESC";

//Bind all of the parameters
$stmt = $db->prepare($stmtString);
if (strpos($stmtString,':licensee') !== false) {
 $stmt->bindValue(':licensee', strval($currentlicensee), PDO::PARAM_STR);
}
if (strpos($stmtString,':source') !== false) {
 $stmt->bindValue(':source', strval($currentsource), PDO::PARAM_STR);
}
if (strpos($stmtString,':substance') !== false) {
 $stmt->bindValue(':substance', strval($currentsubstance), PDO::PARAM_STR);
}
$stmt->bindValue(':dateMin', strval($datemin), PDO::PARAM_STR);
$stmt->bindValue(':dateMax', strval($datemax), PDO::PARAM_STR);
$stmt->bindValue(':volumeMin', strval($volumemin), PDO::PARAM_STR);
$stmt->bindValue(':volumeMax', strval($volumemax), PDO::PARAM_STR);
$stmt->bindValue(':ATSLocation', strval($atsLocation), PDO::PARAM_STR);
$stmt->execute();

//Get the results of the query
$result;
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo header('Content-type: application/json');
echo json_encode($result);

?>

With the ATS location being the primary selection parameter, we’ll also need to update the getSpillLocations.php file to reflect this. Namely line 25, where the `Location` column has been added to the SELECT statement, and required to be distinct as well. This will ensure we are provided up to 100 unique legal subdivisions containing the largest single (non-cumulative) incidents for the current map view port:

<?php
require('config.inc.php');

//Get all of the POST data
$currentlicensee = $_POST['currentLicensee'];
$currentsubstance = $_POST['currentSubstance'];
$currentsource = $_POST['currentSource'];
$yearmin = $_POST['yearMin'];
$yearmax = $_POST['yearMax'];
$volumemin = $_POST['volumeMin'];
$volumemax = $_POST['volumeMax'];
$latmin = $_POST['latMin'];
$latmax = $_POST['latMax'];
$longmin = $_POST['lngMin'];
$longmax = $_POST['lngMax'];

// Fix the years to go from start of first year to end of the last.
$datemin = $yearmin."-01-01";
$datemax = $yearmax."-12-31";

//By using PDO and prepare, everything is automagically escaped
$db = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

//Start building the statement with the base of the query
$stmtString = "SELECT DISTINCT(`Location`), `Latitude`, `Longitude` FROM `Spills` WHERE (((`Longitude` BETWEEN :longMin AND :longMax) AND (`Latitude` BETWEEN :latMin AND :latMax) AND (`IncidentDate` BETWEEN :dateMin AND :dateMax) AND (`Volume Released` BETWEEN :volumeMin AND :volumeMax))";

//Add in the filters if they're set
if ($currentlicensee !== "All") {
 $stmtString .= " AND `LicenseeName` = :licensee";
}
if ($currentsubstance !== "All") {
 $stmtString .= " AND `Substance Released` = :substance";
}
if ($currentsource !== "All") {
 $stmtString .= " AND `Source` = :source";
}

//Finish the statement with the sorting and limit parts
$stmtString .= ") ORDER BY `Volume Released` DESC LIMIT 100";

//Bind all of the parameters
$stmt = $db->prepare($stmtString);
if (strpos($stmtString,':licensee') !== false) {
 $stmt->bindValue(':licensee', strval($currentlicensee), PDO::PARAM_STR);
}
if (strpos($stmtString,':source') !== false) {
 $stmt->bindValue(':source', strval($currentsource), PDO::PARAM_STR);
}
if (strpos($stmtString,':substance') !== false) {
 $stmt->bindValue(':substance', strval($currentsubstance), PDO::PARAM_STR);
}
$stmt->bindValue(':latMin', strval($latmin), PDO::PARAM_STR);
$stmt->bindValue(':latMax', strval($latmax), PDO::PARAM_STR);
$stmt->bindValue(':longMin', strval($longmin), PDO::PARAM_STR);
$stmt->bindValue(':longMax', strval($longmax), PDO::PARAM_STR);
$stmt->bindValue(':dateMin', strval($datemin), PDO::PARAM_STR);
$stmt->bindValue(':dateMax', strval($datemax), PDO::PARAM_STR);
$stmt->bindValue(':volumeMin', strval($volumemin), PDO::PARAM_STR);
$stmt->bindValue(':volumeMax', strval($volumemax), PDO::PARAM_STR);
$stmt->execute();

//Get the results of the query
$result;
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//Spit out the results in json form
echo header('Content-type: application/json');
echo json_encode($result);
?>

Now the index.html file needs to be updated to reflect these changes. This starts with the spillID attributes of the map marker objects being replaced with ATS Location, the plotSpills() function being modified to not duplicate markers when one already exists for that LSD, the loadSpillInfo() function being modified to use the ATS location, then finally populate the Incident Details panel with a “Number of incidents selected” count and a table for each of these incidents.

<!DOCTYPE html>
<html>
    <head>
        <meta name="viewport" content="initial-scale=1.0">
        <meta charset="utf-8">
        <title>Alberta Oil and Gas Incidents 1975 - 2013</title>
        <link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
        <link href="default.css" rel="stylesheet">
        <!-- Google Analytics -->
	<script>
	  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
	  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
	  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
	  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

	  ga('create', 'UA-51737914-1', 'x10.mx');
	  ga('send', 'pageview');

	</script>
	<!-- End Google Analytics -->
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
        <script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
        <script type="text/javascript"
            src="https://maps.googleapis.com/maps/api/js?key=AIzaSyCIxpXOSPJWNG7TnhMYq-Q2hPcM7zEQs8g&sensor=false">
        </script>
        <script>
            //Make a bunch of variables to track the filters and map boundaries
            var sqlParameters = {
                currentSubstance : 'All',
                currentSource : 'All',
                currentLicensee: 'All',
                yearMin : 1975,
                yearMax : 2013,
                volumeMin : 0,
                volumeMax : 37000000,
                latMin : 0,
                latMax : 0,
                lngMin : 0,
                lngMax : 0
            }

            /////////////////////////////////////
            //Nice control widgets from jQueryUI:
            /////////////////////////////////////

            //Popup dialog window for disclaimer
            $(function() {
                $( "#disclaimer" ).dialog({
                    autoOpen: false
                });

                $( "#disclaimer-opener" ).click(function() {
                    $( "#disclaimer" ).dialog( "open" );
                });
            });

            //Popup dialog window for license
            $(function() {
                $( "#license" ).dialog({
                    autoOpen: false,
                    width: 350
                });

                $( "#license-opener" ).click(function() {
                    $( "#license" ).dialog( "open" );
                });
            });

            //No data fetched dialog
            $(function() {
                $("#no-data").dialog({
                    height: 80,
                    autoOpen: false,
                    dialogClass: 'noTitleDialog',
                    open: function(event, ui){
                        setTimeout("$('#no-data').dialog('close')",3000);
                    }
                });
            });

            //Sliders
            $(function () {
                $(".slider").each(function () {
                    var begin = $(this).data("begin"),
                        end = $(this).data("end"),
                        step = $(this).data("step");

                    $(this).slider({
                        range: "true",
                        values: [begin, end],
                        min: begin,
                        max: end,
                        step: step,
                        slide: function (event, ui) {
                            //Update text box quantity when the slider changes
                            var sliderlow = ("#" + $(this).attr("id") + "_amount_low");
                            $(sliderlow).val(ui.values[0]);

                            var sliderhigh = ("#" + $(this).attr("id") + "_amount_high");
                            $(sliderhigh).val(ui.values[1]);
                        },
                        //When the slider changes, update the displayed spills
                        change: function(event, ui) {
                            if ($(this).attr("id") == "years") {
                                sqlParameters.yearMin = ui.values[0];
                                sqlParameters.yearMax = ui.values[1];
                            } else if ($(this).attr("id") == "volume") {
                                sqlParameters.volumeMin = ui.values[0];
                                sqlParameters.volumeMax = ui.values[1];
                            }
                            getSpills();
                        }
                    })

                    //Initialize the text box quantity
                    var sliderlow = ("#" + $(this).attr("id") + "_amount_low");
                    $(sliderlow).val($(this).slider("values", 0));

                    var sliderhigh = ("#" + $(this).attr("id") + "_amount_high");
                    $(sliderhigh).val($(this).slider("values", 1));
                })

                //When the text box is changed, update the slider
                $('.amount1').change(function () {
                    var value = this.value,
                    selector = $("#" + this.id.split('_')[0]);
                    selector.slider("values", 0, value);
                })
                $('.amount2').change(function () {
                    var value = this.value,
                    selector = $("#" + this.id.split('_')[0]);
                    selector.slider("values", 1, value);
                })
            });

            //Accordian divs
            $(function() {
                $( "#accordion" ).accordion({
                    collapsible: true,
                    autoHeight: false,
                    heightStyle: "content"
                });
            });

            //Get the Licensee list for the autocomplete widget
            var licenseeList = [];
            $.ajax({
                async: false,
                url : "getLicensees.php",
                dataType : "json",
                success: function(data){
                    licenseeList = data;
                },
                error: function (data)
                {
                    alert("Couldn't retrieve the licensee list. A page refresh will usually fix this.");
                }
            });

            //Auto Complete Licensee Selector
            $(function() {
                var cache = [];
                $( "#licensee-selector" ).autocomplete({
                    minLength: 2,
                    source: licenseeList,
                    select: function( event, ui ) {
                        sqlParameters.currentLicensee = ui.item.value;
                        getSpills();
                    }
                });

                $( "#licensee-clear" ).click(function() {
                    $( "#licensee-selector" ).val("");
                    sqlParameters.currentLicensee = 'All';
                    getSpills();
                });

            });

            //Drop down menus
            $(function() {
                $( "#substance-menu, #source-menu" ).menu();
            });  

            //When the DOM is loaded, we want to configure stuff like the menus
            $( document ).ready(function() {
                makeMenus();

                //A hackish way to set the spill-info content max height based on window height
                document.getElementById("spill-info").style.maxHeight = $(window).height()*0.40 + "px";

            });

            //Build the menus after the window has loaded (This is called at the end of <body>)
            function makeMenus() {

                //Get the substances and sources for the filter menus
                var substanceList = [];
                $.ajax({
                    async: false,
                    url : "getSubstances.php",
                    dataType : "json",
                    success: function(data){
                        substanceList = data;
                        //replace the initial null element
                        substanceList[0] = "All";
                    },
                    error: function (data)
                    {
                        alert("Couldn't retrieve the substance list. A page refresh will usually fix this.");
                    }
                });

                //And the Sources too
                var sourceList = [];
                $.ajax({
                    async: false,
                    url : "getSources.php",
                    dataType : "json",
                    success: function(data){
                        sourceList = data;
                        //replace the initial null element
                        sourceList[0] = "All";
                    },
                    error: function (data)
                    {
                        alert("Couldn't retrieve the source list. A page refresh will usually fix this.");
                    }
                });

                //Build the lists using the database results
                //Function courtesy of http://stackoverflow.com/questions/11128700/create-a-ul-and-fill-it-based-on-a-passed-array
                function constructLI(domID, array) {

                    var fieldID = (domID.split("-"))[0]+"-selected";

                    for(var i = 0; i < array.length; i++) {
                        // Create the list item:
                        var member = document.createElement('li');

                        // Set its contents:
                        var linkText = document.createTextNode(array[i]);
                        var link = document.createElement('a');
                        link.appendChild(linkText);
                        link.href= "#";
                        link.title= linkText;

                        //Make the onclick aspect of them menu work
                        link.onclick = function() { setText( fieldID, this.firstChild.nodeValue ) };

                        member.appendChild(link);

                        // Add it to the list:
                        document.getElementById(domID).appendChild(member);
                    }
                }
                constructLI("substance-links", substanceList);
                constructLI("source-links", sourceList);
            }

            //Set the drop down menu to reflect the new filter value and update the displayed results
            function setText(domID, text) {
                document.getElementById(domID).innerHTML = text;
                if (domID == "substance-selected") {
                    sqlParameters.currentSubstance = text;
                } else if (domID == "source-selected") {
                    sqlParameters.currentSource = text;
                }
                getSpills();
            };

            //////////////////////////////
            //Start the Google Maps stuff
            //////////////////////////////

            var map;
            var markers = [];
            var selectedMarker = new google.maps.Marker({
                                position: null,
                                icon: 'spotlight-poi.png',
                                map: map,
                                ATSLocation: ""
                        });
            var spillLocations;

            //Initialize when the map is done
            google.maps.event.addDomListener(window, 'load', initialize);

            function initialize() {         clearStyle: true;
                var middleEarth = new google.maps.LatLng(54.5, -115.0);
                var mapOptions = {
                    zoom: 6,
                    center: middleEarth,
                    mapTypeId: google.maps.MapTypeId.ROADMAP
                };

                map = new google.maps.Map(document.getElementById('map-canvas'), mapOptions);       

                makeGetSpillsEvent();
            }

            function makeGetSpillsEvent(){
                google.maps.event.addListener(map, 'idle', function() { getSpills();} );
            }

            function getSpills() {
                var mapCorners = map.getBounds();
                var ne = mapCorners.getNorthEast(); // LatLng of the north-east corner
                var sw = mapCorners.getSouthWest(); // LatLng of the south-west corder

                sqlParameters.latMin = sw.lat();
                sqlParameters.latMax = ne.lat();
                sqlParameters.lngMin = sw.lng();
                sqlParameters.lngMax = ne.lng();

                var newSpillLocations;  

                //Get the spill location data
                $.ajax({
                    url : "getSpillLocations.php",
                    type: "POST",
                    data : sqlParameters,
                    dataType : "json",
                    success: function(data){
                        SpillLocations = data;
                        plotSpills(SpillLocations);
                    },
                    error: function (data)
                    {
                        $( "#no-data" ).dialog( "open" );
                    }
                });
            }

            function plotSpills(spillLocations){
                map.clearMarkers(markers);
                markers = [];
                alreadyMapped = []; //An array to keep track of already populated ATS legal subdivisions
                markers.push(selectedMarker);
                alreadyMapped.push(selectedMarker.ATSLocation);
                //Stick those markers into the map canvas
                for (var i = 0; i < spillLocations.length; i++) {
                    //Dont duplicate the selected marker or LSDs with a marker already.
                    if (jQuery.inArray(spillLocations[i].Location, alreadyMapped) == -1) {
                    	alreadyMapped.push(spillLocations[i].ATSLocation);

                        var marker = new google.maps.Marker({
                            position: new google.maps.LatLng(spillLocations[i].Latitude, spillLocations[i].Longitude),
                            icon: 'spotlight-poi.png',
                            map: map,
                            ATSLocation: spillLocations[i].Location
                        });

                        makeLoadSpillInfoEvent(marker);

                    	markers.push(marker);
                    }
                }
            }

            //The info window function from http://jsfiddle.net/yV6xv/161/
            function makeLoadSpillInfoEvent(marker) {
                google.maps.event.addListener(marker, 'click', function() {
                    //Set the old marker back to red
                    selectedMarker.setIcon('spotlight-poi.png');
                    //Set the new marker to orange
                    selectedMarker = marker;
                    selectedMarker.setIcon('spotlight-poi-orange.png');
                    loadSpillInfo(marker.ATSLocation);
                });
            }

            //A function that fetches the specific spill info and loads it into the spill-info div
            function loadSpillInfo(ATSLocation) {

                var spillInfo = {};

                $.ajax({
                    async: false,
                    url : "getSpillInfo.php",
                    type: "POST",
                    data: $.extend({Location: ATSLocation}, sqlParameters), //send ATS location + filter parameters
                    dataType : "json",
                    success: function(data){
                        spillInfo = data;
                    },
                    error: function (data)
                    {
                        $( "#no-data" ).dialog( "open" );
                    }
                });

                //Clear existing content
                document.getElementById("spill-info").innerHTML = "";

                //A count of the selected incidents for the user to know how many spill info tables have been loaded
                var incidentCount = document.createElement('strong');
                incidentCount.innerHTML = 'Number of incidents selected: '+spillInfo.length.toString()+'<br>';
                document.getElementById("spill-info").appendChild(incidentCount);

                //Iterate through the JSON encoded spill info objects and create a table for each
                for (var i = 0;  i < spillInfo.length; i++){
                	var lineBreak = document.createElement('br');
	                var table = document.createElement('table');

	                //Populated the new table element
	                for (var key in spillInfo[i]) {
	                    if (spillInfo[i].hasOwnProperty(key)) {
	                        var row = document.createElement('tr');
	                        row.style.backgroundColor = "#ffebb8";
	                        var cell1 = row.insertCell(0);
	                        cell1.innerHTML = '<strong>'+key+'</strong>';
	                        var cell2 = row.insertCell(1);
	                        cell2.innerHTML = spillInfo[i][key];
	                        table.appendChild(row);
	                    }
	                }

	                //Put the table into the div
	                document.getElementById("spill-info").appendChild(lineBreak);
	                document.getElementById("spill-info").appendChild(table);
	        }
	        //Open the spill info accordion section
	        $('#accordion').accordion("option", "active", 1);
	    }

            //A customized clearOverlays function to remove the defunct markers but keep the selected one.
            google.maps.Map.prototype.clearMarkers = function() {
                for (var i = 0; i < markers.length; i++ ) {
                    //Dont kill the selected marker, we want it to persist
                    if (!(markers[i] === selectedMarker)) {
                        markers[i].setMap(null);
                    }
                }
            }
        </script>
    </head>
    <body>
        <div id="map-canvas" style="width:100%;height:100%;"></div>
        <div id="info-panel" style="text-align:left;">
            <div class="text-block">
                <h3>Alberta Oil and Gas Incidents 1975 - 2013</h3>
                This is a map that interactively graphs all of the Oil and Gas related spills in alberta between the years 1975 and 2013. It is based on the data acquired by <a href="http://globalnews.ca/news/622513/open-data-alberta-oil-spills-1975-2013/" target="blank">Global News</a> from the <a href="http://en.wikipedia.org/wiki/Energy_Resources_Conservation_Board" target="blank">ERCB</a> (now the <a href="http://www.aer.ca/" target="blank">AER</a>).
                </br>
                </br>
                For optimal loading speeds and a clean map, it caps the number of incidents displayed to the 100 biggest spills (by volume in m<sup>3</sup>) in the current map area. Try zooming in to see more spills, or play with the provided filters to see more incidents.
                </br>
                <p>
                    Learn more about this project at:
                    <a href="http://everettsprojects.com/2014/06/24/mapping-oil-and-gas-incidents-in-alberta-improvements/" target="blank">everettsprojects.com</a>
                </p>
            </div>
            <div id="accordion">
                <h3>Filter the Results</h3>
                <div id="filter-pane">
                    <p>
                        <label for="amount">Years:</label>
                        <span style="float:right;">
                            <input type="text" class="amount1" id="years_amount_low"  size="4">
                            <span class="orange-text"> - </span>
                            <input type="text" class="amount2" id="years_amount_high" size="4">
                        </span>
                    </p>

                    <div class="slider" id="years" data-begin="1975" data-end="2013" data-step="1"> </div>

                    <p>
                        <label for="amount">Volume:</label>
                        <span style="float:right;">
                            <input type="text" class="amount1" id="volume_amount_low" size="9">
                            <span class="orange-text"> - </span>
                            <input type="text" class="amount2" id="volume_amount_high" size="9">
                            <span class="orange-text"> m<sup>3</sup></span>
                        </span>
                    </p>

                    <div class="slider" id="volume" data-begin="0" data-end="37000000" data-step="1000"> </div>
                    <br>
                    <p>
                        <div class="ui-widget">
                            <label for="licensee-selector">Company: </label>
                            <input id="licensee-selector" style="width:17em;" class="orange-text">  <span style="float:right;">[<a href=# id="licensee-clear">X</a>]</span>
                            <br>
                        </div>
                    </p>

                    <p>
                        <ul id="substance-menu">
                            <li><a href="#">Substance: <span id="substance-selected" class="orange-text">All</span></a>
                                <ul id="substance-links">

                                </ul>
                            </li>
                        </ul>
                    </p>
                    <p>
                        <ul id="source-menu">
                            <li><a href="#">Source: <span id="source-selected" class="orange-text">All</span></a>
                                <ul id="source-links">

                                </ul>
                            </li>
                        </ul>
                    </p>
                </div>
                <h3>Incident Details</h3>
                <div id="spill-info">
                    This is where the data for a selected spill will be displayed. Click one to check it out!
                </div>
            </div>
            <div class="text-block">
                <p>
                    <a href="#" id="disclaimer-opener">Disclaimer</a> -
                    <a href="#" id="license-opener">Copyright (c) 2014 Everett Robinson</a> -
		    <a href="http://everettsprojects.com/2014/06/24/mapping-oil-and-gas-incidents-in-alberta-improvements/">ver. 2</a>
                </p>
            </div>
        </div>
        <div id="disclaimer" title="Disclaimer:" style="font-size:0.75em;">
            <p>
                I do not under any circumstances guarantee the accuracy or truthfulness of the provided information. Furthermore, this project should not be taken as representative of the former ERCB, AER, or any other applicable parties.
                <br>
                <br>
                Due to the use of the Alberta Township System, many locations are approximations only. In general, points can be considered accurate to 200 metres.
                <br>
                <br>
                Any spills originating from trans-provincial or trans-national pipelines are not included, since they do not fall under the jursdiction of the AER. Furthermore, many spills under 2 m<sup>3</sup> that did not originate from a pipeline may be absent, as they are not required to be reported.
            </p>
        </div>
        <div id="license" title="MIT License:" style="font-size:0.75em;">
            <p>
                Copyright (c) 2014 Everett Robinson
            </p>
            <p>
This content is released under the MIT License.
<br><br>
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
<br><br>
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
<br><br>
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.

            </p>
        </div>
        <div id="no-data" class="noTitleDialog" style="font-size:0.75em;">
            <p>
                Oops, the spill locations or data couldn't be loaded right now.
            </p>
        </div>
    </body>
</html>

My map still lacks one neat feature of the Global News map; the ability to see the cumulative spill volume for a particular legal subdivision. Since my map does not narrow the substances down to crude oil and its related products, it’s a non-trivial problem to effectively add up the cumulative volumes released without without blindly adding different substances together. Of course, blindly adding these values together will produce confusing and potentially meaningless results. In light of these concerns, cumulative volumes for each LSD is a feature I don’t care to reproduce at the time being.

With all of these changes, I’ve convinced myself that the project is done for the time being. Of course I know this isn’t true, and that there will always be bigger, better, faster, and less buggy versions to work towards. So until then, I hope you enjoy this improved version of the web application.

Posted by Everett in Environmental Science, Programming, Web Applications, 2 comments
Mapping Oil and Gas Incidents in Alberta with Google Maps, JQuery, and PHP

Mapping Oil and Gas Incidents in Alberta with Google Maps, JQuery, and PHP

spills

There is an updated version of this project with a number of improvements.

Sections:

  1. Motivations
  2. The Database
  3. The Code
  4. Considerations and Caveats

Motivation:

This is a project I conceived of back in university as an environmental science student, but never could make because the spill data on which it relies was not freely available. Later, while I was preoccupied with travel, Global News, a Canadian News broadcaster managed to get a copy of the database using a freedom of information request. They released  a news story including an interactive map of their own, which does some cool things this one does not. It plots the spills in such a way that the marker size reflects the cumulative volume spilled at that location, and it even breaks this cumulative spill up into multiple incidents by date if applicable. On the other hand, it only displays spills for Crude Oil and a few other related substances, offers a very limited view-port that cannot show the entire province, and does not provide any real filtering capabilities. Thankfully, Global News released their copy of the database, so I can build my own map that includes some of the features I think are cool (even if I’m a year late to the party). The database is in a .csv format, which I have since converted back to a SQL (mySQL) database. Unfortunately this back and forth conversion poses certain problems like truncated values in certain fields. Luckily these issues are fairly minimal.

So without further delay, lets move on to the process of converting the database back to SQL:

The Database:

I am assuming you have created a database already and have a database user that can access it. Making this user read only will also not be such a bad idea, since the web application will never need to modify values. All of this can be easily accomplished with phpMyAdmin, or a similar tool.

First lets create the table structure:

CREATE TABLE `Spills` (
`IncidentNumber` int DEFAULT NULL,
`IncidentType` varchar(255) DEFAULT NULL,
`Latitude` decimal(10,8) DEFAULT NULL,
`Longitude` decimal(11,8) DEFAULT NULL,
`Location` varchar(255) DEFAULT NULL,
`IncidentLSD` int DEFAULT NULL,
`IncidentSection` int DEFAULT NULL,
`IncidentTownship` int DEFAULT NULL,
`IncidentRange` int DEFAULT NULL,
`IncidentMeridian` int DEFAULT NULL,
`LicenceNumber` varchar(255) DEFAULT NULL,
`LicenceType` varchar(255) DEFAULT NULL,
`IncidentDate` varchar(255) DEFAULT NULL,
`IncidentNotificationDate` varchar(255) DEFAULT NULL,
`IncidentCompleteDate` varchar(255) DEFAULT NULL,
`Source` varchar(255) DEFAULT NULL,
`CauseCategory` varchar(255) DEFAULT NULL,
`CauseType` varchar(255) DEFAULT NULL,
`FailureType` varchar(255) DEFAULT NULL,
`StrikeArea` varchar(255) DEFAULT NULL,
`FieldCentre` varchar(255) DEFAULT NULL,
`LicenseeID` int(4) DEFAULT NULL,
`LicenseeName` varchar(255) DEFAULT NULL,
`InjuryCount` int DEFAULT NULL,
`FatalityCount` int DEFAULT NULL,
`Jurisdiction` varchar(255) DEFAULT NULL,
`ReleaseOffsite` varchar(255) DEFAULT NULL,
`SensitiveArea` varchar(255) DEFAULT NULL,
`PublicAffected` varchar(255) DEFAULT NULL,
`EnvironmentAffected` varchar(255) DEFAULT NULL,
`WildlifeLivestockAffected` varchar(255) DEFAULT NULL,
`AreaAffected` varchar(255) DEFAULT NULL,
`PublicEvacuatedCount` int DEFAULT NULL,
`ReleaseCleanupDate` varchar(255) DEFAULT NULL,
`PipelineLicenceSegmentID` int DEFAULT NULL,
`PipelineLicenceLineNo` int DEFAULT NULL,
`PipeDamageType` varchar(255) DEFAULT NULL,
`PipeTestFailure` varchar(255) DEFAULT NULL,
`PipelineOutsideDiameter(mm)` float DEFAULT NULL,
`PipeGrade` varchar(255) DEFAULT NULL,
`PipeWallThickness(mm)` float DEFAULT NULL,
`Substance Released` varchar(255) DEFAULT NULL,
`Volume Released` float DEFAULT NULL,
`Volume Recovered` float DEFAULT NULL,
`Volume Units` varchar(255) DEFAULT NULL,
`Substance Released 2` varchar(255) DEFAULT NULL,
`Volume Released 2` float DEFAULT NULL,
`Volume Recovered 2` float DEFAULT NULL,
`Volume Units 2` varchar(255) DEFAULT NULL,
`Substance Released 3` varchar(255) DEFAULT NULL,
`Volume Released 3` float DEFAULT NULL,
`Volume Recovered 3` float DEFAULT NULL,
`Volume Units 3` varchar(255) DEFAULT NULL,
`Substance Released 4` varchar(255) DEFAULT NULL,
`Volume Released 4` float DEFAULT NULL,
`Volume Recovered 4` float DEFAULT NULL,
`Volume Units 4` varchar(255) DEFAULT NULL,
 UNIQUE KEY `IncidentNumber` (`IncidentNumber`),
 KEY `IncidentNumber_2` (`IncidentNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

With the table made, we will load the Global News .CSV file into the table. Be sure to point it to the right file location for your server:

LOAD DATA LOCAL INFILE '<PATH TO THE FILE>/OPENDATA_spills.csv' INTO TABLE Spills
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES

Now we want to reformat all of the date fields so that they can be real dates within the database:

UPDATE Spills
SET IncidentDate = DATE(STR_TO_DATE(IncidentDate, '%m/%d/%Y'))
WHERE DATE(STR_TO_DATE(IncidentDate, '%m/%d/%Y')) &lt;&gt; '0000-00-00';

ALTER TABLE `Spills` CHANGE `IncidentDate` `IncidentDate` DATE NULL DEFAULT NULL;

UPDATE Spills
SET IncidentNotificationDate = DATE(STR_TO_DATE(IncidentNotificationDate, '%m/%d/%Y'))
WHERE DATE(STR_TO_DATE(IncidentNotificationDate, '%m/%d/%Y')) &lt;&gt; '0000-00-00';

ALTER TABLE `Spills` CHANGE `IncidentNotificationDate` `IncidentNotificationDate` DATE NULL DEFAULT NULL;

UPDATE Spills
SET IncidentCompleteDate = DATE(STR_TO_DATE(IncidentCompleteDate, '%m/%d/%Y'))
WHERE DATE(STR_TO_DATE(IncidentCompleteDate, '%m/%d/%Y')) &lt;&gt; '0000-00-00';

ALTER TABLE `Spills` CHANGE `IncidentCompleteDate` `IncidentCompleteDate` DATE NULL DEFAULT NULL;

UPDATE Spills
SET ReleaseCleanupDate = DATE(STR_TO_DATE(ReleaseCleanupDate, '%m/%d/%Y'))
WHERE DATE(STR_TO_DATE(ReleaseCleanupDate, '%m/%d/%Y')) &lt;&gt; '0000-00-00';

ALTER TABLE `Spills` CHANGE `ReleaseCleanupDate` `ReleaseCleanupDate` DATE NULL DEFAULT NULL;

There is also an issue with inconsistent units. Some entries have units of “m3”, and others are in units of “103m3”. Now it’s obviously not the case that the units are multiples of 103 in m3, but rather in 103 m3. In order for the volume filter to be implemented correctly, we’ll want consistent units:

UPDATE Spills SET  `Volume Released` = `Volume Released`*1000 WHERE `Volume Units`="103m3";
UPDATE Spills SET  `Volume Recovered` = `Volume Recovered`*1000 WHERE `Volume Units`="103m3";

UPDATE Spills SET `Volume Released 2` = `Volume Released 2`*1000 WHERE `Volume Units 2`="103m3";
UPDATE Spills SET `Volume Recovered 2` = `Volume Recovered 2`*1000 WHERE `Volume Units 2`="103m3";

UPDATE Spills SET `Volume Released 3` = `Volume Released 3`*1000 WHERE `Volume Units 3`="103m3";
UPDATE Spills SET `Volume Recovered 3` = `Volume Recovered 3`*1000 WHERE `Volume Units 3`="103m3";

UPDATE Spills SET `Volume Released 4` = `Volume Released 4`*1000 WHERE `Volume Units 4`="103m3";
UPDATE Spills SET `Volume Recovered 4` = `Volume Recovered 4`*1000 WHERE `Volume Units 4`="103m3";

That takes care of differing units, so now we don’t really need those units columns, since everything is in m3 now.

ALTER TABLE `Spills`
DROP `Volume Units`,
DROP `Volume Units 2`,
DROP `Volume Units 3`,
DROP `Volume Units 4`;

As this table isn’t going to be updated often (if at all), we’ll want to index anything that will be used as a search parameter.


ALTER TABLE `Spills` ADD INDEX( `Latitude`, `Longitude`, `IncidentDate`, `LicenseeName`, `Source`, `Substance Released`, `Volume Released`);

And there we have it, a database that should be ready for the web application that will sit on top of it.

The Code:

There is an updated version of this project with a number of improvements.

If you just want a copy of all the files necessary (for version 1), then I have them all in a zipped archive. Don’t forget to go in and change the values of config.inc.php to reflect your own MySQL database.

For everyone else, lets take a closer look at the code that makes it all work, starting with the JavaScript laden index.html file:

<!DOCTYPE html>
<html>
    <head>
        <meta name="viewport" content="initial-scale=1.0, user-scalable=no">
        <meta charset="utf-8">
        <title>Alberta Oil and Gas Incidents 1975 - 2013</title>
        <link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
        <link href="/spills/default.css" rel="stylesheet">
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
        <script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
        <script type="text/javascript"
            src="https://maps.googleapis.com/maps/api/js?key=AIzaSyCIxpXOSPJWNG7TnhMYq-Q2hPcM7zEQs8g&sensor=false">
        </script>
        <script>
            //Make a bunch of variables to track the filters and map boundaries
            var sqlParameters = {
                currentSubstance : 'All',
                currentSource : 'All',
                currentLicensee: 'All',
                yearMin : 1975,
                yearMax : 2013,
                volumeMin : 0,
                volumeMax : 37000000,
                latMin : 0,
                latMax : 0,
                lngMin : 0,
                lngMax : 0
            }

            /////////////////////////////////////
            //Nice control widgets from jQueryUI:
            /////////////////////////////////////

            //Popup dialog window for disclaimer
            $(function() {
                $( "#disclaimer" ).dialog({
                    autoOpen: false
                });

                $( "#disclaimer-opener" ).click(function() {
                    $( "#disclaimer" ).dialog( "open" );
                });
            });

            //Popup dialog window for license
            $(function() {
                $( "#license" ).dialog({
                    autoOpen: false,
                    width: 350
                });

                $( "#license-opener" ).click(function() {
                    $( "#license" ).dialog( "open" );
                });
            });

            //No data fetched dialog
            $(function() {
                $("#no-data").dialog({
                    height: 80,
                    autoOpen: false,
                    dialogClass: 'noTitleDialog',
                    open: function(event, ui){
                        setTimeout("$('#no-data').dialog('close')",3000);
                    }
                });
            });

            //Sliders
            $(function () {
                $(".slider").each(function () {
                    var begin = $(this).data("begin"),
                        end = $(this).data("end"),
                        step = $(this).data("step");

                    $(this).slider({
                        range: "true",
                        values: [begin, end],
                        min: begin,
                        max: end,
                        step: step,
                        slide: function (event, ui) {
                            //Update text box quantity when the slider changes
                            var sliderlow = ("#" + $(this).attr("id") + "_amount_low");
                            $(sliderlow).val(ui.values[0]);

                            var sliderhigh = ("#" + $(this).attr("id") + "_amount_high");
                            $(sliderhigh).val(ui.values[1]);
                        },
                        //When the slider changes, update the displayed spills
                        change: function(event, ui) {
                            if ($(this).attr("id") == "years") {
                                sqlParameters.yearMin = ui.values[0];
                                sqlParameters.yearMax = ui.values[1];
                            } else if ($(this).attr("id") == "volume") {
                                sqlParameters.volumeMin = ui.values[0];
                                sqlParameters.volumeMax = ui.values[1];
                            }
                            getSpills();
                        }
                    })

                    //Initialize the text box quantity
                    var sliderlow = ("#" + $(this).attr("id") + "_amount_low");
                    $(sliderlow).val($(this).slider("values", 0));

                    var sliderhigh = ("#" + $(this).attr("id") + "_amount_high");
                    $(sliderhigh).val($(this).slider("values", 1));
                })

                //When the text box is changed, update the slider
                $('.amount1').change(function () {
                    var value = this.value,
                    selector = $("#" + this.id.split('_')[0]);
                    selector.slider("values", 0, value);
                })
                $('.amount2').change(function () {
                    var value = this.value,
                    selector = $("#" + this.id.split('_')[0]);
                    selector.slider("values", 1, value);
                })
            });

            //Accordian divs
            $(function() {
                $( "#accordion" ).accordion({
                    collapsible: true,
                    autoHeight: false,
                    heightStyle: "content"
                });
            });

            //Get the Licensee list for the autocomplete widget
            var licenseeList = [];
            $.ajax({
                async: false,
                url : "getLicensees.php",
                dataType : "json",
                success: function(data){
                    licenseeList = data;
                },
                error: function (data)
                {
                    alert("Couldn't retrieve the licensee list. A page refresh will usually fix this.");
                }
            });

            //Auto Complete Licensee Selector
            $(function() {
                var cache = [];
                $( "#licensee-selector" ).autocomplete({
                    minLength: 2,
                    source: licenseeList,
                    select: function( event, ui ) {
                        sqlParameters.currentLicensee = ui.item.value;
                        getSpills();
                    }
                });

                $( "#licensee-clear" ).click(function() {
                    $( "#licensee-selector" ).val("");
                    sqlParameters.currentLicensee = 'All';
                    getSpills();
                });

            });

            //Drop down menus
            $(function() {
                $( "#substance-menu, #source-menu" ).menu();
            });  

            //When the DOM is loaded, we want to configure stuff like the menus
            $( document ).ready(function() {
                makeMenus();

                //A hackish way to set the spill-info content max height based on window height
                document.getElementById("spill-info").style.maxHeight = $(window).height()*0.40 + "px";

            });

            //Build the menus after the window has loaded (This is called at the end of <body>)
            function makeMenus() {

                //Get the substances and sources for the filter menus
                var substanceList = [];
                $.ajax({
                    async: false,
                    url : "getSubstances.php",
                    dataType : "json",
                    success: function(data){
                        substanceList = data;
                        //replace the initial null element
                        substanceList[0] = "All";
                    },
                    error: function (data)
                    {
                        alert("Couldn't retrieve the substance list. A page refresh will usually fix this.");
                    }
                });

                //And the Sources too
                var sourceList = [];
                $.ajax({
                    async: false,
                    url : "getSources.php",
                    dataType : "json",
                    success: function(data){
                        sourceList = data;
                        //replace the initial null element
                        sourceList[0] = "All";
                    },
                    error: function (data)
                    {
                        alert("Couldn't retrieve the source list. A page refresh will usually fix this.");
                    }
                });

                //Build the lists using the database results
                //Function courtesy of http://stackoverflow.com/questions/11128700/create-a-ul-and-fill-it-based-on-a-passed-array
                function constructLI(domID, array) {

                    var fieldID = (domID.split("-"))[0]+"-selected";

                    for(var i = 0; i < array.length; i++) {
                        // Create the list item:
                        var member = document.createElement('li');

                        // Set its contents:
                        var linkText = document.createTextNode(array[i]);
                        var link = document.createElement('a');
                        link.appendChild(linkText);
                        link.href= "#";
                        link.title= linkText;

                        //Make the onclick aspect of them menu work
                        link.onclick = function() { setText( fieldID, this.firstChild.nodeValue ) };

                        member.appendChild(link);

                        // Add it to the list:
                        document.getElementById(domID).appendChild(member);
                    }
                }
                constructLI("substance-links", substanceList);
                constructLI("source-links", sourceList);
            }

            //Set the drop down menu to reflect the new filter value and update the displayed results
            function setText(domID, text) {
                document.getElementById(domID).innerHTML = text;
                if (domID == "substance-selected") {
                    sqlParameters.currentSubstance = text;
                } else if (domID == "source-selected") {
                    sqlParameters.currentSource = text;
                }
                getSpills();
            };

            //////////////////////////////
            //Start the Google Maps stuff
            //////////////////////////////

            var map;
            var markers = [];
            var selectedMarker = new google.maps.Marker({
                                position: null,
                                icon: 'spotlight-poi.png',
                                map: map,
                                spillID: null
                        });
            var spillLocations;

            //Initialize when the map is done
            google.maps.event.addDomListener(window, 'load', initialize);

            function initialize() {         clearStyle: true;
                var middleEarth = new google.maps.LatLng(54.5, -115.0);
                var mapOptions = {
                    zoom: 6,
                    center: middleEarth,
                    mapTypeId: google.maps.MapTypeId.ROADMAP
                };

                map = new google.maps.Map(document.getElementById('map-canvas'), mapOptions);       

                makeGetSpillsEvent();
            }

            function makeGetSpillsEvent(){
                google.maps.event.addListener(map, 'idle', function() { getSpills();} );
            }

            function getSpills() {
                var mapCorners = map.getBounds();
                var ne = mapCorners.getNorthEast(); // LatLng of the north-east corner
                var sw = mapCorners.getSouthWest(); // LatLng of the south-west corder

                sqlParameters.latMin = sw.lat();
                sqlParameters.latMax = ne.lat();
                sqlParameters.lngMin = sw.lng();
                sqlParameters.lngMax = ne.lng();

                var newSpillLocations;  

                //Get the spill location data
                $.ajax({
                    url : "getSpillLocations.php",
                    type: "POST",
                    data : sqlParameters,
                    dataType : "json",
                    success: function(data){
                        SpillLocations = data;
                        plotSpills(SpillLocations);
                    },
                    error: function (data)
                    {
                        $( "#no-data" ).dialog( "open" );
                    }
                });
            }

            function plotSpills(spillLocations){
                map.clearMarkers(markers);
                markers = [];
                markers.push(selectedMarker);
                //Stick those markers into the map canvas
                for (var i = 0; i < spillLocations.length; i++) {
                    //Dont duplicate the selected marker.
                    if (selectedMarker.spillID !==  spillLocations[i].IncidentNumber) {
                        var marker = new google.maps.Marker({
                            position: new google.maps.LatLng(spillLocations[i].Latitude, spillLocations[i].Longitude),
                            icon: 'spotlight-poi.png',
                            map: map,
                            spillID: spillLocations[i].IncidentNumber
                        });
                    }

                    makeLoadSpillInfoEvent(marker);

                    markers.push(marker);
                }
            }

            //The info window function from http://jsfiddle.net/yV6xv/161/
            function makeLoadSpillInfoEvent(marker) {
                google.maps.event.addListener(marker, 'click', function() {
                    //Set the old marker back to red
                    selectedMarker.setIcon('spotlight-poi.png');
                    //Set the new marker to orange
                    selectedMarker = marker;
                    selectedMarker.setIcon('spotlight-poi-orange.png');
                    loadSpillInfo(marker.spillID);
                });
            }

            //A function that fetches the specific spill info and loads it into the spill-info div
            function loadSpillInfo(spillID) {

                var spillInfo = {}

                $.ajax({
                    async: false,
                    url : "getSpillInfo.php",
                    type: "POST",
                    data: {'incidentnumber':spillID},
                    dataType : "json",
                    success: function(data){
                        spillInfo = data;
                    },
                    error: function (data)
                    {
                        $( "#no-data" ).dialog( "open" );
                    }
                });

                //Clear existing content
                document.getElementById("spill-info").innerHTML = "";
                var table = document.createElement('table');

                //Populated the new table element
                for (var key in spillInfo) {
                    if (spillInfo.hasOwnProperty(key)) {
                        var row = document.createElement('tr');
                        row.style.backgroundColor = "#ffebb8";
                        var cell1 = row.insertCell(0);
                        cell1.innerHTML = '<strong>'+key+'</strong>';
                        var cell2 = row.insertCell(1);
                        cell2.innerHTML = spillInfo[key];
                        table.appendChild(row);
                    }
                }

                //Put the table into the div and open the spill info accordion section
                document.getElementById("spill-info").appendChild(table);
                $('#accordion').accordion("option", "active", 1);
            }

            //A customized clearOverlays function to remove the defunct markers but keep the selected one.
            google.maps.Map.prototype.clearMarkers = function() {
                for (var i = 0; i < markers.length; i++ ) {
                    //Dont kill the selected marker, we want it to persist
                    if (!(markers[i] === selectedMarker)) {
                        markers[i].setMap(null);
                    }
                }
            }
        </script>
    </head>
    <body>
        <div id="map-canvas" style="width:100%;height:100%;"></div>
        <div id="info-panel" style="text-align:left;">
            <div class="text-block">
                <h3>Alberta Oil and Gas Incidents 1975 - 2013</h3>
                This is a map that interactively graphs all of the Oil and Gas related spills in alberta between the years 1975 and 2013. It is based on the data acquired by <a href="http://globalnews.ca/news/622513/open-data-alberta-oil-spills-1975-2013/" target="blank">Global News</a> from the <a href="http://en.wikipedia.org/wiki/Energy_Resources_Conservation_Board" target="blank">ERCB</a> (now the <a href="http://www.aer.ca/" target="blank">AER</a>).
                </br>
                </br>
                For optimal loading speeds and a clean map, it caps the number of incidents displayed to the 100 biggest spills (by volume in m<sup>3</sup>) in the current map area. Try zooming in to see more spills, or play with the provided filters to see more incidents.
                </br>
                <p>
                    Learn more about this project at:
                    <a href="http://everettsprojects.com/2014/06/07/mapping-oil-and-gas-incidents-in-alberta-with-google-maps-jquery-and-php/" target="blank">everettsprojects.com</a>
                </p>
            </div>
            <div id="accordion">
                <h3>Filter the Results</h3>
                <div id="filter-pane">
                    <p>
                        <label for="amount">Years:</label>
                        <span style="float:right;">
                            <input type="text" class="amount1" id="years_amount_low"  size="4">
                            <span class="orange-text"> - </span>
                            <input type="text" class="amount2" id="years_amount_high" size="4">
                        </span>
                    </p>

                    <div class="slider" id="years" data-begin="1975" data-end="2013" data-step="1"> </div>

                    <p>
                        <label for="amount">Volume:</label>
                        <span style="float:right;">
                            <input type="text" class="amount1" id="volume_amount_low" size="9">
                            <span class="orange-text"> - </span>
                            <input type="text" class="amount2" id="volume_amount_high" size="9">
                            <span class="orange-text"> m<sup>3</sup></span>
                        </span>
                    </p>

                    <div class="slider" id="volume" data-begin="0" data-end="37000000" data-step="1000"> </div>
                    <br>
                    <p>
                        <div class="ui-widget">
                            <label for="licensee-selector">Company: </label>
                            <input id="licensee-selector" size="29" class="orange-text">  <span style="float:right;">[<a href=# id="licensee-clear">X</a>]</span>
                            <br>
                        </div>
                    </p>

                    <p>
                        <ul id="substance-menu">
                            <li><a href="#">Substance: <span id="substance-selected" class="orange-text">All</span></a>
                                <ul id="substance-links">

                                </ul>
                            </li>
                        </ul>
                    </p>
                    <p>
                        <ul id="source-menu">
                            <li><a href="#">Source: <span id="source-selected" class="orange-text">All</span></a>
                                <ul id="source-links">

                                </ul>
                            </li>
                        </ul>
                    </p>
                </div>
                <h3>Incident Details</h3>
                <div id="spill-info">
                    This is where the data for a selected spill will be displayed. Click one to check it out!
                </div>
            </div>
            <div class="text-block">
                <p>
                    <a href="#" id="disclaimer-opener">Disclaimer</a> -
                    <a href="#" id="license-opener">Copyright (c) 2014 Everett Robinson</a>
                </p>
            </div>
        </div>
        <div id="disclaimer" title="Disclaimer:" style="font-size:75%;">
            <p>
                I do not under any circumstances guarantee the accuracy or truthfulness of the provided information. Furthermore, this project should not be taken as representative of the former ERCB, AER, or any other applicable parties.
                <br>
                <br>
                Due to the use of the Alberta Township System, many locations are approximations only. In general, points can be considered accurate to 200 metres.
                <br>
                <br>
                Any spills originating from trans-provincial or trans-national pipelines are not included, since they do not fall under the jursdiction of the AER. Furthermore, many spills under 2 m<sup>3</sup> that did not originate from a pipeline may be absent, as they are not required to be reported.
            </p>
        </div>
        <div id="license" title="MIT License:" style="font-size:75%;">
            <p>
                Copyright (c) 2014 Everett Robinson
            </p>
            <p>
This content is released under the MIT License.
<br><br>
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
<br><br>
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
<br><br>
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.

            </p>
        </div>
        <div id="no-data" class="noTitleDialog" style="font-size:75%;">
            <p>
                Oops, the spill locations or data couldn't be loaded right now.
            </p>
        </div>
    </body>
</html>

So that’s a bit of a long file, but I’ve tried to describe each function’s purpose, and have laid out the JavaScript as best as possible to provide a rational flow. Overall, the JavaScript is broken into 4 parts:

  1. The JQuery UI widgets that implement the filters and update the map points when changed
  2. The google maps code that displays the map and update the points when the view-port is moved
  3. The JQuery/AJAX code that fetches the map points using the view-port and filter values
  4. The JQuery/AJAX code that gets all of the info for a spill if it is selected

There is then the HTML necessary for rendering the webpage, which relies on the following CSS file (default.css):

html, body {
  background-color:#b0c4de;
  height: 100%;
  margin: 0;
  padding: 0;
  font-size: 100%;
}

#map-canvas, #map_canvas {
  height: 100%;
}

@media print {
  html, body {
    height: auto;
  }

  #map-canvas, #map_canvas {
    height: 650px;
  }
}

#info-panel {
  width: 25%;
  max-height: 96%;
  position: absolute;
  font-size: 75%;
  top: 10px;
  left: 90px;
  background-color: #fff;
  padding: 2px;
  border: 1px solid #999;
  background: rgba(255, 255, 255, 1);
  -webkit-border-radius: 5px;
  -moz-border-radius: 5px;
  -ms-border-radius: 5px;
  -o-border-radius: 5px;
  border-radius: 5px;
  border: outset 1px #a1b5cf;
}

.text-block {
  margin: 10px;
  border-width: 2px;
  text-align: center;
}

#accordion {
  margin: 10px;
  border-width: 2px;
  overflow: auto;
}

#filter-pane {
  overflow: auto;
  font-size: smaller;
}

.amount1, .amount2 {
  border: 0;
  color: #f6931f;
  font-weight: bold;
  text-align: center;
}

ul.ui-autocomplete {
  overflow: auto;
  width: 200px;
  max-height: 200px;
  font-size: 75%;

}

#substance-links {
  overflow: auto;
  width: 200px;
  max-height: 200px;
  z-index: 1;
}

#source-links {
  overflow: auto;
  width: 200px;
  max-height: 200px;
  z-index: 1;
}

.orange-text {
  color: #f6931f;
  font-weight:bold;
}

#spill-info {
  overflow: auto;
  font-size:smaller;
  max-height: 400px;
}

.noTitleDialog {
  text-align: center;
}

.noTitleDialog .ui-dialog-titlebar {
  display:none;
}

.ui-autocomplete-loading {
    background: white url('images/ui-anim_basic_16x16.gif') right center no-repeat;
}

And Finally, six PHP files necessary for interfacing our web page to the database:

<?php
require('config.inc.php');

//Get all of the POST data
$currentlicensee = $_POST['currentLicensee'];
$currentsubstance = $_POST['currentSubstance'];
$currentsource = $_POST['currentSource'];
$yearmin = $_POST['yearMin'];
$yearmax = $_POST['yearMax'];
$volumemin = $_POST['volumeMin'];
$volumemax = $_POST['volumeMax'];
$latmin = $_POST['latMin'];
$latmax = $_POST['latMax'];
$longmin = $_POST['lngMin'];
$longmax = $_POST['lngMax'];

// Fix the years to go from start of first year to end of the last.
$datemin = $yearmin."-01-01";
$datemax = $yearmax."-12-31";

//By using PDO and prepare, everything is automagically escaped
$db = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

//Start building the statement with the base of the query
$stmtString = "SELECT `IncidentNumber`, `Latitude`, `Longitude` FROM `Spills` WHERE (((`Longitude` BETWEEN :longMin AND :longMax) AND (`Latitude` BETWEEN :latMin AND :latMax) AND (`IncidentDate` BETWEEN :dateMin AND :dateMax) AND (`Volume Released` BETWEEN :volumeMin AND :volumeMax))";

//Add in the filters if they're set
if ($currentlicensee !== "All") {
    $stmtString .= " AND `LicenseeName` = :licensee";
}
if ($currentsubstance !== "All") {
    $stmtString .= " AND `Substance Released` = :substance";
}
if ($currentsource !== "All") {
    $stmtString .= " AND `Source` = :source";
}

//Finish the statement with the sorting and limit parts
$stmtString .= ") ORDER BY `Volume Released` DESC LIMIT 100";

//Bind all of the parameters
$stmt = $db->prepare($stmtString);
if (strpos($stmtString,':licensee') !== false) {
    $stmt->bindValue(':licensee', strval($currentlicensee), PDO::PARAM_STR);
}
if (strpos($stmtString,':source') !== false) {
    $stmt->bindValue(':source', strval($currentsource), PDO::PARAM_STR);
}
if (strpos($stmtString,':substance') !== false) {
    $stmt->bindValue(':substance', strval($currentsubstance), PDO::PARAM_STR);
}
$stmt->bindValue(':latMin', strval($latmin), PDO::PARAM_STR);
$stmt->bindValue(':latMax', strval($latmax), PDO::PARAM_STR);
$stmt->bindValue(':longMin', strval($longmin), PDO::PARAM_STR);
$stmt->bindValue(':longMax', strval($longmax), PDO::PARAM_STR);
$stmt->bindValue(':dateMin', strval($datemin), PDO::PARAM_STR);
$stmt->bindValue(':dateMax', strval($datemax), PDO::PARAM_STR);
$stmt->bindValue(':volumeMin', strval($volumemin), PDO::PARAM_STR);
$stmt->bindValue(':volumeMax', strval($volumemax), PDO::PARAM_STR);
$stmt->execute();

//Get the results of the query
$result;
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
//Spit out the results in json form
echo header('Content-type: application/json');
echo json_encode($result);
?>
<?php

$incidentNumber = $_POST['incidentnumber'];

require('config.inc.php');
$db = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
//By using PDO and prepare, everything is automagically escaped
$stmt = $db->prepare("SELECT * FROM `Spills` WHERE `IncidentNumber` = :incidentNumber");
$stmt->bindValue(':incidentNumber', strval($incidentNumber), PDO::PARAM_STR);
/*** execute the prepared statement ***/
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);

echo header('Content-type: application/json');
echo json_encode($result);

?>
<?php
require('config.inc.php');

$db = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
//By using PDO and prepare, everything is automagically escaped
$stmt = $db->prepare("SELECT `LicenseeName` FROM `Spills` GROUP BY `LicenseeName` ORDER BY `Spills`.`LicenseeName` ASC LIMIT 2000");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

echo header('Content-type: application/json');
echo json_encode($result);

?>
<?php
require('config.inc.php');

$db = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
//By using PDO and prepare, everything is automagically escaped
$stmt = $db->prepare("SELECT `Substance Released` FROM `Spills` GROUP BY `Substance Released` ORDER BY `Spills`.`Substance Released` ASC LIMIT 100");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_NUM);

echo header('Content-type: application/json');
echo json_encode($result);

?>
<?php
require('config.inc.php');

$db = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
//By using PDO and prepare, everything is automagically escaped, not that it's necessary here
$stmt = $db->prepare("SELECT `Source` FROM `Spills` GROUP BY `Source` ORDER BY `Spills`.`Source` ASC LIMIT 100");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_NUM);

echo header('Content-type: application/json');
echo json_encode($result);

?>
<?php
// These are the login credentials for your MySQL database,
// don't forget to set them.
$dbhost = change me;
$dbname = change me;
$dbuser = change me;
$dbpass = change me;
?>

The first file, getSpillLocations.php, does what it sounds like. It takes all of the filter parameters along with the map boundaries, and then returns a JSON encoded list of coordinates and spill ID numbers to be plotted. The SQL statement is built based on the parameters passed, and then fulfilled using PHP Data Objects (PDO). The second, getSpillInfo.php, takes a spill ID number, and uses it to return all of the data for that spill as JSON object.
The third, fourth, and fifth scripts are used to fetch lists from the database that are used to populate the menu widgets in the filter panel. They do not require any parameters to be passed, since they just return a list containing all of the existing values for each field. Finally, config.inc.php is simply a file containing the database access credentials, meant to be included in the five other scripts.

Considerations and Caveats:

The ERCB/AER uses the Alberta Township System (ATS) for reporting locations, which means the latitudes and longitudes in the database are converted values that represent the centre of the smallest unit in the ATS scheme; a Legal Sub-Division (LSD). Since a LSD is 400m along each side, it can be said that any plotted location is accurate to +/- 200m in each axis. This poses another problem however; certain legal subdivisions will have had multiple incidents on them in the 37 year period displayed. A great example is the region near Turner Valley:

spills-overlap-animation2

Overlapping spill incidents near Turner Valley need to be differentiated using the provided filters.

Here, several of the spills in this area were isolated using the provided filters. Unfortunately, there is not currently a mechanism to indicate that overlapping markers exist. The user will either need to have keen eyes to spot the signs, like a slight red border around the yellow selected marker, or play around with the filters to confirm any suspicions.

Another consideration, which is not one I have control over is that any spills originating from trans-provincial or trans-national pipelines are not included, since they do not fall under the jurisdiction of the AER. Furthermore, many spills under 2 m3 that did not originate from a pipeline may be absent, as they are not required to be reported.

A final issue is that there are in fact 3 members of the database that do not possess a valid latitude or longitude; these fields are 0. The incident numbers are 19940377, 19850326, and 19871009 for reference. This means they are actually plotted in the south Atlantic, off the coast of Africa:

These incidents are plotted in the wrong location at Latitude: 0, Longitude: 0

These incidents are plotted in the wrong location at Latitude: 0, Longitude: 0

Now, I could theoretically go in and correct the values for these three points by converting the ATS coordinates manually, but I decided not to since they illustrate an important point: None of the data in the database is vetted by me. I can not assure that any other data point is valid, though none of the rest are so obviously incorrect.

So with those concerns out of the way; have fun exploring the often hushed side of the oil industry in Alberta.

Posted by Everett in Environmental Science, Programming, Web Applications, 1 comment
Experimenting with EL wire: A Stickfigure Costume

Experimenting with EL wire: A Stickfigure Costume

IMG_2396

Like most people on the internet, I saw this video leading up to this year’s Halloween. Unlike most people, I thought “I need to make one of those, but adult male sized!” A quick bit of research led me to realize that normal LED light strips as used in the video are a little impractical for a suit my size. They cost more than 4x as much per unit length compared to High brightness EL Wire and they appear to consume far more power as well. Not wanting to carry piles of spare batteries around when I go out for Halloween, I decided to use EL wire and settle for the few tradeoffs it has. The most major tradeoff is that EL wire doesn’t hold up as well to repeated flex in the joints or tight bends. I may regret the choice to use EL wire if it fails on the dance floor, but for now it seems like the smarter choice.

. . . . .

The Parts:

IMG_2369

. . . . .

The Basic Design:

SuitLayout

The suit consists of five separate strands of EL wire of varying lengths all connected to the inverter and power source. The hood of the suit is held into a circular shape using an aluminium ring that was salvaged from an old tomato cage. The inverter and battery pack is crammed into a small reusable container that has been cut up and modified to house the components.

ElectronicsBox

. . . . .

Preparing the Electronics:

The strands of EL wire that I purchased both come with jumpers preinstalled, but since I needed five separate strands it was necessary to cut off the extra strands from the ends and manually solder new connections to them. There were three of these new strands to be soldered using the male in-line connectors. A few images of my soldering efforts are included, but the adafruit guide to soldering EL wire is far superior to anything I could reproduce here.

IMG_2387 IMG_2390

After cutting the EL wire to size and soldering on the jumpers it was time to prepare some of the other wiring essentials, like a Y shaped extension cable for the arms. This was done by simply cutting the female end of the in-line extension cord off, and then resoldering it with a the second female connector attached. The junction was then sealed up tight with a little heat shrink tubing that I slid on before I soldered everything.

IMG_2391

And finally, as far as the electronics are concerned, all that needs to be done is assemble the power supply and inverter box. The box, contrary to my measurements, unfortunately didn’t quite fit the battery pack and inverter. To fix this I decided to modify the box with a little lighter and X-acto knife surgery.  After that, it was necessary to drill a hole for the toggle switch I planned to install. Technically I drilled the hole in the wrong place originally, and had to drill a second one. It’s not a mistake, just ventilation!

IMG_2372 IMG_2373

After getting the box ready to house the electronics, it was time to get soldering again. The connections are all really simple, I just soldered the two black leads together and then protected the joint with some more heat shrink tubing. After that the red leads were connected to the two terminals of the toggle switch I harvested from something too long ago to remember what it was. Once again, the connections were wrapped up tight with heat shrink tubing and then for good measure I placed a small bead of hot glue between them.

IMG_2382

With the soldering done, I just had to fit it all into my box. This was really just as simple as screwing the nut onto the threaded bit of the toggle switch after sticking it through the hole that I had drilled, then squeezing the battery pack and inverter into there. Everything fits so snug that I didn’t need to do any fastening of the components to the inside of the box. Lucky me.

IMG_2375 IMG_2383

. . . . .

Attaching it to the Clothes:

Okay so the electronics are done, and everything glows nicely. Now we jut need to sew it to the clothing. But before that, I decided I wanted a nice round stick person head. To achieve this I went and pulled a nice firm aluminium  ring from an old tomato cage then stuck it through the drawstring part of the hood.

IMG_2384 IMG_2385

To sew the EL wire on I used transparent thread, but that’s really just a fancy marketing gimmick used to sell 6 lb fishing line to the sewing demographic. I used a very simple rib stitch that wrapped around the EL wire then through the fabric over and over again. After this was done I decided to ensure the EL wire stayed put by further tacking it on using a few blobs of strategically placed hot glue. It’s okay, no one will notice in the dark.

I then started to sew on one of the arms, but due to my generally incompetent sewing skills, it failed to hold the EL wire in place after repeated movement. A little fed up, I resorted to the hot glue gun again. By putting little beads in places where the EL wire wasn’t moving too much and leaving high movement areas like the joints free I managed to get a solution that holds the EL wire onto the clothing well while still maintaining a good degree of freedom of movement. I repeated this process for the other arm and both legs, with the added benefit that it is much faster than sewing.

IMG_2394

All that remained to be done at this point is to hook up the EL wire to the power box. This is where the 1 to 4 splitter comes in handy to connect to the torso/head, two legs, and the Y extension to the arms. All of this wiring is hidden inside the hoodie and threaded through a hole on the inside of the front pouch pocket where I’ve hidden the power box.  That’s really all there is to it, and the costume is ready to go for Halloween.

IMG_2395

Posted by Everett in Electronics, 0 comments
An Interactive Route Map for my Travel Blog

An Interactive Route Map for my Travel Blog

travelMap

See the finished project at: http://everett.x10.mx/maps/

For the last three months I have been on a backpacking trip, which is part of the reason why this blog has been so neglected for the past few months. A travel blog hosted at meandmypack.wordpress.com had taken precedence, and I habitually kept that one updated throughout my trip. With all of that over though, I’ve had to find things to do in my time to keep my self from becoming bored and lethargic with life back home in Canada. One such activity is the tying up of loose ends as far as documentation of my trip is concerned, and from early on I had it in my mind to make a nice map of all the places I went. Over time this idea evolved into a whole project in it’s own right, using Google maps and becoming more interactive and feature rich every time my mind drifted back to the idea of it. I couldn’t really spare the time to design it while in Europe, and that probably would have been a waste of the limited time I had there any way. So I stored the idea away and made a promise to my self to figure it out back home. Now some two weeks later, I’ve pulled it off.

To start, I began with the Google Maps API v3 Simple Polylines example code and then added in this code for adding in markers to the map. The poly line consists of a large number of latitude and longitude coordinates that I fetched from Google maps using the LatLng Marker plugin available through Google Maps Labs. With a stubbornness that could be mistaken for OCD, I made sure the PolyLine at least vaguely resembled my true route between major destinations by routing them through all of the intermediate stations that the train called at along the way. This was accomplished with the travel report from my Eurail Pass ( I knew I diligently filled it out for a reason), and the Eurail timetables. With these two tools, I could easily go back and find the true route of most of the train travel I did during my trip. Elsewhere when I didn’t travel by train I figured the route out through some combination of memory and Google. The markers for all of the main cities and attractions that I visited were simply made by selectively harvesting those coordinates from the Polyline list and then adding them to their own modified list with extra fields for the associated tag on my blog, and the blurb for the popup info box. I modified the marker code to put a link to the associated content on meandmypack.wordpress.com inside that popup box.  Finally I felt that it would be nice to calculate the distance travelled from the Polyline, which I did with the help of this code.

With all of the main features of the map coded, and a few hours spent finding the geospatial coordinates of my route, I had a decent looking finished product. I spent just a little more time on the layout and design of the page so that some information about the map was presented in a permanent box in the top left hand corner. I’ve also decided to post the final draft of the code below for easy viewing by all interested parties:

index.html


<!DOCTYPE html>
<html>
  <head>
    <meta name="viewport" content="initial-scale=1.0, user-scalable=no">
    <meta charset="utf-8">
    <title>Me and My Pack Interactive Route Map</title>
    <link href="/maps/default.css" rel="stylesheet">
    <script type="text/javascript"
      src="https://maps.googleapis.com/maps/api/js?key=AIzaSyCIxpXOSPJWNG7TnhMYq-Q2hPcM7zEQs8g&sensor=false">
    </script>
    <script>
	//Standard Google Maps API code with project specific values
	function initialize() {
	  var middleEarth = new google.maps.LatLng(52.01254, 8.2133);
	  var mapOptions = {
	    zoom: 5,
	    center: middleEarth,
	    mapTypeId: google.maps.MapTypeId.ROADMAP
	  };
	
	  var map = new google.maps.Map(document.getElementById('map-canvas'), mapOptions);
	
	  //The Polyline coordinates. Lots and Lots of them.
	  var routeCoordinates = [
	      new google.maps.LatLng(51.51120, -0.11978), 	//London
	      new google.maps.LatLng(53.95795, -1.0934),
	      new google.maps.LatLng(54.9681, -1.6173),
	      new google.maps.LatLng(55.7743, -2.0110),
	      new google.maps.LatLng(55.95324, -3.18827), 	//Edinburgh
	      new google.maps.LatLng(55.982, -3.616),
	      new google.maps.LatLng(56.077, -3.923),
	      new google.maps.LatLng(56.1387, -3.9179), 	//Wallace monument
	      new google.maps.LatLng(56.17843, -4.3821), 	//Aberfoyle
	      new google.maps.LatLng(56.23381, -4.4290),	//loch katrine
	      new google.maps.LatLng(56.2440, -4.2158),
	      new google.maps.LatLng(56.18932, -4.0510),	//doune
	      new google.maps.LatLng(56.077, -3.923),
	      new google.maps.LatLng(55.982, -3.616),
	      new google.maps.LatLng(55.95324, -3.18827), 	//Edinburgh
	      new google.maps.LatLng(55.85931, -4.25836),
	      new google.maps.LatLng(54.9617, -5.0142),
	      new google.maps.LatLng(55.0317, -5.1047),
	      new google.maps.LatLng(55.0271, -5.3356),
	      new google.maps.LatLng(54.7595, -5.6473),
	      new google.maps.LatLng(54.5971, -5.930),		//Belfast
	      new google.maps.LatLng(54.852, -5.811),
	      new google.maps.LatLng(54.982, -5.996),
	      new google.maps.LatLng(55.058, -6.062),
	      new google.maps.LatLng(55.200, -6.239),
	      new google.maps.LatLng(55.24881, -6.48898),	//Giants causeway
	      new google.maps.LatLng(54.745, -6.23),
	      new google.maps.LatLng(54.5971, -5.930),		//Belfast
	      new google.maps.LatLng(54.0011, -6.4129),
	      new google.maps.LatLng(53.34980, -6.26028),	//Dublin
	      new google.maps.LatLng(53.435, -7.941),
	      new google.maps.LatLng(53.27055, -9.0566),	//Galway
	      new google.maps.LatLng(53.271, -8.918),
	      new google.maps.LatLng(53.207, -8.868),
	      new google.maps.LatLng(53.139, -8.931),
	      new google.maps.LatLng(53.114, -9.148),
	      new google.maps.LatLng(53.016, -9.375),
	      new google.maps.LatLng(52.97184, -9.42649),	//Cliffs of Moher
	      new google.maps.LatLng(53.016, -9.375),
	      new google.maps.LatLng(53.114, -9.148),
	      new google.maps.LatLng(53.139, -8.931),
	      new google.maps.LatLng(53.207, -8.868),
	      new google.maps.LatLng(53.271, -8.918),
	      new google.maps.LatLng(53.27055, -9.0566),	//Galway
	      new google.maps.LatLng(53.435, -7.941),
	      new google.maps.LatLng(53.34980, -6.26028),	//Dublin
	      new google.maps.LatLng(53.3076, -4.6310),
	      new google.maps.LatLng(53.204, -4.141),
	      new google.maps.LatLng(53.287, -3.716),	
	      new google.maps.LatLng(53.1968, -2.8798),
	      new google.maps.LatLng(51.5901, -2.9984),
	      new google.maps.LatLng(51.572, -2.649),
	      new google.maps.LatLng(51.44877, -2.5800),
	      new google.maps.LatLng(51.37737, -2.35709),    	//Bath
	      new google.maps.LatLng(51.0705, -1.8066),     	//Salisbury
	      new google.maps.LatLng(51.17885, -1.82618),    	//Stonehenge
	      new google.maps.LatLng(51.0705, -1.8066),     	//Salisbury
	      new google.maps.LatLng(51.53216, -0.12680),  	//London
	      new google.maps.LatLng(51.1086, 1.2870),
	      new google.maps.LatLng(50.9143, 1.805),
	      new google.maps.LatLng(50.62706, 3.0853),
	      new google.maps.LatLng(50.8354, 4.3355),       	//Brussels
	      new google.maps.LatLng(51.2094, 3.2246),	    	//Bruges
	      new google.maps.LatLng(50.8453, 4.3567),       	//Brussels
	      new google.maps.LatLng(51.2191, 4.421),		//Antwerp
	      new google.maps.LatLng(51.809, 4.658),
	      new google.maps.LatLng(52.0598, 4.3099),		//Den Haag
	      new google.maps.LatLng(52.3879, 4.6386),		//Haarlem
	      new google.maps.LatLng(52.3786, 4.9004),		//Amsterdam
	      new google.maps.LatLng(52.3144, 5.113),
	      new google.maps.LatLng(52.549, 5.639),
	      new google.maps.LatLng(52.514, 6.079),		//Zwolle
	      new google.maps.LatLng(53.2173, 6.564),		//Groningen
	      new google.maps.LatLng(53.2316, 7.4657),
	      new google.maps.LatLng(53.0827, 8.815),
	      new google.maps.LatLng(53.5544, 10.005),		//Hamburg
	      new google.maps.LatLng(53.8679, 10.6700),
	      new google.maps.LatLng(54.502, 11.228),
	      new google.maps.LatLng(54.652, 11.36),
	      new google.maps.LatLng(54.7671, 11.8772),
	      new google.maps.LatLng(55.6388, 12.0887),
	      new google.maps.LatLng(55.6730, 12.564),		//Copenhagen
	      new google.maps.LatLng(55.9155, 12.5007),
	      new google.maps.LatLng(55.9641, 12.5333),		//Humlebaek
	      new google.maps.LatLng(55.9155, 12.5007),
	      new google.maps.LatLng(55.6730, 12.564),		//Copenhagen
	      new google.maps.LatLng(55.6314, 12.6768),
	      new google.maps.LatLng(55.5655, 12.8917),
	      new google.maps.LatLng(55.7048, 13.1871),
	      new google.maps.LatLng(56.0443, 12.6954),
	      new google.maps.LatLng(56.5018, 12.9995),
	      new google.maps.LatLng(56.6692, 12.8658),
	      new google.maps.LatLng(57.7104, 11.9819),		//Gothenburg
	      new google.maps.LatLng(58.2876, 12.2990),
	      new google.maps.LatLng(58.9134, 11.9315),
	      new google.maps.LatLng(58.9659, 11.552),
	      new google.maps.LatLng(59.1206, 11.3859),
	      new google.maps.LatLng(59.2857, 11.1183),
	      new google.maps.LatLng(59.4319, 10.6565),
	      new google.maps.LatLng(59.7195, 10.8347),
	      new google.maps.LatLng(59.9095, 10.7598),		//Oslo
	      new google.maps.LatLng(59.913, 10.626),
	      new google.maps.LatLng(59.7407, 10.2042),
	      new google.maps.LatLng(59.7616, 9.919),
	      new google.maps.LatLng(60.052, 10.050),
	      new google.maps.LatLng(60.1688, 10.2490),
	      new google.maps.LatLng(60.4321, 9.4734),
	      new google.maps.LatLng(60.6991, 8.9698),
	      new google.maps.LatLng(60.6261, 8.5623),
	      new google.maps.LatLng(60.5356, 8.2068),
	      new google.maps.LatLng(60.4989, 8.0399),
	      new google.maps.LatLng(60.5607, 7.5869),
	      new google.maps.LatLng(60.6019, 7.5042),
	      new google.maps.LatLng(60.7352, 7.1229),
	      new google.maps.LatLng(60.6293, 6.4098),
	      new google.maps.LatLng(60.5869, 5.8148),
	      new google.maps.LatLng(60.455, 5.736),
	      new google.maps.LatLng(60.3894, 5.3354),		//Bergen
	      new google.maps.LatLng(60.455, 5.736),
	      new google.maps.LatLng(60.5869, 5.8148),
	      new google.maps.LatLng(60.6293, 6.4098),
	      new google.maps.LatLng(60.7352, 7.1229),
	      new google.maps.LatLng(60.6019, 7.5042),
	      new google.maps.LatLng(60.6019, 7.5042),
	      new google.maps.LatLng(60.5607, 7.5869),
	      new google.maps.LatLng(60.4989, 8.0399),
	      new google.maps.LatLng(60.5356, 8.2068),
	      new google.maps.LatLng(60.6261, 8.5623),
	      new google.maps.LatLng(60.6991, 8.9698),
	      new google.maps.LatLng(60.4321, 9.4734),
	      new google.maps.LatLng(60.1688, 10.2490),
	      new google.maps.LatLng(60.052, 10.050),
	      new google.maps.LatLng(59.7616, 9.919),
	      new google.maps.LatLng(59.7407, 10.2042),
 	      new google.maps.LatLng(59.913, 10.626),
	      new google.maps.LatLng(59.9095, 10.7598),		//Oslo
	      new google.maps.LatLng(60.189, 12.005),
	      new google.maps.LatLng(59.6533, 12.5912),
	      new google.maps.LatLng(59.3776, 13.4994),
	      new google.maps.LatLng(59.4182, 13.6920),
	      new google.maps.LatLng(59.2292, 14.4394),
	      new google.maps.LatLng(59.0668, 15.1098),
	      new google.maps.LatLng(58.9964, 16.2101),
	      new google.maps.LatLng(59.1790, 17.6459),
	      new google.maps.LatLng(59.3311, 18.0551),		//Stockholm
	      new google.maps.LatLng(59.3363, 18.2067),
	      new google.maps.LatLng(59.3794, 18.2948),
	      new google.maps.LatLng(59.3594, 18.4460),
	      new google.maps.LatLng(59.3970, 18.4426),
	      new google.maps.LatLng(59.4377, 18.3880),
	      new google.maps.LatLng(59.4482, 18.4287),
	      new google.maps.LatLng(59.4769, 18.4407),
	      new google.maps.LatLng(59.5045, 18.479),
	      new google.maps.LatLng(59.5757, 18.680),
	      new google.maps.LatLng(59.7195, 19.115),
	      new google.maps.LatLng(59.759, 19.319),
	      new google.maps.LatLng(60.068, 19.925),
	      new google.maps.LatLng(60.09231, 19.9279),
	      new google.maps.LatLng(60.068, 19.925),
	      new google.maps.LatLng(60.0130, 19.8542),
	      new google.maps.LatLng(59.807, 19.878),
	      new google.maps.LatLng(59.353, 22.72),
	      new google.maps.LatLng(60.146, 25.001),
	      new google.maps.LatLng(60.16780, 24.9528),	//Helsinki
	      new google.maps.LatLng(52.51630, 13.37769),	//Berlin
	      new google.maps.LatLng(51.0398, 13.7324),
	      new google.maps.LatLng(50.901, 14.221),
	      new google.maps.LatLng(50.7726, 14.2008),
	      new google.maps.LatLng(50.6595, 14.0448),
	      new google.maps.LatLng(50.5093, 14.0601),
	      new google.maps.LatLng(50.0826, 14.4353),		//Prague
	      new google.maps.LatLng(50.0309, 15.7563),
	      new google.maps.LatLng(49.8967, 16.4462),
	      new google.maps.LatLng(49.1898, 16.6130),
	      new google.maps.LatLng(48.7545, 16.8954),
	      new google.maps.LatLng(48.17483, 16.33662),	//Vienna
	      new google.maps.LatLng(48.2082, 15.6257),
	      new google.maps.LatLng(48.2896, 14.2928),
	      new google.maps.LatLng(47.8129, 13.0470),
	      new google.maps.LatLng(48.1405, 11.5569),		//Munich
	      new google.maps.LatLng(47.9854, 10.1867),
	      new google.maps.LatLng(47.54470, 9.6803),
	      new google.maps.LatLng(47.5509, 9.7194),
	      new google.maps.LatLng(47.5155, 9.7557),
	      new google.maps.LatLng(47.5035, 9.7419),
	      new google.maps.LatLng(47.4234, 9.3690),
	      new google.maps.LatLng(47.5002, 8.7228),
	      new google.maps.LatLng(47.3784, 8.5382),		//Zurich
	      new google.maps.LatLng(47.2958, 8.5636),
	      new google.maps.LatLng(47.1736, 8.5156),
	      new google.maps.LatLng(47.1801, 8.4634),
	      new google.maps.LatLng(47.0503, 8.3093),
	      new google.maps.LatLng(46.762, 8.139),
	      new google.maps.LatLng(46.7264, 8.1843),
	      new google.maps.LatLng(46.7548, 8.0368),
	      new google.maps.LatLng(46.6913, 7.8701),		//Interlaken
	      new google.maps.LatLng(46.5989, 7.9081),
	      new google.maps.LatLng(46.5753, 7.9390),
	      new google.maps.LatLng(46.5844, 7.9601),
	      new google.maps.LatLng(46.5745, 7.9742),		//Eiger trail
	      new google.maps.LatLng(46.62418, 8.0337),
	      new google.maps.LatLng(46.6328, 7.9009),
	      new google.maps.LatLng(46.6913, 7.8701),		//Interlaken
	      new google.maps.LatLng(46.7547, 7.6290),
	      new google.maps.LatLng(46.9496, 7.4396),
	      new google.maps.LatLng(46.8028, 7.1511),
	      new google.maps.LatLng(46.5161, 6.6290),
	      new google.maps.LatLng(46.5178, 6.5081),
	      new google.maps.LatLng(46.3851, 6.2366),
	      new google.maps.LatLng(46.21013, 6.1422),		//Geneva
	      new google.maps.LatLng(45.9021, 6.1204),		//Annecy
	      new google.maps.LatLng(45.6878, 5.9084),
	      new google.maps.LatLng(45.802, 5.853),
	      new google.maps.LatLng(45.95342, 5.3423),
	      new google.maps.LatLng(45.7605, 4.8613),
	      new google.maps.LatLng(43.9412, 4.8049),
	      new google.maps.LatLng(43.6849, 4.6327),
	      new google.maps.LatLng(43.5801, 4.9996),
	      new google.maps.LatLng(43.4879, 5.2307),
	      new google.maps.LatLng(43.3042, 5.3838),		//Marseille
	      new google.maps.LatLng(43.4879, 5.2307),
	      new google.maps.LatLng(43.5801, 4.9996),
	      new google.maps.LatLng(43.6849, 4.6327),
	      new google.maps.LatLng(43.8329, 4.3658),
	      new google.maps.LatLng(43.6050, 3.8816),
	      new google.maps.LatLng(43.3370, 3.2190),
	      new google.maps.LatLng(43.1899, 3.0065),
	      new google.maps.LatLng(42.544, 2.848),
	      new google.maps.LatLng(42.2649, 2.9683),
	      new google.maps.LatLng(41.9784, 2.8171),
	      new google.maps.LatLng(41.7753, 2.7407),
	      new google.maps.LatLng(41.548, 2.227),
	      new google.maps.LatLng(41.3795, 2.1418),		//Barcelona
	      new google.maps.LatLng(41.548, 2.227),
	      new google.maps.LatLng(41.7753, 2.7407),
	      new google.maps.LatLng(41.9784, 2.8171),
	      new google.maps.LatLng(42.2649, 2.9683),
	      new google.maps.LatLng(42.544, 2.848),
	      new google.maps.LatLng(43.1899, 3.0065),
	      new google.maps.LatLng(43.2172, 2.3502),
	      new google.maps.LatLng(43.61116, 1.45425),
	      new google.maps.LatLng(43.7035, 1.8137),
	      new google.maps.LatLng(43.5995, 2.2302),		//Castres
	      new google.maps.LatLng(43.7035, 1.8137),
	      new google.maps.LatLng(43.61116, 1.45425),
	      new google.maps.LatLng(44.0139, 1.3405),
	      new google.maps.LatLng(44.2079, 0.6214),
	      new google.maps.LatLng(44.8258, -0.5553),		//Bordeaux
	      new google.maps.LatLng(44.6222, -1.002),
	      new google.maps.LatLng(44.6585, -1.1653),
	      new google.maps.LatLng(44.65592, -1.25991),	//Cap ferret
	      new google.maps.LatLng(44.6585, -1.1653),
	      new google.maps.LatLng(44.6222, -1.002),
	      new google.maps.LatLng(44.8258, -0.5553),		//Bordeaux
	      new google.maps.LatLng(44.9918, -0.440),
	      new google.maps.LatLng(45.7482, -0.6182),
	      new google.maps.LatLng(46.1528, -1.1431),
	      new google.maps.LatLng(46.409, -0.892),
	      new google.maps.LatLng(47.2182, -1.5363),
	      new google.maps.LatLng(48.1027, -1.6725),		//Rennes
	      new google.maps.LatLng(48.6357, -1.5112),		//Mont Saint Michel
	      new google.maps.LatLng(48.1027, -1.6725),
	      new google.maps.LatLng(47.99541, 0.1911),
	      new google.maps.LatLng(48.8778, 2.3605),		//Paris gare de lest
	      new google.maps.LatLng(49.2588, 4.0241),
	      new google.maps.LatLng(49.1096, 6.1771),
	      new google.maps.LatLng(49.5994, 6.1355),		//Luxembourg
	      new google.maps.LatLng(49.1096, 6.1771),
	      new google.maps.LatLng(48.5851, 7.7336),		//Strasbourg	
	      new google.maps.LatLng(48.47824, 7.9475),
	      new google.maps.LatLng(48.9936, 8.4013),
	      new google.maps.LatLng(48.7848, 9.1827),		//Stuttgart
	      new google.maps.LatLng(48.9936, 8.4013),
	      new google.maps.LatLng(50.0507, 8.5709),
	      new google.maps.LatLng(50.9433, 6.9587),		//Cologne
	      new google.maps.LatLng(51.2196, 6.7936),
	      new google.maps.LatLng(51.4291, 6.7765),
	      new google.maps.LatLng(51.53123, 7.1659),
	      new google.maps.LatLng(51.9564, 7.6352),
	      new google.maps.LatLng(52.2759, 7.4342),
	      new google.maps.LatLng(52.2092, 5.9692),
	      new google.maps.LatLng(52.1541, 5.3728),
	      new google.maps.LatLng(52.3786, 4.9004),		//Amsterdam
	      new google.maps.LatLng(52.3879, 4.6386),		//Haarlem
	      new google.maps.LatLng(52.0598, 4.3099),		//Den Haag
	      new google.maps.LatLng(51.809, 4.658),
	      new google.maps.LatLng(51.2191, 4.421),		//Antwerp
	      new google.maps.LatLng(50.8453, 4.3567),       	//Brussels
	      new google.maps.LatLng(50.8354, 4.3355),       	//Brussels
	      new google.maps.LatLng(50.62706, 3.0853),
	      new google.maps.LatLng(48.8822, 2.3563)		//Paris gare du nord 
	  ];
	  
	  var routePath = new google.maps.Polyline({
	    path: routeCoordinates,
	    strokeColor: '#FF0000',
	    strokeOpacity: 1.0,
	    strokeWeight: 2
	  });
	  
	  //Use the Polyline to calculate the distance travelled for later
	  document.getElementById("distanceTravelled").innerHTML = Math.round(routePath.inKm())+' km';
	  
	  //Add the Polyline to the map canvas
	  routePath.setMap(map);
	  
	  //variables and list for the marker's to link back to the travel blog
	  var tagURL = 'http://meandmypack.wordpress.com/tag/';
	  
	  var mainCities = [
	      [51.51120, -0.11978, 'london', 'London'],
	      [55.95324, -3.18827, 'edinburgh', 'Edinburgh'],
	      [56.23381, -4.4290, 'highlands', 'Scottish Highlands'],
	      [54.5971, -5.930, 'belfast', 'Belfast'],
	      [55.24881, -6.48898, 'giants-causeway', 'Giant\'s Causeway'],
	      [53.27055, -9.0566, 'galway', 'Galway'],
	      [52.97184, -9.42649, 'cliffs-of-moher', 'Cliffs of Moher'],
	      [53.34980, -6.26028, 'dublin', 'Dublin'],
	      [51.37737, -2.35709, 'bath', 'Bath'],
	      [51.0705, -1.8066, 'salisbury', 'Salisbury'],
	      [51.17885, -1.82618, 'stonehenge', 'Stonehenge'],
	      [51.2094, 3.2246, 'bruges', 'Bruges'],
	      [50.8354, 4.3355, 'brussels', 'Brussels'],
	      [52.3786, 4.9004, 'amsterdam', 'Amsterdam'],
	      [53.2173, 6.564, 'groningen', 'Groningen'],
	      [53.5544, 10.005, 'hamburg', 'Hamburg'],
	      [55.6730, 12.564, 'copenhagen', 'Copenhagen'],
	      [57.7104, 11.9819, 'gothenburg', 'Gothenburg'],
	      [59.9095, 10.7598, 'oslo', 'Oslo'],
	      [60.3894, 5.3354, 'bergen', 'Bergen'],
	      [59.3311, 18.0551, 'stockholm', 'Stockholm'],
	      [60.16780, 24.9528, 'helsinki', 'Helsinki'],
	      [52.51630, 13.37769, 'berlin', 'Berlin'],
	      [50.0826, 14.4353, 'prague', 'Prague'],
	      [48.17483, 16.33662, 'vienna', 'Vienna'],
	      [48.1405, 11.5569, 'munich', 'Munich'],
	      [47.3784, 8.5382, 'zurich', 'Zurich'],
	      [46.6913, 7.8701, 'interlaken', 'Interlaken'],
	      [46.5745, 7.9742, 'eiger-trail', 'The Eiger Trail'],
	      [45.9021, 6.1204, 'annecy', 'Annecy'],
	      [43.3042, 5.3838, 'marseille', 'Marseille'],
	      [41.3795, 2.1418, 'barcelona', 'Barcelona'],
	      [43.5995, 2.2302, 'castres', 'Castres'],
	      [44.8258, -0.5553, 'bordeaux', 'Bordeaux'],
	      [44.65592, -1.25991, 'cap-ferret', 'Arcachon and Cap Ferret'],
	      [48.1027, -1.6725, 'rennes', 'Rennes'],
	      [48.6357, -1.5112, 'mont-saint-michel', 'Mont Saint Michel'],
	      [49.5994, 6.1355, 'luxembourg', 'Luxembourg'],
	      [48.5851, 7.7336, 'strasbourg', 'Strasbourg'],
	      [48.7848, 9.1827, 'stuttgart', 'Stuttgart'],
	      [50.9433, 6.9587, 'cologne', 'Cologne'],
	      [52.3879, 4.6386, 'haarlem', 'Haarlem'],
	      [48.8822, 2.3563, 'paris', 'Paris']	      
	  ];
	  
	  var markers = [];
	  
	  //Stick those markers into the map canvas
	  for (var i = 0; i < mainCities.length; i++) {
	    var marker = new google.maps.Marker({
	      position: new google.maps.LatLng(mainCities[i][0], mainCities[i][1]),
	      map: map
	    });
	    var infowindow = new google.maps.InfoWindow({
	      content: '<a href="'+tagURL+mainCities[i][2]+'/" target="blank">'+mainCities[i][3]+'</a>'
	    });
	
	    makeInfoWindowEvent(map, infowindow, marker);
	    
	    markers.push(marker);
	  }
	}
	
	//The info window function from http://jsfiddle.net/yV6xv/161/
	function makeInfoWindowEvent(map, infowindow, marker) {
	  google.maps.event.addListener(marker, 'click', function() {
	    infowindow.open(map, marker);
	  });
	}
	
	//The polyline distance code from https://groups.google.com/forum/#!topic/google-maps-js-api-v3/Op87g7lBotc
	google.maps.LatLng.prototype.kmTo = function(a){ 
    	  var e = Math, ra = e.PI/180; 
    	  var b = this.lat() * ra, c = a.lat() * ra, d = b - c; 
    	  var g = this.lng() * ra - a.lng() * ra; 
    	  var f = 2 * e.asin(e.sqrt(e.pow(e.sin(d/2), 2) + e.cos(b) * e.cos(c) * e.pow(e.sin(g/2), 2))); 
    	  return f * 6378.137; 
  	}
  	
  	google.maps.Polyline.prototype.inKm = function(n){ 
    	  var a = this.getPath(n), len = a.getLength(), dist = 0; 
    	    for(var i=0; i<len-1; i++){ 
      	    dist += a.getAt(i).kmTo(a.getAt(i+1)); 
    	  } 
    	  return dist; 
  	}
  	
	
	google.maps.event.addDomListener(window, 'load', initialize);
	
    </script>
  </head>
  <body>
        <div id="map-canvas" style="float:left;width:100%;height:100%;"></div>
        <div id="info-panel" style="float:right;text-align:left;">
        <div style="margin:10px;border-width:2px;float:center;text-align:center;">
          <h3>Me and My Pack Interactive Route Map</h3>
          <b>Distance Travelled: </b>
          <div id="distanceTravelled"></div><br>
          <a href="http://meandmypack.wordpress.com" target="blank">meandmypack.wordpress.com</a><br>
          <a href="http://everettsprojects.com" target="blank">everettsprojects.com</a>
    	</div>
  </body>
</html>

default.css

html, body {
  background-color:#b0c4de;
  height: 100%;
  margin: 0;
  padding: 0;
}

#map-canvas, #map_canvas {
  height: 100%;
}

@media print {
  html, body {
    height: auto;
  }

  #map-canvas, #map_canvas {
    height: 650px;
  }
}

#info-panel {
  width: 25%;
  font-size: 12px;
  position: absolute;
  top: 10px;
  left: 90px;
  background-color: #fff;
  padding: 2px;
  border: 1px solid #999;
  background: rgba(255, 255, 255, 1);
  -webkit-border-radius: 5px;
  -moz-border-radius: 5px;
  -ms-border-radius: 5px;
  -o-border-radius: 5px;
  border-radius: 5px;
  border: outset 1px #a1b5cf;
}
Posted by Everett in Programming, Web Applications, 0 comments
Arduino: Super Graphing Data Logger

Arduino: Super Graphing Data Logger

The intensity of natural light in my basement.
Sections:

  1. Introduction
  2. The Results
  3. How to Make One For Yourself

Introduction

What is the Super Graphing Data Logger (SGDL)? It is an Arduino project that integrates data logging and the graphing of this data online using little more than an Arduino with the appropriate shields and sensors.   It differs from similar projects in that it doesn’t require a separate server or system to collect the data or to run script for the actual plot. Between the Arduino and the user’s browser, everything is taken care of.

If you just want to dive right in, the code is now posted on GitHub: https://github.com/evjrob/super-graphing-data-logger

Some time back I came across this neat javaScript based library for plotting and graphing called Highcharts JS. It didn’t take long for me to realize that charting with javaScript is very convenient for projects in which the server is limited in it’s capabilities, such as when using an Arduino with the Ethernet shield. Since the user’s browser does all the heavy lifting, the Arduino only needs to serve the files which is something it is perfectly capable of. This is especially true now that the Ethernet and SD libraries included in 1.0 support opening of multiple files simultaneously amongst other things. Thus the use of Highcharts allows us to create beautiful interactive charts based on data logged by the Arduino using nothing but the Arduino (and your browser, and a public javaScript CDN).

The Results

The best way to appreciate the final product is to actually play with it. While I’m not going to open up my home network and Arduino to the big wide internet, I have mirrored the pages and datafiles it produces on the webhost I used for my Has the World Ended Yet? project. You can find them here. These won’t be updated with new datapoints like the actual Arduino version will be, but they should at least give a fair impression of how the project looks and feels without the need to actually implement it.

For those who are unsure what they are looking at, I’ll offer a quick interpretation:

The list of data files available for graphing.

The list of data files available for graphing.

Going to the above page, we see that we are presented with a very basic list of the data files that can be selected from. Clicking any of them will cause  the graph for that datafile to be loaded (much more quickly than the Arduino can manage).

A graph for the first week of data collected.

A graph for the first week of data collected.

This chart for the 25-12-12.CSV file is already complete, and won’t have any new data added to it in the future, because the files for subsequent weeks have already been made. There is a lot to see though. The two data points that are at 1000 on the y-axis are from when I pointed a bright flashlight directly at the photo sensor. All of the data points between 300 and 400 on the y-axis are the result of the basement lights being on. The abnormally large gaps in the data are periods when the Arduino was powered off because I was still tweaking and developing it. Finally, the short humps that occur everyday are the result of natural light coming through one of the basement windows. By zooming in on one of them, we can see even more detail:

The intensity of natural light in my basement.

The intensity of natural light in my basement.

The first thing we notice is that the levels rise from zero to about 65 before falling and levelling out at close to 35 for two hours. This is followed by a another small increase before it ultimately decreases down to a value of ~10 where it levels out. That middle valley where the light levels are equal to 35 is due to the shadow cast  on the basement window by our neighbour’s house to the south of us. The levelling out of the light intensity at 10 after all the daylight has disappeared is because a light out in the hallway is usually on in the evening. It is eventually turned off for the night, causing the light levels to drop to zero where they will usually remain until the next morning. I must admit, I’m impressed that the cheap $1.00 photoresistor is capable of capturing this level of detail, and that these trends are so easily interpreted from the graphs.

How to Make One For Yourself

All of the code below is now conveniently hosted on GitHub (https://github.com/evjrob/super-graphing-data-logger) so you no longer need to copy and paste it if you don’t want to.

To replicate this project, a few things are necessary. You’ll obviously need an Arduino capable of connecting over Ethernet and storing files on an SD card. In my case, this is achieved through the use of an Uno with the Ethernet shield. Presumably an Arduino Ethernet model will also work fine, though I have not personally tested it. Other non official Ethernet shields and SD card adapters may also work if they use the same libraries, though I make no guarantees. For the more adventurous, it may be possible to adapt my code to achieve the same functionality using a Wifi shield. You will also need a data source of some sort. For my project I chose to use a very cheap photoresistor, which I rigged up on a small perf board to plug directly into the 5v, gnd, and A0 pins of my Arduino (or more precisely,  the headers on the Ethernet shield). It is set up in such a way that the minimum recordable light intensity is zero, while the maximum is 1024.

The photo sensor board fits like a charm.

The photo sensor board fits like a charm.

DSCF2931

One header is bent to reach A0.

The pins on the male headers don’t quite line up, so I intentionally used extra long ones and added a slight S-curve to the one that goes to A0. This can be seen in better detail above. For those who are interested, the circuit is very simple:

The circuit.

The circuit.

Before we get started, we need to make sure our SD card is good to go. It should be formatted as a FAT16 or FAT32 filesystem, the details of which are available on the official Arduino website. Once that is done, we need to ensure two things are present in the root directory of the card: the HC.htm file, and a data/ directory for our datafiles. The data directory is easily made with the same computer that was used to format the card provided one has an SD card reader of some sort. The HC.htm simply consists of the following code:

You will need to edit this file first to make sure it points towards the preferred  location of your highcharts.js files. You can leave this as the public CDN: http://cdnjs.cloudflare.com/ajax/libs/highcharts/2.3.5/highcharts.js, change it to point towards your own webhost, or it can even be on the Arduino’s SD card (this will be slow). It is not necessary to create a datafile before hand, the SGDL sketch will take care of that when it decides to record its first data point. Before we get that far though, it is necessary to make sure we have configured the EEPROM memory for the SGDL sketch. This is very easily accomplished using a separate sketch, which I have called EEPROM_config. This sketch (along with SGDL itself) requires an extra library called EEPROMAnything, which needs to be added to the Arduino’s libraries folder wherever one’s sketchbook folder is. While you’re at it, you should also add the Time library which we need for SGDL.

I have intentionally commented out the write line so that no one writes junk to the EEPROM by accident. While the EEPROM has a life of ~100,000 write cycles, I’d rather not waste any of them. Please review the sketch carefully and ensure you’ve adjusted it accordingly before uploading it to the Arduino. The most important thing is to ensure that your newFileTime is something sensible (in the near future most of all).

Now that that’s all taken care of, we’re ready to get SGDL all set up! The code will need a few adjustments for your own specific setup, mostly in regards to the Ethernet MAC and IP addresses. I trust that anyone making use of this code already knows how to configure their router to work with the Arduino, and that they can find the appropriate local IP address to update this sketch with. You may also wish to change the timeserver IP address to one that is geographically closer to yourself.

I currently have my code set up to make a measurement every 10 minutes, and to create a new data file every week. You are welcome to change those parameters, just be aware that the current data file management names files using a dd-mm-yy.csv date format, so the new file interval should be at least 24 hours. Another concern, is that the shorter the measurement interval and the longer the new data file interval is, the larger the files will be. Because the Arduino is not especially powerful, this will have consequences for the loading times of each chart.

Posted by Everett in Arduino, Electronics, Programming, Sensors and Data Logging, Web Applications, 75 comments