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.
------------------------------------------------------------------------------
Download Intel® 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
