GeoServer – INTERSECTS CQL Filter Only Looks at One Polygon in Multipolygon

cql-filtergeoserver

I have a pretty typical store called region which contains various multipolygons such as countries, regions within countries, multinational regions such as EU, and so on. Some of these are single polygons and some are multipolygons (although all are typed as multipolygons).

When I run an intersecting query on a point like this:

`http://localhost:6060/geoserver/ows?service=WFS&request=GetFeature&version=1.0.0&typeName=geonode:region&outputFormat=application/json&CQL_FILTER=INTERSECTS(wkb_geometry, POINT (${longitude} ${lattitude}))`

I only obtain the geometries whose first polygon in its multipolygon is intersecting with the specified point.

Specific example: a point within most regions of Denmark will not return Denmark the country, but a point specifically within the 'Bornholm' polygon will return Denmark.

Is there a way to make geoserver make the comparison across all the polygons in the multipolygons? I have looked around quite a bit, and can not find anyone else running into this problem.

The data is stored in a PostGIS 3 database

Please let me know if I should post any further information regarding the geoserver/GIS setup.

Relevant part of log file:

Request: getFeature
    service = WFS
    version = 1.0.0
    baseUrl = http://127.0.0.1:8080/geoserver/
    query[0]:
        filter = [ wkb_geometry intersects POINT (10.204992014900563 55.322133488535314) ]
        typeName[0] = {http://www.geonode.org/}region
    outputFormat = application/json
    resultType = results
2022-04-08 10:40:58,980 INFO [org.geoserver.wfs.json] - about to encode JSON
2022-04-08 10:40:58,981 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
2022-04-08 10:40:58,981 DEBUG [org.geotools.data.jdbc] - exporting PropertyName
2022-04-08 10:40:58,981 DEBUG [org.geotools.data.jdbc] - exporting LiteralExpression
2022-04-08 10:40:58,981 TRACE [org.geotools.data.sqlserver.jtds] - Target not a string
2022-04-08 10:40:58,981 TRACE [org.geotools.data.sqlserver.jtds] - Target is not a Date
2022-04-08 10:40:58,981 DEBUG [org.geotools.data.util] - CRSConverterFactory can be applied from Strings to CRS  only.
2022-04-08 10:40:58,981 DEBUG [org.geotools.data.util] - InterpolationConverterFactory can be applied from Strings to Interpolation only.
2022-04-08 10:40:58,981 DEBUG [org.geotools.data.jdbc] - exporting PropertyName
2022-04-08 10:40:58,981 DEBUG [org.geotools.data.jdbc] - exporting LiteralExpression
2022-04-08 10:40:58,981 TRACE [org.geotools.data.sqlserver.jtds] - Target not a string
2022-04-08 10:40:58,981 TRACE [org.geotools.data.sqlserver.jtds] - Target is not a Date
2022-04-08 10:40:58,981 DEBUG [org.geotools.data.util] - CRSConverterFactory can be applied from Strings to CRS  only.
2022-04-08 10:40:58,981 DEBUG [org.geotools.data.util] - InterpolationConverterFactory can be applied from Strings to Interpolation only.
2022-04-08 10:40:58,981 DEBUG [org.geotools.jdbc] - SELECT count(*) FROM "public"."region" WHERE "wkb_geometry" && ST_GeomFromText('POINT (10.204992014900563 55.322133488535314)', 4326) AND ST_Intersects("wkb_geometry", ST_GeomFromText('POINT (10.204992014900563 55.322133488535314)', 4326))
2022-04-08 10:40:58,982 TRACE [org.geotools.data.sqlserver.jtds] - Target not a string
2022-04-08 10:40:58,982 TRACE [org.geotools.data.sqlserver.jtds] - Target is not a Date
2022-04-08 10:40:58,982 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
2022-04-08 10:40:58,982 DEBUG [org.geotools.jdbc] - CREATE CONNECTION
2022-04-08 10:40:58,983 DEBUG [org.geotools.data.jdbc] - exporting PropertyName
2022-04-08 10:40:58,983 DEBUG [org.geotools.data.jdbc] - exporting LiteralExpression
2022-04-08 10:40:58,983 TRACE [org.geotools.data.sqlserver.jtds] - Target not a string
2022-04-08 10:40:58,983 TRACE [org.geotools.data.sqlserver.jtds] - Target is not a Date
2022-04-08 10:40:58,983 DEBUG [org.geotools.data.util] - CRSConverterFactory can be applied from Strings to CRS  only.
2022-04-08 10:40:58,983 DEBUG [org.geotools.data.util] - InterpolationConverterFactory can be applied from Strings to Interpolation only.
2022-04-08 10:40:58,983 DEBUG [org.geotools.data.jdbc] - exporting PropertyName
2022-04-08 10:40:58,983 DEBUG [org.geotools.data.jdbc] - exporting LiteralExpression
2022-04-08 10:40:58,983 TRACE [org.geotools.data.sqlserver.jtds] - Target not a string
2022-04-08 10:40:58,983 TRACE [org.geotools.data.sqlserver.jtds] - Target is not a Date
2022-04-08 10:40:58,983 DEBUG [org.geotools.data.util] - CRSConverterFactory can be applied from Strings to CRS  only.
2022-04-08 10:40:58,983 DEBUG [org.geotools.data.util] - InterpolationConverterFactory can be applied from Strings to Interpolation only.
2022-04-08 10:40:58,983 DEBUG [org.geotools.jdbc] - SELECT "ogc_fid","gml_id","name","country",encode(ST_AsEWKB("wkb_geometry"), 'base64') as "wkb_geometry" FROM "public"."region" WHERE "wkb_geometry" && ST_GeomFromText('POINT (10.204992014900563 55.322133488535314)', 4326) AND ST_Intersects("wkb_geometry", ST_GeomFromText('POINT (10.204992014900563 55.322133488535314)', 4326)) LIMIT 1000000
2022-04-08 10:40:59,048 DEBUG [org.geotools.jdbc] - CLOSE CONNECTION
2022-04-08 10:40:59,049 INFO [org.geoserver.flow] - releasing flow controllers for [WFS 1.0.0 GetFeature]
2022-04-08 10:40:59,049 INFO [org.geoserver.flow] - Request completed, running requests: 0, blocked requests: 0
2022-04-08 10:40:59,050 TRACE [org.geotools.data.sqlserver.jtds] - Target not a string
2022-04-08 10:40:59,050 TRACE [org.geotools.data.sqlserver.jtds] - Target is not a Date

Best Answer

With Ian Turton's and Andrea Aime's help in the comments I figured out how to fix it. The problem was indeed data validity, and I was able to fix it in the following way:

update region
set wkb_geometry = ST_multi(st_collectionextract(ST_MakeValid(wkb_geometry),3))
where not ST_IsValid(region.wkb_geometry);

The problem was numerous holes outside shell errors as well as a single randomly included geography collection (in spite the column type being multipolygon).

Thank you so much for your help!

Related Question