使用mongodb获取带有$box的文档，并为每个具有特定坐标点的记录添加距离字段

Question

案例

我已经用2dsphere索引将位置坐标存储在MongoDB集合中。我想得到包括在地图上特定边界内的记录。我有一个用经纬度固定的坐标点。我在MongoDB聚合中使用了$geoWithin和$box的方法。我能够正确地获取数据。

问题

我需要在每个记录中添加一个固定纬度和经度的距离字段。

{
"$match": {
"location": {
"$geoWithin": {
"$box": [
[
0.024719919885622943,
51.54643953472475
],
[
-0.1589577534542408,
51.47239969138267
]
]
}
}
}
}

现在我有了像[-0.0649729793707321, 51.50160291888072]这样的坐标阵列

我需要每个记录与上述点的距离。如何使用MongoDB聚合找到这个距离字段？

数据库记录的样本文档

{
"_id" : ObjectId("5e342ac2e0dcfa65273751ea"),
"location" : {
"type" : "Point",
"coordinates" : [ 
-0.123507, 
51.5083228
]
},
"status" : 1,
"price" : {
"value" : 27,
"type" : "day",
"base" : 3.38
},
"name" : "Initial Space with Wormup",
"venuePolicy" : "Venue Policies",
"updatedAt" : ISODate("2020-02-20T12:28:16.024Z"),
"createdAt" : ISODate("2020-01-31T13:25:22.600Z"),
"dimension" : {
"width" : 100
},
"description" : "<p>Hi,&nbsp;</p><p>please find attached document for testing purpose",
}

Answer 1

这个应该有效：

db.collection.aggregate([
{
"$match": {
"location": {
"$geoWithin": {
"$box": [
[0.024719919885622943, 51.54643953472475],
[-0.1589577534542408, 51.47239969138267]
]
}
}
}
},
{ $set: { lon: -0.0649729793707321 } },
{ $set: { lat: 51.50160291888072 } },
{
$set: {
distance: {
$let: {
vars: {
dlon: { $degreesToRadians: { $subtract: [{ $arrayElemAt: ["$location.coordinates", 0] }, "$lon"] } },
dlat: { $degreesToRadians: { $subtract: [{ $arrayElemAt: ["$location.coordinates", 1] }, "$lat"] } },
lat1: { $degreesToRadians: { $arrayElemAt: ["$location.coordinates", 1] } },
lat2: { $degreesToRadians: "$lat" }
},
in: {
// Haversine formula: sin²(dLat / 2) + sin²(dLon / 2) * cos(lat1) * cos(lat2);
$add: [
{ $pow: [{ $sin: { $divide: ["$$dlat", 2] } }, 2] },
{ $multiply: [{ $pow: [{ $sin: { $divide: ["$$dlon", 2] } }, 2] }, { $cos: "$$lat1" }, { $cos: "$$lat2" }] }
]
}
}
}
}
},
{
$set: {
distance: {
// Distance in Meters given by "6372.8 * 1000"
$multiply: [6372.8, 1000, 2, { $asin: { $sqrt: "$distance" } }]
}
}
},
])

请注意，$box仅用于传统坐标对。对于正确的查询(即正确使用2dsphere索引(，您应该使用$geometry:

db.collection.createIndex({ location: "2dsphere" })
db.collection.find(
{
"location": {
"$geoWithin": {
"$geometry": {
type: "Polygon",
coordinates: [[
[-0.1589577534542408, 51.47239969138267],
[-0.1589577534542408, 51.54643953472475],
[0.024719919885622943, 51.54643953472475],
[0.024719919885622943, 51.47239969138267],
[-0.1589577534542408, 51.47239969138267]
]]
}
}
}
}
).explain().queryPlanner.winningPlan.inputStage.indexName
--> location_2dsphere // which means index is used

db.collection.find(
{
"location": {
"$geoWithin": {
"$box": [
[0.024719919885622943, 51.54643953472475],
[-0.1589577534542408, 51.47239969138267]
]
}
}
}
).explain().queryPlanner.winningPlan.stage
--> COLLSCAN // which means full collection scan

或正确使用传统坐标：

db.collection.createIndex({ "location.coordinates": "2d" })
db.collection.find(
{
"location.coordinates": { // <- note this difference
"$geoWithin": {
"$box": [
[0.024719919885622943, 51.54643953472475],
[-0.1589577534542408, 51.47239969138267]
]
}
}
}
).explain().queryPlanner.winningPlan.inputStage.indexName
--> location.coordinates_2d