To determine the distance between two places, translate the Haversine formula to a Mongo query

Working with Data
1

I need to use an aggregate query to determine the distance between two places. Given that the $geoNear query was already used at the beginning of the query and that the distance needed to be calculated again for filterdown, we inserted the Haversine formula, however, the outcome is incorrect. any suggestions for how to fix this? The query samples are given below.

{
          $addFields: {
            lat1Radians: { $multiply: [36.7589882, Math.PI / 180] }, // Convert latitude1 to radians
            lon1Radians: { $multiply: [-119.4249381, Math.PI / 180] }, // Convert longitude1 to radians
            lat2Radians: { $multiply: [36.7151076, Math.PI / 180] }, // Convert latitude2 to radians
            lon2Radians: { $multiply: [-119.4159583, Math.PI / 180] }, // Convert longitude2 to radians
          },
        },
        {
          $addFields: {
            dlat: { $subtract: ['$lat2Radians', '$lat1Radians'] },
            dlon: { $subtract: ['$lon2Radians', '$lon1Radians'] },
          },
        },
        {
          $addFields: {
            a: {
              $add: [
                { $sin: { $divide: ['$dlat', 2] } },
                {
                  $multiply: [
                    { $cos: '$lat1Radians' },
                    { $cos: '$lat2Radians' },
                    { $sin: { $divide: ['$dlon', 2] } },
                  ],
                },
              ],
            },
          },
        },
        {
          $addFields: {
            c: {
              $atan2: [
                { $sqrt: { $max: ['$a', 0] } },
                { $sqrt: { $subtract: [1, '$a'] } },
              ],
            },
          },
        },
        {
          $addFields: {
            distanceH: { $multiply: [3959, '$c'] },
          },
        },
      );
2

Rather than unwind the above, I tried to implement it and it seemed to work:

db.aggregate([
{
    $documents:[
        {
            lat1:36.12,
            long1:-86.67,
            lat2:33.94,
            long2:-118.40            
        }
    ]
},
{
    $addFields:{
        pi:3.14159265359,
        Inverse180:0.00555555555,
        r:6372.8
    }
},
{
     $addFields:{
         dLat:{$subtract:['$lat2', '$lat1']},
         dLon:{$subtract:['$long2', '$long1']}
     }   
},
{
     $addFields:{
         dLatRadians:{$multiply:['$pi', '$dLat', '$Inverse180']},
         dlonRadians:{$multiply:['$pi', '$dLon', '$Inverse180']},
         lat1:{$multiply:['$pi', '$lat1', '$Inverse180']},
         lat2:{$multiply:['$pi', '$lat2', '$Inverse180']}
     }       
},
{
    $addFields:{
        a:{
            $multiply:[
                {
                    $sin:{
                        $divide:['$dLatRadians',2]
                    }
                },
                {
                    $sin:{
                        $divide:['$dLatRadians',2]
                    }
                }
            ]
        },
        a2:{
            $multiply:[
                {
                    $sin:{
                        $divide:['$dlonRadians',2]
                    }
                },
                {
                    $sin:{
                        $divide:['$dlonRadians',2]
                    }
                },
                {
                    $cos:'$lat1'
                },
                {
                    $cos:'$lat2'
                },
            ]            
        }
    }
},
{
    $addFields:{
        a_out:{
            $add:[
                '$a','$a2'
            ]
        }
    }
},
{
    $addFields:{
        c:{
            $multiply:[
                2,
                {
                    $asin:{
                        $sqrt:'$a_out'
                    }
                }
            ]
        }
    }
},
{
    $addFields:{
        retVal:{
            $multiply:[
                '$r',
                '$c',                
            ]
        }
    }
}
])

This is the algo I took it from:

I had some issues, but I debugged it by putting my algo into a debugger and then comparing the variables in the aggregate as it ran against the debugger (C# in this case).

I suggest you do the same with your code to see where the issue is.

4 Likes
3

Also:
https://jira.mongodb.org/browse/SERVER-2990

4

Thank you so much @John_Sewell . The code is working as expected.

5

No problem, I would clean it up a touch though as I laid it out to be simple to read and many of the stages can be combined.
You may want to look at how many decimals you need for the PI constant if you use it like this as opposed to using MATH.PI, out of interest I looked at what NASA / JPL use for calculations for space probes given they were asked if they really just used 3.14 as an approximation (they don’t):

An interesting read!

Also there was some talk in the articles about what value to use for R, but I’ll let you work out what you want to use!

1 Like
6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.