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

[Rob A. <rob...@gm...>]

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
<chr...@nv...> 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
>> Geo...@li...
>> 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
> Geo...@li...
> https://lists.sourceforge.net/lists/listinfo/geoserver-devel
>