Skip to content

Instantly share code, notes, and snippets.

@LauraHornbake

LauraHornbake/README.md

Created September 19, 2013 14:04
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save LauraHornbake/6623968 to your computer and use it in GitHub Desktop.
Save LauraHornbake/6623968 to your computer and use it in GitHub Desktop.
Download ZIP
GSS to GeoJSON
Raw
README.md

About

This tool transforms a Google spreadsheet of "events" with GPS coordinates into a GeoJSON feature collection, which you can copy and use to build custom maps. See this blog post for more on why/how...

How to use it

  • Start with a Google spreadsheet with column headings: "date", "place", "city", "province", "nation", "latitude", "longitude" (*required). See: an example spreadsheet.
  • Publish the spreadsheet and make note of the key in the url.
  • Input the key in the form below and submit. The key to the example spreasheet is the default, try it out before you create your own.

Caveats

  • Recommended: Check your results. Jason Sanford's GeoJSONlint validates GeoJSON.
  • The current limitation on which columns this tool retrieves from Google spreadsheets is entirely arbitrary, based on what I needed. If there is sufficient interest, I can enable users to select headers from their own data.
  • Privacy: this works entirely client-side, so your data is not sent to my or anyone else's servers.
  • Built to GeoJSON format specifications 1.0
Raw
importGSS.js
/**
* @author Laura J. Hornbake
* @license MIT
*
*/
// Callback function for GSS API call, parses JSON result from API and formats as GeoJSON
function importGSS(json) {
// feature constructor
function Feature (date,place,city,province,nation,longitude,latitude) {
this.type="Feature";
this.properties={};
this.properties.date=date;
this.properties.place=place;
this.properties.city=city;
this.properties.province=province;
this.properties.nation=nation;
this.geometry={};
this.geometry.type="Point";
this.geometry.coordinates=[];
this.geometry.coordinates[0]=longitude;
this.geometry.coordinates[1]=latitude;
}
//feature collection constructor
function FeatureCollection () {
this.type="FeatureCollection";
this.features=[];
};
//create a feature collection
var json_result = new FeatureCollection();
//add features to your feature collection
for (var i = 0; i < json.feed.entry.length; i++) {
var f = new Feature(json.feed.entry[i].gsx$date.$t, json.feed.entry[i].gsx$place.$t,
json.feed.entry[i].gsx$city.$t, json.feed.entry[i].gsx$province.$t, json.feed.entry[i].gsx$nation.$t, parseFloat(json.feed.entry[i].gsx$longitude.$t), parseFloat(json.feed.entry[i].gsx$latitude.$t));
json_result.features[i] = f;
};
//append the new feature collection to the document
var data = $('.results').append(JSON.stringify(json_result, null, " "));
return data;
}
Raw
index.html
<html>
<head>
<title>GSS to GeoJSON</title>
</head>
<body>
<div class="container">
<header>
<div id="intro">
<h1>GSS to GeoJSON</h1>
</div>
</header>
<section>
<div id="form">
<form id="keyForm">
<div class="input-group">
<input type="text" id="key" name="key" title="Please enter your spreadsheet key" value="0ArToGQcSQS-IdHRWMlowVXpUMm9JMGtIVm5NdU5yZnc" class="form-control">
<span class="input-group-btn">
<input type="submit" value="Submit" class="btn btn-default">
</span>
</div>
</form>
</div>
</section>
<section>
<div id="json_result">
<pre class="results"></pre>
</div>
</section>
</div>
<script src="http://code.jquery.com/jquery.js"></script>
<script src="js/keyform.js"></script>
<script src="js/importGSS.js"></script>
</body>
</html>
Raw
keyform.js
/**
* @author Laura J. Hornbake
* @license MIT
*
*/
// Form submission handler
$("#keyForm").submit(function(event) {
// Prevent default form submit
event.preventDefault();
// Get key value from input element on the page:
var $form = $(this), term = $form.find("input[name='key']").val();
// Use value to call API;
$('body').append('<script src="http://spreadsheets.google.com/feeds/list/' + encodeURIComponent(term) + '/1/public/values?alt=json-in-script&amp;callback=importGSS"></s' + 'cript>')
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment