How to use one of the current document field as coordinates in $geoIntersects query

Working with Data
,
1

I’m trying to create an aggregation pipeline to intersect some shapes with some others. a simplified pipeline is like this:

[
   {
    '$match': {
      'loc.type': {
        '$eq': 'Polygon'
      }
    }
  }, {
    '$addFields': {
      'cor': [
        [
          -11.337890625, 56.31653672211301
        ], [
          13.1396484375, 42.8115217450979
        ]
      ]
    }
  }, {
    '$match': {
      'loc': {
        '$geoIntersects': {
          '$geometry': {
            'type': 'LineString', 
            'coordinates': '$cor'
          }
        }
      }
    }
  }
]

At the first step, I selected every polygon shape from a collection, in the next stage added some coordinates to it, and at the end match the polygon with the shape added in stage 2. the problem is in stage 3, I can’t fill the coordinates field with the cor field created in stage 2.

the error I get is: GeoJSON coordinates must be an array of coordinates.

another similar problem is this:

[
  {
    '$match': {
      'loc.type': {
        '$eq': 'Polygon'
      }
    }
  }, {
    '$addFields': {
      'myshape': {
        'type': 'LineString', 
        'coordinates': [
          [
            -11.337890625, 56.31653672211301
          ], [
            13.1396484375, 42.8115217450979
          ]
        ]
      }
    }
  }, {
    '$match': {
      'loc': {
        '$geoIntersects': {
          '$geometry': '$myshape'
        }
      }
    }
  }
]

here in stage 2, I created a complete GeoJson shape (field myshape ). I also can’t use myshape as the shape for $geometry . here the error I get is: unknown geo specifier: $geometry: "$myshape" .

how I can use a field from the current document as value for the fields $geometry or $geometry.coordinates ?

2

Hi @ciyeh_c - Welcome to the community :wave:

In your first pipeline example:

the error I get is: GeoJSON coordinates must be an array of coordinates.

I believe this error is due to the 3rd $match stage, specifically the 'coordinates': '$cor' reference in which the coordinates need to be a GeoJSON coordinate and not a variable reference.

However, in saying so - could you advise the use case for using $addFields in stage 2? The appropriate documents can be returned if you set the "coordinates" value in stage 3 to the co-ordinates provided in the $addFields stage. Please refer to the example below where the second stage with $addFields is not used:

[primary] geodb> stage1
{ '$match': { 'location.type': { '$eq': 'Polygon' } } }
[primary] geodb> stage3
{
  '$match': {
    location: {
      '$geoIntersects': {
        '$geometry': { type: 'LineString', coordinates: [ [ 1, 4 ], [ 5, -1 ] ] }
      }
    }
  }
}
[primary] geodb> db.geocoll.aggregate([stage1,stage3])
[
  {
    _id: ObjectId("61b9568490d396c8ef8d4e6e"),
    location: {
      type: 'Polygon',
      coordinates: [ [ [ 0, 0 ], [ 3, 6 ], [ 6, 1 ], [ 0, 0 ] ] ]
    }
  }
]
[primary] geodb>

here in stage 2, I created a complete GeoJson shape (field myshape ). I also can’t use myshape as the shape for $geometry . here the error I get is: unknown geo specifier: $geometry: "$myshape" .

For your second pipeline example, a similar scenario is happening in stage 3 of the pipeline ($match stage), specifically the $geometry value, expects a document in the form below rather than a variable reference of $myshape:

{
   type: "<GeoJSON object type>",
   coordinates: [ <coordinates> ]
}

Additionally, if loc.type is not indexed then a COLLSCAN would occur (scanning whole collection, document by document, to return matched documents).

Please note in the above example I am using MongoDB version 4.4.10 on Atlas so the error messages may differ depending on your version.

Regards,
Jason

3

Hi @Jason_Tran , thanks for replying

the actual work in stage 2 is more complicated than what I mentioned and I tried to simulate the final result in a simplified manner to be easy to comprehend. in the real scenario, the coordinates come from another collection and include a $lookup and $unwind, so I simplified that.

[
    {
        '$match': {
          'loc.type': {
            '$eq': 'Polygon'
          }
        }
    },
    {
      '$lookup': {
        'from': 'Routes', 
        ...
        'as': 'route'
      }
    }, {
      '$unwind': {
        'path': '$route'
      }
    }, {
      '$match': {
        'loc': {
          '$geoIntersects': {
            '$geometry':{
                'type': 'LineString', 
                'coordinates': '$$ROOT.route.position'
            }
          }
        }
      }
    }
]

this is the actual (incomplete of course because of the problem) pipeline i stuck with if it makes any difference.

so if it is not possible to use reference as the coordinates value, is there an alternate method to do this?

4

Hi @ciyeh_c,

Thank you for providing the more comprehensive version of the aggregation for clarification.

Unfortunately the use of variable references are only available in certain pipeline stages and the stage that it’s being used in for in this particular case does not allow variable references to be used. However, I believe the behaviour you’re describing is being tracked on and worked on in SERVER-34766. Feel free to add your use case to the ticket as well.

In the meantime, you may need to split your query into 2 parts: one query to get the necessary route.position values, and another query containing only the $geoIntersects stage to get the final result.

Regards,
Jason

2 Likes