[GIS] CQL_FILTER not liking ‘%’ wildcards

cql-filtergeoserverwildcard

I am setting up a query against a layer on my GeoServer instance. My query string can become quite complex, and some statements require using multiple '%' wildcards.

For starter's here's an example:

Search for Property Owner: ___________

If the user types in 'Smith', I need to find values that meet the following criteria:

CQL_FILTER=Owner ilike '%Smith%'

However, this is returning all features in my layer.

In fact, when I use just one wildcard, ex. 'Smith%', I still get all features.

What makes this even more difficult is when my query string becomes quite lengthy:

CQL_FILTER=Owner ilike '%Smith%' AND Address ilike '%Main%' AND Value < 100000

Reading the GeoServer docs, I see the wildcard is supported in CQL/ECQL. The syntax is very close to SQL which I have used in the past to do the very same queries.

Anyone have an idea what may be causing this issue? Anyone else have much luck using wildcards in their CQL filters?

Thanks in advance!

Best Answer

You have not posted a complete example of the url you are building and sending to GeoServer so it is difficult to know for sure what the problem is. However, I suspect the problem lies in the use of the % for the wildcard.

In a url the % character has a special meaning as it is used when certain characters should be encoded. For example, you may well recall seeing %20 as part of a url in the past, this is an encoding for a space. If you are using the url method then you need to make sure your CQL_FILTER parameter is properly encoded. In the example you give it would need to be:

CQL_FILTER=Owner%20ilike%20%27%25Smith%25%27%20AND%20Address%20ilike%20%27%25Main%25%27%20AND%20Value%20%3C%20100000

The other thing to consider is case-sensitivity of your attribute names. If your data back-end cares about case then you need to make sure the case is correct in the CQL_FILTER. For example, if the data back-end is PostGIS then there would be a difference in result between Owner, OWNER, and owner. Likewise for the Value attribute.

Related Question