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

Running Docker on Linux Mint

Docker doesn't come natively installed on Linux Mint. To be able to run it you will need apparmor and cgroup-lite packages. Follow the steps below to get it working: Add repository to APT sources sudo echo deb https://get.docker.com/ubuntu docker main > /etc/apt/sources.list.d/docker.list Import repository keys sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 36A1D7869245C8950F966E92D8576A8BA88D21E9 Install Docker  sudo apt-get update sudo apt-get install -y docker.io cgroup-lite apparmor  Check if Docker daemon is running  ps aux | grep docker If docker daemon is not running, try the following command: sudo service docker start Check if docker.sock exists. This file needs to be owned by docker group to be able to connect to it without root permissions   ls -al /var/run/docker.* Check if user is part of docker group   id -a If user is not in the docker group you can add them using this command:  s...
Post a Comment
Read more