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

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

Installing Tomcat on Mac OS X

Installing Tomcat on Mac can be done in three steps. Following the steps below you can have tomcat running on your Mac in no time. Step 1: Download and Install Tomcat Binaries Download the most recent stable build of Tomcat from an Apache project mirror site . Unzip the binaries into a simple location (I did it in /Users/username/Documents).  By default, the unpacked folder name will be something like "apache-tomcat-x-x-xx".  I recommend changing it to "tomcat" for ease of use. Step 2: Configure the env variables  For our purpose we need to set up JAVA_HOME and CATALINA_HOME variables. Setting up these variables will ensure we don't have to use the complete path name. To set the variables, open a new Terminal window and use the following command to open the system profile for editing. vi ~/.profile Once you've opened the profile, add the following lines to set the JAVA_HOME and CATALINA_HOME variables: export JAVA_HOME=/Library/Java/Home ...
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