2

I want to filter columns in GeoServer WMS layer using 'ilike'. I tried following cql query.

"column_name" ilike 'string'

It works for columns of datatype string, not working for integer columns.

How to convert integer column into string using CQL ?

UPDATE

Usecase Example: Lets say I have two columns in a WMS layer such as one is address and another is zipcode. Both address and zip code has numbers in their column, but zipcode is of datatype integer and address is of datatype string. Below is sample column.

Column

address

338/4 -yyy,zzz.

444/3 - zxy

323 - abc.

zipcode

33431

385493

30293

Now I want to run CQL query to pick rows even if one column has a search string. My query for search string '38' is like follows.

Query

address ilike '38' or zipcode ilike '38'

For the above query I want the result which contains first and second rows.

Result

address

338/4 -yyy,zzz.

444/3 - zxy

zipcode

33431

385493

Like this many layers, and so many columns. So changing query for each column is not easy.

4
  • 3
    can you give example of using ilike with integer, as usually integer values can be queried using other functions. – Shiko Apr 21 '17 at 4:11
  • @Shiko I am trying to filter all columns in a layer using string/number. I feel if I convert all columns to string it will be easy. – S. Thiyaku Apr 21 '17 at 15:04
  • To press Shiko's point: do you want to query using ilike so you can find all features where (say) my_column contains (say) '3' - so you would find 123, 345, 3, 78963 etc or do you want to find values of a similar value (i.e. 2 and 4 in this case). If is the latter ilike will not help you. Please edit your question to show an example as your use-case seems rather odd as stated. – MappaGnosis Apr 21 '17 at 15:54
  • @MappaGnosis updated the question – S. Thiyaku Apr 21 '17 at 16:25
1
+50

Create a view into your database and cast numbers to strings with to_char https://www.postgresql.org/docs/9.3/static/functions-formatting.html. Publish the view with GeoServer and your numbers should be seachable with ilike.

2
  • Is it possible to automatically create and publish view every time I upload a layer ? – S. Thiyaku Apr 24 '17 at 15:03
  • I suppose that with some work yes. You can create the view and add metadata into geometry_columns with PSQL. The publishing with GeoServer feels like a simplified version of this REST workflow boundlessgeo.com/2012/10/…. – user30184 Apr 24 '17 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.