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.

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.

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

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

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.

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

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:

<!DOCTYPE HTML>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>Super Graphing Data Logger!</title>

        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
        <script type="text/javascript">
function getDataFilename(str){
    point = str.lastIndexOf("file=")+4;

    tempString = str.substring(point+1,str.length)
    if (tempString.indexOf("&") == -1){
    return(tempString);
    }
    else{
        return tempString.substring(0,tempString.indexOf("&"));
    }
        
}

query  = window.location.search;

var dataFilePath = "/data/"+getDataFilename(query);

$(function () {
    var chart;
    $(document).ready(function() {
    
        // define the options
        var options = {
    
            chart: {
                renderTo: 'container',
                zoomType: 'x',
                spacingRight: 20
            },
    
            title: {
                text: 'Light levels recorded by the Arduino'
            },
    
            subtitle: {
                text: 'Click and drag in the plot area to zoom in'
            },
    
            xAxis: {
                type: 'datetime',
                maxZoom: 2 * 3600000
            },
    
            yAxis: {
                title: {
                    text: 'Light Levels (0 - 1024)'
                },
                min: 0,
                startOnTick: false,
                showFirstLabel: false
            },
    
            legend: {
                enabled: false
            },
    
            tooltip: {
                formatter: function() {
                        return '<b>'+ this.series.name +'</b><br/>'+
                        Highcharts.dateFormat('%H:%M - %b %e, %Y', this.x) +': '+ this.y;
                }
            },
    
            plotOptions: {
                series: {
                    cursor: 'pointer',
                    lineWidth: 1.0,
                    point: {
                        events: {
                            click: function() {
                                hs.htmlExpand(null, {
                                    pageOrigin: {
                                        x: this.pageX,
                                        y: this.pageY
                                    },
                                    headingText: this.series.name,
                                    maincontentText: Highcharts.dateFormat('%H:%M - %b %e, %Y', this.x) +':<br/> '+
                                        this.y,
                                    width: 200
                                });
                            }
                        }
                    },
                }
            },
    
            series: [{
                name: 'Light Levels',
                marker: {
                    radius: 2
                }
            }]
        };
    
    
        // Load data asynchronously using jQuery. On success, add the data
        // to the options and initiate the chart.
        // http://api.jquery.com/jQuery.get/
        jQuery.get(dataFilePath, null, function(csv, state, xhr) {
            var lines = [],
                date,
    
                // set up the two data series
                lightLevels = [];
    
            // inconsistency
            if (typeof csv !== 'string') {
                csv = xhr.responseText;
            }
    
            // split the data return into lines and parse them
            csv = csv.split(/\n/g);
            jQuery.each(csv, function(i, line) {
    
                // all data lines start with a double quote
                line = line.split(',');
                date = parseInt(line[0], 10)*1000;
    
                lightLevels.push([
                    date,
                    parseInt(line[1], 10)
                ]);
                
            });
    
            options.series[0].data = lightLevels;
    
            chart = new Highcharts.Chart(options);
        });
    });
    
});
        </script>
    </head>
    <body>
        <p style="text-align:center;">Please allow the chart to load, it may take up to 30 seconds </p>
        <hr/>
<script src="http://cdnjs.cloudflare.com/ajax/libs/highcharts/2.3.5/highcharts.js"></script>

<!-- Additional files for the Highslide popup effect -->
<script type="text/javascript" src="http://www.highcharts.com/highslide/highslide-full.min.js"></script>
<script type="text/javascript" src="http://www.highcharts.com/highslide/highslide.config.js" charset="utf-8"></script>
<link rel="stylesheet" type="text/css" href="http://www.highcharts.com/highslide/highslide.css" />

<div id="container" style="min-width: 400px; height: 400px; margin: 0 auto"></div>

    </body>
</html>

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.

/* ************************************************************************
 * ***            Super Graphing Data Logger - EEPROM config            ***
 * ************************************************************************
 * Everett Robinson, December 2012.
 *
 * The following extra non standard libraries were used, and will need to be
 * added to the libraries folder:
 * - EEPROMAnything: http://playground.arduino.cc/Code/EEPROMWriteAnything
 *
 * This sketch helps you set the values in EEPROM which are necessary for
 * Super Graphing Data Logger. It should only need the be run once before
 * the first time you set up SGDL, or in the unlikely event that the EEPROM
 * becomes corrupted.
 *
 * Please ensure that the values in configuration config are appropriate for
 * your project before uncommenting the EEPROM_writeAnything(0, config); line.
 *
 */

#include <EEPROM.h>
#include <EEPROMAnything.h>

typedef struct{
    unsigned long newFileTime;
    char workingFilename[19];
  } configuration;

//This is a one off thing, so everything is in setup
void setup(){
  Serial.begin(9600);
  
  //Create the config struct to write to EEPROM, change values as appropriate
  //Make sure your filename is not too long for the workingFilename char array 
  configuration config = {1356912000L,"/data/25-12-12.csv"};
  //Write the values to the EEPROM
  //EEPROM_writeAnything(0, config);       //Uncomment when you're sure everything is correct
  configuration config2;                   //Create a second config struct for verification
  EEPROM_readAnything(0,config2);
  Serial.print("The value read from EEPROM for newFileTime is: ");
  Serial.println(config2.newFileTime);
  Serial.print("The value read from EEPROM for workingFilename is: ");
  Serial.println(config2.workingFilename);
  Serial.println("If those values are correct then everything went as planned. Otherwise,");
  Serial.println("please double check that the values declared for the struct config are");
  Serial.println("correct and that that EEPROM_writeAnything line is uncommented.");
}


void loop(){
}

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.

/* ************************************************************************
 * ***                    Super Graphing Data Logger                    ***
 * ************************************************************************
 * Everett Robinson, December 2012. More at: http://everettsprojects.com
 *
 * This sketch relies on the SD and ethernet libraries in arduino 1.0 or newer.
 * The following extra non standard libraries were also used, and will need to
 * be added to the libraries folder:
 * - Time: http://playground.arduino.cc/Code/Time
 * - EEPROMAnything: http://playground.arduino.cc/Code/EEPROMWriteAnything
 *
 * If this is your first time setting up this project, please go get the
 * EEPROM_config sketch from http://everettsprojects.com so that you can 
 * configure the config struct in the EEPROM memory. Usage of the EEPROM 
 * is needed to make the project resiliant against a temporary loss of power.
 *
 * You must also ensure that you have the HC.htm file in the root directory
 * of your SD card, as well as a data directory where the datafiles will be
 * stored.
 *
 * This sketch combines the functionality of an existing fileserver example
 * which can be found at http://www.ladyada.net/learn/arduino/ethfiles.html
 * with the Datalogger example that comes with the new SD library from 1.0,
 * as well as some code from the UdpNtpClient example that cones with the
 * ethernet library. 
 *
 * Added to all of these are some tricks to make it manage and serve up the
 * datafiles in conjunction with a page which uses highcharts JS to graph it.
 * This is basically accomplished using the arduino by itself. Because I
 * actually host the highcharts.js files externally, this is true more in
 * theory than in actual practice, but oh well. It should work just fine to
 * have the highcharts.js file on the arduino's SD card, though loading the 
 * page will be painfully slow.
 *
 * Some of the code this was derived from may or may not be under a GPL
 * licence; I'm not entirely sure. I suppose anyone using this should treat 
 * it like it is too, but I don't really care too much.
 * Also if one intends to use this for commercial applications, it may be
 * necessary to purchase a license for Highcharts.
 *
 * Changes:   -------------------------------------------------------------
 * January 2013: Updated so that the dd-mm-yy.csv file format is properly 
 * followed, all single digit days, months, and years will have a leading 
 * zero now. 
 *
 */

#include <SD.h>
#include <Ethernet.h>
#include <EthernetUdp.h>
#include <SPI.h>
#include <string.h>
#include <Time.h>
#include <EEPROM.h>
#include <EEPROMAnything.h>
#include <avr/pgmspace.h>

/************ ETHERNET STUFF ************/
byte mac[] = { 0x90, 0xA2, 0xDA, 0x00, 0x4C, 0x64 };
byte ip[] = { 192,168,1, 100 };
EthernetServer server(80);

/************** NTP STUFF ***************/
unsigned int localPort = 8888;          // local port to listen for UDP packets
IPAddress timeServer(132, 163, 4, 101); //NIST time server IP address: for more info
                                        //see http://tf.nist.gov/tf-cgi/servers.cgi

const int NTP_PACKET_SIZE= 48; //NTP time stamp is in the first 48 bytes of the message
byte packetBuffer[ NTP_PACKET_SIZE]; //buffer to hold incoming and outgoing packets 
EthernetUDP Udp;

/*** DATA LOGGER AND TIMER CONTROLS ****/
const int analogPin = 0;
unsigned long lastIntervalTime = 0; //The time the last measurement occured.
#define MEASURE_INTERVAL 600000     //10 minute intervals between measurements (in ms)
unsigned long newFileTime;          //The time at which we should create a new week's file
#define FILE_INTERVAL 604800        //One week worth of seconds

//A structure that stores file config variables from EEPROM
typedef struct{                     
    unsigned long newFileTime;      //Keeps track of when a newfile should be made.
    char workingFilename[19];       //The path and filename of the current week's file
} configuration;
  
configuration config;               //Actually make our config struct


// Strings stored in flash mem for the Html Header (saves ram)
prog_char HeaderOK_0[] PROGMEM = "HTTP/1.1 200 OK";            //
prog_char HeaderOK_1[] PROGMEM = "Content-Type: text/html";    //
prog_char HeaderOK_2[] PROGMEM = "";                           //

// A table of pointers to the flash memory strings for the header
PROGMEM const char *HeaderOK_table[] = {   
  HeaderOK_0,
  HeaderOK_1,
  HeaderOK_2
};

// A function for reasy printing of the headers  
void HtmlHeaderOK(EthernetClient client) {
  
    char buffer[30]; //A character array to hold the strings from the flash mem
    
    for (int i = 0; i < 3; i++) {
      strcpy_P(buffer, (char*)pgm_read_word(&(HeaderOK_table[i]))); 
      client.println( buffer );
    }
} 
  
  
// Strings stored in flash mem for the Html 404 Header
prog_char Header404_0[] PROGMEM = "HTTP/1.1 404 Not Found";     //
prog_char Header404_1[] PROGMEM = "Content-Type: text/html";    //
prog_char Header404_2[] PROGMEM = "";                           //
prog_char Header404_3[] PROGMEM = "<h2>File Not Found!</h2>"; 

// A table of pointers to the flash memory strings for the header
PROGMEM const char *Header404_table[] = {   
  Header404_0,
  Header404_1,
  Header404_2,
  Header404_3
};

// Easy peasy 404 header function
void HtmlHeader404(EthernetClient client) {
  
    char buffer[30]; //A character array to hold the strings from the flash mem
    
    for (int i = 0; i < 4; i++) {
      strcpy_P(buffer, (char*)pgm_read_word(&(Header404_table[i]))); 
      client.println( buffer );
    }
} 


void setup() {
  Serial.begin(9600);
  
  pinMode(10, OUTPUT);          // set the SS pin as an output (necessary!)
  digitalWrite(10, HIGH);       // but turn off the W5100 chip! 
  
  // see if the card is present and can be initialized:
  if (!SD.begin(4)) {
    Serial.println("Card failed, or not present");
    // don't do anything more:
    return;
  }
  Serial.println("card initialized.");
  
  // The SD card is working, start the server and ethernet related stuff!
  Ethernet.begin(mac, ip);
  server.begin();
  Udp.begin(localPort);
  EEPROM_readAnything(0,config); // make sure our config struct is syncd with EEPROM
}


// A function that takes care of the listing of files for the
// main page one sees when they first connect to the arduino.
// it only lists the files in the /data/ folder. Make sure this
// exists on your SD card.
void ListFiles(EthernetClient client) {
  
  File workingDir = SD.open("/data");
  
  client.println("<ul>");
  
    while(true) {
      File entry =  workingDir.openNextFile();
       if (! entry) {
         break;
       }
       client.print("<li><a href=\"/HC.htm?file=");
       client.print(entry.name());
       client.print("\">");
       client.print(entry.name());
       client.println("</a></li>");
       entry.close();
    }
  client.println("</ul>");
  workingDir.close();
}

// A function to get the Ntp Time. This is used to make sure that the data
// points recorded by the arduino are referenced to some meaningful time
// which in our case is UTC represented as unix time (choosen because it 
// works simply with highcharts without too much unecessary computation).
unsigned long getTime(){
  sendNTPpacket(timeServer); // send an NTP packet to a time server

  // wait to see if a reply is available
  delay(1000);  
  if ( Udp.parsePacket() ) {  
    // We've received a packet, read the data from it
    Udp.read(packetBuffer,NTP_PACKET_SIZE);  // read the packet into the buffer

    //the timestamp starts at byte 40 of the received packet and is four bytes,
    // or two words, long. First, esxtract the two words:

    unsigned long highWord = word(packetBuffer[40], packetBuffer[41]);
    unsigned long lowWord = word(packetBuffer[42], packetBuffer[43]);  
    // combine the four bytes (two words) into a long integer
    // this is NTP time (seconds since Jan 1 1900):
    unsigned long secsSince1900 = highWord << 16 | lowWord;  
    // Unix time starts on Jan 1 1970. In seconds, that's 2208988800:
    const unsigned long seventyYears = 2208988800UL;     
    // subtract seventy years:
    unsigned long epoch = secsSince1900 - seventyYears;  
    // return Unix time:
    return epoch;
  }
}

// send an NTP request to the time server at the given address,
// necessary for getTime().
unsigned long sendNTPpacket(IPAddress& address){
  
  // set all bytes in the buffer to 0
  memset(packetBuffer, 0, NTP_PACKET_SIZE); 
  // Initialize values needed to form NTP request
  // (see URL above for details on the packets)
  packetBuffer[0] = 0b11100011;   // LI, Version, Mode
  packetBuffer[1] = 0;     // Stratum, or type of clock
  packetBuffer[2] = 6;     // Polling Interval
  packetBuffer[3] = 0xEC;  // Peer Clock Precision
  // 8 bytes of zero for Root Delay & Root Dispersion
  packetBuffer[12]  = 49; 
  packetBuffer[13]  = 0x4E;
  packetBuffer[14]  = 49;
  packetBuffer[15]  = 52;

  // all NTP fields have been given values, now
  // you can send a packet requesting a timestamp:         
  Udp.beginPacket(address, 123); //NTP requests are to port 123
  Udp.write(packetBuffer,NTP_PACKET_SIZE);
  Udp.endPacket(); 
}


// How big our line buffer should be for sending the files over the ethernet.
// 75 has worked fine for me so far.
#define BUFSIZ 75

void loop(){
  if ((millis() % lastIntervalTime) >= MEASURE_INTERVAL){ //Is it time for a new measurement?
     
    char dataString[20] = "";
    int count = 0;
    unsigned long rawTime;
    rawTime = getTime();

    while((rawTime == 39) && (count < 12)){     //server seems to send 39 as an error code
      delay(5000);                              //we want to retry if this happens. I chose
      rawTime = getTime();                      //12 retries because I'm stubborn/persistent.
      count += 1;                               //NIST considers retry interval of <4s as DoS
    }                                           //attack, so fair warning.
    
    if (rawTime != 39){                         //If that worked, and we have a real time
      
      //Decide if it's time to make a new file or not. Files are broken
      //up like this to keep loading times for each chart bearable.
      //Lots of string stuff happens to make a new filename if necessary.
      if (rawTime >= config.newFileTime){
        int dayInt = day(rawTime);
        int monthInt = month(rawTime);
        int yearInt = year(rawTime);
        char newFilename[18] = "";
        char dayStr[3];
        char monthStr[3];
        char yearStr[5];
        char subYear[3];
        strcat(newFilename,"data/");
        itoa(dayInt,dayStr,10);
        if (dayInt < 10){
          strcat(newFilename,"0");
        }
        strcat(newFilename,dayStr);
        strcat(newFilename,"-");
        itoa(monthInt,monthStr,10);
        if (monthInt < 10){
          strcat(newFilename,"0");
        }
        strcat(newFilename,monthStr);
        strcat(newFilename,"-");
        itoa(yearInt,yearStr,10);
        //we only want the last two digits of the year
        memcpy( subYear, &yearStr[2], 3 );
        strcat(newFilename,subYear);
        strcat(newFilename,".csv");
        
        //make sure we update our config variables:
        config.newFileTime += FILE_INTERVAL;
        strcpy(config.workingFilename,newFilename);
        //Write the changes to EEPROM. Bad things may happen if power is lost midway through,
        //but it's a small risk we take. Manual fix with EEPROM_config sketch can correct it.
        EEPROM_writeAnything(0, config); 
      }
        
      //get the values and setup the string we want to write to the file
      int sensor = analogRead(analogPin);  
      char timeStr[12];
      char sensorStr[6];
      
      ultoa(rawTime,timeStr,10); 
      itoa(sensor,sensorStr,10);
      
      strcat(dataString,timeStr);
      strcat(dataString,",");
      strcat(dataString,sensorStr);
      
      //open the file we'll be writing to.
      File dataFile = SD.open(config.workingFilename, FILE_WRITE);
  
      // if the file is available, write to it:
      if (dataFile) {
        dataFile.println(dataString);
        dataFile.close();
        // print to the serial port too:
        Serial.println(dataString);
      }  
      // if the file isn't open, pop up an error:
      else {
        Serial.println("Error opening datafile for writing");
      }
    }
    else{
      Serial.println("Couldn't resolve a time from the Ntp Server.");
    }
    //Update the time of the last measurment to the current timer value
    lastIntervalTime = millis();
  }
  //No measurements to be made, make sure the webserver is available for connections.
  else{
    char clientline[BUFSIZ];
    int index = 0;
    
    EthernetClient client = server.available();
    if (client) {
      // an http request ends with a blank line
      boolean current_line_is_blank = true;
      
      // reset the input buffer
      index = 0;
      
      while (client.connected()) {
        if (client.available()) {
          char c = client.read();
          
          // If it isn't a new line, add the character to the buffer
          if (c != '\n' && c != '\r') {
            clientline[index] = c;
            index++;
            // are we too big for the buffer? start tossing out data
            if (index >= BUFSIZ) 
              index = BUFSIZ -1;
            
            // continue to read more data!
            continue;
          }
          
          // got a \n or \r new line, which means the string is done
          clientline[index] = 0;
          
          // Print it out for debugging
          Serial.println(clientline);
          
          // Look for substring such as a request to get the root file
          if (strstr(clientline, "GET / ") != 0) {
            // send a standard http response header
            HtmlHeaderOK(client);
            // print all the data files, use a helper to keep it clean
            client.println("<h2>View data for the week of (dd-mm-yy):</h2>");
            ListFiles(client);
          }
          else if (strstr(clientline, "GET /") != 0) {
            // this time no space after the /, so a sub-file!
            char *filename;
            
            filename = strtok(clientline + 5, "?"); // look after the "GET /" (5 chars) but before
            // the "?" if a data file has been specified. A little trick, look for the " HTTP/1.1"
            // string and turn the first character of the substring into a 0 to clear it out.
            (strstr(clientline, " HTTP"))[0] = 0;
            
            // print the file we want
            Serial.println(filename);
            File file = SD.open(filename,FILE_READ);
            if (!file) {
              HtmlHeader404(client);
              break;
            }
            
            Serial.println("Opened!");
                      
            HtmlHeaderOK(client);
            
            int16_t c;
            while ((c = file.read()) > 0) {
                // uncomment the serial to debug (slow!)
                //Serial.print((char)c);
                client.print((char)c);
            }
            file.close();
          }
          else {
            // everything else is a 404
            HtmlHeader404(client);
          }
          break;
        }
      }
      // give the web browser time to receive the data
      delay(1);
      client.stop();
    }
  }
}

Has the world ended yet? A first attempt at web development

Despite the sheer nuttiness of it, everyone keeps going on about the end of the world as “predicted” by the Mayan calendar. National Geographic even had and entire day devoted to it. Building on that theme, I decided to make a very convenient (and pretty much useless) webpage that helps you figure out if the world has in fact ended: http://everett.x10.mx/end-of-the-world.php. This project was really simple, didn’t involve a lot of code or design, and was basically thrown together over the course of an hour and a half. It turns out PHP is extremely easy if your host is already configured for it, and I’m looking forward to doing some more web development related stuff both with PHP and other languages or tools. The HTML side of the page was also relatively straightforward. I’m impressed by what’s possible design wise using modern HTML and CSS3. My inspiration on that front was this amazing site: http://www.tubalr.com/

screenshot

It works as the page implies, by polling google.com for a response. If google is down, then it is assumed the world has ended, and the result is Yes.in big red letters. The PHP that does the trick is a slightly modified version of what’s posted at the following site: http://css-tricks.com/snippets/php/check-if-website-is-available/. The background is not mine, but I’ve left attribution on the image, and you can find the originals here: http://m3-f.deviantart.com/gallery/?offset=24#/d3b4qgn.

And because I see no reason not to release it, here is the entire source code for the page:

 <?php
   function Visit($url){
     $agent = "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)";$ch=curl_init();
     curl_setopt ($ch, CURLOPT_URL,$url );
     curl_setopt($ch, CURLOPT_USERAGENT, $agent);
     curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1);
     curl_setopt ($ch,CURLOPT_VERBOSE,false);
     curl_setopt($ch, CURLOPT_TIMEOUT, 5);
     curl_setopt($ch,CURLOPT_SSL_VERIFYPEER, FALSE);
     curl_setopt($ch,CURLOPT_SSLVERSION,3);
     curl_setopt($ch,CURLOPT_SSL_VERIFYHOST, FALSE);
     $page=curl_exec($ch);
     //echo curl_error($ch);
     $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
     curl_close($ch);
     if($httpcode>=200 && $httpcode<300) return true;
     else return false;
   }
   if (Visit("http://www.google.com")){
     $answer = "No.";
     $colour = "green";
   }
   else{
     $answer = "Yes.";
     $colour = "red";
   }
?>

<!DOCTYPE html>
<html>
  <head>
    <title>Has the World Ended Yet?</title>
<style>
  a:link {color:#FFFFFF;}
  a:visited {color:#FFFFFF;}

html {
  overflow-y: scroll;
  background: url(/backgrounds/eow.jpg) no-repeat center center fixed;
  -webkit-background-size: cover;
  -moz-background-size: cover;
  -o-background-size: cover;
  background-size: cover;

}

body {
  font-family: 'Open Sans', sans-serif;
  font-size: 24px;
  color: #fff;
  padding-bottom: 20px;
}

#main
{
  text-align: center;
  margin-top: 50px;
  margin-bottom: 20px;
  background: #000;
  background: rgba(0, 0, 0, 0.85);
  -webkit-border-radius: 5px;
  -moz-border-radius: 5px;
  -ms-border-radius: 5px;
  -o-border-radius: 5px;
  border-radius: 5px;
  -webkit-box-shadow: 0 0 5px rgba(0, 0, 0, 0.5);
  -moz-box-shadow: 0 0 5px rgba(0, 0, 0, 0.5);
  box-shadow: 0 0 5px rgba(0, 0, 0, 0.5);
  border: solid 1px #000;
  width:800px;
  margin-left:auto;
  margin-right:auto;
}
#result
{
  font-family: 'Open Sans', sans-serif;
  font-size: 112px;
  color: <?=$colour?>;
}

#disclaimer
{
  font-family: 'Open Sans', sans-serif;
  font-size: 12px;
  color: #fff;
  margin-top: 80px;
  margin-left: 100px;
  margin-right: 100px;
  margin-bottom: 50px;
}
</style>

  </head>
  <body>
    <div id="main">
        <H1>Has the world ended yet? <sup>*</sup></H1>
        <br>
        <div id="result">
            <b> <?=$answer?></b>
        </div>
        <div id="disclaimer">
            <sup>*</sup> Does not actually check if the world has ended. Result is based on the assumption that if Google.com is not responding, the world has probably ended. <br><br> <a href="http://everettsprojects.com">http://everettsprojects.com/</a>
        </div>
    </div>
</body>
</html>

A place to present the useless things I do in my spare time

Follow

Get every new post delivered to your Inbox.