Using "IN" CQL function in Geoserver Cross-Layer Filtering

Question

I've two layers "cities" (has the fields: fid, code, and geom) and "cities_details" (has the fields: id, city_code, and area).

I need to filter "cities" layer with a condition applied on the "cities_details" layer. I believe that Geoserver's Cross-layer Filtering extension can do that.

The following CQL filter runs successfully:

code = querySingle('cities_details', 'city_code','area = 5000') It returns the city with area = 5000.

But the following CQL filter failed:

code IN (queryCollection('cities_details', 'city_code','area >= 5000'))

I tried also the following, but didn't work:

code IN (collectGeometries(queryCollection('cities_details', 'city_code','area >= 5000')))

In both cases the log tells: org.geoserver.platform.ServiceException: Could not parse CQL filter list

Can anybody help me, how to use "IN" with the Cross-Layer Filtering

Answer 1

That is because "IN" requires an explicit list of values, and given OGC Filter has no notion of "in", so something like a in (1, 2) translates down (before being evaluated) to a = 1 OR a = 2.

So your example translates down to code = queryCollection('cities_details', 'city_code','area >= 5000'), which of course does not work.

Now, to support multi-valued properties in complex features it's nowadays possible to do something like "collection = value", where the semantic is driven by a MatchAction that luckily for you defaults to "MatchAny", so if any item in the list matches the value, it should return true.

In other words, while I have not tried, it may be that using queryCollection('cities_details', 'city_code','area >= 5000') = code is going to work (was not really intended to, and as said I did not try, but there is a chance it might work for you).

If that does not work, the only other option that I can think of is to write java code and create a inList function that would accept a list of values, see this tutorial: http://docs.geotools.org/latest/userguide/tutorial/function.html