DocumentDB-如何在查询的SELECT部分返回距离

Question

我想返回我的搜索坐标和我正在搜索的字段之间的距离。

例如，您可以使用此"查询游乐场"：https://www.documentdb.com/sql/demo#geospatial

他们使用以下示例查询：

-- 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"

他们得到以下结果：

{
"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/"
}

假设我想把火山(在[-121.758, 46.87])和搜索坐标[-122.19, 47.36]之间的距离(以米为单位)调回来

我的T-SQL开发人员说，我可以从WHERE子句中提取整个ST_DISTANCE位，并将其与SELECT一起包含，如下所示：

-- 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"

然而，这不起作用，它只是给了我一个语法错误：

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

我尝试了一系列的方法，比如v.*，将ST_DISTANCE的结果与AS混淆，但我没有取得任何进展，也没有在谷歌中找到我需要的东西。

那么我需要做什么呢？对我来说，在一定距离内查询是至关重要的，但如果我必须在客户端重新计算所有这些距离，那么它的用处就有限了。

Answer 1

查询必须使用SELECT v, ST_DISTANCE(...)代替SELECT *, ST_DISTANCE(...)。与ANSI-SQL类似，DocumentDB中的SELECT子句可以包括一个值列表，也可以使用*，但不能同时使用这两者。

完整查询：

-- 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"