Find nearest latitude/longitude in a DB collection with C# MongoDB query

Question

I have a MongoDB collection which holds millions of records like below

{ 
    "_id" : ObjectId("5e662d6e9ce8bf144c715afd"), 
    "X" : 19.229000091552734, 
    "Y" : 233.723388671875,  
    "Data" : {
       // Some data
    }
}
{ 
    "_id" : ObjectId("5e662d6e9ce8bf144c715afe"), 
    "X" : 19.229000091552734, 
    "Y" : 2773.426513671875, 
    "Data" : {
        // Some data
    }
}

I came up with distant calculator for two GeoCoordindates

public static double DistanceTo(this Coordinates baseCoordinates, Coordinates targetCoordinates, UnitOfLength unitOfLength)
        {
            var baseRad = Math.PI * baseCoordinates.Latitude / 180;
            var targetRad = Math.PI * targetCoordinates.Latitude / 180;
            var theta = baseCoordinates.Longitude - targetCoordinates.Longitude;
            var thetaRad = Math.PI * theta / 180;

            double dist =
                Math.Sin(baseRad) * Math.Sin(targetRad) + Math.Cos(baseRad) *
                Math.Cos(targetRad) * Math.Cos(thetaRad);
            dist = Math.Acos(dist);

            dist = dist * 180 / Math.PI;
            dist = dist * 60 * 1.1515;

            return unitOfLength.ConvertFromMiles(dist);
        }

With this I can calculate the distance form given coordinates to all coordinates in the collection and take the closest one. This will work when my collection have tens of documents in it. But here I have millions of records. This approach is going to be extremely painful (actually fails).

So, I need to write a C# MongoDB Driver query that can find a closest document for a giving x,y points. I see a good answer for SQL Server here. Here is a nice article to do the same, but again they are explain this in SQL query approach. How do I come up with similar query for C# MongoDB version?

I am using MongoDB.Driver 2.10.2

Update

Found an approach on Mongo documentation, geoNear, this looks cool, but it requires changes to my document structure (also need to check how much time it will take to index the collection. As my collection keeps updating/replacing every one hour). Lets say, I will go ahead an make change to my document structure, is there reference for C# MongoDB driver way of querying it? instead of Mongo Shell script?

Update (could come up with query)

Finally I made changes to my document structure to comply with GEOJSON and added 2dsphere index

Here is my query

               var geoNearOptions = new BsonDocument
                            {
                                //{"near",new BsonArray(new Double[]{longitude,latitude})},
                                 {"near",new BsonDocument{
                                     { "type", "Point" },
                                     { "coordinates",
                                        new BsonArray(new Double[] { longitude, latitude }) } } } ,
                                {"distanceField","dist.calculated"},
                                //{"distanceMultiplier",  (2.00).NauticalMilesToMeters().MetersToKiloMeters() },
                                {"maxDistance",  (2.00).NauticalMilesToMeters().MetersToKiloMeters() },
                                { "query", new BsonDocument() },
                                {"includeLocs","dist.location" },
                                {"allowDiskUse",true},
                                {"spherical", true}
                            };
            var geonear = new BsonDocument { { "$geoNear", geoNearOptions } };
            //var stage = new BsonDocumentPipelineStageDefinition<BsonDocument, BsonDocument>(new BsonDocument { { "$geoNear", geoNearOptions } });
            var colAggregate = _myCollection.Aggregate().AppendStage<NationalBlendModel>(geonear)
                .Limit(3)
                .ToList();

This code works as long as query is empty

{ "query", new BsonDocument() }

if I change the query as below it fails

var filter = new BsonDocument { { "$gt", new BsonArray { "validTime", new BsonDateTime(DateTime.Now) } } };
{ "query", filter }

Exception:

MongoCommandException: Command aggregate failed: Failed to determine whether query system can provide a covered projection :: caused by :: unknown top level operator: $gt.

[$geoNear](https://docs.mongodb.com/manual/reference/operator/aggregation/geoNear/) should do the job. However, coordinates need a certain format, then you can put a spatial index on it and it should be much faster. Check also this: https://stackoverflow.com/questions/60433217/get-the-document-with-box-with-mongodb-and-add-distance-field-for-each-record-w/60434625#60434625 — Wernfried Domscheit, Mar 09 '20 at 16:12
The best solution is to BIN the points. So if you made a bin 10 degrees latitude and 10 degree longitude you would have 18 * 36 BINS (180 degrees/ 10 * 360/ 10) = 648. If you then pick a location the closest point would be in the rectangle (9 BINS) surrounding the chosen point. — jdweng, Mar 09 '20 at 16:13
@WernfriedDomscheit looked at geoNear. looks cool, Lets say I modified my structure to meet GeoJSON standard. but all query examples I find are in Shell script nothing with C# MongoDB driver. Can you please throw some light on this? — HaBo, Mar 09 '20 at 16:19
I never worked with the C# MongoDB driver but found this one: https://stackoverflow.com/questions/7995097/mongodb-c-sharp-geonear-query-construction and https://api.mongodb.com/csharp/current/html/Overload_MongoDB_Driver_MongoCollection_1_GeoNear.htm — Wernfried Domscheit, Mar 09 '20 at 16:22
Saw this, links are broken in it. Will first start with changing structure of my documents and then try this(above) link — HaBo, Mar 09 '20 at 16:31
here's a much more convenient way of querying geo data: https://dev.to/djnitehawk/tutorial-geospatial-search-in-mongodb-the-easy-way-kbd — Dĵ ΝιΓΞΗΛψΚ, Mar 09 '20 at 16:49

Find nearest latitude/longitude in a DB collection with C# MongoDB query

0 Answers0