Error when using dynamic coordinates in MongoDB aggregation pipeline for geospatial queries

Working with Data
1

When attempting to use dynamic coordinates ($lat and $lng ) in a MongoDB aggregation pipeline for geospatial queries, I encountered the error message “Point must only contain numeric elements.” This error suggests that the dynamic values are not being recognized as numeric elements within the geospatial query. As a result, the query fails to execute properly. I am seeking a solution to resolve this issue and successfully use dynamic values in geospatial queries in MongoDB.

The working solution"

db.dataofweek9of2023.aggregate([
  {
    $match: {
      "data.featureName": "TripsBoard",
      "data.lat": { $ne: null },
      "data.lng": { $ne: null }
    }
  },
  {
    $limit: 1
  },
  {
    $addFields: {
      lat: { $toDouble: "$data.lat" },
      lng: { $toDouble: "$data.lng" }
    }
  },
  {
    $lookup: {
      from: "bangladesh_geojson",
      let: {
        lat: "$lat",
        lng: "$lng"
      },
      pipeline: [
        {
          $unwind: "$features"
        },
        {
          $match: {
            "features.geometry": {
              $geoIntersects: {
                $geometry: {
                  type: "Point",
                  coordinates: [ 90.416, 23.7935 ]
                }
              }
            }
          }
        },
        {
          $project: {
            _id: 0,
            district: "$features.properties.shapeName"
          }
        }
      ],
      as: "districts"
    }
  }
])

The non working solution:

db.dataofweek9of2023.aggregate([
  {
    $match: {
      "data.featureName": "TripsBoard",
      "data.lat": { $ne: null },
      "data.lng": { $ne: null }
    }
  },
  {
    $limit: 1
  },
  {
    $addFields: {
      lat: { $toDouble: "$data.lat" },
      lng: { $toDouble: "$data.lng" }
    }
  },
  {
    $lookup: {
      from: "bangladesh_geojson",
      let: {
        lat: "$lat",
        lng: "$lng"
      },
      pipeline: [
        {
          $unwind: "$features"
        },
        {
          $match: {
            "features.geometry": {
              $geoIntersects: {
                $geometry: {
                  type: "Point",
                  coordinates: [ "$$lat", "$$lng" ]
                }
              }
            }
          }
        },
        {
          $project: {
            _id: 0,
            district: "$features.properties.shapeName"
          }
        }
      ],
      as: "districts"
    }
  }
])

Error log:

{
	"message" : "Point must only contain numeric elements",
	"ok" : 0,
	"code" : 2,
	"codeName" : "BadValue"
}
3

Hi @R_Hasan,

The $match stage you’ve provided in the pipeline for the $lookup won’t make use of the variables as it is requires use of the $expr operator. As per the $lookup documentation, specific to the let field:

The let variables can be accessed by the stages in the pipeline, including additional $lookup stages nested in the pipeline.

  • A $match stage requires the use of an $expr operator to access the variables. The $expr operator allows the use of aggregation expressions inside of the $match syntax.
  • Other (non-$match) stages in the pipeline do not require an $expr operator to access the variables.

I’m not sure if this particular feedback post relates to what you’re after but could possibly help with the $match stage you’ve provided in the pipeline.

You could also consider performing the query in 2 parts if that works for your use case(s). Off the top of my head, one example is to perform the stages prior to the $lookup as the first aggregation and then use the lat and long values retrieved from the initial aggregation to perform another query with those values with a second aggregation query. Although this example is based off what I assume the resulting fields would appear like since I am not sure what the input documents actually look like.

Regards,
Jason

1 Like
4

Thank you, @Jason_Tran for your response. I appreciate your suggestion of using two separate aggregations to achieve the desired result. While that approach works well with MongoDB clients like NoSQLBooster or similar tools, my specific use case requires the use of Metabase as the MongoDB client.

In Metabase, I don’t have the flexibility to write separate queries or utilize JavaScript-like operations to retrieve results from one query and use them in another. Instead, I need to find a solution that can be implemented within the constraints of the Metabase platform.

I understand that your proposed solution involving separate queries may not be applicable in this scenario. I will continue to focus on finding a solution that works seamlessly within Metabase’s capabilities.

If you have any further suggestions or recommendations specific to using Metabase as a MongoDB client, I would greatly appreciate your insights.

Thank you once again for your understanding and assistance.

Best regards,
R_Hasan

1 Like
5

Hello again, @Jason_Tran,

After considering your feedback and suggestions, I wanted to ask if you could provide guidance regarding the use of the $expr operator inside the $match stage. As you mentioned, the $expr operator allows the use of aggregation expressions within the $match syntax, and it is necessary to access the variables defined in the let field.

Since my use case involves working with Metabase as the MongoDB client, I am looking for a solution that can be implemented within the limitations of the Metabase platform. It would be immensely helpful if you could provide an example or further information on utilizing the $expr operator inside the $match stage.

Thank you in advance for your assistance and support. I truly appreciate your expertise and willingness to help.

Here is the link for the geoJson file I’ve imported as the collection named bangladesh_geojson Bangladesh GeoJSON

6

Hi @R_Hasan,

There is an example of $expr used within the $match stage of the pipeline field in the $lookup documented here. You can see from that particular example, the let field variables: { order_item: "$item", order_qty: "$ordered" } are used in the $match stage.

Regards,
Jason