[Geoserver-devel] Creating feature types from SQL queries: a plan for GeoServer

GeoServer GeoServer Developer
1

Hi,
I'm looking into how to integrate the sql query to feature type
support in GeoServer.

At first I was thinking to use a datastore parameter structured like
a property file, but it's a quite clunky approach.
The definition in the property file would have been something like:

virt=select a, b, c, the_geom from realtable where a > 3
virt.pk=a,b
virt.geom.the_geom=POINT,4326

Basically it's the query, plus information about which attributes
are part of the primary key, plus information about what type the
geometry is.

Instead of this I would like to have a configuration GUI (and
one less parameter in the never-ending list of params for JDBC
data stores).

First approach: starting from the "new layer" panel
-------------------------------------------------------

Ideally, a user would go to the layer list, choose "new layer",
and upon choosing a JDBC data store he would be presented with
the option to create a feature type by SQL using a SQL query
as the definition of it.

The next page would provide the user a text area in which he
could edit the query, and a "test" button which would both
test the query for validity and provide a GUI to setup
the primary key fields and the geometry details (more
or less a table of the attributes, with name, type,
a checkbox to make a field part of the pk and the possibility
to edit what we know about the geometry).

Past that the user would get into the usual layer page, which
would sport a new panel, equal to the contents of the previous
page, to allow editing of the sql and details.

Implementation wise I would like to store the sql query as part
of the FeatureTypeInfo metadata map.

Now, the issue is, what do we do on restart? We'd need to
configure the sql in the data store again, and to do so
we'd need either the resource pool to do so, making it aware
of the sql definitions, or a callback that would configure
the existing sql feature types into it by inspecting the
FeatureTypeInfo associated to the store.

Second approach: starting from the datastore config panel
---------------------------------------------------------

In this case, we make a custom GUI to configure the JDBC data
stores that has a list of the existing sql definitions
and allows to add/edit/remove them.

On add the workflow would be the same as above, but we
won't show an edit panel in the existing layer definition.

The set of sql definitions would be stored in the property
file like parameter I described at the beginning of the mail,
we'd just provide a nicer GUI for it.

The upside is that we would not need to touch the ResourcePool,
the downside is that the workflow would be less natural
to the user imho, especially now that we're about to add the
ability to create a new feature type in the "new layer" page
as well (see GSIP 47)

Oh, in any case the work would be done on trunk only.

What do you think?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

2

Andrea could you do this in a similar manner to app schema and the feature generalisation data stores.

It would be a new DataStore; that would look up other JDBCDataStores and use their connection pool and dialect to issue SQL queries; process the result set etc... In effect it each FeatureSource defined would act "view" defined against a couple new methods you would need to add to the JDBCDataStore base class in order to issue SQL.

Perhaps this is similar to what you are already describing?

Another approach would be to define a new table in the database; which could be used to hold the definitions; not sure if that has any appeal? You could still create a UI for it; but it would be a custom UI much like for ArcSDEDataStore.

Jody

On 28/04/2010, at 11:46 PM, Andrea Aime wrote:

Hi,
I'm looking into how to integrate the sql query to feature type
support in GeoServer.

At first I was thinking to use a datastore parameter structured like
a property file, but it's a quite clunky approach.
The definition in the property file would have been something like:

virt=select a, b, c, the_geom from realtable where a > 3
virt.pk=a,b
virt.geom.the_geom=POINT,4326

Basically it's the query, plus information about which attributes
are part of the primary key, plus information about what type the
geometry is.

Instead of this I would like to have a configuration GUI (and
one less parameter in the never-ending list of params for JDBC
data stores).

First approach: starting from the "new layer" panel
-------------------------------------------------------

Ideally, a user would go to the layer list, choose "new layer",
and upon choosing a JDBC data store he would be presented with
the option to create a feature type by SQL using a SQL query
as the definition of it.

The next page would provide the user a text area in which he
could edit the query, and a "test" button which would both
test the query for validity and provide a GUI to setup
the primary key fields and the geometry details (more
or less a table of the attributes, with name, type,
a checkbox to make a field part of the pk and the possibility
to edit what we know about the geometry).

Past that the user would get into the usual layer page, which
would sport a new panel, equal to the contents of the previous
page, to allow editing of the sql and details.

Implementation wise I would like to store the sql query as part
of the FeatureTypeInfo metadata map.

Now, the issue is, what do we do on restart? We'd need to
configure the sql in the data store again, and to do so
we'd need either the resource pool to do so, making it aware
of the sql definitions, or a callback that would configure
the existing sql feature types into it by inspecting the
FeatureTypeInfo associated to the store.

Second approach: starting from the datastore config panel
---------------------------------------------------------

In this case, we make a custom GUI to configure the JDBC data
stores that has a list of the existing sql definitions
and allows to add/edit/remove them.

On add the workflow would be the same as above, but we
won't show an edit panel in the existing layer definition.

The set of sql definitions would be stored in the property
file like parameter I described at the beginning of the mail,
we'd just provide a nicer GUI for it.

The upside is that we would not need to touch the ResourcePool,
the downside is that the workflow would be less natural
to the user imho, especially now that we're about to add the
ability to create a new feature type in the "new layer" page
as well (see GSIP 47)

Oh, in any case the work would be done on trunk only.

What do you think?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------------
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

3

Jody Garnett ha scritto:

Andrea could you do this in a similar manner to app schema and the
feature generalisation data stores.

It would be a new DataStore; that would look up other JDBCDataStores
and use their connection pool and dialect to issue SQL queries;
process the result set etc... In effect it each FeatureSource defined
would act "view" defined against a couple new methods you would need
to add to the JDBCDataStore base class in order to issue SQL.

How would I configure the new datastore? The GUI would be the same no,
the two options would still apply... I see no improvement, just
an extra indirection.

Another approach would be to define a new table in the database;
which could be used to hold the definitions; not sure if that has any
appeal? You could still create a UI for it; but it would be a custom
UI much like for ArcSDEDataStore.

I don't see a particular appeal, if the user can manipulate the database
he can also create a view and use the current support for views.

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

4

I think either solution seems good to me but indeed I agree that the first approach seems quite nicer from a workflow standpoint.

As for the restart issue the GEoServerInitializer interface gives you the callback you need. The question is how does it know what feature types correspond to an sql query without doing a full scan of the catalog? Perhaps maybe storing some metadata on the DataStoreInfo instance as well, a list of the feature type names configured via SQL query perhaps.

2c.

On 4/28/10 7:46 AM, Andrea Aime wrote:

Hi,
I'm looking into how to integrate the sql query to feature type
support in GeoServer.

At first I was thinking to use a datastore parameter structured like
a property file, but it's a quite clunky approach.
The definition in the property file would have been something like:

virt=select a, b, c, the_geom from realtable where a> 3
virt.pk=a,b
virt.geom.the_geom=POINT,4326

Basically it's the query, plus information about which attributes
are part of the primary key, plus information about what type the
geometry is.

Instead of this I would like to have a configuration GUI (and
one less parameter in the never-ending list of params for JDBC
data stores).

First approach: starting from the "new layer" panel
-------------------------------------------------------

Ideally, a user would go to the layer list, choose "new layer",
and upon choosing a JDBC data store he would be presented with
the option to create a feature type by SQL using a SQL query
as the definition of it.

The next page would provide the user a text area in which he
could edit the query, and a "test" button which would both
test the query for validity and provide a GUI to setup
the primary key fields and the geometry details (more
or less a table of the attributes, with name, type,
a checkbox to make a field part of the pk and the possibility
to edit what we know about the geometry).

Past that the user would get into the usual layer page, which
would sport a new panel, equal to the contents of the previous
page, to allow editing of the sql and details.

Implementation wise I would like to store the sql query as part
of the FeatureTypeInfo metadata map.

Now, the issue is, what do we do on restart? We'd need to
configure the sql in the data store again, and to do so
we'd need either the resource pool to do so, making it aware
of the sql definitions, or a callback that would configure
the existing sql feature types into it by inspecting the
FeatureTypeInfo associated to the store.

Second approach: starting from the datastore config panel
---------------------------------------------------------

In this case, we make a custom GUI to configure the JDBC data
stores that has a list of the existing sql definitions
and allows to add/edit/remove them.

On add the workflow would be the same as above, but we
won't show an edit panel in the existing layer definition.

The set of sql definitions would be stored in the property
file like parameter I described at the beginning of the mail,
we'd just provide a nicer GUI for it.

The upside is that we would not need to touch the ResourcePool,
the downside is that the workflow would be less natural
to the user imho, especially now that we're about to add the
ability to create a new feature type in the "new layer" page
as well (see GSIP 47)

Oh, in any case the work would be done on trunk only.

What do you think?

Cheers
Andrea

--
Justin Deoliveira
OpenGeo - http://opengeo.org
Enterprise support for open source geospatial.

5

I think this is a very good idea. while I was doing some configuration using
real world data, I was thinking of something similar as well (which is why I
found this thread). frequently information required for a feature are stored
in various tables and each time I create a mapping, I have to create a view
specifically for the mapping.

Creating views specifically for a mapping can clutter a database.

Secondly this will provide a lot more flexibility

Andrea Aime-4 wrote:

Hi,
I'm looking into how to integrate the sql query to feature type
support in GeoServer.

At first I was thinking to use a datastore parameter structured like
a property file, but it's a quite clunky approach.
The definition in the property file would have been something like:

virt=select a, b, c, the_geom from realtable where a > 3
virt.pk=a,b
virt.geom.the_geom=POINT,4326

Basically it's the query, plus information about which attributes
are part of the primary key, plus information about what type the
geometry is.

Instead of this I would like to have a configuration GUI (and
one less parameter in the never-ending list of params for JDBC
data stores).

First approach: starting from the "new layer" panel
-------------------------------------------------------

Ideally, a user would go to the layer list, choose "new layer",
and upon choosing a JDBC data store he would be presented with
the option to create a feature type by SQL using a SQL query
as the definition of it.

The next page would provide the user a text area in which he
could edit the query, and a "test" button which would both
test the query for validity and provide a GUI to setup
the primary key fields and the geometry details (more
or less a table of the attributes, with name, type,
a checkbox to make a field part of the pk and the possibility
to edit what we know about the geometry).

Past that the user would get into the usual layer page, which
would sport a new panel, equal to the contents of the previous
page, to allow editing of the sql and details.

Implementation wise I would like to store the sql query as part
of the FeatureTypeInfo metadata map.

Now, the issue is, what do we do on restart? We'd need to
configure the sql in the data store again, and to do so
we'd need either the resource pool to do so, making it aware
of the sql definitions, or a callback that would configure
the existing sql feature types into it by inspecting the
FeatureTypeInfo associated to the store.

Second approach: starting from the datastore config panel
---------------------------------------------------------

In this case, we make a custom GUI to configure the JDBC data
stores that has a list of the existing sql definitions
and allows to add/edit/remove them.

On add the workflow would be the same as above, but we
won't show an edit panel in the existing layer definition.

The set of sql definitions would be stored in the property
file like parameter I described at the beginning of the mail,
we'd just provide a nicer GUI for it.

The upside is that we would not need to touch the ResourcePool,
the downside is that the workflow would be less natural
to the user imho, especially now that we're about to add the
ability to create a new feature type in the "new layer" page
as well (see GSIP 47)

Oh, in any case the work would be done on trunk only.

What do you think?

Cheers
Andrea

--
Andrea Aime
OpenGeo - http://opengeo.org
Expert service straight from the developers.

------------------------------------------------------------------------------
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

--
View this message in context: http://old.nabble.com/Creating-feature-types-from-SQL-queries%3A-a-plan-for-GeoServer-tp28388933p28431396.html
Sent from the GeoServer - Dev mailing list archive at Nabble.com.