MongoDB GeoSpatial Querying without embedded latitude / longitude
I have a very large database (~ 6 million rows) that was imported from a csv file. I've been looking at MongoDB and their GeoSpatial indexing documentation. You need latitude and longitude fields embedded in the array. Unfortunately, the structure I imported has separate fields.
"latitude": 54.770233, "longitude": -6.537741,
Is there any possible way to query the DB to compute the twenty nearest documents within a 25 mile radius using latitude 54 and longitude -6.
I created a query in MySQL perfectly:
SELECT *, (3959 * ACOS (COS (RADIANS (54)) * COS (RADIANS ( latitude
)) * COS (RADIANS ( longitude
)) - RADIANS (-6)) + SIN (RADIANS (54)) * SIN (RADIANS ( latitude
) ))) AS distance FROM table_name Distance to <25 ORDER BY distance LIMIT 0, 20
However, I'm not sure how to do this in Mongo without inline fields.
Any help would be much appreciated.
source to share
I don't think you can. I ran a script to go through and update all the documents, which will take a while, but not too long. Then you can create a 2d index as usual.
> db.test.insert({latitude:73,longitude:-47});
> db.test.insert({latitude:20,longitude:-30});
> db.test.find().forEach(function(doc) {
doc.loc = [doc.longitude, doc.latitude];
db.test.save(doc);
});
> db.test.find();
{ "_id" : ObjectId("4f7c63f117cd93783bba936d"), "latitude" : 73, "longitude" : -47, "loc" : [ 73, -47 ] }
{ "_id" : ObjectId("4f7c63f817cd93783bba936e"), "latitude" : 20, "longitude" : -30, "loc" : [ 20, -30 ] }
Updated Actually, I suppose you could do it with a where clause, but you wouldn't use any indexes. But if it is for one request, it will probably be ok.
db.test.find("( 3959 * Math.acos(Math.cos( 54 * 0.0174532925 ) * Math.cos( this.latitude * 0.0174532925 ) * Math.cos( this.longitude * 0.0174532925 ) - (-6 * 0.0174532925)) + Math.sin( 54 * 0.0174532925 ) * Math.sin( this.latitude * 0.0174532925 )) > 25 ");
It doesn't actually work - the calculation result is too large. I was just trying to copy your math, but something must have been wrong. Either way, sorting by distance will also be a problem. I think the first solution is a little easier to work with.
source to share