Create Geo Index and query near

Question

I am using azure cosmos db how to create index and query nearest top 10 user if the document is like this, in C#:

{
  "_id" : "146138792054898475572",
  "email" : "abc@gmail.com",
  "firstName" : "abc",
  "lastName" : "abc",
  "loc" : {
    "lat" : 31.5200788,
    "lng" : 74.3236112
  },
  "gender" : "Male",
  "deviceId" : "YWg8crAjZLCrV",
  "createdDate" : ISODate("2017-06-11T11:35:41.601Z"),
  "updatedDate" : ISODate("2017-06-17T17:33:10.743Z")
}

Query:

db.User.find(
   {
      "loc":
        { $near :
           {
             $geometry: { type: "Point",  coordinates: [ 31.5200788, 74.3236112 ] },
             $minDistance: 1000,
             $maxDistance: 5000
           }
        }
   }
)

Answer 1

There are a couple of problems here that mostly relate to how you have stored the documents. MongoDB supports storing a coordinate point in one of three formats:

• Legacy coordinate pairs as array Where the data is listed in longitude and then latitude order

  [ 74.3236112, 31.5200788 ]
  

• Legacy coordinate pairs as an object Where the data can be organized by named keys, but these must be ordered and named explicitly as "lon" and "lat" respectively "in order":

  { "lon": 74.3236112, "lat": 31.5200788 }
  

• As GeoJSON format where the data stored can be any valid GeoJSON object format. For a "Point" type this is:

  {
    "type": "Point",
    "coordinates": [ 74.3236112, 31.5200788 ]
  }
  

To demonstrate I have a collection with your sample document in four different formats. We are going to create an index on that collection with .createIndex({ "loc": "2dsphere" }) and then use the aggregation pipeline $geoNear to query and return the actual distance from the queried point:

db.geotest.aggregate([
  { "$geoNear": {
    "near": {
      "type": "Point",
      "coordinates": [ 74.3236112, 31.5200788 ]
    },
    "spherical": true,
    "distanceField": "distance"
  }}
])

Shows the four different formats and the calculated distance from the query. Note that only the two "valid" formats return the correct distance of 0 from the query location:

{
        "_id" : ObjectId("5945f800b4051c7e52c90d1c"),
        "email" : "abc@gmail.com",
        "firstName" : "abc",
        "lastName" : "abc",
        "loc" : {
                "type" : "Point",
                "coordinates" : [
                        74.3236112,
                        31.5200788
                ]
        },
        "gender" : "Male",
        "deviceId" : "YWg8crAjZLCrV",
        "createdDate" : ISODate("2017-06-11T11:35:41.601Z"),
        "updatedDate" : ISODate("2017-06-17T17:33:10.743Z"),
        "distance" : 0
}
{
        "_id" : ObjectId("5945f8f6b4051c7e52c90d1e"),
        "email" : "abc@gmail.com",
        "firstName" : "abc",
        "lastName" : "abc",
        "loc" : {
                "lon" : 74.3236112,
                "lat" : 31.5200788
        },
        "gender" : "Male",
        "deviceId" : "YWg8crAjZLCrV",
        "createdDate" : ISODate("2017-06-11T11:35:41.601Z"),
        "updatedDate" : ISODate("2017-06-17T17:33:10.743Z"),
        "distance" : 0
}
{
        "_id" : "146138792054898475572",
        "email" : "abc@gmail.com",
        "firstName" : "abc",
        "lastName" : "abc",
        "loc" : {
                "lat" : 31.5200788,
                "lng" : 74.3236112
        },
        "gender" : "Male",
        "deviceId" : "YWg8crAjZLCrV",
        "createdDate" : ISODate("2017-06-11T11:35:41.601Z"),
        "updatedDate" : ISODate("2017-06-17T17:33:10.743Z"),
        "distance" : 5315650.25629941
}
{
        "_id" : ObjectId("5945f8b7b4051c7e52c90d1d"),
        "email" : "abc@gmail.com",
        "firstName" : "abc",
        "lastName" : "abc",
        "loc" : {
                "lat" : 31.5200788,
                "lon" : 74.3236112
        },
        "gender" : "Male",
        "deviceId" : "YWg8crAjZLCrV",
        "createdDate" : ISODate("2017-06-11T11:35:41.601Z"),
        "updatedDate" : ISODate("2017-06-17T17:33:10.743Z"),
        "distance" : 5315650.25629941
}

So in order to query correctly, you need a "valid" format for the index. I personally recommend using the GeoJSON format as it is widely used as a standard, and also gives you the option of storing any valid GeoJSON object as opposed to just "point coordinates".

You can convert the data with an operation like the following:

var ops = [];

db.User.find({ 
  "loc.lng": { "$exists": true }, 
  "loc.lat": { "$exists": true }
}).forEach(function(doc) {
  ops.push({
    "updateOne": {
      "filter": { "_id": doc._id },
      "update": {
        "$set": {
          "loc": {
            "type": "Point",
            "coordinates": [ doc.loc.lng, doc.loc.lat ]
          }
        }
      }
    }
  });
  if ( ops.length >= 1000 ) {
    db.User.bulkWrite(ops);
    ops = [];
  }
});

if ( ops.length > 0 ) {
  db.User.bulkWrite(ops);
  ops = [];
}

This will rewrite the "loc" data to the corrected format so the index and query will work.

You should in fact really do a .dropIndexes() on the collection before updating to save on the write cost and then re-create the index once complete. It's not a necessary step, but it would be recommended.

N.B Also the $minDistace argument here with data corrected would actually still exclude the result since as demonstrated the actual distance from the queried point ( which is the same coordinates ) is 0. So remove that constraint on testing, or better yet test with $geoNear as demonstrated.

Also Note There is mention of DocumentDB, which does not support the aggregation pipeline protocols. The examples here work against MongoDB without issue, and are mainly here to demonstrate the "format" problem with the data. Regular $near and other geospatial general queries are supported. But the data must be in the correct supported format.

Answer 2

Your coordinates are backward. Per MongoDB docs:

If you use longitude and latitude, specify coordinates in order of: longitude, latitude.

You need to reverse your coordinates:

db.User.find(
   {
      "loc":
        { $near :
           {
             $geometry: { type: "Point",  coordinates: [ 74.3236112, 31.5200788 ] },
             $minDistance: 1000,
             $maxDistance: 5000
           }
        }
   }
)

Also, in your loc property, the ordering is longitude, latitude (yours is reversed in the doc you showed).