Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In MongoDB, how do I use a field in the document as input to a $geoWithin/$centerSphere expression?

I'm trying to write a MongoDB query that searches for documents within a radius centered on a specified location.

The query below works. It finds all documents that are within searching.radius radians of searching.coordinates.

However what I would like to do is add the current documents allowed_radius value to the searching.radius value, so that the allowed sphere is actually larger.

How can I phrase this query to make this possible?

Present Query:

collection.aggregate([
        {
            $project:{
                location: "$location",
                allowed_radius: "$allowed_radius"
            }
        },
        {
            $match: {
                $and:
                    [
                        { location: { $geoWithin: { $centerSphere: [ searching.coordinates, searching.radius ] }}},
                        {...},
                    ...]
                ...}
]);

What I am trying to do (pseudo-query):

collection.aggregate([
        {
            $project:{
                location: "$location",
                allowed_radius: "$allowed_radius"
            }
        },
        {
            $match: {
                $and:
                    [
                        { location: { $geoWithin: { $centerSphere: [ searching.coordinates, { $add: [searching.radius, $allowed_radius]} ] }}},
                        {...},
                    ...]
                ...}
]);
like image 551
CodyBugstein Avatar asked Feb 13 '18 05:02

CodyBugstein


1 Answers

I tried using $geoWithin / $centerSphere, but couldn't make it work this way.

Here is another way of doing so, using the $geoNear operator:

Given this input:

db.collection.insert({
  "airport": "LGW",
  "id": 1,
  "location": { type: "Point", coordinates: [-0.17818, 51.15609] },
  "allowed_radius": 100
})
db.collection.insert({
  "airport": "LGW",
  "id": 2,
  "location": { type: "Point", coordinates: [-0.17818, 51.15609] },
  "allowed_radius": 0
})
db.collection.insert({
  "airport": "ORY",
  "id": 3,
  "location": { type: "Point", coordinates: [2.35944, 48.72528] },
  "allowed_radius": 10
})

And this index (which is required for $geoNear):

db.collection.createIndex( { location : "2dsphere" } )

With searching.radius = 1000:

db.collection.aggregate([
  { $geoNear: {
    near: { "type" : "Point", "coordinates":  [7.215872, 43.658411] },
    distanceField: "distance",
    spherical: true,
    distanceMultiplier: 0.001
  }},
  { $addFields: { radius: { "$add": ["$allowed_radius", 1000] } } },
  { $addFields: { isIn: { "$subtract": ["$distance", "$radius" ] } } },
  { $match: { isIn: { "$lte": 0 } } }
])

would return documents with id 1 (distance=1002 <= radius=1000+100) and 3 (distance=676 <= radius=1000+10) and discard id 2 (distance=1002 > 1000+0).

The distanceMultiplier parameter is used to bring back units to km.

$geoNear must be the first stage of an aggregation (due to the usage of the index I think), but one of the parameters of $geoNear is a match query on other fields.

Even if it requires the geospacial index, you can add additional dimensions to the index.

$geoNear doesn't take the location field as an argument, because it requires the collection to have a geospacial index. Thus $geoNear implicitly uses as location field (whatever the name of the field) the one indexed.

Finally, I'm pretty sure the last stages can be simplified.

The $geoNear stage is only used to project the distance on each record:

{ "airport" : "ORY", "distance" : 676.5790971238937, "location" : { "type" : "Point", "coordinates" : [ 2.35944, 48.72528 ] }, "allowed_radius" : 10, "id" : 3 }
{ "airport" : "LGW", "distance" : 1002.3351814526812, "location" : { "type" : "Point", "coordinates" : [ -0.17818, 51.15609 ] }, "allowed_radius" : 100, "id" : 1 }
{ "airport" : "LGW", "distance" : 1002.3351814526812, "location" : { "type" : "Point", "coordinates" : [ -0.17818, 51.15609 ] }, "allowed_radius" : 0, "id" : 2 }

In fact, the geoNear operator requires the use of the distanceField argument, which is used to project the computed distance on each record for the next stages of the query. At the end of the aggregation, returned records look like:

{
  "airport" : "ORY",
  "location" : { "type" : "Point", "coordinates" : [ 2.35944, 48.72528 ] },
  "allowed_radius" : 10,
  "id" : 3,
  "distance" : 676.5790971238937,
  "radius" : 1010,
  "isIn" : -333.4209028761063
}

If necessary, you can remove fields produced by the query for the query (distance, radius, isIn) with a final $project stage. For instance: {"$project":{"distance":0}}

like image 114
Xavier Guihot Avatar answered Oct 15 '22 11:10

Xavier Guihot