Re: [Geoserver-users] GeoServer's spatial filter preventing SQL view layer from working

[Martin Davis]

+1 - agreed that this seems like the "simplest" way forward.



On Thu, Nov 19, 2015 at 10:08 AM, cheesybiscuits 
wrote:

> Wow, that is a long thread!
>
> I read / skimmed through the full conversation and what I take from it is
> there are numerous ways this could be done that would increase complexity,
> deviate from GeoServer's UI philosophy, and consume lots of developer time,
> but that Andrea's original suggestion - to not append an SDO_FILTER clause
> if a spatial index doesn't exist for the geometry column - is the most
> suitable. It can simply be the Oracle function / procedure writer's
> responsibility to ensure their code filters output features to the queried
> bbox.
>
> Ideally GeoServer would make minx, miny, maxx, and maxy of the query
> available by default (e.g. accessible in your SQL View via %%minx%%) so
> that
> these would not have to be provided by viewparams.
>
> There is a lot of documentation on starting GeoServer development but I
> have
> previously been confused about how to get involved in extension
> development.
> Any pointers for the best way to get started in this?
>
>
>
>
> --
> View this message in context:
> http://osgeo-org.1560.x6.nabble.com/GeoServer-s-spatial-filter-preventing-SQL-view-layer-from-working-tp5237269p5237488.html
> Sent from the GeoServer - User mailing list archive at Nabble.com.
>
>
> --
> ___
> Geoserver-users mailing list
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
--
___
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Re: [Geoserver-users] GeoServer's spatial filter preventing SQL view layer from working

[cheesybiscuits]

Wow, that is a long thread!

I read / skimmed through the full conversation and what I take from it is
there are numerous ways this could be done that would increase complexity,
deviate from GeoServer's UI philosophy, and consume lots of developer time,
but that Andrea's original suggestion - to not append an SDO_FILTER clause
if a spatial index doesn't exist for the geometry column - is the most
suitable. It can simply be the Oracle function / procedure writer's
responsibility to ensure their code filters output features to the queried
bbox.

Ideally GeoServer would make minx, miny, maxx, and maxy of the query
available by default (e.g. accessible in your SQL View via %%minx%%) so that
these would not have to be provided by viewparams.

There is a lot of documentation on starting GeoServer development but I have
previously been confused about how to get involved in extension development.
Any pointers for the best way to get started in this?




--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/GeoServer-s-spatial-filter-preventing-SQL-view-layer-from-working-tp5237269p5237488.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

--
___
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Re: [Geoserver-users] GeoServer's spatial filter preventing SQL view layer from working

[Martin Davis]

Actually after some more thought I think this situation is slightly
different (although the technical solution might end up being the same).
Here's the two cases as I understand them:

1) In my original case the need was to run a query against an Oracle
spatial table and return a geometry value computed from the base geometry.
So the SDO_FILTER clause for the query bounding box was still required, it
just needed to be run against the base geometry, not the geometry returned
by the query.

2) In the cluster case (if I understand it) no SDO_FILTER clause is
required, but instead the bounding box values need to be passed to a custom
Oracle function.

It seems like #1 might be simpler to implement than #2.  For instance, in
#1 GeoServer can still run an "empty query" to determine the geometry
column(s), whereas that doesn't seem feasible in #2.


On Wed, Nov 18, 2015 at 11:22 PM, Andrea Aime 
wrote:

> Hi Martin,
> it certainly looks similar, most likely same.
>
> No new development, still not possible, people still welcome to chip in
> with resources/funds:
>
> https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer
>
> Cheers
> Andrea
>
> On Thu, Nov 19, 2015 at 6:32 AM, Martin Davis  wrote:
>
>> I think this is the same issue discussed in this (lengthy) thread:
>>
>>
>> http://osgeo-org.1560.x6.nabble.com/Reducing-query-data-size-in-Oracle-td5192620.html
>>
>> TLDR: not possible right now, apparently requires clever development to
>> make it work.
>>
>> If there has been improvement on this front it would be great to know
>> about it.  Or would be great to have someone implement a workaround!
>>
>> On Wed, Nov 18, 2015 at 12:08 PM, cheesybiscuits <
>> thomaschrist...@gmail.com> wrote:
>>
>>> I've created a function within Oracle for on-the-fly clustering that I
>>> hoped
>>> to query via GeoServer using an SQL view layer.
>>>
>>> The function takes as its arguments the bounding box and the number of
>>> grid
>>> cells to use in a basic gridded clustering process (i.e. find the centre
>>> of
>>> mass for points within each grid cell). Please note I've tried the Point
>>> Stacker extension and it isn't adequate for my needs. Executing the
>>> following query within Oracle gives the list of sdo_geometry objects
>>> that I
>>> expect:
>>>
>>> select * from table(package_name.function_name(-180, -90, 180, 90, 3);
>>>
>>> My intention is to make WMS requests to GeoServer with the viewparams
>>> parameter included that provides all required arguments. Because of the
>>> way
>>> the function works the returned sdo_geometry objects will only ever be
>>> within the provided bounding box.
>>>
>>> I can successfully create an SQL view layer within GeoServer using this
>>> syntax and everything seems OK while publishing. However when I access
>>> the
>>> layer everything falls apart. GeoServer takes my query and wraps it as
>>> follows:
>>>
>>> SELECT GEOMETRY as GEOMETRY FROM (select * from
>>> table(package_name.function_name(-180, -90, 180, 90, 3))) VTABLE WHERE
>>> SDO_FILTER(GEOMETRY, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'
>>>
>>> Oracle then errors because the function's table return type doesn't have
>>> a
>>> spatial index to use with SDO_FILTER. Although I did find a  somewhat
>>> similar problem on GIS.SE
>>> <
>>> http://gis.stackexchange.com/questions/92554/can-i-create-an-oracle-spatial-view-from-a-non-spatial-table
>>> >
>>> the same solution does not apply as my function returns a table, not a
>>> single object, and Oracle doesn't permit indexes on named table types.
>>>
>>> As things currently stand I don't think I can make this work, and the
>>> only
>>> thing that could potentially help is to prevent GeoServer from appending
>>> its
>>> SDO_FILTER mask. However I'm guessing this just isn't possible without
>>> digging into the source and creating a custom build (totally not an
>>> option).
>>>
>>> Can anyone tell me if there's something I've missed here, or suggest a
>>> different approach for solving the same problem? Any thoughts would be
>>> very
>>> much appreciated.
>>>
>>>
--
___
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

[Geoserver-users] GeoServer's spatial filter preventing SQL view layer from working

[cheesybiscuits]

I've created a function within Oracle for on-the-fly clustering that I hoped
to query via GeoServer using an SQL view layer.

The function takes as its arguments the bounding box and the number of grid
cells to use in a basic gridded clustering process (i.e. find the centre of
mass for points within each grid cell). Please note I've tried the Point
Stacker extension and it isn't adequate for my needs. Executing the
following query within Oracle gives the list of sdo_geometry objects that I
expect:

select * from table(package_name.function_name(-180, -90, 180, 90, 3);

My intention is to make WMS requests to GeoServer with the viewparams
parameter included that provides all required arguments. Because of the way
the function works the returned sdo_geometry objects will only ever be
within the provided bounding box.

I can successfully create an SQL view layer within GeoServer using this
syntax and everything seems OK while publishing. However when I access the
layer everything falls apart. GeoServer takes my query and wraps it as
follows:

SELECT GEOMETRY as GEOMETRY FROM (select * from
table(package_name.function_name(-180, -90, 180, 90, 3))) VTABLE WHERE 
SDO_FILTER(GEOMETRY, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'

Oracle then errors because the function's table return type doesn't have a
spatial index to use with SDO_FILTER. Although I did find a  somewhat
similar problem on GIS.SE

  
the same solution does not apply as my function returns a table, not a
single object, and Oracle doesn't permit indexes on named table types.

As things currently stand I don't think I can make this work, and the only
thing that could potentially help is to prevent GeoServer from appending its
SDO_FILTER mask. However I'm guessing this just isn't possible without
digging into the source and creating a custom build (totally not an option).

Can anyone tell me if there's something I've missed here, or suggest a
different approach for solving the same problem? Any thoughts would be very
much appreciated.



--
View this message in context: 
http://osgeo-org.1560.x6.nabble.com/GeoServer-s-spatial-filter-preventing-SQL-view-layer-from-working-tp5237269.html
Sent from the GeoServer - User mailing list archive at Nabble.com.

--
___
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Re: [Geoserver-users] GeoServer's spatial filter preventing SQL view layer from working

[Martin Davis]

I think this is the same issue discussed in this (lengthy) thread:

http://osgeo-org.1560.x6.nabble.com/Reducing-query-data-size-in-Oracle-td5192620.html

TLDR: not possible right now, apparently requires clever development to
make it work.

If there has been improvement on this front it would be great to know about
it.  Or would be great to have someone implement a workaround!

On Wed, Nov 18, 2015 at 12:08 PM, cheesybiscuits 
wrote:

> I've created a function within Oracle for on-the-fly clustering that I
> hoped
> to query via GeoServer using an SQL view layer.
>
> The function takes as its arguments the bounding box and the number of grid
> cells to use in a basic gridded clustering process (i.e. find the centre of
> mass for points within each grid cell). Please note I've tried the Point
> Stacker extension and it isn't adequate for my needs. Executing the
> following query within Oracle gives the list of sdo_geometry objects that I
> expect:
>
> select * from table(package_name.function_name(-180, -90, 180, 90, 3);
>
> My intention is to make WMS requests to GeoServer with the viewparams
> parameter included that provides all required arguments. Because of the way
> the function works the returned sdo_geometry objects will only ever be
> within the provided bounding box.
>
> I can successfully create an SQL view layer within GeoServer using this
> syntax and everything seems OK while publishing. However when I access the
> layer everything falls apart. GeoServer takes my query and wraps it as
> follows:
>
> SELECT GEOMETRY as GEOMETRY FROM (select * from
> table(package_name.function_name(-180, -90, 180, 90, 3))) VTABLE WHERE
> SDO_FILTER(GEOMETRY, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'
>
> Oracle then errors because the function's table return type doesn't have a
> spatial index to use with SDO_FILTER. Although I did find a  somewhat
> similar problem on GIS.SE
> <
> http://gis.stackexchange.com/questions/92554/can-i-create-an-oracle-spatial-view-from-a-non-spatial-table
> >
> the same solution does not apply as my function returns a table, not a
> single object, and Oracle doesn't permit indexes on named table types.
>
> As things currently stand I don't think I can make this work, and the only
> thing that could potentially help is to prevent GeoServer from appending
> its
> SDO_FILTER mask. However I'm guessing this just isn't possible without
> digging into the source and creating a custom build (totally not an
> option).
>
> Can anyone tell me if there's something I've missed here, or suggest a
> different approach for solving the same problem? Any thoughts would be very
> much appreciated.
>
>
>
> --
> View this message in context:
> http://osgeo-org.1560.x6.nabble.com/GeoServer-s-spatial-filter-preventing-SQL-view-layer-from-working-tp5237269.html
> Sent from the GeoServer - User mailing list archive at Nabble.com.
>
>
> --
> ___
> Geoserver-users mailing list
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
--
___
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users

Re: [Geoserver-users] GeoServer's spatial filter preventing SQL view layer from working

[Andrea Aime]

Hi Martin,
it certainly looks similar, most likely same.

No new development, still not possible, people still welcome to chip in
with resources/funds:
https://github.com/geoserver/geoserver/wiki/Successfully-requesting-and-integrating-new-features-and-improvements-in-GeoServer

Cheers
Andrea

On Thu, Nov 19, 2015 at 6:32 AM, Martin Davis  wrote:

> I think this is the same issue discussed in this (lengthy) thread:
>
>
> http://osgeo-org.1560.x6.nabble.com/Reducing-query-data-size-in-Oracle-td5192620.html
>
> TLDR: not possible right now, apparently requires clever development to
> make it work.
>
> If there has been improvement on this front it would be great to know
> about it.  Or would be great to have someone implement a workaround!
>
> On Wed, Nov 18, 2015 at 12:08 PM, cheesybiscuits <
> thomaschrist...@gmail.com> wrote:
>
>> I've created a function within Oracle for on-the-fly clustering that I
>> hoped
>> to query via GeoServer using an SQL view layer.
>>
>> The function takes as its arguments the bounding box and the number of
>> grid
>> cells to use in a basic gridded clustering process (i.e. find the centre
>> of
>> mass for points within each grid cell). Please note I've tried the Point
>> Stacker extension and it isn't adequate for my needs. Executing the
>> following query within Oracle gives the list of sdo_geometry objects that
>> I
>> expect:
>>
>> select * from table(package_name.function_name(-180, -90, 180, 90, 3);
>>
>> My intention is to make WMS requests to GeoServer with the viewparams
>> parameter included that provides all required arguments. Because of the
>> way
>> the function works the returned sdo_geometry objects will only ever be
>> within the provided bounding box.
>>
>> I can successfully create an SQL view layer within GeoServer using this
>> syntax and everything seems OK while publishing. However when I access the
>> layer everything falls apart. GeoServer takes my query and wraps it as
>> follows:
>>
>> SELECT GEOMETRY as GEOMETRY FROM (select * from
>> table(package_name.function_name(-180, -90, 180, 90, 3))) VTABLE WHERE
>> SDO_FILTER(GEOMETRY, ?, 'mask=anyinteract querytype=WINDOW') = 'TRUE'
>>
>> Oracle then errors because the function's table return type doesn't have a
>> spatial index to use with SDO_FILTER. Although I did find a  somewhat
>> similar problem on GIS.SE
>> <
>> http://gis.stackexchange.com/questions/92554/can-i-create-an-oracle-spatial-view-from-a-non-spatial-table
>> >
>> the same solution does not apply as my function returns a table, not a
>> single object, and Oracle doesn't permit indexes on named table types.
>>
>> As things currently stand I don't think I can make this work, and the only
>> thing that could potentially help is to prevent GeoServer from appending
>> its
>> SDO_FILTER mask. However I'm guessing this just isn't possible without
>> digging into the source and creating a custom build (totally not an
>> option).
>>
>> Can anyone tell me if there's something I've missed here, or suggest a
>> different approach for solving the same problem? Any thoughts would be
>> very
>> much appreciated.
>>
>>
>>
>> --
>> View this message in context:
>> http://osgeo-org.1560.x6.nabble.com/GeoServer-s-spatial-filter-preventing-SQL-view-layer-from-working-tp5237269.html
>> Sent from the GeoServer - User mailing list archive at Nabble.com.
>>
>>
>> --
>> ___
>> Geoserver-users mailing list
>> Geoserver-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>>
>
>
>
> --
>
> ___
> Geoserver-users mailing list
> Geoserver-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geoserver-users
>
>


-- 
==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.
==

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054  Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39  339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi