I'm trying to filter features from a feature table in a PostGIS enabled database with GeoTools.
My configuration:
Setup
I set up my postgis enabled database by executing these sql scripts in order:
Then I import some .osm data I extracted from europe.osm using OSMembrane.
Everything is fine, so far. All the tables contain some data. Then I try to read a feature table e.g. 'ways' which looks like this:
CREATE TABLE ways
(
id bigint NOT NULL,
"version" integer NOT NULL,
user_id integer NOT NULL,
tstamp timestamp without time zone NOT NULL,
changeset_id bigint NOT NULL,
tags hstore,
nodes bigint[],
CONSTRAINT pk_ways PRIMARY KEY (id)
)
Especially the 'tags' column contains key/values pairs I'd like to use for filtering. When trying to filter rows by "natural = coastline" in SQL I get ~550 resulting rows.
SELECT tags FROM ways where tags @> 'natural => coastline'
Example result: '"source"=>"PGS", "natural"=>"coastline", "created_by"=>"almien_coastlines"'
Trying this with GeoTools does not work as expected as this example will hopefully show you.
package getfeaturesapplication;
import java.util.HashMap;
import java.util.Map;
import org.geotools.data.DataStore;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.postgis.PostgisNGDataStoreFactory;
import org.geotools.data.simple.SimpleFeatureSource;
public class GetFeaturesApplication {
public static void main(String[] args) {
try {
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put(PostgisNGDataStoreFactory.DBTYPE.key, "postgis");
parameters.put(PostgisNGDataStoreFactory.HOST.key, "localhost");
parameters.put(PostgisNGDataStoreFactory.PORT.key, new Integer(5432));
parameters.put(PostgisNGDataStoreFactory.DATABASE.key, "postgis");
parameters.put(PostgisNGDataStoreFactory.SCHEMA.key, "public");
parameters.put(PostgisNGDataStoreFactory.USER.key, "osm");
parameters.put(PostgisNGDataStoreFactory.PASSWD.key, "osm");
DataStore dataStore = DataStoreFinder.getDataStore(parameters);
String featureName = "ways";
SimpleFeatureSource featureSource = dataStore.getFeatureSource(featureName); //=> WARNINGS
SimpleFeatureCollection features1 = featureSource.getFeatures();
System.out.println("Feature count: " + features1.size()); //406391
FilterFactory2 filterFactory = CommonFactoryFinder.getFilterFactory2(null);
Filter filter = filterFactory.equals(filterFactory.literal("natural"), filterFactory.literal("coastline"));
SimpleFeatureCollection features2 = featureSource.getFeatures(filter);
System.out.println("Features found after filtering: " + !features2.isEmpty()); //SEEMS TO BE ALWAYS EMPTY
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
When running this application I get:
31.01.2012 15:27:49 org.geotools.jdbc.JDBCFeatureSource buildFeatureType
WARNING: Could not find mapping for 'tags', ignoring the column and setting the feature type read only
31.01.2012 15:27:49 org.geotools.jdbc.JDBCFeatureSource buildFeatureType
WARNING: Could not find mapping for 'nodes', ignoring the column and setting the feature type read only
Feature count: 406391
Features found after filtering: false
Is there an issue with hstore and bigint[] columns or am I misusing GeoTools? Maybe, you can give me some hints.
I'm not sure that the GeoTools PostGIS reader supports hstore columns. Here are some notes I made on importing OSM data in PostGIS. My aim was to display them in GeoServer which uses the GeoTools datastore to read it. I split the data up by tag to make it work.