Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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,
                "objectsLoaded" : 2,
                "objectsLoaded" : 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/

share|improve this answer
    
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
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.