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.

There is an updated updated version of this project that uses AWS Lambda functions.

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

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:

#### getSpillInfo.php

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:

#### getSpillLocations.php

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.

#### index.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.