3

I can't seem to get a bounding box query working with MongoDB, that actually uses the spatial index.

Here's my indexes:

[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "config.Listings"
    },
    {
        "v" : 2,
        "key" : {
            "LatLong" : "2dsphere"
        },
        "name" : "LatLong_2dsphere",
        "ns" : "config.Listings",
        "2dsphereIndexVersion" : 3
    },
    {
        "v" : 2,
        "key" : {
            "ListingId" : 1
        },
        "name" : "ListingId",
        "ns" : "config.Listings"
    },
    {
        "v" : 2,
        "key" : {
            "Category" : 1,
            "Status" : 1
        },
        "name" : "Category_and_Status",
        "ns" : "config.Listings"
    },
    {
        "v" : 2,
        "key" : {
            "Category" : 1,
            "Status" : 1,
            "Suburb" : 1
        },
        "name" : "Basic_Suburb_Search",
        "ns" : "config.Listings"
    },
    {
        "v" : 2,
        "key" : {
            "LatLong.Coordinates" : "2d"
        },
        "name" : "LatLong.Coordinates_2d",
        "ns" : "config.Listings"
    }
]

I initially just had the 2dsphere index, but then i saw this answer saying box queries only work with 2d indexes.

When i try the box query, it doesn't seem to use either index:

db.Listings.find( {
   LatLong: { $geoWithin: { $box:  [ [ 144.9941034, -37.8220633 ], [ 145.0551353, -37.79494 ] ] } }
} ).explain()

It does return results, but does a COLLSCAN and is very slow.

I've also tried this:

db.Listings.find( {
   "LatLong.coordinates": { $geoWithin: { $box:  [ [ 144.9941034, -37.8220633 ], [ 145.0551353, -37.79494 ] ] } }
} )

Same result - returns data but does a COLLSCAN.

LatLong is a GeoJSON object. Here's an example document:

{
    "_id" : ObjectId("5bd64f2274cb40f172a328fb"),
    "LatLong" : {
        "type" : "Point",
        "coordinates" : [ 
            151.2504, 
            -33.96767
        ]
    },
    // other props removed for simplicity
}

Can someone tell me what i'm doing wrong?

RPM1984
  • 72,246
  • 58
  • 225
  • 350
  • Please replace images with code snippets. The object in the last image is out of the box boundaries. – Alex Blex Oct 29 '18 at 10:03
  • @AlexBlex does that help? Otherwise not sure what code snippet you're asking for? – RPM1984 Oct 30 '18 at 02:39
  • Indexes, queries and a document. I can try to reproduce it locally but am too lazy to type in everything. I am almost sure it is a matter of case but wanted to confirm it before answering. – Alex Blex Oct 30 '18 at 11:21
  • @AlexBlex updated, and i found the problem. You're right, it was the casing on the index (was `Coordinates`, should have been `coordinates`). Why does MongoDB even allow that then? Like it _accepts_ the index with the wrong case, but rejects queries using them because of the case? Anyway - problem solved - chuck up an answer if you like, and i'll give you some points :) – RPM1984 Oct 30 '18 at 23:58
  • 1
    Mongodb is schemaless. The case is wrong only in context of your app. Other apps can have documents with `Coordinates`, `coordinates` or even both in the same document. You don't need documents with particular field to create an index - it allows to define indexes on empty collections and add documents later. It doesn't reject the query either - it scans the index, find 0 documents in there and honestly returns the result. Basically it is the cost of flexibility. All these constraints and checkups should be implemented on application level when it makes sense to particular app. – Alex Blex Oct 31 '18 at 09:31

0 Answers0