[SERVER-12618] $near query not working with compound 2dsphere index Created: 05/Feb/14  Updated: 10/Dec/14  Resolved: 05/Feb/14

Status: Closed
Project: Core Server
Component/s: Geo
Affects Version/s: 2.5.5
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Stuart Hall Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Participants:

 Description   

Running the following query:

db.routeResult.find({ "customerId" : "24585", "assetId" : "451219", "coordinate" : { "$near" : [-0.0843918, 51.5136045], "maxDistance" : 0.0025232195545501768 } });

...on a collection with the following shape of documents:

{
  "_id" : ObjectId("52f0f3e295e914f87d13198d"),
  "customerId" : "23138",
  "assetId" : "426598",
  "coordinate" : {
    "longitude" : -1.4841666,
    "latitude" : 52.938473
  }

...with the following geospatial index:

{ "customerId" : 1, "assetId" : 1, "coordinate" : "2dsphere"}

....results in the following error being raised in the mongod logs:

2014-02-05T11:50:22.473+0000 [conn2] assertion 17007 Unable to execute query: error processing query: ns=stuarttest.routeResult limit=50 skip=0
Tree: $and
    assetId == "451219" First: notFirst: 0 1 4 full path: assetId
    customerId == "24585" First: 0 1 2 3 4 notFirst: full path: customerId
    GEONEAR  field=coordinate maxdist=1.79769e+308 isNearSphere=0 First: notFirst: full path: coordinate
Sort: {}
Proj: {}
 planner returned error: unable to find index for $geoNear query ns:stuarttest.routeResult query:{ customerId: "24585", assetId: "451219", coordinate:
 { $near: [ -0.0843918, 51.5136045 ], maxDistance: 0.002523219554550177 } }
2014-02-05T11:50:22.477+0000 [conn2]  ntoskip:0 ntoreturn:50

Can you confirm why this might be happening?



 Comments   
Comment by Stuart Hall [ 05/Feb/14 ]

Thanks Hari. That was it - $nearSphere works fine

You can close this ticket. I'll continue with our testing....

Comment by hari.khalsa@10gen.com [ 05/Feb/14 ]

The documentation is not entirely clear here, but I think this does work as designed.

$near: [0,0]

is a query that wants things near a point but on a flat surface. This can use a 2d index. Because you're asking for a flat $near query here so we do not use 2dsphere indices as they cannot be flat.

$nearSphere: [0,0]

is a query that wants things near a point on a sphere. This can use 2d or a 2dsphere index.

$near: {$geometry: blahblah}

is a query that wants things near a point, but the point implies a spherical geometry, so we try to use the 2dsphere index.

And yes, if you have a compound 2dsphere index you must have predicates over the fields that prefix the 2dsphere-indexed field or we cannot use that index for a near search

Comment by Stuart Hall [ 05/Feb/14 ]

For your reference, here is a sample complete document:

{
  "_id" : ObjectId("52f0f3e295e914f87d13198d"),
  "customerId" : "23138",
  "shardDateTime" : "2014_02",
  "assetId" : "426598",
  "_class" : "com.masternaut.platform.domain.routeResult.RouteResult",
  "eventDateTime" : ISODate("2014-02-04T14:06:19Z"),
  "coordinate" : {
    "longitude" : -1.4841666,
    "latitude" : 52.938473
  },
  "numberOfGpsSatellites" : 11,
  "eventCategory" : 6,
  "eventCode" : 4,
  "eventStatus" : "behaviourHarshCornering",
  "personName" : "Unknown",
  "assetRegistration" : "Van 18 (FD11 XUF)",
  "assetHierarchyNodeId" : "5267de92a7f4ea0f5dacebd2",
  "assetSpeedLimitKph" : 48.0,
  "address" : {
    "formattedAddress" : "Unnamed road near Darley Park Drive, Derby, DE22 1",
    "road" : "Unnamed road near Darley Park Drive",
    "city" : "Derby",
    "postCode" : "DE22 1",
    "country" : "England",
    "roadSpeedKph" : 48.0,
    "roadType" : "BuiltUpArea"
  },
  "places" : [],
  "directionDegrees" : 70.0,
  "speedKph" : 16.0,
  "durationSeconds" : NumberLong(0),
  "distanceKms" : 0.0,
  "sequenceId" : NumberLong("1391522786721"),
  "isPrivate" : false,
  "journeyCompleted" : false,
  "createdTime" : ISODate("2014-02-04T14:06:26.767Z"),
  "lastModifiedTime" : ISODate("2014-02-04T14:06:26.767Z")
}

Comment by Stuart Hall [ 05/Feb/14 ]

Here you go:

> db.routeResult.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "stuarttest.routeResult",
                "name" : "_id_"
        },
        {
                "v" : 1,
                "key" : {
                        "shardDateTime" : 1,
                        "customerId" : 1,
                        "assetId" : 1
                },
                "ns" : "stuarttest.routeResult",
                "name" : "shardDateTime_1_customerId_1_assetId_1"
        },
        {
                "v" : 1,
                "key" : {
                        "customerId" : 1,
                        "eventDateTime" : 1,
                        "assetId" : 1
                },
                "ns" : "stuarttest.routeResult",
                "name" : "customerId_eventDateTime_assetId",
                "dropDups" : false,
                "sparse" : false
        },
        {
                "v" : 1,
                "key" : {
                        "customerId" : 1,
                        "assetId" : 1,
                        "shardDateTime" : 1,
                        "eventDateTime" : 1
                },
                "ns" : "stuarttest.routeResult",
                "name" : "customerId_assetId_shardDateTime_eventDateTime",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "customerId" : 1,
                        "personId" : 1,
                        "shardDateTime" : 1,
                        "eventDateTime" : 1
                },
                "ns" : "stuarttest.routeResult",
                "name" : "customerId_personId_shardDateTime_eventDateTime",
                "background" : true
        },
        {
                "v" : 1,
                "key" : {
                        "customerId" : 1,
                        "sequenceId" : 1
                },
                "ns" : "stuarttest.routeResult",
                "name" : "customerId_sequenceId",
                "background" : false
        },
        {
                "v" : 1,
                "key" : {
                        "customerId" : 1,
                        "assetId" : 1,
                        "coordinate" : "2dsphere"
                },
                "name" : "customerId_assetId_coordinate",
                "ns" : "stuarttest.routeResult"
        }
]

Comment by Adam Comerford [ 05/Feb/14 ]

Can you post the indexes you have in place on the stuarttest.routeResult collection for reference also?

Asking for the full definition of the indexes so we can try to reproduce, especially since the error looks to be related to an issue finding the geo index here.

Thanks,

Adam

Generated at Thu Feb 08 03:29:03 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.