Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
OverPassToGoogleSheet.gs
//var langCode ='ta'; -- TODO Make it language independent.
function doGet() {
return HtmlService.createTemplateFromFile('Index.html')
.evaluate();
}
function doSomething() {
Logger.log('I was called!');
}
function OverpassToGoogleSheets() {
Logger.clear();
//Show web popup of map?
//Suggest using Wikidata | Xlate memory?.
//Allow verification.
//Allow download of josm / other upload file.
//Explore using OSM API to directly upload.
//OverpassQueryBuilder();
//var query = GetOverpassQueryFromUser()
//var query = '[out:json][timeout:3600][bbox:12.9201721,80.1238331,13.2401721,80.4438331];(node["place"]["name"~"."]["name:ta"!~"."];);out meta;';
var query = '[out:json][timeout:3600][bbox:12.9201721,80.1238331,13.2401721,80.4438331];(node["amenity"="hospital"]["name"~"."]["name:ta"!~"."];);out meta;'
//var query = '[out:json][timeout:3600][bbox:12.9201721,80.1238331,13.2401721,80.4438331];(node["highway"]["name"~"."]["name:ta"!~"."];);out meta;'
//var query = 'https://overpass-api.de/api/convert?data=%5Bout%3Ajson%5D%5Btimeout%3A360%5D%3B%0A%2F%2F%20gather%20results%0A(%0A%20%20area%5Bname%3D%22Tamil%20Nadu%22%5D%3B%0A%20%20%2F%2F%20query%20for%20%22place%22%20%22without%20name%3Ata%22%20tags%0A%20%20node%5B%22place%22%5D(area)%5B%22name%3Ata%22!~%22.%22%5D(area)%3B%0A)%3B%0A%2F%2F%20print%20results%0Aout%20meta%3B%0A%3E%3B%0Aout%20meta%20qt%3B%0A&target=compact'
//var query = '[out:json][timeout:3600];(node["railway"="station"](7.8586434,76.2256046,13.5375733,80.682728)["name:ta"!~"."](7.8586434,76.2256046,13.5375733,80.682728););out meta;>;out meta qt;';
//var query = '[out:json][timeout:3600];(node["railway"="station"](7.8586434,76.2256046,13.5375733,80.682728)["name:ta"!~"."](7.8586434,76.2256046,13.5375733,80.682728););out meta;>;out meta qt;';
//Chennai Highway nodes - tertiary'[out:json][timeout:3600];(way["highway"="tertiary"](12.9201721,80.1238331,13.2401721,80.4438331)["name:ta"!~"."](12.9201721,80.1238331,13.2401721,80.4438331););out meta;>;out meta qt;';
//Chennai Highway nodes - secondary[out:json][timeout:3600];(way["highway"="secondary"](12.9201721,80.1238331,13.2401721,80.4438331)["name:ta"!~"."](12.9201721,80.1238331,13.2401721,80.4438331););out meta;>;out meta qt;
//Chennai Highway nodes'[out:json][timeout:3600];(node["highway"](12.9201721,80.1238331,13.2401721,80.4438331)["name:ta"!~"."](12.9201721,80.1238331,13.2401721,80.4438331););out meta;>;out meta qt;';
//Tamil Nadu Railway stations'[out:json][timeout:3600];(node["railway"="station"](7.8586434,76.2256046,13.5375733,80.682728)["name:ta"!~"."](7.8586434,76.2256046,13.5375733,80.682728););out meta;>;out meta qt;'
//Chennai Railway stations'[out:json][timeout:3600];(node["railway"="station"](12.9201721,80.1238331,13.2401721,80.4438331)["name:ta"!~"."](12.9201721,80.1238331,13.2401721,80.4438331););out meta;>;out meta qt;'
//- Chennai Suburbs '[out:json][timeout:3600];(node["place"](12.9201721,80.1238331,13.2401721,80.4438331)["name:ta"!~"."](12.9201721,80.1238331,13.2401721,80.4438331););out meta;>;out meta qt;';
//'[out:json][timeout:3600];(node["place"]({{geocodeBbox:Chennai}})["name:ta"!~"."]({{geocodeBbox:Chennai}}););out meta;>;out meta qt;'
//var query = '[out:json][timeout:3600];(area[name="Andhra Pradesh"];node["place"](area)["name:te"!~"."](area););out meta;>;out meta qt;'
//var query = '[out:json][timeout:3600];(node["place"]["name:ta"!~"."](5.916667,79.516667,9.833333,81.866667););out meta;>;out meta qt;';
//var query = '[out:json][timeout:3600];(node["place"~"town"]["name:ta"!~"."]({{geocodeBbox:India}}););out meta;>;out meta qt;';
//area[name="Troisdorf"];way(area)[highway][name];out;
//var query = '[out:json][timeout:3600];([highway]["name:ta"!~"."]({{geocodeBbox:Chennai}}););out meta;>;out meta qt;';
//var query = '[out:json][timeout:3600];({{geocodeArea:India}}->.searchArea;node["place"~"city"]["name:te"!~"."](area.searchArea););out meta;'
var jsonresponse = QueryOverpass(query);
ParseReponsePopulateSheets(jsonresponse);
}
function ParseReponsePopulateSheets(jsonresponse) {
var doc = SpreadsheetApp.getActiveSpreadsheet();
if (doc.getSheetByName('OverpassOutput') != null)
doc.deleteSheet(doc.getSheetByName('OverpassOutput'));
var sheet = doc.insertSheet('OverpassOutput');
var OSMdataAll = JSON.parse(jsonresponse.getContentText());
var data = OSMdataAll.elements;
var OSMdata = [];
var count = 0;
var OSMNodedata = [];
sheet.getRange(2,1,1,7).setValues([[["Node ID"],["Name"],["Lat"],["Lon"],["Type"],["Google Translate"],["Wikidata Translate"]]]);
sheet.getRange(2,1,1,7).setFontFamily("Arial Bold");
for(element in OSMdataAll.elements) {
OSMdata = [];
OSMNodedata['id'] = "=HYPERLINK(CONCATENATE(\"www.openstreetmap.org/node/\"," + data[element]['id'] + ")," + data[element]['id'] + ")"
OSMNodedata['name'] = data[element]['tags']['name'];
OSMNodedata['lat'] = data[element]['lat'];
OSMNodedata['lon'] = data[element]['lon'];
OSMNodedata['place'] = data[element]['tags']['place'];
OSMNodedata['googx'] = "=GOOGLETRANSLATE(\"" + OSMNodedata['name'] + "\",\"en\",\"ta\")";
//OSMNodedata['wikix'] = QueryWikidata(OSMNodedata['name']);
OSMNodedata['wikix'] = "=INDEX(WIKITRANSLATE(CONCATENATE(\"en:" + OSMNodedata['name'] + "\"),\"ta\",true),1,1)"
//TODO Add xliterate option
OSMdata.push([OSMNodedata['id'],OSMNodedata['name'],OSMNodedata['lat'],OSMNodedata['lon'],OSMNodedata['place'],OSMNodedata['googx'],OSMNodedata['wikix']]);
count++;
sheet.getRange(2+count,1,1,7).setValues(OSMdata);
}
}
function OverpassQueryBuilder() {
//Generate Overpass Queries using common query template and defined inputs?
/*
* City / Area
* Target Language
* Untranslated / Review / All
* Node / Relation / Ways / POIs
* Filter
*/
}
function GetOverpassQueryFromUser() {
var ui = SpreadsheetApp.getUi();
var response = ui.prompt('Overpass Query', 'Input OverpassQuery with json output', ui.ButtonSet.OK_CANCEL);
if (response.getSelectedButton() == ui.Button.OK) {
query = response.getResponseText();
Logger.log(query);
}
return query;
}
function QueryOverpass(query) {
//Logger.clear();
var url = 'http://overpass.osm.rambler.ru/cgi//interpreter';
var options = {
'method': 'post',
'payload': query
//'muteHttpExceptions': false
};
var jsonresponse = UrlFetchApp.fetch(url, options);
Logger.log(jsonresponse);
Logger.log('End of QueryOverpass');
return jsonresponse;
}
function PopulateWikidataColumn() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var places = doc.getActiveSheet().getRange(1693,2,600,1);
Logger.clear();
var wikidataplaces = [];
for (place in places.getValues()) {
wikidataplaces.push([QueryWikidata(places.getValues()[place])]);
}
doc.getActiveSheet().getRange(1693,7,wikidataplaces.length,1).setValues(wikidataplaces);
}
/*function QueryWikidata(query) {
var HEADERS = { headers: {'X-User-Agent': 'OSM Translation using Google Spreadsheets'} };
var url = 'https://www.wikidata.org/w/api.php?action=wbgetentities&sites=enwiki&titles=' + query + '&languages=ta&props=labels&format=json';
var jsonresponse = UrlFetchApp.fetch(url, HEADERS);
var data = JSON.parse(jsonresponse.getContentText());
for (entity in data.entities) {
if(data.entities[entity].hasOwnProperty('labels')) {
if (data.entities[entity].labels.hasOwnProperty('ta')) {
return data.entities[entity].labels.ta.value;
}
}
}
return "";
}*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.