I've been experimenting with a 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 faatures returned, but count says there should be.
Q: is there any way to force geotools to send the complete query to
the database? which would be something like:
select * from editable where ST_relate(
begrenzing_perceel,
st_geomfromtext('LINESTRING(63712 553056,100576 556640,146912
555360,190176 557664)',28992),
'1F20F1102'
);
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
--
Disclaimer;
This message is just a reflection of what I thought at the time of
sending. The message may contain information that is not intended for
you or that you don't understand.
------------------------------------------------------------------------------
_______________________________________________
GeoTools-GT2-Users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geotools-gt2-users
