we have a problem for find near users point with distance in mongodb database. the our main problem is we can't aggregate near point with distance. we used $geoWithin with below query:
db.users.find({ location: { $geoWithin: { $centerSphere: [ [ 51.410608, 35.744042 ], 2/6378.1 ] } } }).sort({"user.lastActivity":-1}).limit(100)
this query return 100 records from 500204 records in 0.005 sec.
we also used $nearSphere with below query:
db.users.find({ location: { $nearSphere: { $geometry: { type: "Point", coordinates: [ 51.410608, 35.744042 ] }, $maxDistance: 2000 } } }).sort({"user.lastActivity":-1}).limit(100)
this query return 100 records from 500204 records in 8.486 sec.
we also used geoNear with below query:
db.runCommand( { geoNear: "users", near: { type: "Point" , coordinates: [ 51.410608, 35.744042 ]}, spherical: true , "limit": 100, "maxDistance": 2000 } )
this query return 100 records from 500204 records in 6.215 sec. this query find near point with distance but execute takes a long time.
we add index 2dsphere on users.locations field.
1) please say me about why $nearSphere execute time more than $nearSphere? 2) how to find near point with $nearSphere and calculate return records distance? 3) how to execute geoNear with less time?
we also find near point with mysql on 1394018 records. execute time is 0.0011. this time is really fantastic.
SELECT st_distance_sphere(fld_geom, point( 35.744042,51.410608)) as distance from testgeom where st_distance_sphere(fld_geom, point( 35.744042,51.410608))<=2000 LIMIT 100
i think mysql spatial is very powerful than mongodb spatial.
