[Geoserver-users] Reducing query data size in Oracle

[Martin_Davis1]

We’re working on tuning a GeoServer implementation over an Oracle SDO database. We think we are seeing that the network latency of queries is the hotspot in the performance of GeoServer render requests.

It seems that for some situations one way to reduce the data being pushed over the wire is to reduce the data size in the database query. In particular, for some datasets of polygons we’re happy just to see points representing the polygons. So we tried using a SQLView with a statement like:

select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP

and then used an SLD selector to render just the point.

However, this doesn’t work because we get the dreaded ORA-13226: interface not supported without a spatial index error. This is because GeoServer is actually emitting the query

SELECT PT as PT FROM (select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(PT, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

and there is no index defined on the PT column.

Now, this would work if GeoServer could emit the query:

SELECT PT as PT FROM (select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

Is there any way to get this to happen?

Or does anyone have other ideas about how to reduce query data size on Oracle?

[Andrea_Aime4]

On Tue, Mar 10, 2015 at 7:00 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

We're working on tuning a GeoServer implementation over an Oracle SDO
database. We think we are seeing that the network latency of queries is the
hotspot in the performance of GeoServer render requests.

It seems that for some situations one way to reduce the data being pushed
over the wire is to reduce the data size in the database query. In
particular, for some datasets of polygons we're happy just to see points
representing the polygons. So we tried using a SQLView with a statement
like:

select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP

and then used an SLD <Geometry> selector to render just the point.

However, this doesn't work because we get the dreaded ORA-13226:
interface not supported without a spatial index error. This is because
GeoServer is actually emitting the query

SELECT PT as PT FROM (select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1)
PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(PT, ?, 'mask=anyinteract
querytype=WINDOW') = 'TRUE'

and there is no index defined on the PT column.

Now, this would work if GeoServer could emit the query:

SELECT PT as PT FROM (select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from
CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(GEOMETRY, ?, 'mask=anyinteract
querytype=WINDOW') = 'TRUE'

Is there any way to get this to happen?

You should fix this: http://jira.codehaus.org/browse/GEOT-4768
which in turns requires changes to the oracle store to figure out that a
field does not have a index (this info should be cached) and
emit the right syntax accordingly.
I believe you'd need two relatively simple changes to OracleDialect and
OracleFilterToSql

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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 dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

[Martin_Davis1]

Well, good that there’s an issue for this.

I don’t understand how the SQL can be changed to not require a spatial index? What I’m suggesting is a way to use the spatial filter on the underlying geometry, but return a different geometry column. Not sure if this is possible in the current GS architecture?

···

On Tue, Mar 10, 2015 at 11:21 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Tue, Mar 10, 2015 at 7:00 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

We’re working on tuning a GeoServer implementation over an Oracle SDO database. We think we are seeing that the network latency of queries is the hotspot in the performance of GeoServer render requests.

It seems that for some situations one way to reduce the data being pushed over the wire is to reduce the data size in the database query. In particular, for some datasets of polygons we’re happy just to see points representing the polygons. So we tried using a SQLView with a statement like:

select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP

and then used an SLD selector to render just the point.

However, this doesn’t work because we get the dreaded ORA-13226: interface not supported without a spatial index error. This is because GeoServer is actually emitting the query

SELECT PT as PT FROM (select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(PT, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

and there is no index defined on the PT column.

Now, this would work if GeoServer could emit the query:

SELECT PT as PT FROM (select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

Is there any way to get this to happen?

You should fix this: http://jira.codehaus.org/browse/GEOT-4768
which in turns requires changes to the oracle store to figure out that a field does not have a index (this info should be cached) and
emit the right syntax accordingly.
I believe you’d need two relatively simple changes to OracleDialect and OracleFilterToSql

Cheers
Andrea

–

==

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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 dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

---

[Jukka_Rahkonen]

Hi,

Why do you select geometry and centroid into SQL view if you are only interested in the centroid?

"select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP "

-Jukka Rahkonen-

···

---

Martin Davis wrote:

We’re working on tuning a GeoServer implementation over an Oracle SDO database. We think we are seeing that the network latency of queries is the hotspot in the performance of GeoServer render requests.

It seems that for some situations one way to reduce the data being pushed over the wire is to reduce the data size in the database query. In particular, for some datasets of polygons we’re happy just to see points representing the polygons. So we tried using a SQLView with a statement like:

select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP

and then used an SLD selector to render just the point.

However, this doesn’t work because we get the dreaded ORA-13226: interface not supported without a spatial index error. This is because GeoServer is actually emitting the query

SELECT PT as PT FROM (select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(PT, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

and there is no index defined on the PT column.

Now, this would work if GeoServer could emit the query:

SELECT PT as PT FROM (select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

Is there any way to get this to happen?

Or does anyone have other ideas about how to reduce query data size on Oracle?

[Andrea_Aime4]

On Tue, Mar 10, 2015 at 7:27 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Well, good that there's an issue for this.

I don't understand how the SQL can be changed to not require a spatial
index? What I'm suggesting is a way to use the spatial filter on the
underlying geometry, but return a different geometry column. Not sure if
this is possible in the current GS architecture?

It is, you just have to figure out that your column is built at runtime by
the virtual table definition, and thus does not have a index, and encode
the search queries accordingly.
Virtual table is just a special case of not having a spatial index (the
other being having a real table without a spatial index).

But yes, you cannot query the original geometry because GeoServer cannot
know how your computed geometry relates to the original geometry, and
Oracle does not know either afaik.... I believe other software allows you a
special parameter where you could embed a simple bbox query against the
original data... but that is really simpleton, in GeoServer we don't just
have bbox searches... but maybe
we could have some logic that tries to map back the query down to a bbox
one, and have a primary filter/secondary filter approach (thus, keeping the
real query against the computed geometry, but injecting a simple bbox one
against the original geometry too)

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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 dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

[Martin_Davis1]

Because I was hoping that GeoServer would make use of the GEOMETRY column in the spatial filter. I tried NOT including it first of all, and that didn’t work, since the filter was applied on the PT field, causing the no-index error. Unfortunately, GeoServer is too smart - if the SLD is based on the PT column then it uses that column in the spatial filter!

···

On Tue, Mar 10, 2015 at 11:29 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

Hi,

Why do you select geometry and centroid into SQL view if you are only interested in the centroid?

"select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP "

-Jukka Rahkonen-

---

Martin Davis wrote:

We’re working on tuning a GeoServer implementation over an Oracle SDO database. We think we are seeing that the network latency of queries is the hotspot in the performance of GeoServer render requests.

It seems that for some situations one way to reduce the data being pushed over the wire is to reduce the data size in the database query. In particular, for some datasets of polygons we’re happy just to see points representing the polygons. So we tried using a SQLView with a statement like:

select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP

and then used an SLD selector to render just the point.

However, this doesn’t work because we get the dreaded ORA-13226: interface not supported without a spatial index error. This is because GeoServer is actually emitting the query

SELECT PT as PT FROM (select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(PT, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

and there is no index defined on the PT column.

Now, this would work if GeoServer could emit the query:

SELECT PT as PT FROM (select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

Is there any way to get this to happen?

Or does anyone have other ideas about how to reduce query data size on Oracle?

[Martin_Davis1]

When you say “encode the search queries accordingly” do you mean leave off the spatial filter entirely? That won’t work in my case, there is too much data to not use an index. I think what I want is a way to force the use of another geometry column (but this seems like it would have to be a bit of a hack - ie. look around to see if there was another geometry column available and use that).

This sounds a bit like your second suggestion. A bbox query against the “original geometry” would work I think. Not sure how you would identify the “original geometry” though? It would have to be present in the SQLView columns, presumably - and then determined by just assuming the “other geometry column” was the one to filter against? Or would require UI/model changes to allow user to pick which geometry column is to be filtered by.

Or, drastically generalize SQLViews to allow user to fully specify the emitted query, substituting WMS_EXTENT parameters into his own custom SQL (+1 for this!)

···

On Tue, Mar 10, 2015 at 11:32 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Tue, Mar 10, 2015 at 7:27 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Well, good that there’s an issue for this.

I don’t understand how the SQL can be changed to not require a spatial index? What I’m suggesting is a way to use the spatial filter on the underlying geometry, but return a different geometry column. Not sure if this is possible in the current GS architecture?

It is, you just have to figure out that your column is built at runtime by the virtual table definition, and thus does not have a index, and encode the search queries accordingly.
Virtual table is just a special case of not having a spatial index (the other being having a real table without a spatial index).

But yes, you cannot query the original geometry because GeoServer cannot know how your computed geometry relates to the original geometry, and Oracle does not know either afaik… I believe other software allows you a special parameter where you could embed a simple bbox query against the original data… but that is really simpleton, in GeoServer we don’t just have bbox searches… but maybe
we could have some logic that tries to map back the query down to a bbox one, and have a primary filter/secondary filter approach (thus, keeping the real query against the computed geometry, but injecting a simple bbox one against the original geometry too)

Cheers

Andrea

–

==

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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 dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.

---

[Jukka_Rahkonen]

Hi,

I am not sure but perhaps you could fool Oracle by inserting the point geometry of the polygon into the SDO_POINT array of SDO_GEOMETRY. Oracle documentation says that if SDO_ELEM_INFO and SDO_ORDINATES arrays are not both null then SDO_POINT will be skipped. However, it would be so nice trick if that works that I suggest to make a try.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objrelschema.htm

-Jukka-

···

---

Martin Davis wrote:

Because I was hoping that GeoServer would make use of the GEOMETRY column in the spatial filter. I tried NOT including it first of all, and that didn’t work, since the filter was applied on the PT field, causing the no-index error. Unfortunately, GeoServer is too smart - if the SLD is based on the PT column then it uses that column in the spatial filter!

On Tue, Mar 10, 2015 at 11:29 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

Hi,

Why do you select geometry and centroid into SQL view if you are only interested in the centroid?

"select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP "

-Jukka Rahkonen-

Martin Davis wrote:

We’re working on tuning a GeoServer implementation over an Oracle SDO database. We think we are seeing that the network latency of queries is the hotspot in the performance of GeoServer render requests.

It seems that for some situations one way to reduce the data being pushed over the wire is to reduce the data size in the database query. In particular, for some datasets of polygons we’re happy just to see points representing the polygons. So we tried using a SQLView with a statement like:

select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP

and then used an SLD selector to render just the point.

However, this doesn’t work because we get the dreaded ORA-13226: interface not supported without a spatial index error. This is because GeoServer is actually emitting the query

SELECT PT as PT FROM (select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(PT, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

and there is no index defined on the PT column.

Now, this would work if GeoServer could emit the query:

SELECT PT as PT FROM (select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

Is there any way to get this to happen?

Or does anyone have other ideas about how to reduce query data size on Oracle?

---

[Martin_Davis1]

Do you mean creating a new SDO_GEOMETRY on the fly in the query, but using only a single point from the original geometry? Wouldn’t Oracle still detect that the “virtual geometry” doesn’t have an index? Or am I missing something? Can you give an example of what you mean?

···

On Tue, Mar 10, 2015 at 11:46 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

Hi,

I am not sure but perhaps you could fool Oracle by inserting the point geometry of the polygon into the SDO_POINT array of SDO_GEOMETRY. Oracle documentation says that if SDO_ELEM_INFO and SDO_ORDINATES arrays are not both null then SDO_POINT will be skipped. However, it would be so nice trick if that works that I suggest to make a try.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objrelschema.htm

-Jukka-

---

Martin Davis wrote:

Because I was hoping that GeoServer would make use of the GEOMETRY column in the spatial filter. I tried NOT including it first of all, and that didn’t work, since the filter was applied on the PT field, causing the no-index error. Unfortunately, GeoServer is too smart - if the SLD is based on the PT column then it uses that column in the spatial filter!

On Tue, Mar 10, 2015 at 11:29 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

Hi,

Why do you select geometry and centroid into SQL view if you are only interested in the centroid?

"select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP "

-Jukka Rahkonen-

---

Martin Davis wrote:

We’re working on tuning a GeoServer implementation over an Oracle SDO database. We think we are seeing that the network latency of queries is the hotspot in the performance of GeoServer render requests.

It seems that for some situations one way to reduce the data being pushed over the wire is to reduce the data size in the database query. In particular, for some datasets of polygons we’re happy just to see points representing the polygons. So we tried using a SQLView with a statement like:

select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP

and then used an SLD selector to render just the point.

However, this doesn’t work because we get the dreaded ORA-13226: interface not supported without a spatial index error. This is because GeoServer is actually emitting the query

SELECT PT as PT FROM (select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(PT, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

and there is no index defined on the PT column.

Now, this would work if GeoServer could emit the query:

SELECT PT as PT FROM (select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

Is there any way to get this to happen?

Or does anyone have other ideas about how to reduce query data size on Oracle?

[Jukka_Rahkonen]

SDO_GEOMETRY is built this way:

CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER, 
 SDO_SRID NUMBER,
 SDO_POINT SDO_POINT_TYPE,
 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES SDO_ORDINATE_ARRAY);

You can query "select sdo_point from geometry_column" if SDO_POINT has data in it. 
Like you query "select SDO_SRID from geometry_column". However, I do not know if it works. What should work
is to create a secondary column of SDO_GEOMETRY, populate it with centroid, build spatial index etc. That way 
you would not need to copy all the normal attributes as if you would make a new table for points.

-Jukka-

···

---

Martin Davis wrote:

Do you mean creating a new SDO_GEOMETRY on the fly in the query, but using only a single point from the original geometry? Wouldn’t Oracle still detect that the “virtual geometry” doesn’t have an index? Or am I missing something? Can you give an example of what you mean?

On Tue, Mar 10, 2015 at 11:46 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

Hi,

I am not sure but perhaps you could fool Oracle by inserting the point geometry of the polygon into the SDO_POINT array of SDO_GEOMETRY. Oracle documentation says that if SDO_ELEM_INFO and SDO_ORDINATES arrays are not both null then SDO_POINT will be skipped. However, it would be so nice trick if that works that I suggest to make a try.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objrelschema.htm

-Jukka-

Martin Davis wrote:

Because I was hoping that GeoServer would make use of the GEOMETRY column in the spatial filter. I tried NOT including it first of all, and that didn’t work, since the filter was applied on the PT field, causing the no-index error. Unfortunately, GeoServer is too smart - if the SLD is based on the PT column then it uses that column in the spatial filter!

On Tue, Mar 10, 2015 at 11:29 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

Hi,

Why do you select geometry and centroid into SQL view if you are only interested in the centroid?

"select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP "

-Jukka Rahkonen-

Martin Davis wrote:

We’re working on tuning a GeoServer implementation over an Oracle SDO database. We think we are seeing that the network latency of queries is the hotspot in the performance of GeoServer render requests.

It seems that for some situations one way to reduce the data being pushed over the wire is to reduce the data size in the database query. In particular, for some datasets of polygons we’re happy just to see points representing the polygons. So we tried using a SQLView with a statement like:

select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP

and then used an SLD selector to render just the point.

However, this doesn’t work because we get the dreaded ORA-13226: interface not supported without a spatial index error. This is because GeoServer is actually emitting the query

SELECT PT as PT FROM (select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(PT, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

and there is no index defined on the PT column.

Now, this would work if GeoServer could emit the query:

SELECT PT as PT FROM (select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

Is there any way to get this to happen?

Or does anyone have other ideas about how to reduce query data size on Oracle?

---

---

[Martin_Davis1]

I’m pretty sure the SDO_POINT field is only populated if the geometry is in fact a point (this is to reduce the size of the geometry object). So that won’t work in our case, since the data is polygons.

We could indeed create a new column, populated via a trigger, and create an index on it (I think - might need a functional spatial index perhaps). But that’s a much heavier weight solution than needed. All the capability is there in Oracle to make a query returning points using a spatial filter against the geometry column. GeoServer just needs to be able to emit the required queries.

···

On Tue, Mar 10, 2015 at 11:58 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

SDO_GEOMETRY is built this way:

CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER, 
 SDO_SRID NUMBER,
 SDO_POINT SDO_POINT_TYPE,
 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES SDO_ORDINATE_ARRAY);

You can query "select sdo_point from geometry_column" if SDO_POINT has data in it. 
Like you query "select SDO_SRID from geometry_column". However, I do not know if it works. What should work
is to create a secondary column of SDO_GEOMETRY, populate it with centroid, build spatial index etc. That way 
you would not need to copy all the normal attributes as if you would make a new table for points.

-Jukka-

---

Martin Davis wrote:

Do you mean creating a new SDO_GEOMETRY on the fly in the query, but using only a single point from the original geometry? Wouldn’t Oracle still detect that the “virtual geometry” doesn’t have an index? Or am I missing something? Can you give an example of what you mean?

On Tue, Mar 10, 2015 at 11:46 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

Hi,

I am not sure but perhaps you could fool Oracle by inserting the point geometry of the polygon into the SDO_POINT array of SDO_GEOMETRY. Oracle documentation says that if SDO_ELEM_INFO and SDO_ORDINATES arrays are not both null then SDO_POINT will be skipped. However, it would be so nice trick if that works that I suggest to make a try.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14255/sdo_objrelschema.htm

-Jukka-

---

Martin Davis wrote:

Because I was hoping that GeoServer would make use of the GEOMETRY column in the spatial filter. I tried NOT including it first of all, and that didn’t work, since the filter was applied on the PT field, causing the no-index error. Unfortunately, GeoServer is too smart - if the SLD is based on the PT column then it uses that column in the spatial filter!

On Tue, Mar 10, 2015 at 11:29 AM, Rahkonen Jukka (MML) <jukka.rahkonen@anonymised.com> wrote:

Hi,

Why do you select geometry and centroid into SQL view if you are only interested in the centroid?

"select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP "

-Jukka Rahkonen-

---

Martin Davis wrote:

We’re working on tuning a GeoServer implementation over an Oracle SDO database. We think we are seeing that the network latency of queries is the hotspot in the performance of GeoServer render requests.

It seems that for some situations one way to reduce the data being pushed over the wire is to reduce the data size in the database query. In particular, for some datasets of polygons we’re happy just to see points representing the polygons. So we tried using a SQLView with a statement like:

select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP

and then used an SLD selector to render just the point.

However, this doesn’t work because we get the dreaded ORA-13226: interface not supported without a spatial index error. This is because GeoServer is actually emitting the query

SELECT PT as PT FROM (select GEOMETRY, SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(PT, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

and there is no index defined on the PT column.

Now, this would work if GeoServer could emit the query:

SELECT PT as PT FROM (select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP t) VTABLE WHERE SDO_FILTER(GEOMETRY, ?, ‘mask=anyinteract querytype=WINDOW’) = ‘TRUE’

Is there any way to get this to happen?

Or does anyone have other ideas about how to reduce query data size on Oracle?

[Andrea_Aime4]

On Tue, Mar 10, 2015 at 7:45 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

When you say "encode the search queries accordingly" do you mean leave off
the spatial filter entirely? That won't work in my case, there is too much
data to not use an index. I think what I want is a way to force the use of
another geometry column (but this seems like it would have to be a bit of a
hack - ie. look around to see if there was another geometry column
available and use that).

This sounds a bit like your second suggestion. A bbox query against the
"original geometry" would work I think. Not sure how you would identify
the "original geometry" though? It would have to be present in the SQLView
columns, presumably - and then determined by just assuming the "other
geometry column" was the one to filter against? Or would require UI/model
changes to allow user to pick which geometry column is to be filtered by.

You would not identify the original geometry, GeoServer does not parse the
sql view at all, does not know anything about it, and I believe it should
stay that way, because doing otherwise would open a big can on worms (sql
parsing, database differences, understanding calling stored procedures...
ooof)

Nope, what I'm suggesting is more similar to the mapserver way where they
provide a "box" template parameter that will be replaced with a real bbox:

DATA "the_geom from (select g.gid, ST_Union(g.the_geom, 10.0) as
      the_geom from geotable g where ST_Intersects(g.geom,!BOX!)) as
      subquery using unique gid using srid=4326"

Now... I find the above still quite unsatisfactory, as you are forcing a
intersection test even when you don't have one in your original query,
and you have to make up a non existent bbox.

Maybe something like this instead (open to alternative syntax):

select .... from geotable where .... %BOX%{and ST_Intersects(g.geom,$bbox)}

you know, a bit of sql that would be explanded only if we find that the
query condition we have going on can actually be
matched to some bbox (there is a filter visitor taking a filter and
returning a bbox that would contain its results).

This would be the "primary filter", the encoding of the full ifilter would
still happen against the computed geometry like today,
but it would end up being a "secondary filter" that just makes sure we
return the right features (the fact the original geometry
intersects the bbox does not mean its centroid matches the original query,
and we need to provide correct responses, as
this would also be used for WFS)

Probably not a quick hack, but doable.

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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 dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

[Martin_Davis1]

Not sure I get all the constraints around this 100%, but generally the idea sounds good. I like the idea of being able to take control of the filter condition SQL being used (which pretty much requires access to the $bbox parameter). And it’s a good idea to make it optional, so it isn’t always executed.

Just to be clear, the %BOX%{and ST_Intersects(g.geom,$bbox)} syntax would be added to the SQLView SQL text? Is the $bbox variable already available?

Would an alternative be to use the same kind of syntax to specify the actual geometry column to use in the bbox filter? That would be less general, but also less error-prone.

···

On Tue, Mar 10, 2015 at 1:13 PM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Tue, Mar 10, 2015 at 7:45 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

When you say “encode the search queries accordingly” do you mean leave off the spatial filter entirely? That won’t work in my case, there is too much data to not use an index. I think what I want is a way to force the use of another geometry column (but this seems like it would have to be a bit of a hack - ie. look around to see if there was another geometry column available and use that).

This sounds a bit like your second suggestion. A bbox query against the “original geometry” would work I think. Not sure how you would identify the “original geometry” though? It would have to be present in the SQLView columns, presumably - and then determined by just assuming the “other geometry column” was the one to filter against? Or would require UI/model changes to allow user to pick which geometry column is to be filtered by.

You would not identify the original geometry, GeoServer does not parse the sql view at all, does not know anything about it, and I believe it should
stay that way, because doing otherwise would open a big can on worms (sql parsing, database differences, understanding calling stored procedures… ooof)

Nope, what I’m suggesting is more similar to the mapserver way where they provide a “box” template parameter that will be replaced with a real bbox:

DATA “the_geom from (select g.gid, ST_Union(g.the_geom, 10.0) as
the_geom from geotable g where ST_Intersects(g.geom,!BOX!)) as
subquery using unique gid using srid=4326”

Now… I find the above still quite unsatisfactory, as you are forcing a intersection test even when you don’t have one in your original query,
and you have to make up a non existent bbox.

Maybe something like this instead (open to alternative syntax):

select … from geotable where … %BOX%{and ST_Intersects(g.geom,$bbox)}

you know, a bit of sql that would be explanded only if we find that the query condition we have going on can actually be
matched to some bbox (there is a filter visitor taking a filter and returning a bbox that would contain its results).

This would be the “primary filter”, the encoding of the full ifilter would still happen against the computed geometry like today,
but it would end up being a “secondary filter” that just makes sure we return the right features (the fact the original geometry
intersects the bbox does not mean its centroid matches the original query, and we need to provide correct responses, as
this would also be used for WFS)

Probably not a quick hack, but doable.

[Andrea_Aime4]

On Tue, Mar 10, 2015 at 11:07 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Not sure I get all the constraints around this 100%, but generally the
idea sounds good. I like the idea of being able to take control of the
filter condition SQL being used (which pretty much requires access to the
$bbox parameter). And it's a good idea to make it optional, so it isn't
always executed.

Just to be clear, the %BOX%{and ST_Intersects(g.geom,$bbox)} syntax would
be added to the SQLView SQL text?

Yep

Is the $bbox variable already available?

No, it would be something new that we replace only withing the context of a
%BOX%{....} thing (aside, not really in love with the syntax,
any suggestion to make it look better? or just can just say that you like
it)

Would an alternative be to use the same kind of syntax to specify the
actual geometry column to use in the bbox filter? That would be less
general, but also less error-prone.

No, that would require geoserver to understand the sql, and know where to
add the filter, as said, would like to avoid going there.
You know your sql, you place the optional box filter in the suitable
position, with the suitable syntax.
For the point from x, y case we might also want to provide
minx,miny,maxx,maxy.... actually, just providing these
could also be the way to go, if you want to build an envelope you have the
spatial db syntax to do so.
Just thinking out loud.

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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 dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

[Martin_Davis1]

On Tue, Mar 10, 2015 at 4:23 PM, Andrea Aime <andrea.aime@anonymised.com>
wrote:

On Tue, Mar 10, 2015 at 11:07 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Not sure I get all the constraints around this 100%, but generally the
idea sounds good. I like the idea of being able to take control of the
filter condition SQL being used (which pretty much requires access to the
$bbox parameter). And it's a good idea to make it optional, so it isn't
always executed.

Just to be clear, the %BOX%{and ST_Intersects(g.geom,$bbox)} syntax would
be added to the SQLView SQL text?

Yep

Is the $bbox variable already available?

No, it would be something new that we replace only withing the context of
a %BOX%{....} thing (aside, not really in love with the syntax,
any suggestion to make it look better? or just can just say that you like
it)

Yes, syntax is a bit ugly, but it follows the current pattern of using %
for parameters. The only change I might suggest is to use:

%BBOX({...})%

(That is, % delimits the entire substituted expression, which can itself
contain parameters with the ( ) block. The { } delimits raw code strings.
  This syntax would support future extensions fairly well I think)

Would an alternative be to use the same kind of syntax to specify the
actual geometry column to use in the bbox filter? That would be less
general, but also less error-prone.

No, that would require geoserver to understand the sql, and know where to
add the filter, as said, would like to avoid going there.

Why? There would be no more parsing of the SQL than currently done to
replace parameters. What I'm suggesting is to supply a hint to the WMS SLQ
builder, to use a different geometry name than the one identified from the
query. E.g.

select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP
%QUERY_GEOM(GEOMETRY)%

The parameter would be removed from the SQL (e.g. substituted with '') and
the contents provided as a hint to the query builder. But maybe I'm not
understanding how the current process works?

You know your sql, you place the optional box filter in the suitable
position, with the suitable syntax.
For the point from x, y case we might also want to provide
minx,miny,maxx,maxy.... actually, just providing these
could also be the way to go, if you want to build an envelope you have the
spatial db syntax to do so.
Just thinking out loud.

+1 on providing minx, miny, maxx, maxy - that's more general-purpose.

<wild craziness starts here>

To think of this in a more general way, the issue under discussion reflects
the fundamental dichotomy between WMS (rendering) queries and WFS (data)
queries. For WMS queries it's useful to allow lots of scope to manipulate
the data to reduce size, etc, whereas for WFS just the raw data is
required. GeoServer already provides lots of capability to manipulate WMS
data in the engine (e.g. Geometry Transformations), but currently limits
what can be expressed on the server side. The recent PostGIS simplify
addition is an example of providing a bit more capability, but IMHO that is
still too limited and hard-coded.

In the extreme case I can see that the user might specify an entirely
different query in the SQLView for WMS usage. That's a bit extreme and
error prone, though. So dialing that back a bit we get closer to what
we're talking about - where various parts of the query can be altered for
WMS usage. So far we've just talked about the filter part, but I think it
might be nice to allow the geometry column to be manipulated as well - i.e.
by substituting an entirely new expression for it. This would subsume the
current PostGIS simplify capability, but in a much more general way. For
example, a query could convert the geometry to a point, or simplify it
using a custom function (e.g. decimation), etc.

This might look like:

select %GEOM( GEOMETRY, {SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) GEOMETRY } from
CUT_BLOCK_SP

Note that the %QUERY_GEOM% parameter is not needed here, because the
transformed geometry has the same name as the original, so it's fine to use
the default GeoServer-generated filter.

[Andrea_Aime4]

On Wed, Mar 11, 2015 at 8:12 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

On Tue, Mar 10, 2015 at 4:23 PM, Andrea Aime <andrea.aime@anonymised.com
> wrote:

On Tue, Mar 10, 2015 at 11:07 PM, Martin Davis <mtnclimb@anonymised.com>
wrote:

Not sure I get all the constraints around this 100%, but generally the
idea sounds good. I like the idea of being able to take control of the
filter condition SQL being used (which pretty much requires access to the
$bbox parameter). And it's a good idea to make it optional, so it isn't
always executed.

Just to be clear, the %BOX%{and ST_Intersects(g.geom,$bbox)} syntax
would be added to the SQLView SQL text?

Yep

Is the $bbox variable already available?

No, it would be something new that we replace only withing the context of
a %BOX%{....} thing (aside, not really in love with the syntax,
any suggestion to make it look better? or just can just say that you like
it)

Yes, syntax is a bit ugly, but it follows the current pattern of using %
for parameters. The only change I might suggest is to use:

%BBOX({...})%

(That is, % delimits the entire substituted expression, which can itself
contain parameters with the ( ) block. The { } delimits raw code strings.
  This syntax would support future extensions fairly well I think)

I like the idea of % going around the entire code, not sure about the
double parenthesis ({ })... any specific reason for it?

Would an alternative be to use the same kind of syntax to specify the
actual geometry column to use in the bbox filter? That would be less
general, but also less error-prone.

No, that would require geoserver to understand the sql, and know where to
add the filter, as said, would like to avoid going there.

Why? There would be no more parsing of the SQL than currently done to
replace parameters. What I'm suggesting is to supply a hint to the WMS SLQ
builder, to use a different geometry name than the one identified from the
query. E.g.

select SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) PT from CUT_BLOCK_SP
%QUERY_GEOM(GEOMETRY)%

We do not do any parsing, just a lame string replace? And you are assuming
you have a source geometry, which currently the
sql views do not have. If you are making a point from x/y you don't really
have anything to work against.
If you have a geometry with funky processing on it (e.g., a offset) then
you don't want to apply the filter on the original geometry,
you need to un-offset it first.

You know your sql, you place the optional box filter in the suitable

position, with the suitable syntax.
For the point from x, y case we might also want to provide
minx,miny,maxx,maxy.... actually, just providing these
could also be the way to go, if you want to build an envelope you have
the spatial db syntax to do so.
Just thinking out loud.

+1 on providing minx, miny, maxx, maxy - that's more general-purpose.

Cool

<wild craziness starts here>

To think of this in a more general way, the issue under discussion
reflects the fundamental dichotomy between WMS (rendering) queries and WFS
(data) queries. For WMS queries it's useful to allow lots of scope to
manipulate the data to reduce size, etc, whereas for WFS just the raw data
is required. GeoServer already provides lots of capability to manipulate
WMS data in the engine (e.g. Geometry Transformations), but currently
limits what can be expressed on the server side. The recent PostGIS
simplify addition is an example of providing a bit more capability, but
IMHO that is still too limited and hard-coded.

In the extreme case I can see that the user might specify an entirely
different query in the SQLView for WMS usage. That's a bit extreme and
error prone, though. So dialing that back a bit we get closer to what
we're talking about - where various parts of the query can be altered for
WMS usage. So far we've just talked about the filter part, but I think it
might be nice to allow the geometry column to be manipulated as well - i.e.
by substituting an entirely new expression for it. This would subsume the
current PostGIS simplify capability, but in a much more general way. For
example, a query could convert the geometry to a point, or simplify it
using a custom function (e.g. decimation), etc.

This might look like:

select %GEOM( GEOMETRY, {SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) GEOMETRY }
from CUT_BLOCK_SP

Note that the %QUERY_GEOM% parameter is not needed here, because the
transformed geometry has the same name as the original, so it's fine to use
the default GeoServer-generated filter.

Hemm... not in a place that allows me to think about it (and won't be for
several days) but... but I have the impression you're thinking about it
in too general terms, and it's not really fitting with the reality of the
actual operations and/or the existing code (at least, I read it and it's not
readily making sense to me).

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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 dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

[Martin_Davis1]

The reason for the { } is to allow for multiple comma-separated parameters in the future. If commas are used as separators, then something needs to delimit any SQL code provided. But I realize this is looking pretty far ahead.

Formally, the syntax is:

subst := % name [ ( paramList ) ] %
paramList := param [ , param ]
param := simple-string | { complex-string }

···

I like the idea of % going around the entire code, not sure about the double parenthesis ({ })… any specific reason for it?

Yes, syntax is a bit ugly, but it follows the current pattern of using % for parameters. The only change I might suggest is to use:

%BBOX({…})%

(That is, % delimits the entire substituted expression, which can itself contain parameters with the ( ) block. The { } delimits raw code strings. This syntax would support future extensions fairly well I think)

[Martin_Davis1]

In the extreme case I can see that the user might specify an entirely
different query in the SQLView for WMS usage. That's a bit extreme and
error prone, though. So dialing that back a bit we get closer to what
we're talking about - where various parts of the query can be altered for
WMS usage. So far we've just talked about the filter part, but I think it
might be nice to allow the geometry column to be manipulated as well - i.e.
by substituting an entirely new expression for it. This would subsume the
current PostGIS simplify capability, but in a much more general way. For
example, a query could convert the geometry to a point, or simplify it
using a custom function (e.g. decimation), etc.

This might look like:

select %GEOM( GEOMETRY, {SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) GEOMETRY }
from CUT_BLOCK_SP

Note that the %QUERY_GEOM% parameter is not needed here, because the
transformed geometry has the same name as the original, so it's fine to use
the default GeoServer-generated filter.

Erk - I just realized that the %BBOX% parameter is still needed, because

otherwise Oracle will realize there is no index on the computed expression
(because it has the same name as the underlying GEOMETRY, and so that is
the value bound to the GEOMETRY in the spatial filter emitted by GeoServer.

Maybe a better name for %BBOX% would be something like %VIEW_FILTER% or
%USER_FILTER%? Assuming the change is made to substitute $minx, $maxx,
etc.

Hmmm... would this provide a way to create a spatial view on top of an XY
table?! E.g.:

SELECT SDO_GEOMETRY(2001, srid, SDO_POINT_TYPE(x, y, NULL), NULL, NULL))
FROM tablexy WHERE true AND %BBOX{ x >= $ minx AND x <= $maxx AND y >=
$miny AND y <= $maxy }%

[Martin_Davis1]

<wild craziness starts here>

To think of this in a more general way, the issue under discussion
reflects the fundamental dichotomy between WMS (rendering) queries and WFS
(data) queries. For WMS queries it's useful to allow lots of scope to
manipulate the data to reduce size, etc, whereas for WFS just the raw data
is required. GeoServer already provides lots of capability to manipulate
WMS data in the engine (e.g. Geometry Transformations), but currently
limits what can be expressed on the server side. The recent PostGIS
simplify addition is an example of providing a bit more capability, but
IMHO that is still too limited and hard-coded.

In the extreme case I can see that the user might specify an entirely
different query in the SQLView for WMS usage. That's a bit extreme and
error prone, though. So dialing that back a bit we get closer to what
we're talking about - where various parts of the query can be altered for
WMS usage. So far we've just talked about the filter part, but I think it
might be nice to allow the geometry column to be manipulated as well - i.e.
by substituting an entirely new expression for it. This would subsume the
current PostGIS simplify capability, but in a much more general way. For
example, a query could convert the geometry to a point, or simplify it
using a custom function (e.g. decimation), etc.

This might look like:

select %GEOM( GEOMETRY, {SDO_GEOM.SDO_CENTROID(GEOMETRY, 1) GEOMETRY }
from CUT_BLOCK_SP

Note that the %QUERY_GEOM% parameter is not needed here, because the
transformed geometry has the same name as the original, so it's fine to use
the default GeoServer-generated filter.

Hemm... not in a place that allows me to think about it (and won't be for
several days) but... but I have the impression you're thinking about it
in too general terms, and it's not really fitting with the reality of the
actual operations and/or the existing code (at least, I read it and it's not
readily making sense to me).

Let me put it another way. I'm proposing a way of giving the user the

ability to define how geometries are transformed on the DB side to make
them more efficient for WMS rendering (while still making the raw data
available for WFS). The current PostGIS & DB2 simplify hacks are examples
of doing this in a hard-coded way. But they're pretty limited. To do this
more generally seems to require two things:

1. allow specifying a SQL transformation expression to be invoked only when
the query is being made for WMS rendering
2. this may require some special handling of the WMS spatial filter (as
provided by the %BBOX% parameter being discussed)

MapServer apparently allows this, because it lets the user specify the
entire query to be used.

[Andrea_Aime4]

On Wed, Mar 11, 2015 at 11:35 PM, Martin Davis <mtnclimb@anonymised.com> wrote:

Let me put it another way. I'm proposing a way of giving the user the
ability to define how geometries are transformed on the DB side to make
them more efficient for WMS rendering (while still making the raw data
available for WFS). The current PostGIS & DB2 simplify hacks are examples
of doing this in a hard-coded way. But they're pretty limited.

I believe you're not appreciating what GeoServer tries to be here. It's
meant for end users, forcing people to write sql queries by hand in order
to get
the benefits of generalization is simply not acceptable, a checkbox is all
that many users are willing/able to deal with.

And I'm not saying that we should not care about developers that want to
push the boundaries, we also want to reach there and improve
over those cases of course (we would not have sql views to start with if we
were not trying), but belittling the on the fly generalitation
work as a hack is imho missing the point of what GeoServer tries to provide
to the larger user base.

To do this more generally seems to require two things:

1. allow specifying a SQL transformation expression to be invoked only
when the query is being made for WMS rendering

What I proposed previously (the bbox thing) has no WMS specific
limitations, and should still allow you to get what you want.

2. this may require some special handling of the WMS spatial filter (as
provided by the %BBOX% parameter being discussed)

MapServer apparently allows this, because it lets the user specify the
entire query to be used.

GeoServer does something similar with sql views, but it's more ambitious in
the implementation in that it pushes
more filters down into the db, not just the bbox, and it's not geared
towards wms only, but any type of data access.
You are having a problem with it because your sql view is creating a
geometry on the fly, it's a case we
don't cover efficiently now, but we can improve on it.

Cheers
Andrea

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 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 dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------