Executing ST_WITHIN using stored procedure in documentdb

Question

I am trying to get a count for my location inside a polygon. Here is my stored proc:

function count(poly) {
var collection = getContext().getCollection();
var query = {query: 'Select f.id from f WHERE ST_WITHIN(f.location, @poly)',
            parameters: [{name: '@poly', value: poly}]};

var isAccepted = collection.queryDocuments(
    collection.getSelfLink(),
    query,
    function (err, docs, options) {
        if (err) throw err;
        if (!docs || !docs.length) getContext().getResponse().setBody('no docs found');
        else getContext().getResponse().setBody(docs.length);
    });

if (!isAccepted) throw new Error('The query was not accepted by the server.');}

When I execute the same query in query explorer, I get the results, but through stored procedures, its returning "no docs found". It is returning the results for simpler queries but for that too, the max count returned is always 100. Not sure what am I doing wrong.

Thanks in advance.

P.S.: I tried using ST_DISTANCE for these coordinates. It did returned count as 100(max value), but is not at all working for ST_WITHIN.

Edit: It was not working.So I tried this as described in the official example for counting the results. And voila!! It worked. So I moved to the next step to count all the locations in all the polygons I had as locally, there were too many round trips to get the count for each polygon. But calling the same function from a loop does'nt return anything. I have already tested each query of the array in documentdb studio and it does return results. Please help!!! The code for new procedure is:

function countABC(filterQueryArray) {

var results = [];
for (i = 0; i < filterQueryArray.length; i++) {
    countnew(filterQueryArray[i].QueryString, "");

}
getContext().getResponse().setBody(results);

function countnew(filterQuery, continuationToken) {
    var collection = getContext().getCollection();
    var maxResult = 50000;
    var result = 0;

    tryQuery(continuationToken);
    function tryQuery(nextContinuationToken) {
        var responseOptions = {
            continuation: nextContinuationToken,
            pageSize: maxResult
        };

        if (result >= maxResult || !query(responseOptions)) {
            setBody(nextContinuationToken);
        }
    }

    function query(responseOptions) {
        return (filterQuery && filterQuery.length) ?
            collection.queryDocuments(collection.getSelfLink(), filterQuery, responseOptions, onReadDocuments) :
            collection.readDocuments(collection.getSelfLink(), responseOptions, onReadDocuments);
    }

    function onReadDocuments(err, docFeed, responseOptions) {
        if (err) {
            throw 'Error while reading document: ' + err;
        }

        result += docFeed.length;

        if (responseOptions.continuation) {
            tryQuery(responseOptions.continuation);
        } else {
            setBody(null);
        }
    }


    function setBody(continuationToken) {
        var body = {
            count: result,
            continuationToken: continuationToken
        };            
        results.push(body);
    }
}

}

{'coordinates':[[[-72.56244522109489,42.195626800619728],[-72.5503062656826,42.195626160743714],[-72.550316627691416,42.186632945589423],[-72.562453856102479,42.186633585263621],[-72.56244522109489,42.195626800619728]]],'type':'Polygon'} — Leo
Have you tried it without the parameters abstraction? I'm wondering if it's an escaping issue with the @poly substitution? — Larry Maccherone
Yes. I tried with full query as well. For ST_Distance, the same parameter is working. — Leo
Added to the question. I am not able to understand why no count is being set in the body which is being pushed to the results array — Leo

Michael Koltachev Michael Koltachev · Accepted Answer · 2016-10-27T17:47:03

With new sproc, it's not helpful to set result after the loop because at that time no queries are executed (results array will be empty). The idea is that all CRUD/query calls are queued and are executed after the script that queued them is finished (in this case main script).

Setting result/body needs to be done from callback. This is partially done already, but there is an issue that for every call of countnew, the "result" variable is reset to 0. Essentially, "var result = 0" needs to be done in main script.

Also, it's not recommended to use loops like the "for" loop when it calls CRUD/queries in a loop without waiting for previous CRUD/query to finish (due to async nature), otherwise checking for isAccepted is not reliable. What's recommended it to serialize this loop, something like this:

var result = 0;
step();

function step() {
  if (filterQueryArray.length == 0) setBody(null);
  else {
    var query = filterQueryArray.shift();
  
    // process current query. In the end (from callback), call step() again.
  }
}

Does this make sense?

Executing ST_WITHIN using stored procedure in documentdb

1 Answers