unexpected results using setMaxFeatures on RELATE query w/ DE9IM filter

Description

RELATE + DE-9IM matrix query and the setMaxFeatures method on postgis and it appears that setting the max features prevents returning proper results because the query to the database sets a limit, but the filter is executed locally...(so only run on the retrieved features) which leads to some unexpected results, eg. no features returned, but count says there should be.

I think the postgis filter should just pass the sql to he database, postgis has supported ST_relate since 1.5, but is not it shouldn't set a limit

some code (full code including data at: https://github.com/mprins/geotools-relate-test):

{{
final String filter = "RELATE(begrenzing_perceel,LINESTRING(63456 553056,103136 555872,140256 555872,182240 551264),1F20F1102)";

public void run() throws IOException, CQLException {
System.out.println("GeoTools version: " + GeoTools.getVersion());

Map<String, Object> params = new HashMap<>();
params.put("dbtype", "postgis");
params.put("host", "localhost");
params.put("port", 5433);
params.put("schema", "public");
params.put("database", "editable_data");
params.put("user", "mark_postgis_editing");
params.put("passwd", "mark_postgis_editing");
DataStore dataStore = DataStoreFinder.getDataStore(params);

FeatureSource fs = dataStore.getFeatureSource("editable");

System.out.println("feature count: " + fs.getCount(Query.ALL));

Query q = new Query(fs.getName().toString());

System.out.println("query: " + q.toString());

SimpleFeatureType ft = null;
setFilter(q, ft);
System.out.println("query: " + q.toString());
System.out.println("feature count: " + fs.getCount(q));

// setting this will prevent the result from being correct
// q.setMaxFeatures(5);
System.out.println("query: " + q.toString());
System.out.println("feature count: " + fs.getCount(q));

FeatureCollection fc = fs.getFeatures(q);
//System.out.println("feature collection count: " + fc.size());
System.out.println("feature collection is " + (fc.isEmpty() ? "not " : "") + "empty.");

Feature f;
FeatureIterator feats = fc.features();
while (feats.hasNext()) {
f = feats.next();
System.out.println(((Geometry) f.getDefaultGeometryProperty().getValue()).toText());
}
}

private void setFilter(Query q, SimpleFeatureType ft) throws CQLException {
if (filter != null && filter.trim().length() > 0) {
Filter f = CQL.toFilter(filter);
// f = (Filter) f.accept(new RemoveDistanceUnit(), null);
// f = (Filter) f.accept(new ChangeMatchCase(false), null);
// f = FeatureToJson.reformatFilter(f, ft);
q.setFilter(f);
}
}
}}

program output:

{{
GeoTools version: 9.5
feature count: 51
query: Query:
feature type: editable
filter: Filter.INCLUDE
[properties: ALL ]
query: Query:
feature type: editable
filter: [ relatePattern([begrenzing_perceel], [LINESTRING (63456 553056, 103136 555872, 140256 555872, 182240 551264)], [1F20F1102]) = true ]
[properties: ALL ]
feature count: 2
query: Query:
feature type: editable
filter: [ relatePattern([begrenzing_perceel], [LINESTRING (63456 553056, 103136 555872, 140256 555872, 182240 551264)], [1F20F1102]) = true ]
[properties: ALL ]
feature count: 2
feature collection is not empty.
MULTIPOLYGON (((167408 571152, 175856 527888, 122096 546832, 122608 568336, 167408 571152)))
MULTIPOLYGON (((96796.41214691175 567465.6786085605, 84248.45230810788 558031.0766402688, 95764.49246930401 545524.4746719771, 120302.92517941644 546402.7400264493, 120837.6686631641 568454.2093175049, 96796.41214691175 567465.6786085605)))
}}
database log:
{{
2015-05-28 15:50:05 CEST LOG: execute <unnamed>: SELECT TYPE FROM
geometry_columns WHERE F_TABLE_SCHEMA = 'public' AND F_TABLE_NAME = 'editable' AND f_geometry_column = 'begrenzing_perceel'
2015-05-28 15:50:05 CEST LOG: execute <unnamed>: select PostGIS_Lib_Version()
2015-05-28 15:50:05 CEST LOG: execute <unnamed>: SELECT SRID FROM
GEOGRAPHY_COLUMNS WHERE F_TABLE_SCHEMA = 'public' AND F_TABLE_NAME = 'editable' AND F_GEOGRAPHY_COLUMN = 'begrenzing_perceel'
2015-05-28 15:50:05 CEST LOG: execute <unnamed>: SELECT SRID FROM
GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = 'public' AND F_TABLE_NAME = 'editable' AND F_GEOMETRY_COLUMN = 'begrenzing_perceel'
2015-05-28 15:50:05 CEST LOG: execute <unnamed>: SELECT count FROM "public"."editable"
2015-05-28 15:50:05 CEST LOG: execute S_1: BEGIN
2015-05-28 15:50:05 CEST LOG: execute <unnamed>: SELECT
"id","text",encode(ST_AsEWKB("begrenzing_perceel"),'base64') as "begrenzing_perceel" FROM "public"."editable"
2015-05-28 15:50:05 CEST LOG: execute S_2: ROLLBACK
2015-05-28 15:50:05 CEST LOG: execute S_1: BEGIN
2015-05-28 15:50:05 CEST LOG: execute <unnamed>: SELECT
"id","text",encode(ST_AsEWKB("begrenzing_perceel"),'base64') as "begrenzing_perceel" FROM "public"."editable"
2015-05-28 15:50:05 CEST LOG: execute S_2: ROLLBACK
2015-05-28 15:50:05 CEST LOG: execute S_1: BEGIN
2015-05-28 15:50:05 CEST LOG: execute <unnamed>: SELECT
"id","text",encode(ST_AsEWKB("begrenzing_perceel"),'base64') as "begrenzing_perceel" FROM "public"."editable" LIMIT 1
2015-05-28 15:50:05 CEST LOG: execute S_2: ROLLBACK
2015-05-28 15:50:05 CEST LOG: execute S_1: BEGIN
2015-05-28 15:50:05 CEST LOG: execute <unnamed>: SELECT
"id","text",encode(ST_AsEWKB("begrenzing_perceel"),'base64') as "begrenzing_perceel" FROM "public"."editable"
2015-05-28 15:50:05 CEST LOG: unexpected EOF on client connection with an open transaction
}}

After setting q.setMaxFeatures(5) the results are:
{{
GeoTools version: 9.5
feature count: 51
query: Query:
feature type: editable
filter: Filter.INCLUDE
[properties: ALL ]
query: Query:
feature type: editable
filter: [ relatePattern([begrenzing_perceel], [LINESTRING (63456 553056, 103136 555872, 140256 555872, 182240 551264)], [1F20F1102]) = true ]
[properties: ALL ]
feature count: 2
query: Query:
feature type: editable
filter: [ relatePattern([begrenzing_perceel], [LINESTRING (63456 553056, 103136 555872, 140256 555872, 182240 551264)], [1F20F1102]) = true ]
[properties: ALL ]
feature count: 0
feature collection is not empty.
}}

{{
2015-05-28 15:47:42 CEST LOG: execute <unnamed>: SELECT TYPE FROM
geometry_columns WHERE F_TABLE_SCHEMA = 'public' AND F_TABLE_NAME = 'editable' AND f_geometry_column = 'begrenzing_perceel'
2015-05-28 15:47:43 CEST LOG: execute <unnamed>: select PostGIS_Lib_Version()
2015-05-28 15:47:43 CEST LOG: execute <unnamed>: SELECT SRID FROM
GEOGRAPHY_COLUMNS WHERE F_TABLE_SCHEMA = 'public' AND F_TABLE_NAME = 'editable' AND F_GEOGRAPHY_COLUMN = 'begrenzing_perceel'
2015-05-28 15:47:43 CEST LOG: execute <unnamed>: SELECT SRID FROM
GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = 'public' AND F_TABLE_NAME = 'editable' AND F_GEOMETRY_COLUMN = 'begrenzing_perceel'
2015-05-28 15:47:43 CEST LOG: execute <unnamed>: SELECT count FROM "public"."editable"
2015-05-28 15:47:43 CEST LOG: execute S_1: BEGIN
2015-05-28 15:47:43 CEST LOG: execute <unnamed>: SELECT
"id","text",encode(ST_AsEWKB("begrenzing_perceel"),'base64') as "begrenzing_perceel" FROM "public"."editable"
2015-05-28 15:47:43 CEST LOG: execute S_2: ROLLBACK
2015-05-28 15:47:43 CEST LOG: execute S_1: BEGIN
2015-05-28 15:47:43 CEST LOG: execute <unnamed>: SELECT
"id","text",encode(ST_AsEWKB("begrenzing_perceel"),'base64') as "begrenzing_perceel" FROM "public"."editable" LIMIT 5
2015-05-28 15:47:43 CEST LOG: execute S_2: ROLLBACK
2015-05-28 15:47:43 CEST LOG: execute S_1: BEGIN
2015-05-28 15:47:43 CEST LOG: execute <unnamed>: SELECT
"id","text",encode(ST_AsEWKB("begrenzing_perceel"),'base64') as "begrenzing_perceel" FROM "public"."editable" LIMIT 1
2015-05-28 15:47:43 CEST LOG: execute S_2: ROLLBACK
2015-05-28 15:47:43 CEST LOG: execute S_1: BEGIN
2015-05-28 15:47:43 CEST LOG: execute <unnamed>: SELECT
"id","text",encode(ST_AsEWKB("begrenzing_perceel"),'base64') as "begrenzing_perceel" FROM "public"."editable" LIMIT 5
2015-05-28 15:47:43 CEST LOG: unexpected EOF on client connection with an open transaction
}}

Environment

postgis 2.1.3 on postgres 9.4

Activity

Show:
Mark Prins
May 29, 2015, 7:47 AM
Andrea Aime
October 30, 2015, 8:07 AM

Hi Mark, I believe this one has been fixed by changes, could you verify?

Mark Prins
November 2, 2015, 9:04 AM

Yes, I can confirm this is fixed in the current snapshots, thanx .

Before:

After:

and:

Andrea Aime
November 2, 2015, 9:06 AM

Thanks for confirming!

Andrea Aime
February 15, 2017, 11:34 AM

Mass transitioning all resolved issues that have not been updated in the last month to closed state

Fixed

Assignee

Unassigned

Reporter

Mark Prins

Triage

None

Components

Fix versions

Affects versions

Priority

Medium
Configure