Recently for a project involving 311 reports in New York city, I had to find a speedy way to query over 3 million records within a certain radius of a latitude longitude point. Mysql is slow for this sort of thing, so I decided to try the data set out with Solr. Solr worked great and was quite responsive, but required a massive amount of RAM on our server to hold the entire dataset. This was “ok” for our current dataset, but what would happen when we started to expand beyond NYC? or if we kept adding on new 311 reports from NYC as they come in over time? Solr was not scaleable. Mongodb to the rescue!<\/p>\n
I wrote a simple import script in PHP to pull in the data we had already imported into mysql into mongodb. The only special part of the data mirgation was the creation of a “lnglat” field in each imported document array which is a “2d index” in mongo. You have to set a multivalued field with the lat\/lng values cast as floats in the document array, like this:<\/p>\n
$doc['lnglat'] = array('lng'=>(float)$row['lng'],'lat'=>(float)$row['lat']);<\/pre>\nAfter the data has been imported to mongo, you create the 2d index on that multivalued field using the mongo command line, with this command:<\/p>\n
my_mongodb.my_collection.ensureIndex( { lnglat : \"2d\" } )<\/pre>\nOnce I had the data in mongo, I had a hard time finding good examples of Geospatial queries for mongodb in php. I finally found this page:
\nhttp:\/\/comments.gmane.org\/gmane.comp.db.mongodb.user\/48789<\/a>
\nwhich solved all my problems and pointed out that when querying mongo with a lat\/lng point as an array, mongodb expects the lng to be first, not the lat. Durrrr…<\/p>\n