CODEWITHSUNDEEP
azure-cosmosdb
bgs264
bgs264

Reputation: 4642

DocumentDB - How do I return the distance in the SELECT part of the query?

I would like to return the distance between my search co-ordinates and the field I'm searching for.

As an example, you can use this "Query Playground": https://www.documentdb.com/sql/demo#geospatial

They use the following example query:

-- Find all volcanoes of type Stratovolcano
-- (http://www.sciencedaily.com/terms/stratovolcano.htm) 
-- that are within 100 km of Redmond, WA. 
SELECT *
FROM volcanoes v
WHERE ST_DISTANCE(v.Location, { 
    "type": "Point", 
    "coordinates": [-122.19, 47.36] 
    }) < 100 * 1000
AND v.Type = "Stratovolcano"
AND v["Last Known Eruption"] = "Last known eruption from 1800-1899, inclusive"

And they get the following results:

{
  "Volcano Name": "Rainier",
  "Country": "United States",
  "Region": "US-Washington",
  "Location": {
    "type": "Point",
    "coordinates": [
      -121.758,
      46.87
    ]
  },
  "Elevation": 4392,
  "Type": "Stratovolcano",
  "Status": "Dendrochronology",
  "Last Known Eruption": "Last known eruption from 1800-1899, inclusive",
  "id": "33eff74b-e331-bca5-bf32-f8ece733465a",
  "_rid": "FX8tANMM6QEeBAAAAAAAAA==",
  "_ts": 1438994836,
  "_self": "dbs/FX8tAA==/colls/FX8tANMM6QE=/docs/FX8tANMM6QEeBAAAAAAAAA==/",
  "_etag": "\"00008304-0000-0000-0000-55c551940000\"",
  "_attachments": "attachments/"
}

Let's say that I want to bring the distance back in metres between the volcano (at [-121.758, 46.87]) and the search co-ordinates [-122.19, 47.36]

The T-SQL developer in me says I can just take the whole ST_DISTANCE bit from the WHERE clause, and include it along with the SELECT like this:

-- Find all volcanoes of type Stratovolcano
-- (http://www.sciencedaily.com/terms/stratovolcano.htm) 
-- that are within 100 km of Redmond, WA. 
SELECT *, ST_DISTANCE(v.Location, { 
    "type": "Point", 
    "coordinates": [-122.19, 47.36] 
    })
FROM volcanoes v
WHERE ST_DISTANCE(v.Location, { 
    "type": "Point", 
    "coordinates": [-122.19, 47.36] 
    }) < 100 * 1000
AND v.Type = "Stratovolcano"
AND v["Last Known Eruption"] = "Last known eruption from 1800-1899, inclusive"

However that doesn't work, it just gives me a syntax error:

{
  "errors": [
    {
      "severity": "Error",
      "location": {
        "start": 153,
        "end": 154
      },
      "code": "SC1001",
      "message": "Syntax error, incorrect syntax near ','."
    }
  ]
}

I've tried a range of things, like v.*, aliasing the result of ST_DISTANCE with an AS, but I'm not getting anywhere and I'm not finding what I need in Google.

So what do I need to do? For me, querying within a certain distance is crucial, but it's of limited use if I then have to re-calculate all those distances client-side.

Upvotes: 0

Views: 475

Answers (1)

bgs264
bgs264

Reputation: 4642

The query must use SELECT v, ST_DISTANCE(...) in place of SELECT *, ST_DISTANCE(...). Similar to ANSI-SQL, a SELECT clause in DocumentDB can either include a list of values or use *, but not both at the same time.

Full query: 

-- Find all volcanoes of type Stratovolcano
-- (http://www.sciencedaily.com/terms/stratovolcano.htm) 
-- that are within 100 km of Redmond, WA. 
SELECT v, ST_DISTANCE(v.Location, { 
    "type": "Point", 
    "coordinates": [-122.19, 47.36] 
    }) AS DistanceMetres
FROM volcanoes v
WHERE ST_DISTANCE(v.Location, { 
    "type": "Point", 
    "coordinates": [-122.19, 47.36] 
    }) < 100 * 1000
AND v.Type = "Stratovolcano"
AND v["Last Known Eruption"] = "Last known eruption from 1800-1899, inclusive"

Upvotes: 2

Related Questions