So does this mean that there is no way to provide the ability for custom
geometry transformation on the database side?
On Thu, Mar 12, 2015 at 9:56 PM, Andrea Aime <[email protected]>
wrote:
> Martin,
> I see what you mean, and it would work in your particular case, but it
> does not make
> sense in general, please review how sql views work, under the notion of a
> general
> mechanism (as a data source that can be queried), not under the lenses of
> your
> specific wms generalization use case, and it will be pretty evident this
> road cannot be
> taken into consideration
>
> Cheers
> Andrea
>
> On Fri, Mar 13, 2015 at 3:38 AM, Martin Davis <[email protected]> wrote:
>
>>
>>>
>>>
>>>>
>>>> To take another slant on this, I realized that the source of the
>>>> problem is that for SQL views the "custom SQL" is wrapped in parens before
>>>> the WMS bbox filter condition is appended to it. This is what's causing the
>>>> DB to complain that the geometry column expression doesn't have an index.
>>>> If there was a way to avoid using the parens that would solve the problem
>>>> nicely (there would be no need for any macros in the user-supplied SQL,
>>>> they could use any expression around the GEOMETRY column (renaming it to
>>>> GEOMETRY) and they wouldn't have to supply their own bbox filter
>>>> condition. One issue is deciding how to append the bbox condition - it
>>>> would be necessary to know whether or not a WHERE clause was already
>>>> present. This could perhaps be determined by a flag supplied by the user.
>>>>
>>>>
>>> Hmmm... example please?
>>>
>>
>>
>> Sure (although making this up from memory since I'm not near my
>> GeoServer)...
>>
>> If the SQL view is
>>
>> SELECT GEOMETRY, A FROM TBL
>>
>> then during WMS rendering Geoserver emits the query
>>
>> SELECT GEOMETRY FROM (SELECT GEOMETRY, A FROM TBL) WHERE
>> SDO_FILTER(GEOMETRY, ...)
>>
>> If the SQLView is
>>
>> SELECT SDO_CENTROID(GEOMETRY) GEOMETRY, A FROM TBL
>>
>> then GeoServer emits
>>
>> SELECT GEOMETRY FROM (SELECT SDO_CENTROID(GEOMETRY) GEOMETRY A FROM TBL)
>> WHERE SDO_FILTER(GEOMETRY, ...)
>>
>> And Oracle squawks because the GEOMETRY referred to in the SDO_FILTER is
>> an alias for an expression, which has not index.
>>
>> Using a different alias (say PT) doesn't work either, because GeoServer
>> emits:
>>
>> SELECT PT FROM (SELECT SDO_CENTROID(GEOMETRY) PT A FROM TBL)
>> WHERE SDO_FILTER(PT, ...)
>>
>> If the SQLView was not contained in a subselect then this would work fine:
>>
>> SELECT SDO_CENTROID(GEOMETRY) GEOMETRY A FROM TBL WHERE
>> SDO_FILTER(GEOMETRY, ...)
>>
>>
>>
>>
>
------------------------------------------------------------------------------
Dive into the World of Parallel Programming The Go Parallel Website, sponsored
by Intel and developed in partnership with Slashdot Media, is your hub for all
things parallel software development, from weekly thought leadership blogs to
news, videos, case studies, tutorials and more. Take a look and join the
conversation now. http://goparallel.sourceforge.net/
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
