NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

I would like to use Google Apps Scripts to import data from an Overpass Turbo request into a Google sheet. The QL request is something like this:

( node (user:"greenmtdave") (6.960464727056788,79.86112117767334,6.9797191061459305,79.88375902175903); way (user:"greenmtdave") (6.960464727056788,79.86112117767334,6.9797191061459305,79.88375902175903); relation (user:"greenmtdave") (6.960464727056788,79.86112117767334,6.9797191061459305,79.88375902175903); node._(changed:"2016-05-15T07:00:00Z","2016-06-6T07:01:00Z"); ); out count;

Note the use of the "out count" option, which does not seem to work if using the QL request in HTTP, so I presume I need to use XML format, but I don't know if the Google Apps Scripts class URLFetchApp supports this. I'm not much of a coder, so I can't figure this out.

asked 07 Jun '16, 03:17

greenmtdave's gravatar image

greenmtdave
1813412
accept rate: 0%

1

I believe you should fix your query before trying to automate it. node._(changed:"2016-05-15T07:00:00Z","2016-06-6T07:01:00Z"); does not seem right, as there are no nodes in the inputset ._. Right now, this last statement has no effect.

If you get a response in overpass turbo, you should also get the same response via any other tool using HTTP. Whether that tool is able to parse the XML respnse, is beyond the scope of overpass api.

(07 Jun '16, 09:39) mmd

Thanks for your reply. I've studied the Overpass API/Overpass QL wiki page for many hours now, and I can't figure this out. I want to get a count of all of the objects that a user has contributed within a bounding box during a certain time period. I've tried placing the change filter in various positions in the code and none seem to work. The Wiki says the change filter doesn't take an input dataset? How can that be? and the example given "node._(changed:...)" seems to indicate that the default dataset is the input dataset?

I am a newbie and I apologize for that. But the Wiki page only gives examples of single lines of code and only three complete examples of code, none of which show the usage of the change filter. A general Google search on this also comes up with nothing.

(08 Jun '16, 20:14) greenmtdave

This is the QL script that seems to work:

node(user:"greenmtdave")(6.72325,79.66323,7.95451,81.11206); node.(changed:"2016-05-22T00:00:00Z","2016-06-10T00:00:00Z"); (.; <;); out count;

Now have to figure out how to get Google Apps Script to run it.

(10 Jun '16, 22:40) greenmtdave

function startHere(){
  // input variables
  var OSMUserNameCol = "A";
  var totalObjectsCol = "B";
  var startRow = 2; 
  var lastRow = 3;

  var sheet = SpreadsheetApp.getActiveSheet();

  // Fetch the range of cells
  var dataRange1 = sheet.getRange(OSMUserNameCol + startRow + ":" + OSMUserNameCol + lastRow);
  var dataRange2 = sheet.getRange(totalObjectsCol + startRow + ":" + totalObjectsCol + lastRow);

  // Fetch values for each row in the Range.
  var data1 = dataRange1.getValues();
  var data2 = dataRange2.getValues();

  for (var i = 0; i < data1.length; ++i) {
    var row1 = data1[i];
    var row2 = data2[i];

    //Build API request parameters
    var rawXML = '<osm-script>'
       +'<query into="_" type="node"><user into="_" name="' + row1 + '" type="node" uid=""/>'
       +'<bbox-query e="81.11206" into="_" n="7.95451" s="6.72325" w="79.66323"/></query>'
       +'<query into="_" type="node"><item set="_"/><changed into="_" since="2016-05-23T00:00:00Z" until="2016-06-12T00:00:00Z"/>'
       +'</query><union into="_"><item set="_"/><recurse from="_" into="_" type="up"/></union>'
       +'<print e="" from="_" geometry="skeleton" limit="" mode="count" n="" order="id" s="" w=""/>'
       +'</osm-script>';
    var url = 'http://overpass-api.de/api/interpreter';
    var options =
     {
       "method" : "post",
       "payload" : rawXML
     };

   //Fetch count data
   var countData = UrlFetchApp.fetch(url, options).getContentText();
   var total = countData.substring(countData.indexOf("count total=") + 13, countData.indexOf("nodes") - 2);
   sheet.getRange(totalObjectsCol + (startRow + i)).setValue(total);
   SpreadsheetApp.flush();
  }
}
permanent link

answered 18 Jun '16, 00:26

greenmtdave's gravatar image

greenmtdave
1813412
accept rate: 0%

Just to wrap this up, I finally did figure out how to write a Google Apps Script to bring data from OSM into a spreadsheet. Could use some expert help to clean it up, do error checking, and make it more user friendly, so feel free to contact me at dwlitke@comcast.net if you are interested.

permanent link

answered 17 Jun '16, 21:13

greenmtdave's gravatar image

greenmtdave
1813412
accept rate: 0%

Feel free to post it here. Or a link to GitHub or some other platform where you might host your script.

(17 Jun '16, 21:29) scai ♦

How do I post it here without exceeding the character count? I tried using the Code Sample button, but that did some strange things to the formatting.

(17 Jun '16, 21:38) greenmtdave

just post in your answer text ("edit" link) and indent each code line by at least 4 spaces or 1 tab (a text editor helps). If you want to preview, use https://daringfireball.net/projects/markdown/dingus since our live preview here is insufficient. Or just past the code and someone else fill fix the formatting for you.

(18 Jun '16, 00:12) aseerel4c26 ♦
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×228
×1

question asked: 07 Jun '16, 03:17

question was seen: 3,097 times

last updated: 18 Jun '16, 00:26

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum