[Geoserver-users] WPS gs:Aggregate and strMatches problem

emmexx · June 9, 2022, 7:48am

I want to use WPS Aggregation process to get a pivot table based on some
attribute for a subset of the features of a layer.
[https://docs.geoserver.org/latest/en/user/services/wps/processes/gs.html#aggregation-process]

I started with a simple gs:CollectGeometries:

<?xml version="1.0" encoding="UTF-8"?><wps:Execute version="1.0.0"
service="WPS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.opengis.net/wps/1.0.0"
xmlns:wfs="http://www.opengis.net/wfs"
xmlns:wps="http://www.opengis.net/wps/1.0.0"
xmlns:ows="http://www.opengis.net/ows/1.1"
xmlns:gml="http://www.opengis.net/gml"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:wcs="http://www.opengis.net/wcs/1.1.1"
xmlns:xlink="http://www.w3.org/1999/xlink"
xsi:schemaLocation="http://www.opengis.net/wps/1.0.0
http://schemas.opengis.net/wps/1.0.0/wpsAll.xsd">
  <ows:Identifier>gs:CollectGeometries</ows:Identifier>
  <wps:DataInputs>
    <wps:Input>
      <ows:Identifier>features</ows:Identifier>
      <wps:Reference mimeType="text/xml"
xlink:href="http://geoserver/wfs" method="POST">
        <wps:Body>
          <wfs:GetFeature service="WFS" version="1.0.0"
outputFormat="GML2" xmlns:bi="http://bi.org">
            <wfs:Query typeName="bi:ways">
        <ogc:PropertyIsEqualTo>
      <ogc:Function name="strMatches">
            <ogc:PropertyName>myField</ogc:PropertyName>
        <ogc:Literal>.*\b(131)\b.*</ogc:Literal>
      </ogc:Function>
      <ogc:Literal>true</ogc:Literal>
        </ogc:PropertyIsEqualTo>
        </wfs:Query>
          </wfs:GetFeature>
        </wps:Body>
      </wps:Reference>
    </wps:Input>
  </wps:DataInputs>
  <wps:ResponseForm>
    <wps:RawDataOutput mimeType="text/xml; subtype=gml/3.1.1">
      <ows:Identifier>result</ows:Identifier>
    </wps:RawDataOutput>
  </wps:ResponseForm>
</wps:Execute>

It works and I get a gml:MultiLineString with the features of the subset
as a result.

So I try the aggregate function with a simple filter:

<?xml version="1.0" encoding="UTF-8"?><wps:Execute version="1.0.0"
service="WPS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.opengis.net/wps/1.0.0"
xmlns:wfs="http://www.opengis.net/wfs"
xmlns:wps="http://www.opengis.net/wps/1.0.0"
xmlns:ows="http://www.opengis.net/ows/1.1"
xmlns:gml="http://www.opengis.net/gml"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:wcs="http://www.opengis.net/wcs/1.1.1"
xmlns:xlink="http://www.w3.org/1999/xlink"
xsi:schemaLocation="http://www.opengis.net/wps/1.0.0
http://schemas.opengis.net/wps/1.0.0/wpsAll.xsd">
  <ows:Identifier>gs:Aggregate</ows:Identifier>
  <wps:DataInputs>
    <wps:Input>
      <ows:Identifier>features</ows:Identifier>
      <wps:Reference mimeType="text/xml"
xlink:href="http://geoserver/wfs" method="POST">
        <wps:Body>
          <wfs:GetFeature service="WFS" version="1.0.0"
outputFormat="GML2" xmlns:bicitalia="http://bi.org">
            <wfs:Query typeName="bi:ways">
                  <ogc:Filter>
                <ogc:PropertyIsEqualTo>
            <ogc:PropertyName>myField</ogc:PropertyName>
              <ogc:Literal>131</ogc:Literal>
           <ogc:Literal>true</ogc:Literal>
        </ogc:PropertyIsEqualTo>
                  </ogc:Filter>

        </wfs:Query>
          </wfs:GetFeature>
        </wps:Body>
      </wps:Reference>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>aggregationAttribute</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>length</wps:LiteralData>
      </wps:Data>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>function</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>Sum</wps:LiteralData>
      </wps:Data>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>singlePass</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>false</wps:LiteralData>
      </wps:Data>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>groupByAttributes</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>surface</wps:LiteralData>
      </wps:Data>
    </wps:Input>
     <wps:Input>
      <ows:Identifier>groupByAttributes</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>width</wps:LiteralData>
      </wps:Data>
    </wps:Input>
  </wps:DataInputs>
  <wps:ResponseForm>
    <wps:RawDataOutput mimeType="application/json">
      <ows:Identifier>result</ows:Identifier>
    </wps:RawDataOutput>
  </wps:ResponseForm>
</wps:Execute>

It works as expected.

Now I dare to use strMatches with the aggregate function:

<?xml version="1.0" encoding="UTF-8"?><wps:Execute version="1.0.0"
service="WPS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.opengis.net/wps/1.0.0"
xmlns:wfs="http://www.opengis.net/wfs"
xmlns:wps="http://www.opengis.net/wps/1.0.0"
xmlns:ows="http://www.opengis.net/ows/1.1"
xmlns:gml="http://www.opengis.net/gml"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:wcs="http://www.opengis.net/wcs/1.1.1"
xmlns:xlink="http://www.w3.org/1999/xlink"
xsi:schemaLocation="http://www.opengis.net/wps/1.0.0
http://schemas.opengis.net/wps/1.0.0/wpsAll.xsd">
  <ows:Identifier>gs:Aggregate</ows:Identifier>
  <wps:DataInputs>
    <wps:Input>
      <ows:Identifier>features</ows:Identifier>
      <wps:Reference mimeType="text/xml"
xlink:href="http://geoserver/wfs" method="POST">
        <wps:Body>
          <wfs:GetFeature service="WFS" version="1.0.0"
outputFormat="GML2" xmlns:bicitalia="http://bi.org">
            <wfs:Query typeName="bi:ways">
                  <ogc:Filter>
                <ogc:PropertyIsEqualTo>

           <ogc:Function name="strMatches">
            <ogc:PropertyName>myField</ogc:PropertyName>
            <ogc:Literal>.*\b(131)\b.*</ogc:Literal>
           </ogc:Function>
           <ogc:Literal>true</ogc:Literal>
        </ogc:PropertyIsEqualTo>
                  </ogc:Filter>

        </wfs:Query>
          </wfs:GetFeature>
        </wps:Body>
      </wps:Reference>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>aggregationAttribute</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>length</wps:LiteralData>
      </wps:Data>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>function</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>Sum</wps:LiteralData>
      </wps:Data>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>singlePass</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>false</wps:LiteralData>
      </wps:Data>
    </wps:Input>
    <wps:Input>
      <ows:Identifier>groupByAttributes</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>surface</wps:LiteralData>
      </wps:Data>
    </wps:Input>
     <wps:Input>
      <ows:Identifier>groupByAttributes</ows:Identifier>
      <wps:Data>
        <wps:LiteralData>width</wps:LiteralData>
      </wps:Data>
    </wps:Input>
  </wps:DataInputs>
  <wps:ResponseForm>
    <wps:RawDataOutput mimeType="application/json">
      <ows:Identifier>result</ows:Identifier>
    </wps:RawDataOutput>
  </wps:ResponseForm>
</wps:Execute>

Now I get an error:

<wps:ExecuteResponse xml:lang="en" service="WPS"
serviceInstance="https://bi.org/geoserver/ows?" version="1.0.0">
<wps:Process wps:processVersion="1.0.0">
<ows:Identifier>gs:Aggregate</ows:Identifier>
<ows:Title>Aggregate</ows:Title>
<ows:Abstract>
Computes one or more aggregation functions on a feature attribute.
Functions include Count, Average, Max, Median, Min, StdDev, and Sum.
</ows:Abstract>
</wps:Process>
<wps:Status creationTime="2022-06-09T07:44:02.223Z">
<wps:ProcessFailed>
<ows:ExceptionReport version="1.1.0">
<ows:Exception exceptionCode="NoApplicableCode">
<ows:ExceptionText>
Process failed during execution java.io.IOExceptionERROR: function
strmatches(character varying, unknown) does not exist Hint: No function
matches the given name and argument types. You might need to add
explicit type casts. Position: 108
</ows:ExceptionText>
</ows:Exception>
</ows:ExceptionReport>
</wps:ProcessFailed>
</wps:Status>
</wps:ExecuteResponse>

Why is that? Why does strMatches work with gs:CollectGeometries but not
with gs:Aggregate?

Any clue?

thank you
maxx

ianturton · June 9, 2022, 10:19am

That looks like an SQL error message - can you turn on GEOTOOLS-DEVELOPER logging and try again, then post the relevant part of the log file - it should include the actual SQL being sent to the database.
And if you could mention the type of database and the version of GeoServer you are using that will help too,

Ian

···

Ian Turton

emmexx · June 9, 2022, 1:59pm

On 6/9/22 12:19 PM, Ian Turton wrote:

That looks like an SQL error message - can you turn on
GEOTOOLS-DEVELOPER logging and try again, then post the relevant part of
the log file - it should include the actual SQL being sent to the database.
And if you could mention the type of database and the version of
GeoServer you are using that will help too,

I Attach the relevant log.

Build Information

    GeoServer Version
    2.19.0
    Git Revision
    24c5d0b5a1b170f09e383ba35aae63f2ae9a2925
    Build Date
    18-Mar-2021 14:59
    GeoTools Version
    25.0 (rev d187663948b47212514991a71c0fc3021908b034)
    GeoWebCache Version
    1.19.0 (rev 1.19.x/c3b897ec57838c49f4cbb325c1e9a7678f9edd98)

PostgreSQL 9.2.24

postgis_version()
2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

I tested the query that you can find in the log and, of course, it
doesn't work because postgresql has not a strMatches function.
In my notes about filters for my tables I found the equivalent of
strMatches:

Select * from myTable where myField ~ '.*\y(131)\y.*'

But probably you already know that.

Let me know if you need other info.

Thank you
maxx

wps-strMatches.txt (9.34 KB)

ianturton · June 9, 2022, 2:33pm

That looks like a bug, the JDBCStore should not be passing strMatches to Postgres without modifying it or (more likely) handling it in code after the results are returned. Can you file a bug report at https://osgeo-org.atlassian.net/jira/software/c/projects/GEOS including the WPS you are sending that fails and that log file extract.

If you need a workaround in the mean time a Like filter should be able to give you the same functionality,

Ian

···

Ian Turton

ianturton · June 9, 2022, 4:00pm

I went ahead a made a report - https://osgeo-org.atlassian.net/browse/GEOT-7161

Ian

···

Ian Turton

emmexx · June 9, 2022, 4:51pm

On 6/9/22 6:00 PM, Ian Turton wrote:

I went ahead a made a report -
https://osgeo-org.atlassian.net/browse/GEOT-7161

Thank you.

And thank you for the Like workaround but I'm not sure it can work in my
case.

maxx

ianturton · June 14, 2022, 7:29am

This should now be fixed in the nightly builds.

Ian

···

Ian Turton