MongoDB, How to query subdocuments close to specific location?

Question

I have MongoDb database with collection users containing documents structured as below:

{
firstName: "firstname",
"phone": "123456",
"places":[

{
            "name" : "somename",
            "address" : "Woollahra, New South Wales, Australia",
            "loc" : {
                "type" : "Point",
                "coordinates" : [
                    151.23721839999996,
                    -33.8884085
                ]
            },
            "url" : "ttttt2",
            "registeredOn" : ISODate("2015-06-17T20:14:10.986Z"),
            "id" : ObjectId("5517632982ae879883216fe2b2")
        },
{
            "name" : "somename",
            "address" : "something else, Australia",
            "loc" : {
                "type" : "Point",
                "coordinates" : [
                    151.23721839999996,
                    -33.8884085
                ]
            },
            "url" : "ttttt2",
            "registeredOn" : ISODate("2015-06-17T20:14:10.986Z"),
            "id" : ObjectId("5517632982ae879883216fe2b2")
        }
]}

Each document has bunch of properties e.g firstName, phone etc. It also has places property that is an array of subdocuments.

Each subdocument has loc property that stores coordinates of the "place" subdocument describes. I basically need to pull out places objects in order of distance from specific location I pass to query.

I cannot figure out how can I run collection.find $near queries to get list of places based on its location. I figured first of all I need to set up 2dsphere index on places.loc and tried:

db.users.createIndex({"places.loc":"2dsphere"})

But I'm getting "errmsg" : "exception: Can't extract geo keys.

Is this even possible with structure I already have in database? If so how would I do it? My documents sample is below, thank you in advance for any help. BTW I'm using NodeJs with native mongoDB driver.

EDIT:

I tried:

db.users.createIndex({"loc":"2dsphere"})

and this result in:

{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 3,
    "numIndexesAfter" : 3,
    "note" : "all indexes already exist",
    "ok" : 1
}

and that gave me hope but then when I try to run query:

db.users.find({
            'places.loc': {
                $near: {
                    $geometry: {
                        type: "Point",
                        coordinates: [-73.965355, 40.782865]
                    },
                    $maxDistance: 20000
                }
            }
        })

I get this:

Error: error: {
    "$err" : "Unable to execute query: error processing query: ns=marankings.users limit=0 skip=0\nTree: GEONEAR  field=places.loc maxdist=20000 isNearSphere=0\nSort: {}\nProj: {}\n planner returned error: unable to find index for $geoNear query",
    "code" : 17007
}

So you tried to create the 2dsphere index. What then? Did it work? If no, what error do you get? If yes, what prevents you from proceeding? — Philipp, Jun 18 '15 at 10:46
Could this be a duplicate of http://stackoverflow.com/questions/23923909/mongodb-2dsphere-index-fails-malformed-geometry ? — Philipp, Jun 18 '15 at 10:51
[This](http://stackoverflow.com/questions/25569901/mongodb-geojson-cant-extract-geo-keys-from-object-malformed-geometry-when-i) might be helpful to you. — Vishwas, Jun 18 '15 at 10:57
@Philipp I doubt it. But there is one thing that rings true despite current errors. While you can get a document match ( and even the matching sub-documents ) from locations within an array, the one thing you cannot get is a "distance" from **all** matches found within the array. Any distance projected ( see [**`$geoNear`**](http://docs.mongodb.org/manual/reference/operator/aggregation/geoNear/ ) aggregation ) can only be the "first" or "nearest" match from the array entries in the document. If you want something else, you need to flatten this. Or calculate externally. — , Jun 18 '15 at 11:00
The other note for those leaving notes for "malformed geometry" is that I try on the above sample ( not a great one since both points are the same and the ObjectId values are not valid ) but both indexing and query works. — , Jun 18 '15 at 11:02
hmm.. Based on one of the other answers from other question I tried to index on "loc" directly and updated my answer with results. Thanks for help so far everyone — spirytus, Jun 18 '15 at 11:08
It's `db.users.ensureIndex({"places.loc": "2dsphere"})` and make sure you remove other created 2d indexes. But as I already commented, using an array is likely not going to give you the results you want anyway since you won't get the distance for all matching array entries near the query point. — , Jun 18 '15 at 11:13

score 3 · Accepted Answer · answered Jun 18 '15 at 11:50

As stated, the closest you can get to this with your current structure is using $geoNear which is an aggregation framework operator. This has the necessary projection needs required to resolve the "match" from the sub-documents.

But first a reworking of your sample without the errors:

{
    "firstName": "firstname",
    "phone": "123456",
    "places":[
        {
            "name" : "somename",
            "address" : "Woollahra, New South Wales, Australia",
            "loc" : {
                "type" : "Point",
                "coordinates" : [
                    151.23721839999996,
                    -33.8884085
                ]
            },
            "url" : "ttttt2",
            "registeredOn" : ISODate("2015-06-17T20:14:10.986Z"),
       },
       {
            "name" : "somename",
            "address" : "something else, Australia",
            "loc" : {
                "type" : "Point",
                "coordinates" : [
                    151.23721839999996,
                    -36.8884085
                ]
            },
            "url" : "ttttt2",
            "registeredOn" : ISODate("2015-06-17T20:14:10.986Z"),
        }
    ]
 }

I'll create that in a collection called "places" and then places the index on that collection like so:

db.places.ensureIndex({ "places.loc": "2dsphere" })

Now let's try a basic .find() operation:

db.places.find({
    "places.loc": {
        "$near": {
            "$geometry": {
                "type": "Point",
                "coordinates": [
                    151.23721839999996,
                    -33.8884085
                ]
            }
        }
    }
})

That will match and return your "whole document" but does not tell you anything about the array element matched, or the distance from the queried point.

Lets see the operation using $geoNear now:

db.places.aggregate([
    { "$geoNear": {
        "near": {
            "type": "Point",
            "coordinates": [
                151.23721839999996,
                -33.8884085
            ]
        },
        "distanceField": "dist",
        "includeLocs": "locs",
        "spherical": true
    }}
])

Which at this stage gives us the result:

{
    "_id" : ObjectId("558299b781483914adf5e423"),
    "firstName" : "firstname",
    "phone" : "123456",
    "places" : [
            {
                    "name" : "somename",
                    "address" : "Woollahra, New South Wales, Australia",
                    "loc" : {
                            "type" : "Point",
                            "coordinates" : [
                                    151.23721839999996,
                                    -33.8884085
                            ]
                    },
                    "url" : "ttttt2",
                    "registeredOn" : ISODate("2015-06-17T20:14:10.986Z")
            },
            {
                    "name" : "somename",
                    "address" : "something else, Australia",
                    "loc" : {
                            "type" : "Point",
                            "coordinates" : [
                                    151.23721839999996,
                                    -36.8884085
                            ]
                    },
                    "url" : "ttttt2",
                    "registeredOn" : ISODate("2015-06-17T20:14:10.986Z")
            }
    ],
    "dist" : 0,
    "locs" : {
            "type" : "Point",
            "coordinates" : [
                    151.23721839999996,
                    -33.8884085
            ]
    }
}

Note the extra fields in there for "dist" and "locs". These are respectively the "distance" from the queried point for the match and the "location" data that was matched from the sub-document paired to that particular distance.

The document is still the same, but since this is the aggregation framework you can take that further:

db.places.aggregate([
    { "$geoNear": {
        "near": {
            "type": "Point",
            "coordinates": [
                151.23721839999996,
                -33.8884085
            ]
        },
        "distanceField": "dist",
        "includeLocs": "locs",
        "spherical": true
    }},
    { "$redact": {
        "$cond": {
            "if": { "$eq": [ 
                 { "$ifNull": [ "$loc", "$$ROOT.locs" ] },
                 "$$ROOT.locs"
             ]},
             "then": "$$DESCEND",
             "else": "$$PRUNE"
        }
    }}
])

So $redact is used as a method to "filter" the array contents to only the "entries" that match the found location:

{
    "_id" : ObjectId("558299b781483914adf5e423"),
    "firstName" : "firstname",
    "phone" : "123456",
    "places" : [
            {
                    "name" : "somename",
                    "address" : "Woollahra, New South Wales, Australia",
                    "loc" : {
                            "type" : "Point",
                            "coordinates" : [
                                    151.23721839999996,
                                    -33.8884085
                            ]
                    },
                    "url" : "ttttt2",
                    "registeredOn" : ISODate("2015-06-17T20:14:10.986Z")
            }
    ],
    "dist" : 0,
    "locs" : {
            "type" : "Point",
            "coordinates" : [
                    151.23721839999996,
                    -33.8884085
            ]
    }
}

Of course as I already said, there can be "only one" match in the array per document because that is all $geoNear will return.

For anything else you need to "flatten" the document by placing your sub-documents in their own collection also containing your "outer" document properties where you need them, or do some "joining" logic with additional queries for that information.

Also note that only $geoNear and the geoNear commands will return a projected "distance" value into the document. The former gives you control over the field name and the latter is arbitrary.

I ended up flattening places into their own collection. Took a bit of refactoring but I got what I need. Thank you for your help — spirytus, Jun 23 '15 at 20:18

MongoDB, How to query subdocuments close to specific location?

1 Answers1