Hello,
I was willing to use the gt-postgis module to query a postgresql database
where the data are stored inside different schema, and was surprised by the
behaviour of the library in its current state.
First, the module is able to find every relevant tables no matter the
(database) schema it is stored into, but then trying to access the returned
typenames lead to SQL query errors as it generally does not explicitely
target the expected database schema.
Considering a database with the following structure (schema.table):
* nongeo.flup
* fo.armoires
* "savoie"."73-Savoie"
And the following pseudocode (it is actually valid groovy code):
import org.geotools.api.data.DataStoreFinder
import org.geotools.api.data.Query
import org.geotools.feature.NameImpl
def ds = DataStoreFinder.getDataStore([
"dbtype": "postgis",
"host": "localhost",
"port": 5432,
"database": "mel",
"user": "pmauduit",
"passwd": "secret",
])
println ds.getTypeNames()
ds.getTypeNames().each {
def fs = ds.getFeatureSource(it)
println "${fs.getCount(Query.ALL)} features in ${it}"
}
The call to getTypeNames() will return [armoires, flup, 73-savoie] which I
was expecting, meaning that the module was able to discover all the tables
of interest from my database, no matter the schema they are nested in.
But then the call to getFeatureSource() in the loop will fail, because the
table is not in the search_path of my user, leading to the following
warnings/errors:
WARNING: Failure occurred while looking up the primary key with finder:
org.geotools.jdbc.HeuristicPrimaryKeyFinder@4996c99
org.postgresql.util.PSQLException: ERROR: relation "armoires" does not exist
WARNING: Error occured determing srid for armoires.geom
[...]
org.postgresql.util.PSQLException: ERROR: relation "public.armoires" does
not exist
WARNING: Failed to retrieve information about public.armoires.geom by
examining the first sample geometry
org.postgresql.util.PSQLException: ERROR: relation "public.armoires" does
not exist
[...]
I then skimmed into the codebase, looking for a way to have versions of the
classes that would be "schema-agnostic". I stumbled upon the PostGisDialect
class which looked as a good start, but was not sufficient to circumvent my
issue. So I began to relax some code enforcement (removing final modifier
on the JDBCDataStore class, copy/pasting code to get more familiar with the
codebase and how objects interact each other, ...), and created a version
of the objects (e.g. SchemaAwareJDBCDataStore,
SchemaAwareJDBCFeatureSource). I also stumbled upon the class being used to
represent typenames (NameImpl), and figured out it was mainly composed of a
namespaceURI, a separator and a localName, each being a String. So I
wondered why not considering the namespaceURI could be in our case the name
of the postgresql schema the table belongs to, even if it is not a "URI"
per se ?
At this point, my current modifications are more of a PoC, but using a
custom parameter (schemaAware set to true in the params list), I could
switch to my custom version of the classes. As a result, using the
following pseudocode:
def ds = DataStoreFinder.getDataStore([
"dbtype": "postgis",
"host": "localhost",
"port": 5432,
"database": "mel",
"user": "pmauduit",
"passwd": "secret",
"schemaAware": true
])
ds.getTypeNames().each {
def fs = ds.getFeatureSource(it)
println "${fs.getCount(Query.ALL)} features in ${it}"
}
will produce the following output:
56 features in fo.armoires
4 features in nongeo.flup
305 features in savoie.73-savoie
But the getCount() is almost the only call working for now.
To summarize, before getting further, here are some questions I am asking
myself and would have liked to share with you / have your inputs on it:
* Is there any reason that I may have missed for the existing
implementation to react this way ?
* Am I on the right track with my approach and/or do you think I missed
some understandings on how the library is designed and works ?
* Would a custom JDBC datastore being able to be schema-agnostic of
interest for the project ?
Thanks in advance for your inputs,
Regards,
--
Pierre Mauduit
Ingénieur développement
Camptocamp France SAS 18 rue du Lac Saint André Savoie Technolac - Bâtiment
Le Dauphin F-73370 Le Bourget du Lac Tel. +33 (0)4 58 48 20 24 Fax +33(0)4
58 48 20 10 www.camptocamp.com
_______________________________________________
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel
