2

I'm running a query on a GeoServer WFS layer, using a CQL_FILTER. This results in duplicates, whereas I only want to see one instance of each matching result.

The query I'm running is:

&CQL_FILTER=name=%27BLAH%27&propertyName=repairer_name

which returns multiple instances of "CITY MOTORS":

enter image description here

Is it possible to use a SELECT DISTINCT statement in the CQL_FILTER, so that only one instance of "CITY MOTORS" is returned?

For example, in PGAdmin this query returns the results I need:

enter image description here

I can't see anything in the Filter Function Reference or ECQL Reference pages showing the syntax for using SELECT DISTINCT, and the only other question seems unrelated.

  • could or should this be published as a postgis view instead? but maybe its valid to do this in geoserver and should work...? – DPSSpatial Nov 15 '17 at 1:52
  • It's a good idea, and I'm separately looking into the PG view approach. The problem is that I have other queries to append onto this one, so I think the view(s) will become unmanageable. The dataset is complex and unfortunately I can't do much to change it. – Stephen Lead Nov 15 '17 at 2:01
  • In that case I recommend the 'materialized view'... !!! – DPSSpatial Nov 15 '17 at 4:19
3

The WFS specification does not allow aggregation, the filter, regardless of the language used is the equivalent of a "where" clause only. If you need aggregation you'll have to combine WFS with WPS, do the filtering in a WFS request and then pipe it into a "Aggregate" process.

Try using the "WPS request builder" to setup a template of your layer of choice (and then edit it, adding the filters you want into t), if expressed that way GeoServer should be smart enough to turn the whole thing into a single SQL query.

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.