Friday, June 14

eBird Checklist Map

Yesterday I threw some code together that generates a map of all eBird checklists submitted in my area.

I haven't had time to perfect the idea yet, and I may add a few more features. In the meantime, here's how you can do this too:

You will need a computer which can run Microsoft Excel, Windows Notepad, and an internet browser (preferentially Chrome, Firefox, or IE 10).

STEP 1: Download regional eBird Data. Go to Explore Data, click on "Download Data" in the bottom right of the page. Select "All Species", search for any region you wish (e.g. county, state, province), select "All Dates", and check "Include Unvetted Data". You will get an email anytime from a few minutes to a few days after requesting this data, but it will come, so be patient. Bear in mind that your region should not contain more than 100,000 observations or Excel will not be able to process your data.

STEP 2: You will receive a folder of downloaded documents, open the large data file using Excel. Open the data ribbon, and click the filter icon.
     If you cannot find the filter icon, or if you are using an older version of Excel, click on a cell and press Ctrl+A to select all data. While selected, click "Insert Table" on the main Insert ribbon. You need to sort your file by Locality ID, so click on the filter arrow in S2 (cell for Locality ID) and sort A to Z.

STEP 3: To minimize computer processing time, we only want one record of each checklist, so go to the Data ribbon again, and click on Remove Duplicates. Unselect all and select only "Sampling Event Identifier". Click ok, Excel will tell you how many unique values remain -- that is the number of checklists submitted in your area.

STEP 4: Now paste the following formula into cell AP2:

=CONCATENATE("[",SUBSTITUTE(S2,"L",""),",",U2,",",V2,",'",TEXT(W2,"mm/dd/yyyy"),"','",TEXT(X2,"hh:mm"),"','",SUBSTITUTE(R2,"'",""),"',","'",SUBSTITUTE(AB2,"'","")," ",SUBSTITUTE(AC2,"'",""),"','",AD2,"','",T2,"'],")

This should give you output like the following:

[1000070,50.9742106,5.8666992,'04/25/2008','00:00','Munstergeleen','Jamie Glydon','S6925390','P'],

Apply this formula to every cell in the column by dragging the bottom right corner of the cell all the way down to the last row with data. Warning: There may be a LOT of data!

On a Windows computer, open up Notepad by clicking on the Start Menu > All Programs > Accessories > Notepad. Using a similar text editor on a Mac should work as well. Copy and paste the following code into the file:

<!DOCTYPE html>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no">
<meta charset="utf-8">
<title>Checklist Map</title>
<style type="text/css">
html, body {height: 100%;margin: 0;padding: 0;}
#map_canvas {height: 100%;}
@media print {html,body{height: auto;}
#map_canvas {height:100%;width:100%;}}
<script src=""></script>
function initialize() {

var CenterCoordinates=new google.maps.LatLng(42.45,-76.51); // ***** YOU MAY CHANGE THIS COORDINATE TO SET YOUR MAP'S DEFAULT CENTER POINT AT A DIFFERENT LOCATION
var mapProp = {center:CenterCoordinates,zoom:6,mapTypeId:google.maps.MapTypeId.ROADMAP}; // **** YOU MAY ADJUST THE DEFAULT STARTING ZOOM TO A HIGHER OR LOWER VALUE
var map=new google.maps.Map(document.getElementById("googleMap"),mapProp);

var locations = [  // ****** PASTE YOUR EXCEL OUTPUT BELOW THIS LINE


var markers = [];
var infowindow = new google.maps.InfoWindow();

for (var i = 0; i < locations.length; i++) {
        var location = locations[i];
          var nextlocation = locations[i+1];
          var nextlocid = nextlocation[0];
        else {nextlocid = "";}
        var locid = location[0];
        var lat = location[1];
        var lng = location[2];
        var date = location[3];
        var time = location[4];
        var locname = location[5];
        var observer = location[6];
        var checklistid = location[7];
        var private = location[8];

  if(private=="P"){var FewIcon="";}
  if(private=="H"){var FewIcon="";}
  var marker = new google.maps.Marker({position: new google.maps.LatLng (lat,lng),map: map,icon:FewIcon});
  var infowindowContent =  "<b>" + locname + "</b><br />" + date + " " + time + " " + observer + " <a href='" + checklistid + "'>checklist</a>";
else {
  var infowindowContent = "<b>" + locname + "</b><br /><table>";
  infowindowContent +=  "<tr><td>1.</td><td>" + date + "</td><td>" + observer + "</td><td><a href='" + checklistid + "'>List</a></td></tr>";  // may wish to include:  <td>" + comparetime + "</td>
  for(var k=0;k<locations.length;k++) {
      if(private=="P"){var ManyIcon="";}
      if(private=="H"){var ManyIcon="";}
    else {
      if(private=="P"){var ManyIcon="";}
      if(private=="H"){var ManyIcon="";}
   comparelocid = locations[i+1];
  if(locid==comparelocid[0]) {
    var prevdata = locations[i+1];
    var comparelocid = prevdata[0];
    var comparelat = prevdata[1];
    var comparelng = prevdata[2];
    var comparedate = prevdata[3];
    var comparetime = prevdata[4];
    var comparelocname = prevdata[5];
    var compareobserver = prevdata[6];
    var comparechecklistid = prevdata[7];
    var compareprivate = prevdata[8];
    infowindowContent +=  "<tr><td>" + (k+2) + ".</td><td>" + comparedate + "</td><td>" + compareobserver + "</td><td><a href='" + comparechecklistid + "'>List</a></td></tr>";  // may wish to include:  <td>" + comparetime + "</td>
    else{infowindowContent += "</table>";break;}
  var marker = new google.maps.Marker({position: new google.maps.LatLng (lat,lng),map: map,icon:ManyIcon});
makeInfoWindowEvent(map, infowindow,infowindowContent, marker);

function makeInfoWindowEvent(map, infowindow, contentString, marker) {
  google.maps.event.addListener(marker, 'click', function() {
    infowindow.setContent(contentString);, marker);

google.maps.event.addDomListener(window, 'load', initialize);
<div id="googleMap" style="width:100%;height:100%;"></div>

Now highlight all data in column AP of your Excel spreadsheet. Press Ctrl+C to copy the data. Navigate to your notepad file, click between the asterisks indicating where to paste your data, then press Ctrl+V to paste all your data.

Now save your notepad file WITH THE EXTENSION .html. For example, tompkinschecklistmap.html. You cannot forget the extension!!! If you do this correctly, your file should now appear in the location you saved it with the default opening program as your default web browser. Open up the file, if it doesn't work after you've done what you can, please email me with your .html file attached at

You can change some settings of your map in the .html file you saved. I have put in detailed notes so you know exactly what to do.

If you have comments, or would like to share one of your maps, feel free to do so. If you need help, email, and I'll get right back with you. I'm probably going to do further development with this idea, so I'll post again if I have any updates!

No comments:

Post a Comment

Please leave your comment below. If you have a question or issue which may be specific to your situation only, please email Thank you!