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, 2017 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, 2017 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. Apr 21, 2017 at 15:54
  • @MappaGnosis updated the question
    – S. Thiyaku
    Apr 21, 2017 at 16:25

1 Answer 1

2
+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, 2017 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, 2017 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.