DocumentDB: ST_DISTANCE function does not work with '<' (smaller than) sign

0
votes

I have a perfectly working query that looks as follows:

SELECT p.id FROM place p WHERE ST_DISTANCE(p.geometry, {'type': 'Point', 'coordinates':[52.0826443333333, 5.11771783333333]} ) > 6000

It returns a list of id's of documents that are more than 6000 m from the geospatial point. Everything seems fine. However, if I turn around the '>' (greater than) sign to '<' (smaller than), it does not give any result. Interestingly, it does return false/true statements if I put the WHERE clause in a SELECT statement, as follows:

SELECT ST_DISTANCE(p.geometry, {'type': 'Point', 'coordinates':[52.0826443333333, 5.11771783333333]}) < 6000 AS result FROM place p

It generates both true and false statements as expected. So the evaluation seems to work, but it does not return any output. Currently, I just use this latter work around, and also select the computed distances. But now I have to compute the points that are within a certain distance somewhere else (like on the client side or in a stored procedure).

UPDATE I tested with a specified index policy (thanks to this example):

'indexingPolicy': {'includedPaths': [{'path': '/"geometry"/?', 'indexes': [ {'kind': 'Spatial', 'dataType': 'LineString'}]}, {'path': '/'}]}

And that solved the problem. I still think it is odd that the spatial function did work on 'greater than' and not on 'smaller than', but I think it is solved with this.

1
azure-cosmosdb
Do you have a Spatial index specified for the geometry field? If you do then please post an example of the geometry field. - Larry Maccherone
No, I did not specify any kind of index. I am actually not familiar with the index options, but as far as I can see it creates an index on all attributes by default, and should automatically recognize spatial data? Since I am about to do some more testing, I will also try to vary a bit with the kind of indexing options. If anything changes I will update this post. However, since the 'greater than' sign does seem to work, I can hardly imagine it will have anything to do with the way the data is indexed. - Wassink R
update: Updated post - Wassink R
Great! I added an answer specifying that so it shows as answered and accepted (once you accept it). - Larry Maccherone

1 Answers

0
votes

You should specify a Spatial index on that field like this:

'indexingPolicy': {
    'includedPaths': [
        {
            'path': '/"geometry"/?', 
            'indexes': [ 
               {'kind': 'Spatial', 'dataType': 'LineString'}
            ]
        }, 
        {'path': '/'}
    ]
}