[Geoserver-devel] New functionality: creating new feature types with an sql query in jdbc data stores

GeoServer GeoServer Developer
1

Hi,
during the NY OpenGeo code sprint last month we've designed
and coded support for what we call "virtual tables", that
is, feature types that are defined via a straight, native
sql query.

If you cannot wait to see the code there is a patch attached
to http://jira.codehaus.org/browse/GEOT-2123.

Interaction wise, with the current state of the patch you
can define virtual tables either via a factory parameter
or with code.

The factory parameter is a string in property file format,
the definition of a virtual table can look like:

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

The definition states that the "myVirtTable" is defined
by the query "select a, b, c, the_geom from realtable where a > 3",
that the fids are generated using the columns a and b (it
does not mean it's writable, just allows for stable fids),
and that the "the_geom" column is geometric, POINT type,
and the native srid is 4326.

You can add as many definitions as you want in the property file.

Programmatically it looks like:

VirtualTable vt = new VirtualTable("myVirtTable",
         "select a, b, c, the_geom from realtable where a > 3");
vt.addGeometryMetadatata("the_geom", Point.class, 4326);
vt.setPrimaryKeyColumns(Arrays.asList("a", "b"));
dataStore.addVirtualTable(vt);

Internally the code takes the SQL provided and builds queries
that use the SQL as a subquery in the FROM clause, something
we verified all major databases support today. Something like:

select <the fields in the Query>
from (<your sql>)
where <filters in the Query>

GeoServer wise I'm not too happy with the usage of the
factory parameter and I was considering dropping it.

Since we are already planning to have a UI to add new feature
types, this would fit squarely as a variant to it, instead of
creating a new table you give me a query and some metadata.
So it would end up in the "create new layer" workflow.
The query and the metadata would be stored in the FeatureTypeInfo
metadata map, and we'd need a callback from ResourcePool that
allows for configuration of the data store: grab all the feature
type info connected to that datastore, scan for view definitions,
register them as the datastore is being created.
Add and drop dynamically as these are created, removed or
updated by the user.

Soo... how does it sound?

And also, where will it ring? Trunk only? GS wise it would
be a nice way to push users to upgrade once GS 2.1 is out.

Cheers
Andrea

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

2

On Fri, Mar 19, 2010 at 10:22 AM, Andrea Aime <aaime@anonymised.com> wrote:
[... lots of cool stuff ...]

This sounds really cool Andrea, I'll see if I can test it out next
week before marking kicks in. I really wish I had known that the Java
tribe was going to the NY Sprint as I'd have come over, however all
the publicity gave the impression it was a C tribe event.

Ian

--
Ian Turton

3

Ian Turton ha scritto:

On Fri, Mar 19, 2010 at 10:22 AM, Andrea Aime <aaime@anonymised.com> wrote:
[... lots of cool stuff ...]

This sounds really cool Andrea, I'll see if I can test it out next
week before marking kicks in. I really wish I had known that the Java
tribe was going to the NY Sprint as I'd have come over, however all
the publicity gave the impression it was a C tribe event.

OpenGeo had the annual retreat a few days later than the sprint,
so me, Justin and Gabriel decided to join and work a bit along with
them.
This functionality was actually coded during the retreat, where
we had a few more hours to spend coding cool stuff.

Cheers
Andrea

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

4

This is a very good feature. IMHO, we should integrate it into trunk and 2.x series a soon as possible.

Andrea Aime writes:

Hi,
during the NY OpenGeo code sprint last month we've designed
and coded support for what we call "virtual tables", that
is, feature types that are defined via a straight, native
sql query.

If you cannot wait to see the code there is a patch attached
to http://jira.codehaus.org/browse/GEOT-2123.

Interaction wise, with the current state of the patch you
can define virtual tables either via a factory parameter
or with code.

The factory parameter is a string in property file format,
the definition of a virtual table can look like:

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

The definition states that the "myVirtTable" is defined
by the query "select a, b, c, the_geom from realtable where a > 3",
that the fids are generated using the columns a and b (it
does not mean it's writable, just allows for stable fids),
and that the "the_geom" column is geometric, POINT type,
and the native srid is 4326.

You can add as many definitions as you want in the property file.

Programmatically it looks like:

VirtualTable vt = new VirtualTable("myVirtTable",
         "select a, b, c, the_geom from realtable where a > 3");
vt.addGeometryMetadatata("the_geom", Point.class, 4326);
vt.setPrimaryKeyColumns(Arrays.asList("a", "b"));
dataStore.addVirtualTable(vt);

Internally the code takes the SQL provided and builds queries
that use the SQL as a subquery in the FROM clause, something
we verified all major databases support today. Something like:

select <the fields in the Query>
from (<your sql>)
where <filters in the Query>

GeoServer wise I'm not too happy with the usage of the
factory parameter and I was considering dropping it.

Since we are already planning to have a UI to add new feature
types, this would fit squarely as a variant to it, instead of
creating a new table you give me a query and some metadata.
So it would end up in the "create new layer" workflow.
The query and the metadata would be stored in the FeatureTypeInfo
metadata map, and we'd need a callback from ResourcePool that
allows for configuration of the data store: grab all the feature
type info connected to that datastore, scan for view definitions,
register them as the datastore is being created.
Add and drop dynamically as these are created, removed or
updated by the user.

Soo... how does it sound?

And also, where will it ring? Trunk only? GS wise it would
be a nice way to push users to upgrade once GS 2.1 is out.

Cheers
Andrea

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

------------------------------------------------------------------------------
Download Intel&#174; Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Geotools-devel mailing list
Geotools-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

5

I see Gabriel is in the loop - this functionality sounds similar to
the sql-datastore module included in the 1.6 community-schemas
dependencies - can we get an analysis of how this new initiative
compares - and if its equivalent (or more complete) work with Ben to
ensure we have some unit tests using app-schema for the functionality.

Rob

On Tue, Mar 23, 2010 at 2:57 AM, Christian Müller
<christian.mueller@anonymised.com> wrote:

This is a very good feature. IMHO, we should integrate it into trunk and 2.x
series a soon as possible.

Andrea Aime writes:

Hi,
during the NY OpenGeo code sprint last month we've designed
and coded support for what we call "virtual tables", that
is, feature types that are defined via a straight, native
sql query.

If you cannot wait to see the code there is a patch attached
to http://jira.codehaus.org/browse/GEOT-2123.

Interaction wise, with the current state of the patch you
can define virtual tables either via a factory parameter
or with code.

The factory parameter is a string in property file format,
the definition of a virtual table can look like:

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

The definition states that the "myVirtTable" is defined
by the query "select a, b, c, the_geom from realtable where a > 3",
that the fids are generated using the columns a and b (it
does not mean it's writable, just allows for stable fids),
and that the "the_geom" column is geometric, POINT type,
and the native srid is 4326.

You can add as many definitions as you want in the property file.

Programmatically it looks like:

VirtualTable vt = new VirtualTable("myVirtTable",
"select a, b, c, the_geom from realtable where a > 3");
vt.addGeometryMetadatata("the_geom", Point.class, 4326);
vt.setPrimaryKeyColumns(Arrays.asList("a", "b"));
dataStore.addVirtualTable(vt);

Internally the code takes the SQL provided and builds queries
that use the SQL as a subquery in the FROM clause, something
we verified all major databases support today. Something like:

select <the fields in the Query>
from (<your sql>)
where <filters in the Query>

GeoServer wise I'm not too happy with the usage of the
factory parameter and I was considering dropping it.

Since we are already planning to have a UI to add new feature
types, this would fit squarely as a variant to it, instead of
creating a new table you give me a query and some metadata.
So it would end up in the "create new layer" workflow.
The query and the metadata would be stored in the FeatureTypeInfo
metadata map, and we'd need a callback from ResourcePool that
allows for configuration of the data store: grab all the feature
type info connected to that datastore, scan for view definitions,
register them as the datastore is being created.
Add and drop dynamically as these are created, removed or
updated by the user.

Soo... how does it sound?

And also, where will it ring? Trunk only? GS wise it would
be a nice way to push users to upgrade once GS 2.1 is out.

Cheers
Andrea

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

------------------------------------------------------------------------------
Download Intel&#174; Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Geotools-devel mailing list
Geotools-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

------------------------------------------------------------------------------
Download Intel&#174; Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

6

Rob Atkinson ha scritto:

I see Gabriel is in the loop - this functionality sounds similar to
the sql-datastore module included in the 1.6 community-schemas
dependencies - can we get an analysis of how this new initiative
compares - and if its equivalent (or more complete) work with Ben to
ensure we have some unit tests using app-schema for the functionality.

Should this evaluation delay the integration of the free-form jdbc database sql queries?
And what do you intend to do if this functionality is not general
enough?

Cheers
Andrea

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

7

On Tue, Mar 23, 2010 at 8:18 AM, Andrea Aime <aaime@anonymised.com> wrote:

Rob Atkinson ha scritto:

I see Gabriel is in the loop - this functionality sounds similar to
the sql-datastore module included in the 1.6 community-schemas
dependencies - can we get an analysis of how this new initiative
compares - and if its equivalent (or more complete) work with Ben to
ensure we have some unit tests using app-schema for the functionality.

Should this evaluation delay the integration of the free-form jdbc database
sql queries?

no - dont see why it should - it was always planned to deprecate
sql-datastore in favour of full support in jdbc

And what do you intend to do if this functionality is not general
enough?

Fix it after establishing suitable unit tests.
It does raise an issue of unit testing jdbc functionality across all
the dependent modules - theoretically the API should define the
contract, and normal full integration tests should check for any
regressions. I would not be suprised however if there are API aspects
that are not fully defined - for example transactions against a
feature type such a sql query might fail.

So, the first step would be to have Gabriel's view, as the original
implementor, whether this functionality is equivalent, and then a
sanity check against the JDBC data store API to see if we have
generated any inconsistencies or hidden contracts that will break
things.

Rob

Cheers
Andrea

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

8

On 19/03/10 22:22, Andrea Aime wrote:

during the NY OpenGeo code sprint last month we've designed
and coded support for what we call "virtual tables", that
is, feature types that are defined via a straight, native
sql query.

Um, Andrea, did you just invent a generic solution that supersedes the discontinued geometryless data store?

That is, can you use a custom select to manufacture a spatial view from a non-spatial table?

For example:

SELECT id, name, url, ST_SetSRID(ST_Point(longitude, latitude),4326) as location FROM non_spatial_table;

where longitude and latitude are numeric columns.

The next question is: do spatial queries work if geometries are manufactured in the SELECT? We used to fall back to brute force queries (?) for geometries without indices. Since GT 2.6 they just fail.

I think you also just replaced the old sqldatastore as well, all in one hit.

Wow! (Count that as a +1 if you are canvassing for votes.)

Kind regards,

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

9

This did occur to me - but I thought I'd stick with the first issue -
can we dump sql-datastore and also have user-defined native sql
procedures exposed as filter functions?

On Tue, Mar 23, 2010 at 12:53 PM, Ben Caradoc-Davies
<Ben.Caradoc-Davies@anonymised.com> wrote:

On 19/03/10 22:22, Andrea Aime wrote:

during the NY OpenGeo code sprint last month we've designed
and coded support for what we call "virtual tables", that
is, feature types that are defined via a straight, native
sql query.

Um, Andrea, did you just invent a generic solution that supersedes the
discontinued geometryless data store?

That is, can you use a custom select to manufacture a spatial view from
a non-spatial table?

For example:

SELECT id, name, url, ST_SetSRID(ST_Point(longitude, latitude),4326) as
location FROM non_spatial_table;

where longitude and latitude are numeric columns.

The next question is: do spatial queries work if geometries are
manufactured in the SELECT? We used to fall back to brute force queries
(?) for geometries without indices. Since GT 2.6 they just fail.

I think you also just replaced the old sqldatastore as well, all in one hit.

Wow! (Count that as a +1 if you are canvassing for votes.)

Kind regards,

--
Ben Caradoc-Davies <Ben.Caradoc-Davies@anonymised.com>
Software Engineering Team Leader
CSIRO Earth Science and Resource Engineering
Australian Resources Research Centre

------------------------------------------------------------------------------
Download Intel&#174; Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

10

Ben Caradoc-Davies ha scritto:

On 19/03/10 22:22, Andrea Aime wrote:

during the NY OpenGeo code sprint last month we've designed
and coded support for what we call "virtual tables", that
is, feature types that are defined via a straight, native
sql query.

Um, Andrea, did you just invent a generic solution that supersedes the discontinued geometryless data store?

That is, can you use a custom select to manufacture a spatial view from a non-spatial table?

For example:

SELECT id, name, url, ST_SetSRID(ST_Point(longitude, latitude),4326) as location FROM non_spatial_table;

where longitude and latitude are numeric columns.

You can, but it would not be worth using performance wise.
For example, a bbox filter against the feature type defined
above would be turned into the following sql query:

select id, name, url, location
from
(
    SELECT id, name, url,
           ST_SetSRID(ST_Point(longitude, latitude),4326) as location
    FROM non_spatial_table)
)
where location && <the bbox>

which would not give enough info to the db to turn that
into a filter against longitude and latitude.

The advantages of the approach we've used are:
- simplicity: the query you give the datastore is not parsed,
   not understood, it's just used as a subquery in the from clause
- generality: you can really invoke whatever you want, database
   specific as you want, you just have to make sure it's a valid
   query. I did not try, but I believe it should work with stored
   procedures as well (provided they return a resultset, that is).
The main disadvantage is that to get good performance you're
depending on the database own query plan optimizer to link
the outer filters with the inner fields. Which in general
happens, but won't work on manufactured fields.

The next question is: do spatial queries work if geometries are manufactured in the SELECT? We used to fall back to brute force queries (?) for geometries without indices. Since GT 2.6 they just fail.

See above. They work, but they are not worth using performance wise.
To make it work and be performant imho we should have two levels:
- one that can take any query and make it usable as a new feature type
- one, possibly a datastore wrapper, that is in the business of
   transformations of fields and filters and that can transform them
   two ways (or decide that the filter will have to be performed in
   memory if the back transform is not possible).
   Turning 2 doubles into a point and 4 doubles into a rectangle and
   turning spatial filters into scalar comparisons is
   just one of the possible transformations that this object could
   do.

I think the app-schema store is somewhat close to this second
goal already?

Cheers
Andrea

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

11

Rob Atkinson ha scritto:

This did occur to me - but I thought I'd stick with the first issue -
can we dump sql-datastore and also have user-defined native sql
procedures exposed as filter functions?

I did not look into the latter (exposing native sql procedures
as filter functions) but the jdbc datatores are the right place to add
that functionality. Time and resources allowing, of course :wink:

Cheers
Andrea

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

12

Andrea Aime ha scritto:

For example:

SELECT id, name, url, ST_SetSRID(ST_Point(longitude, latitude),4326) as location FROM non_spatial_table;

where longitude and latitude are numeric columns.

You can, but it would not be worth using performance wise.
For example, a bbox filter against the feature type defined
above would be turned into the following sql query:

select id, name, url, location
from
(
    SELECT id, name, url,
           ST_SetSRID(ST_Point(longitude, latitude),4326) as location
    FROM non_spatial_table)
)
where location && <the bbox>

which would not give enough info to the db to turn that
into a filter against longitude and latitude.

Thinking thinking, evil thinking...

Since we're already giving the user the control to make
her own query, what about giving her the tools to make her
own optimizations as well?
What if, for example, we allow a variable substitution mechanism
based on the simplest and more common filter, the bbox one?
We allow the user to add a special part of the definition query
that is actually expanded only if a bbox filter can be extracted
from the original gt2 query, and then the user could give the
jdbc datastore something like:

select id, name, url,
        ST_SetSRID(ST_Point(longitude, latitude),4326) as location
from non_spatial_table
{where longitude >= $bbox.minx and longitude <= $bbox.maxx
    and latitude >= $bbox.miny and latitude <= $bbox.maxy}

or something like:

select id, st_buffer(geometry, 1500), name
from my_spatial_table
where flow > 143
{and geometry && $bbox}

Assuming the $bbox would be expanded into the literal representation
of a bounding box for that database (using the sql dialect).
Or, to make things a little easier for us, we pass down
$minx, $miny, $maxx, $maxy and let the user build whatever she
feels like with them.

Too evil?

Cheers
Andrea

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

13

If I understand the concept, this feature will not add functionality.
The purpose is to make the statement more efficient

right ?

Andrea Aime writes:

Andrea Aime ha scritto:

For example:

SELECT id, name, url, ST_SetSRID(ST_Point(longitude, latitude),4326) as location FROM non_spatial_table;

where longitude and latitude are numeric columns.

You can, but it would not be worth using performance wise.
For example, a bbox filter against the feature type defined
above would be turned into the following sql query:

select id, name, url, location
from
(
    SELECT id, name, url,
           ST_SetSRID(ST_Point(longitude, latitude),4326) as location
    FROM non_spatial_table)
)
where location && <the bbox>

which would not give enough info to the db to turn that
into a filter against longitude and latitude.

Thinking thinking, evil thinking...

Since we're already giving the user the control to make
her own query, what about giving her the tools to make her
own optimizations as well?
What if, for example, we allow a variable substitution mechanism
based on the simplest and more common filter, the bbox one?
We allow the user to add a special part of the definition query
that is actually expanded only if a bbox filter can be extracted
from the original gt2 query, and then the user could give the
jdbc datastore something like:

select id, name, url,
        ST_SetSRID(ST_Point(longitude, latitude),4326) as location
from non_spatial_table
{where longitude >= $bbox.minx and longitude <= $bbox.maxx
    and latitude >= $bbox.miny and latitude <= $bbox.maxy}

or something like:

select id, st_buffer(geometry, 1500), name
from my_spatial_table
where flow > 143
{and geometry && $bbox}

Assuming the $bbox would be expanded into the literal representation
of a bounding box for that database (using the sql dialect).
Or, to make things a little easier for us, we pass down
$minx, $miny, $maxx, $maxy and let the user build whatever she
feels like with them.

Too evil?

Cheers
Andrea

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

------------------------------------------------------------------------------
Download Intel&#174; Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Geotools-devel mailing list
Geotools-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

14

Christian Müller ha scritto:

If I understand the concept, this feature will not add functionality.
The purpose is to make the statement more efficient
right ?

Correct.

Though in fact in my mind it would make another class of usages
possible in practical terms (as opposed to possible in theory
but too slow for practical purposes beyond toy data sets),
so I personally see it as a new functionality all right.
In practical terms it would allow this patch to replace
the geometryless datastore as well.

Cheers
Andrea

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

15

Hmmmmmm

And how do you rewrite a query if the bbox param is missing ?

e.g

where st_area($bbox) > 100000

or

where anAttr = aValue or <bbox expresion>

or

where anAttr = aValue and <bbox expresion>

?

Andrea Aime writes:

Christian Müller ha scritto:

If I understand the concept, this feature will not add functionality.
The purpose is to make the statement more efficient
right ?

Correct.

Though in fact in my mind it would make another class of usages
possible in practical terms (as opposed to possible in theory
but too slow for practical purposes beyond toy data sets),
so I personally see it as a new functionality all right.
In practical terms it would allow this patch to replace
the geometryless datastore as well.

Cheers
Andrea

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

16

Christian Müller ha scritto:

Hmmmmmm
And how do you rewrite a query if the bbox param is missing ?
e.g
where st_area($bbox) > 100000
or
where anAttr = aValue or <bbox expresion>
or
where anAttr = aValue and <bbox expresion>
?

That's why I enclosed the part with the bbox in { } and
said "We allow the user to add a special part of the definition query
that is actually expanded only if a bbox filter can be extracted
from the original gt2 query"

So if there is no bbox

select id, name, url,
         ST_SetSRID(ST_Point(longitude, latitude),4326) as location
from non_spatial_table
{where longitude >= $bbox.minx and longitude <= $bbox.maxx
     and latitude >= $bbox.miny and latitude <= $bbox.maxy}

becomes just:

select id, name, url,
         ST_SetSRID(ST_Point(longitude, latitude),4326) as location
from non_spatial_table

In fact using just { } is probably a poor choice.

If we use minx, miny, max, maxy, srid instead of passing down a fully
encoded envelope and use the {bbox} as the way to mark the
special portion of the query:

select id, name, url,
         ST_SetSRID(ST_Point(longitude, latitude),4326) as location
from non_spatial_table
{bbox} where longitude >= $minx and longitude <= $maxx
     and latitude >= $miny and latitude <= $maxy {bbox}

and

select id, st_buffer(geometry, 1500), name
from my_spatial_table
where flow > 143
{bbox}
and geometry && st_buffer(ST_MakeEnvelope($minx, $miny, $maxx, $maxy, $srid), 1500) {bbox}

If you don't want to add the optimization you just don't add the
{bbox}...{bbox} portion.

How does it look?

Cheers
Andrea

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

17

Yep, sounds good using {bbox}

At the moment I have the problem with tons of views in my database. The virtual table concept could be a solution for this. If you like I would try
to deploy virtual tables in Geoserver 2.x

Is there anything preventing the patch to be applied to gt-2.6.x ?.
Is there anything preventing virtual tables to work with geoserver 2.x ?

Otherwise I would try a "real world" deployment.

Andrea Aime writes:

Christian Müller ha scritto:

Hmmmmmm
And how do you rewrite a query if the bbox param is missing ?
e.g
where st_area($bbox) > 100000
or
where anAttr = aValue or <bbox expresion>
or
where anAttr = aValue and <bbox expresion>
?

That's why I enclosed the part with the bbox in { } and
said "We allow the user to add a special part of the definition query
that is actually expanded only if a bbox filter can be extracted
from the original gt2 query"

So if there is no bbox

select id, name, url,
        ST_SetSRID(ST_Point(longitude, latitude),4326) as location
from non_spatial_table
{where longitude >= $bbox.minx and longitude <= $bbox.maxx
    and latitude >= $bbox.miny and latitude <= $bbox.maxy}

becomes just:

select id, name, url,
        ST_SetSRID(ST_Point(longitude, latitude),4326) as location
from non_spatial_table

In fact using just { } is probably a poor choice.

If we use minx, miny, max, maxy, srid instead of passing down a fully
encoded envelope and use the {bbox} as the way to mark the
special portion of the query:

select id, name, url,
        ST_SetSRID(ST_Point(longitude, latitude),4326) as location
from non_spatial_table
{bbox} where longitude >= $minx and longitude <= $maxx
    and latitude >= $miny and latitude <= $maxy {bbox}

and

select id, st_buffer(geometry, 1500), name
from my_spatial_table
where flow > 143
{bbox}
and geometry && st_buffer(ST_MakeEnvelope($minx, $miny, $maxx, $maxy, $srid), 1500) {bbox}

If you don't want to add the optimization you just don't add the
{bbox}...{bbox} portion.

How does it look?

Cheers
Andrea

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

18

Christian Müller ha scritto:

Yep, sounds good using {bbox}
At the moment I have the problem with tons of views in my database. The virtual table concept could be a solution for this. If you like I would try
to deploy virtual tables in Geoserver 2.x
Is there anything preventing the patch to be applied to gt-2.6.x ?.
Is there anything preventing virtual tables to work with geoserver 2.x ?
Otherwise I would try a "real world" deployment.

I did not try to apply on 2.6.x, but I don't see any reason why they
should not work.

Mind that you'd be playing with a preliminary version, that the
{bbox} optimization is still to be made, and that I'd still like to
remove the views factory parameter in favor of programmatic configuration + storing the view definition as a FeatureTypeInfo
metadata in GeoServer.

I'm also very hesitant on the idea of adding this functionality
to GeoServer 2.0.x, it would be better to add the set of changes
needed only to trunk imho

Cheers
Andrea

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

19

Andrea, I applied the patch to 2.6.x and it works

About: {bbox}
We could create a JIRA issue (improvement)

About: geoserver integration.

Since I will use the virtual tables within a pregeneralized feature, I create the virtual tables programmatically.

I think your headaches are caused by the VIRTUAL_TABLES parameter which open as a text field when creating a datastore ?

I tried comment out the VIRTUAL_TABLES stuff, no compile error.

Is this a possibility to apply this patch to 2.6.x ?
Otherwise I have to make my own special version.
This feature is like a weapon in my hands :slight_smile:

Andrea Aime writes:

Christian Müller ha scritto:

Yep, sounds good using {bbox}
At the moment I have the problem with tons of views in my database. The virtual table concept could be a solution for this. If you like I would try
to deploy virtual tables in Geoserver 2.x
Is there anything preventing the patch to be applied to gt-2.6.x ?.
Is there anything preventing virtual tables to work with geoserver 2.x ?
Otherwise I would try a "real world" deployment.

I did not try to apply on 2.6.x, but I don't see any reason why they
should not work.

Mind that you'd be playing with a preliminary version, that the
{bbox} optimization is still to be made, and that I'd still like to
remove the views factory parameter in favor of programmatic configuration + storing the view definition as a FeatureTypeInfo
metadata in GeoServer.

I'm also very hesitant on the idea of adding this functionality
to GeoServer 2.0.x, it would be better to add the set of changes
needed only to trunk imho

Cheers
Andrea

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

20

evil man!

Actually, you've pretty much described the extension to app-schema Ben
and I have discussed, and I was going to have a play with once I'd
gort a coupel of other jobs out of the way...

Our idea was to create an "inverse mapping" using substitution
parameters - which is pretty much what you have discussed, with the
ability to allow a user defined one, or a well-known one such as
geometryless.

This will also solve another ugly problem - querying against
manufactured string values - for example a internal database ID being
append to a data-set-specific URN to create a globally unique
identifier. At the moment this is inefficiently queried.

Our goal, as you have suggested yourself is to remove sql-datastore
and geometryless as separate modules, and app-schema already has the
capabilties to do the forward-transform.

Rob

On Tue, Mar 23, 2010 at 7:21 PM, Andrea Aime <aaime@anonymised.com> wrote:

Andrea Aime ha scritto:

For example:

SELECT id, name, url, ST_SetSRID(ST_Point(longitude, latitude),4326) as
location FROM non_spatial_table;

where longitude and latitude are numeric columns.

You can, but it would not be worth using performance wise.
For example, a bbox filter against the feature type defined
above would be turned into the following sql query:

select id, name, url, location
from
(
SELECT id, name, url,
ST_SetSRID(ST_Point(longitude, latitude),4326) as location
FROM non_spatial_table)
)
where location && <the bbox>

which would not give enough info to the db to turn that
into a filter against longitude and latitude.

Thinking thinking, evil thinking...

Since we're already giving the user the control to make
her own query, what about giving her the tools to make her
own optimizations as well?
What if, for example, we allow a variable substitution mechanism
based on the simplest and more common filter, the bbox one?
We allow the user to add a special part of the definition query
that is actually expanded only if a bbox filter can be extracted
from the original gt2 query, and then the user could give the
jdbc datastore something like:

select id, name, url,
ST_SetSRID(ST_Point(longitude, latitude),4326) as location
from non_spatial_table
{where longitude >= $bbox.minx and longitude <= $bbox.maxx
and latitude >= $bbox.miny and latitude <= $bbox.maxy}

or something like:

select id, st_buffer(geometry, 1500), name
from my_spatial_table
where flow > 143
{and geometry && $bbox}

Assuming the $bbox would be expanded into the literal representation
of a bounding box for that database (using the sql dialect).
Or, to make things a little easier for us, we pass down
$minx, $miny, $maxx, $maxy and let the user build whatever she
feels like with them.

Too evil?

Cheers
Andrea

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

------------------------------------------------------------------------------
Download Intel&#174; Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel