Grouping by Polygons in MongoDB

Question

I have a json containing the polygons that represent the delimiters of all cities (78 in total) in a country. For example:

{
    "City1":[
        [-67.103276,18.513426],
        [-67.10339411502086,18.514532657212502],
        [-67.093752,18.515757],
        [-67.05297606966441,18.512073045833525]
    ],
    "City2":[
        [-67.16901339052771,18.472154288860388],
        [-67.169016,18.478488],
        [-67.138249,18.507776],
        [-67.12906260088943,18.510642646697203],
        [-67.125655,18.511706]
    ],
    "City3":[
        [-66.034932,18.333452],
        [-66.03494,18.332214],
        [-66.035911,18.328084],
        [-66.035731,18.31961],
        [-66.036859,18.318627]
    ],
    ...
}

I'm using the ruby driver for MongoDB. The database contains a collection of points (indexed as 2dsphere). I need to calculate the total count of points inside a city. The result should say, for example, "City1 contains 56 points, City2 contains 40 points, City3 contains 100 points".

This is code that I'm using to find the points inside a single polygon, and setting a date range from_date to end_date (since the points have a date property)

polygon = [[-67.103276,18.513426],[-67.10339411502086,18.514532657212502],[-67.093752,18.515757],[-67.05297606966441,18.512073045833525]]
from_date = DateTime.strptime(params[:from_date], '%Y-%m-%d')
from_date = Time.utc(from_date.year, from_date.month, from_date.day)
to_date = DateTime.strptime(params[:to_date], '%Y-%m-%d')
to_date = Time.utc(to_date.year, to_date.month, to_date.day)

@coll = db.collection("points")
recordset = @coll.find(
    { 
        "$and" => [
            { "geometry.coordinates" => {
                "$within" => {"$polygon" => polygon}
            }}, 
            { "properties.time" => {
                :$gte => from_date,
                :$lte => to_date
            }}
        ]
    },
    :fields => { :_id => false }
)

Is there any way that I can group the results of all cities with a single query, or I have to query the database 78 times to get the count for each city?

Philipp Philipp · Accepted Answer · 2014-05-12T07:22:02

When you want to group, you need to use the aggregation framework. You will need an aggregation pipeline with two steps and an optional 3rd step:

The above query with a $match operator (by the way: the $and is unnecessary. You can put "geometry.coordinates" and "properties.time" in the same associative array)
a $group operator which _id => "$cityname" (I guessed cityname is the field where the name of the city is stored) and "number_of_points" => { "$sum" => 1 }. The new field number_of_points will contain the number of points per city.
(optional) The name of the city will now be in the field _id. When you want the field to have a different name, you can rename it with an additional $project step.

Grouping by Polygons in MongoDB

1 Answers