Calculate distance point to point in kilometer at aggregation pipeline stages

Question

This document returned by aggregation pipeline stages:

//1
{
    "_id" : {
        "h" : 8,
        "d" : 6,
        "m" : 3,
        "y" : 2017
    },
    "docs" : [ ....],
"firstDoc" : {
    "ts" : ISODate("2017-03-06T08:07:21.008Z"),
    "coordinate" : [ 
        59.5......, 
        36.2......
    ]
   }
} ,
//2
{

} , // 3 , 4

I have 4 documents like above that these firstDoc field are different.Now i want to calculate distance between points in kilometer.like this:

coordinate1---km?---coordinate2
coordinate2---km?---coordinate3
coordinate3---km?---coordinate4

I see this link but i do not use GeoJSON and i do not want proximity.I want to calculate distance between points How could i do that? I have searched a lot but i can not find any result in mongoDB!!

I saw guys do this by other language like java and c# and java Script but no one did not with mongo query. Is it possible with mongoDB?

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

Well it's actually covered in the very first link, as the "distanceMultipler" field can be used to do the conversion between the "radians" which is returned when the data is not stored in GeoJSON and uses the legacy coordinate pairs in any accepted format.

From the $geoNear documentation for the "distanceField" option:

Optional. The factor to multiply all distances returned by the query. For example, use the distanceMultiplier to convert radians, as returned by a spherical query, to kilometers by multiplying by the radius of the Earth.

And from Calculate Distance Using Spherical Geometry

distance to radians: divide the distance by the radius of the sphere (e.g. the Earth) in the same units as the distance measurement.

radians to distance: multiply the radian measure by the radius of the sphere (e.g. the Earth) in the units system that you want to convert the distance to.

The equatorial radius of the Earth is approximately 3,963.2 miles or 6,378.1 kilometers.

Therefore you simply fill in the options:

db.collection.aggregate([
  { "$geoNear": {
    "near": [<longitude>,<latitude>],
    "distanceField": "distance",
    "spherical": true,
    "distanceMultiplier": 6378.1      // convert radians to kilometers
  }}
])

Or if you actually read the documentation carefully, you will see there is a special case for the conditions supplied to "near", by the "spherical" option:

If true, then MongoDB uses spherical geometry to calculate distances in meters if the specified (near) point is a GeoJSON point and in radians if the specified (near) point is a legacy coordinate pair.

Therefore, simply providing the queried coordinates in GeoJSON format returns in meters, regardless of the storage format:

db.collection.aggregate([
  { "$geoNear": {
    "near": {
      "type": "Point",
      "coordinates": [<longitude>,<latitude>]
    },
    "distanceField": "distance",
    "spherical": true
  }}
])

Also note that unlike some other "query" operators, the $geoNear aggregation pipeline stage requires that you have only one geospatial index on the collection. You cannot specify a "field name" to query on as the command format is expecting only one index of either "2d" or "2dsphere" type to be present on the collection. You should only ever need one anyway, but you should be aware of this.

The mandatory options to $geoNear are of course the arguments to "near" for the coordinates to search from, and the "distanceField" which returns the actual calculated distance within the returned documents. The "spherical" is required for "2dsphere" indexes, which you should be using for accurate measurement, and of course the "distanceMultiplier"is completely optional, but will affect the result returned in the property specified by "distanceField".

There are other options according to whether you need additional query conditions or constraints, but these are the main ones for the purpose of this exercise.

Also note the "longitude" and "latitude" order. This is mandatory for MongoDB geospatial queries to work properly. If your data has these values reversed ( as may be the case when sourced from other API's ) then you need to correct it, by either simply reversing the stored order or better yet converting to GeoJSON entirely.

GeoJSON conversion

Of course the other option is to simply update your data to actually use the GeoJSON format:

var ops = [];
db.colllection.find({ "coordinate": { "$exists": true } }).forEach( doc => {
  ops.push({
    "updateOne": {
      "filter": { "_id": doc._id },
      "update": {
        "$set": { "location": { "type": "Point", "coordinates": doc.coordinate } },
        "$unset": { "coordinate": "" }
      }
    }
  });

  if ( ops.length >= 1000 ) {
    db.collection.bulkWrite(ops);
    ops = [];
  }
})

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

And then drop any previous index and create the new one:

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

Of course swapping collection for your actual collection name in all cases.

Then you can query using GeoJSON arguments as well and simply have the distances returned in meters, as per the standard.

Either are viable options, but really using GeoJSON should keep compatibility with most other external library usage as well as maintaining support with all MongoDB geospatial query operations.

Calculate distance point to point in kilometer at aggregation pipeline stages

1 Answers1

GeoJSON conversion