CODEWITHSUNDEEP
azure-cosmosdb
Wassink R
Wassink R

Reputation: 5

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

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.

Upvotes: 0

Views: 523

Answers (1)

Larry Maccherone
Larry Maccherone

Reputation: 9523

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

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

Upvotes: 0

Related Questions