Skip to main content

Geospatial queries in MongoDB - Part 1

While building a food truck service using which a user can browse and search for food options in a given area, I had to work with geographic latitude/longitude information. I was using MongoDB as my data store which supports geospatial queries. A sample document in my foodtruck collection looks like this:

> db.foodtrucks.find().pretty().limit(1);
{
    "_id" : ObjectId("53d07bf1e3e11dacc2105697"),
    "locationid" : 559777,
    "Applicant" : "Mang Hang Catering",
    "FacilityType" : "Truck",
    "cnn" : 131000,
    "LocationDescription" : "02ND ST: JESSIE ST to MISSION ST (69 - 99)",
    "Address" : "85 02ND ST",
    "blocklot" : 3708019,
    "block" : 3708,
    "lot" : 19,
    "permit" : "14MFF-0109",
    "Status" : "REQUESTED",
    "FoodItems" : "COLD TRUCK. Deli: bbq chicken skewer: Chinese spring roll: Chinese fried rice/noodle: fried chicken leg/wing: bbq chicken sandwich: chicken cheese burger: burrito: lumpia. Snack: sunflower seeds: muffins: chips: snickers: kit-kat: 10 types of chocolate. Drinks: Coke: 7-Up: Dr. Pepper: Pepsi: Redbull: Vitamin Water: Rockstar: Coconut Juice: Water. Hot drinks: coffee: tea.",
    "X" : 6012696.635,
    "Y" : 2115129.488,
    "Latitude" : 37.7884570288289,
    "Longitude" : -122.399884160566,
    "Schedule" : "http://bsm.sfdpw.org/PermitsTracker/reports/report.aspx?title=schedule&report=rptSchedule&params=permit=14MFF-0109&ExportPDF=1&Filename=14MFF-0109_schedule.pdf",
    "NOISent" : "",
    "Approved" : "",
    "Received" : "Jul 10 2014  2:56PM",
    "PriorPermit" : 1,
    "ExpirationDate" : "03/15/2015 12:00:00 AM",
    "Location" : "(37.7884570426015, -122.399884160556)",
 }

Before I could run geospatial queries on my collection, I needed to make sure the latitude/longitude elements were stored in the document's field in a certain format - either as a map in the format "pos:{Longitude: -122.399884, Latitude: 37.7884570}" or an array of two elements in the format "pos:[-122.399884, 37.7884570]". For my implementation I went with the map option. Here are the steps I took to accomplish this:

 i. Adding coordinate entry into all the documents of the collection

Next step was to add an entry with the coordinate information into each of the documents in the collection. MongoDB supports scripts for the mongo shell to manipulate data in the database. I wrote a small javascript module to do this for me. Here it is:

XDB = db.foodtrucks;
var iter = XDB.find()

do {
    var doc = iter.next();
    var newdoc = {};
    newdoc.pos = [];
    var newpair =  {Longitude: doc.Longitude,Latitude: doc.Latitude} ;
    newdoc.pos.push(newpair);
    XDB.update({_id: doc._id},{$set:{pos: newpair}});
 } while ( iter.hasNext() );

To execute this run this command in your shell: mongo localhost:27017/testdb <name of your javascript file>

 > db.foodtrucks.find().pretty().limit(1);
{
    "_id" : ObjectId("53d07bf1e3e11dacc2105697"),
    "locationid" : 559777,
    "Applicant" : "Mang Hang Catering",
    "FacilityType" : "Truck",
    "cnn" : 131000,
    "LocationDescription" : "02ND ST: JESSIE ST to MISSION ST (69 - 99)",
    "Address" : "85 02ND ST",
    "blocklot" : 3708019,
    "block" : 3708,
    "lot" : 19,
    "permit" : "14MFF-0109",
    "Status" : "REQUESTED",
    "FoodItems" : "COLD TRUCK. Deli: bbq chicken skewer: Chinese spring roll: Chinese fried rice/noodle: fried chicken leg/wing: bbq chicken sandwich: chicken cheese burger: burrito: lumpia. Snack: sunflower seeds: muffins: chips: snickers: kit-kat: 10 types of chocolate. Drinks: Coke: 7-Up: Dr. Pepper: Pepsi: Redbull: Vitamin Water: Rockstar: Coconut Juice: Water. Hot drinks: coffee: tea.",
    "X" : 6012696.635,
    "Y" : 2115129.488,
    "Latitude" : 37.7884570288289,
    "Longitude" : -122.399884160566,
    "Schedule" : "http://bsm.sfdpw.org/PermitsTracker/reports/report.aspx?title=schedule&report=rptSchedule&params=permit=14MFF-0109&ExportPDF=1&Filename=14MFF-0109_schedule.pdf",
    "NOISent" : "",
    "Approved" : "",
    "Received" : "Jul 10 2014  2:56PM",
    "PriorPermit" : 1,
    "ExpirationDate" : "03/15/2015 12:00:00 AM",
    "Location" : "(37.7884570426015, -122.399884160556)",
    "pos" : {
        "Longitude" : -122.399884160566,
        "Latitude" : 37.7884570288289
    }
}

The "pos" entry has now been added to the document in the collection (in red).

ii. Executing the query

Before writing any queries I had to create a geospatial index on the "pos" field using the command below:

> db.foodtrucks.ensureIndex({pos:"2d"})

Now we can start writing queries on this collection. For ex- if we want to find the closest foodtrucks to the position which is passed in, we would write something as follows:

> db.foodtrucks.find({pos :{$near:[-122,37]}}).pretty().limit(1)
{
    "_id" : ObjectId("53d07bf1e3e11dacc210579f"),
    "locationid" : 528583,
    "Applicant" : "Golden Catering",
    "FacilityType" : "Truck",
    "cnn" : 5374000,
    "LocationDescription" : "EXECUTIVE PARK BLVD: CRESCENT WAY to HARNEY WAY (300 - 399)",
    "Address" : "301 EXECUTIVE PARK BLVD",
    "blocklot" : 4991240,
    "block" : 4991,
    "lot" : 240,
    "permit" : "14MFF-0072",
    "Status" : "APPROVED",
    "FoodItems" : "Cold Truck: Pre-packaged Sandwiches: Various Beverages: Salads: Snacks",
    "X" : 6014982.64302,
    "Y" : 2087104.0558,
    "Latitude" : 37.7116322811949,
    "Longitude" : -122.390016333636,
    "Schedule" : "http://bsm.sfdpw.org/PermitsTracker/reports/report.aspx?title=schedule&report=rptSchedule&params=permit=14MFF-0072&ExportPDF=1&Filename=14MFF-0072_schedule.pdf",
    "NOISent" : "",
    "Approved" : "03/21/2014 01:52:04 PM",
    "Received" : "Mar 21 2014  1:42PM",
    "PriorPermit" : 1,
    "ExpirationDate" : "03/15/2015 12:00:00 AM",
    "Location" : "(37.711632295042, -122.390016333627)",
    "pos" : {
        "Longitude" : -122.390016333636,
        "Latitude" : 37.7116322811949
    }
}

If we want more choices,  we just change the value in the limit to the number of values we want returned. MongoDB finds the places closest to the position provided and returns them in a sorted order by distance.
Labels:

Comments

Post a Comment

Popular posts from this blog

Geospatial queries in MongoDB - Part 2 : {"err": "location object expected, location array not in correct format" "code": 16804}

In my last post we saw we had to create a geospatial index on the " pos " field as follows: > db.foodtrucks.ensureIndex({pos:"2d"}) However, when executing the command above we get the following error:   > db.foodtrucks.ensureIndex({pos:"2d"}) {     "createdCollectionAutomatically" : false,     "numIndexesBefore" : 3,     "ok" : 0,     "errmsg" : "location object expected, location array not in correct format",     "code" : 16804 } After some further investigation I realized that we had some documents which didn't had any latitude/longitude information. Due to this some of the documents looked like this: {     "_id" : ObjectId("53d07bf1e3e11dacc21056a8") ,     "locationid" : 437222 ,     "Applicant" : "Natan's Catering" ,     "FacilityType" : "Truck" ,     "cnn" : 188101 ,     "Locatio...
Post a Comment
Read more

Installing Google styleguide in Intellij on Mac

Download the   intellij-java-google-style.xml  file from repo:   http://code.google.com/p/google-styleguide/ . Download it and go into  Preferences -> Editor -> Code Style .  Click on  Manage  and import the downloaded Style Setting file.  Select  GoogleStyle  as new coding style.      Additional Java Style Guides:       https://google.github.io/styleguide/javaguide.html       https://github.com/twitter/commons/blob/master/src/java/com/twitter/common/styleguide.md
Post a Comment
Read more

Remove and ignore files like .DS_Store, .classpath so that it doesn't get added to your github repo

When promoting code on github it is usually a good idea not to promote files like .DS_Store. .classpath etc. To accomplish this you can include a .gitignore file in your first commit . Git uses it to determine which files and directories to ignore, before you make a commit. Here is what my .gitignore looks like: /.buildpath /build/ */archive/ # Compiled source # ################### *.com *.class *.dll *.exe *.o *.so   .project .settings .classpath # Packages # ############ # it's better to unpack these files and commit the raw source # git has its own built in compression methods *.7z *.dmg *.gz *.iso *.jar *.rar *.tar *.zip # Logs and databases # ###################### *.log *.sql *.sqlite # OS generated files # ###################### .DS_Store .DS_Store? ._* .Spotlight-V100 .Trashes ehthumbs.db Thumbs.db Icon?     Alternatively, you can create a global .gitignor...
Post a Comment
Read more