3

I'm trying to convert a query into uppercase in a GeoServer WFS request. According to the Filter Function reference this can be achieved using strToUpperCase().

This is working successfully on a sample GeoServer site where both of these queries return the expected result "STATE_NAME": "Illinois"

However, on my own GeoServer (version 2.10.2), an equivalent cql_filter (against a text field) works correctly without strToUpperCase:

http://apollo:8080/geoserver/loceng/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=loceng:rv_claims_lodgement_geo&CQL_FILTER=locality%3D%27Rozelle%27

... but fails with the following error when I include the strToUpperCase filter function:

http://apollo:8080/geoserver/loceng/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=loceng:rv_claims_lodgement_geo&CQL_FILTER=strToUpperCase(locality)%3D%27ROZELLE%27

<ServiceException>
   java.lang.RuntimeException: java.io.IOException java.io.IOException
   Cannot do natural order without a primary key, please add it or
   specify a manual sort over existing attributes
</ServiceException>

The layer's data source is a PostgreSQL table with the field defined as character varying, and there is an ascending index on that field.

Other layers in my local GeoServer, which are sourced from the same PostgreSQL instance, do not exhibit this error when I use strToUpperCase so the problem appears to be isolated to this layer/PostgreSQL table.

How can I debug this problem and allow the use of strToUpperCase on this dataset?

  • 1
    can you add the exact URLs you are requesting (use localhost and topp:states) - it looks like you have triggered a sort on the second request. Also does your table have a primary key? – Ian Turton Aug 28 '17 at 10:46
  • 1
    In addition to Ian's good suggestion, that demo server is also a 2.10.x, in particular, the nightly build of the day... did you try upgrading to the latest 2.10.x release? Also, try attaching the full stack trace to the report (you'll find it in geoserver.log) – Andrea Aime Aug 28 '17 at 12:01
  • @iant thanks for the tips, which set me on the right track. See my answer below for the resolution – Stephen Lead Aug 29 '17 at 0:05
2

It turns out there were two problems:

  • firstly I needed to add the Primary Key, as mentioned in the GeoServer error message
  • the second problem was with the character varying field definition in PostgreSQL. I changed this to text and GeoServer's strToUpperCase() now works correctly.
  • As an aside, I found that strToUpperCase() introduced a noticeable performance penalty, so I decided to pre-calculate a new field in the PostgreSQL table which was the uppercase value of my desired field and simply search this directly. Searching against this field was much faster – Stephen Lead Aug 29 '17 at 3:43
1

Why would you even use strToUpperCase there, if you just need case insensitive comparison, would it be easier to use strEqualsIgnoreCase from the docs you linked

strEqualsIgnoreCase a:String, b:String  Returns true if the two strings are equal ignoring case considerations
  • Thanks for the tip, I missed that function. strEqualsIgnoreCase. For simplicity, let's just say I want to get strToUpperCase working and leave out the case-insensitivity – Stephen Lead Aug 28 '17 at 2:02

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.