Unable to execute queries on geospatial atributes

I have a little project in C# which consists of an API that receives data about (made up) infection cases. Each case object have the geographic coordinates of such case.

The program is supposed to query the nearest city to that case and return it as a response. I try to do this with the following method:

private void ObterCidadeMaisProxima(Infectado infectado){
    var collection = GetCidadeCollection();

    string coordinates = $"[ {infectado.Localizacao.Latitude}, {infectado.Localizacao.Longitude} ]";

    string query = "{ location: { $near : { $geometry: { type: \"Point\",";

    query += $"coordinates: {coordinates}";

    query += "}, $maxDistance: 1000000 } } }";

    var e = BsonDocument.Parse(query);
    var doc = new QueryDocument(e);

    var result = collection.Find<Cidade>(doc).ToList<Cidade>();

    //So far I just want to test the function, that's why I don't return the result         
    foreach(var r in result) Console.WriteLine(r);
}

Both my classes (Infectado and Cidade) have the following attribute:

public GeoJson2DGeographicCoordinates Localizacao { get; set; }

And in MongoDB Atlas I have created the following indexes for both collections (infectado and cidade):

loc

So, I have two collections using geospatial indexes, and I’m trying to execute a $near query, but MongoDB can’t find the indexes.

Here’s the output:

fail: Microsoft.AspNetCore.Server.Kestrel[13] Connection id “0HM7HA1MKQ9RL”, Request id “0HM7HA1MKQ9RL:00000001”: An unhandled exception was thrown by the application. MongoDB.Driver.MongoCommandException: Command find failed: error processing query: ns=covidDB.cidadeTree: GEONEAR field=location maxdist=1e+06 isNearSphere=0 Sort: {} Proj: {} planner returned error :: caused by :: unable to find index for $geoNear query.

Here’s some information about my enviroment:

  • .NET SDK version: 5.0.201
  • MongoDB Driver version: 2.12.1
  • OS: Linux Mint 19.3 Tricia
  • I’m connecting to MongoDB Atlas

I could really use some help. Thanks in advance.

:wave: Hi @Neophyte and welcome to the MongoDB Community Forums!

Thanks for posting your detailed question.

Can you also share a sample document from the collection you’re querying?

Hi @yo_adrienne , sure

Here’s the collection (printed from Atlas):
doc

1 Like

Hi @Neophyte, thanks for the additional context!

I see you have both a 2d and 2dsphere on your location data, is there a reason why?

Usually, 2d indexes are needed if you are dealing with plane geometry or using legacy coordinate pairs like so:

location: [ <longitude>, <latitude> ]

Data stored in this format needs to be queried like so:

{ location : { $near : [ <longitude>, <latitude> ], $maxDistance: , <maxDistanceInRadians> } }

On the other hand, a 2dsphere index, expects the data to be stored as one of the GeoJSON object types.

I’d actually recommend using the 2dsphere index as you’re dealing with spherical geometry (e.g. finding nearest cities).

Also, queries need to specify longitude first and then latitude .

So there are two things to try, but first do these two steps, no matter which option you choose:

  1. Drop both of your geospatial indexes.
  2. Modify your coordinates variable to list longitude first, then latitude

OPTION 1: If keeping your legacy coordinate pairs:

  • Only recreate your 2d index
  • Remove this entire section:

and replace with this:

string query = $@"{ Localizacao: { $near: {coordinates}, $maxDistance: 100000 }";

As you can see, your query needed to remove the $geometry operator and match the field name you created the index on.

Try running the query and see if it returns what you’re expecting (note you may have to adjust the maxDistance you input in this query format as it expects Radians).

OR

OPTION 2: If changing your data to GeoJSON format (recommended):

  • Update your data to be in proper GeoJSON Point object type.
  • Only recreate the 2dsphere index on your newly formed GeoJSON objects.
  • Modify your existing query to match your actual field name that has the 2dsphere index:
string query = "{ Localizacao: { $near: { $geometry: { type: \"Point\",";

Try running the query and see if what you expect returns.

Let me know how this goes and feel free to ask any questions if you need me to clarify anything here. :slight_smile:

1 Like

Hi, @yo_adrienne

I’ve create the two indexes just to be sure and tried using both $near and $nearSphere to see if any of them would work.

So, following your advices in OPTION 2 I did some research on how to create a GeoJSON field and turns out both my collections had its coordinates declared as GeoJson2dGeographicCoordinates, when it is supposed to be GeoJsonPoint<GeoJson2dGeographicCoordinates>. I dropped and repopulated both of them, and recreated my index on the Cidade collection using MongoDB

Just to bre sure, I also used the following code to create an index via the C# MongoDB Driver:

var keys = Builders<Cidade>.IndexKeys.Ascending("Localizacao");
var indexOptions = new CreateIndexOptions { Unique = true };
var model = new CreateIndexModel<Cidade>(keys, indexOptions);
var col = CollectionPopulator.GetCidadeCollection();
col.Indexes.CreateOne(model);

Now I have these two:

But the error persists. I’ve even tried querying with both of them, and with each one separatedly. (Are both collections supposed to have an index on the coordinates object?)

1 Like

Here’s a sample of the new collection (had to put it in another post since I’m a new user):

d

1 Like

Thanks for the additional information!

If you plan on running geospatial queries against the coordinates objects of both collections, then yes.

I don’t see you choosing the actual Geo2DSphere key; did you create the 2d sphere index similar to the following?

collection.Indexes.CreateOne(
  new CreateIndexModel<TDocument>(new IndexKeysDefinitionBuilder<TDocument>().Geo2DSphere(x => x.Localizacao)));

Alternatively, have you tried creating the index in Atlas directly? I don’t think this is the issue though, as your screenshot shows that there is a geospatial 2dsphere index present.

Can you try changing your $near query like so (the more “C#” way to do this query):

double lng = double.Parse(infectado.Localizacao.Longitude);
double lat = double.Parse(infectado.Localizacao.Latitude);

// point of interest
var point = new GeoJson2DGeographicCoordinates(lng, lat);
var pnt = new GeoJsonPoint<GeoJson2DGeographicCoordinates>(point);

var distanceInMeters = 100000; // multiply by 1609.34 if converting to miles

var filter = Builders<Cidade>.Filter.Near(x => x.Localizacao.Coordinates, pnt, distanceInMeters );

// This is the actual query execution
List<Cidade> cities = collection.Find(filter).ToList().Result;

Hi, @yo_adrienne

I changed the query and got a new error:

System.InvalidOperationException: Unable to determine the serialization information for x => x.Localizacao.Coordinates.

Then I followed this workaround and the exception disappeared, but it still isn’t able to find the index.

I also had to change

double lng = double.Parse(infectado.Localizacao.Longitude);
double lat = double.Parse(infectado.Localizacao.Latitude);

to

double lng = infectado.Localizacao.Coordinates.Longitude;
double lat = infectado.Localizacao.Coordinates.Latitude;

since the former yields the following error:

‘GeoJsonPoint’ does not contain a definition for ‘Longitude’ and no accessible extension method ‘Longitude’ accepting a first argument of type ‘GeoJsonPoint’ could be found (are you missing a using directive or an assembly reference?)

I’ve tried dropping my indexes and creating one again in C#, but it wasn’t effective.

1 Like

I’m wondering if it’s possible to create some function in Atlas that executes this query for me, one that would require only to the C# program to call it and provide the arguments. I’ve done something like this using Mongoose in a NodeJS side project, but the code was declared locally in Model. Is it possible to do this in C#?

1 Like

Hi @Neophyte.

I’m sorry that this has been more difficult than it needs to be. Let me try to get a definitive answer from the Drivers team on how to properly execute this geospatial query.

Yes, you can create Realm Functions and then use the .NET SDK for Realm to call those functions, but that would be more complicated than it needs to be. Please feel free to try it out in the meantime, though!

I’ll leave an update once I get a proper working example for the .NET driver. Thank you for your patience!