# Mongodb - Returning the points nearest to a location, with distance

What is the MongoDB's equivalent to this query:

``````SELECT 111151.29341326 * SQRT( pow(-6.186753-`Latitude`, 2)
+ pow(106.772835-`Longitude`, 2)
* cos(-6.186753*0.017453292519943)
* cos(`Latitude`*0.017453292519943)
)
as distance
from tablename ;
``````

This uses the haversine formula to compute the great-circle distance to a fixed point. We want to get nearest 20 points from a location and then display the distance.

-

MongoDB has built in support for geoindexing. You don't need to do the calculation yourself.

Basically, you would create a field with the lat/long stored as an array or as sub documents, something like one of these:

``````{ loc : [ 50 , 30 ] } //SUGGESTED OPTION
{ loc : { x : 50 , y : 30 } }
{ loc : { lon : 40.739037, lat: 73.992964 } }
``````

Then index the new loc field appropriately:

``````db.places.ensureIndex( { loc : "2d" } )
``````

Finally you can then use one of the operators to query a point for the nearest 20 results:

``````db.places.find( { loc : { \$near : [50,50] } } ).limit(20)
``````

You could, of course, just use MongoDB to store the data, then pull the information out of the DB with a find() and do the calculation client-side but I imagine that is not what you want to do.

If the distance part of the equation is what you want:

http://www.mongodb.org/display/DOCS/Geospatial+Indexing#GeospatialIndexing-geoNearCommand

The \$geoNear operator will return the distance also. An example:

``````> db.runCommand( { geoNear : "places" , near : [50,50], num : 10 } );
{
"ns" : "test.places",
"near" : "1100110000001111110000001111110000001111110000001111",
"results" : [
{
"dis" : 69.29646421910687,
"obj" : {
"_id" : ObjectId("4b8bd6b93b83c574d8760280"),
"y" : [
1,
1
],
"category" : "Coffee"
}
},
{
"dis" : 69.29646421910687,
"obj" : {
"_id" : ObjectId("4b8bd6b03b83c574d876027f"),
"y" : [
1,
1
]
}
}
],
"stats" : {
"time" : 0,
"btreelocs" : 1,
"btreelocs" : 1,
"nscanned" : 2,
"nscanned" : 2,
"avgDistance" : 69.29646421910687
},
"ok" : 1
}
``````

The `"dis" : 69.29646421910687` elements are what you are looking for, there is also a spherical distance option.

For all this, how to use the distances, and more, take a look here for more information on geo indexes and how to use them:

http://www.mongodb.org/display/DOCS/Geospatial+Indexing/

-
I understand that already. But I want the return result to contain "distance" field. How to achieve that? – Jim Thio Aug 21 '12 at 10:17
I don't think you read the page I linked - I expanded the answer to include the operators that return distance, they are just further down the page – Adam C Aug 21 '12 at 11:41
What about if the additional column we want to have is not distance but something else? – Jim Thio Aug 22 '12 at 4:21
"something else" is too vague to answer, if you want generic calculation then Map/Reduce or the Aggregation Framework in 2.2 – Adam C Aug 22 '12 at 5:58
In MySQL you can add columns to the result you want, usually based on formula. If the table has 4 columns you can get a view with 6 columns. Can you do that on mongodb? Looks like you need to get the data and compute the columns your self. I am just trying to make sure. – Jim Thio Aug 22 '12 at 7:48