MongoDB GeoNear Aggregate

Question

The question is:

Consider the following location: [-72, 42] and the range (circle) of radius 2 around this point. Write a query to find all the states that intersect this range (circle). Then, you should return the total population and the number of cities for each of these states. Rank the states based on number of cities.

I have written this so far:

db.zips.find({loc: {$near: [-72, 42], $maxDistance: 2}})

and a sample output of that is: { "city" : "WOODSTOCK", "loc" : [ -72.004027, 41.960218 ], "pop" : 5698, "state" : "CT", "_id" : "06281" }

In SQL i would simply do a group by "state", how would i be able to do that here while also counting all the cities and total population?

Answer 1

assuming you follow the mongoimport routine for its zipcode data (i brought mine into a collection called zips7):

mongoimport --db mydb --collection zips7 --type json --file c:\users\drew\downloads\zips.json

or

mongoimport --db mydb --collection zips7 --type json --file /data/playdata/zips.json

(depending on your OS and paths)

then

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

db.zips7.find({loc: {$near: [-72, 42], $maxDistance: 2}}).forEach(function(doc){
   db.zips8.insert(doc);
});

note that db.zips7.stats() shows like 30k rows and zips8 has 100 rows

 db.zips8.aggregate( { $group :
 { _id : "$state",
   totalPop : { $sum : "$pop" },
   town_count:{$sum:1} 
 }}
 )


 {
        "result" : [
                {
                        "_id" : "RI",
                        "totalPop" : 39102,
                        "town_count" : 10
                },
                {
                        "_id" : "MA",
                        "totalPop" : 469583,
                        "town_count" : 56
                },
                {
                        "_id" : "CT",
                        "totalPop" : 182617,
                        "town_count" : 34
                }
        ],
        "ok" : 1
}