|
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
}}
|
