[Geotools-devel] JDBC Plugin for SAP HANA

2018-03-13 Thread Uhrig, Stefan 
Hi,

I would like to contribute a JDBC Plugin for the SAP HANA database.

High-level information on HANA is available at 
https://www.sap.com/products/hana.html (in case you are interested). There is 
also an Express Edition of HANA that is free for up to 32GB of data 
(https://www.sap.com/cmp/ft/crm-xu16-dat-hddedft/index.html).

Do you have general objections to such a contribution? I was asked by our Open 
Source department to clarify that before we start the whole contribution 
process.

Thanks and best regards,
Stefan

Stefan Uhrig
Development Expert
SAP SE, Dietmar-Hopp-Allee 16, 69190 Walldorf, Germany
stefan.uh...@sap.com


--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

Re: [Geotools-devel] JDBC Plugin for SAP HANA

2018-09-21 Thread Uhrig, Stefan 
Hi all,

My company finally managed to sign the Corporate CLA and Astrid Emde from OSGeo 
confirmed that we might contribute to OSGeo projects now.

I uploaded the JDBC plugin for HANA to GitHub and will create a pull request 
after sending this mail. The code is located in the folder Andrea pointed out. 
I tried to get everything right, but probably I’ve overlooked something. 
Feedback is welcome.

We would like our module to become an officially supported one. I would act as 
module maintainer. Can you help us with the necessary steps?

Thank you in advance!

Cheers,
Stefan


From: andrea.a...@gmail.com  On Behalf Of Andrea Aime
Sent: Dienstag, 13. März 2018 14:19
To: Uhrig, Stefan 
Cc: geotools-devel@lists.sourceforge.net
Subject: Re: [Geotools-devel] JDBC Plugin for SAP HANA

Hi Stefan,
contributions are welcomed, SAP just needs to sign a OSGeo CLA:
http://docs.geotools.org/latest/developer/procedures/contribution_license.html

You can then make a pull request, adding the module around here: 
https://github.com/geotools/geotools/tree/master/modules/unsupported/jdbc-ng

See also general contribution rules here:
https://github.com/geotools/geotools/blob/master/CONTRIBUTING.md

You'll also be given direct commit access, with the agreement that you just 
commit
within your module, for module maintenance (can also change core classes if 
needed,
but please go though a pull request for those changes).

The module will start as an unsupported one, and then if you're interested in 
having
it as officially supported, there is a procedure to follow (some basic quality 
checks, and
an official module maintainer are needed, typically you or someone else from 
SAP):

http://docs.geotools.org/latest/developer/procedures/supported.html

Hope this helps, if you have any question, just ask :-)

Cheers
Andrea


On Tue, Mar 13, 2018 at 1:40 PM, Uhrig, Stefan 
mailto:stefan.uh...@sap.com>> wrote:
Hi,

I would like to contribute a JDBC Plugin for the SAP HANA database.

High-level information on HANA is available at 
https://www.sap.com/products/hana.html (in case you are interested). There is 
also an Express Edition of HANA that is free for up to 32GB of data 
(https://www.sap.com/cmp/ft/crm-xu16-dat-hddedft/index.html).

Do you have general objections to such a contribution? I was asked by our Open 
Source department to clarify that before we start the whole contribution 
process.

Thanks and best regards,
Stefan

Stefan Uhrig
Development Expert
SAP SE, Dietmar-Hopp-Allee 16, 69190 Walldorf, Germany
stefan.uh...@sap.com<mailto:stefan.uh...@sap.com>



--
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net<mailto:GeoTools-Devel@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/geotools-devel



--

Regards,

Andrea Aime

==
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 di Montramito 3/A
55054  Massarosa (LU)
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 c

[Geotools-devel] JDBC Plugin for SAP HANA - Request for promotion to supported module

2018-12-18 Thread Uhrig, Stefan 
Hi all,

I’d like to request that the JDBC Plugin for SAP HANA 
(modules/unsupported/jdbc-ng/jdbc-hana) gets promoted to a supported module.

With regards to the Gold Star Quality Assurance Check (Plugin QA Test) I’ve 
taken the following steps:

  *   I’ve added module status files (NOTICE.txt, README.md, REVIEW.md) with 
https://github.com/geotools/geotools/pull/2213, but review is still missing, of 
course.
  *   There are no known bugs.
  *   I’ve uploaded some screenshots of the plugin being used in GeoServer to 
https://sap-my.sharepoint.com/:f:/p/stefan_uhrig/EpIpvp4ghn9Pthe020gkP5wBQXyte0U1kNpFt2hXt1t-6Q?e=tprHsR.
 I created some layers on OpenStreetMap data of the county I’m living in. I’m 
not much of a cartographer, but it should hopefully suffice to show that the 
plugin can work with larger amounts of data.
  *   Documentation is available from 
http://docs.geotools.org/latest/userguide/library/jdbc/hana.html. I will act as 
module maintainer and answer questions regarding the module.

There is also a Jacoco coverage report at 
https://sap-my.sharepoint.com/:f:/p/stefan_uhrig/EpIpvp4ghn9Pthe020gkP5wBQXyte0U1kNpFt2hXt1t-6Q?e=tprHsR
 (I tried to attach it to the mail, but the mailing list rejected attachments). 
Unit-test line coverage is about 89%, branch coverage about 81%.

I’d appreciate if someone could do the IP check. Are there any further steps 
that are necessary to become a supported module? If you have any questions, 
don’t hesitate to ask.

Thanks and best regards,
Stefan


From: Andrea Aime 
mailto:andrea.a...@geo-solutions.it>>
Sent: Freitag, 21. September 2018 15:34
To: Uhrig, Stefan mailto:stefan.uh...@sap.com>>
Cc: Geotools-Devel list 
mailto:geotools-devel@lists.sourceforge.net>>
Subject: Re: [Geotools-devel] JDBC Plugin for SAP HANA

On Fri, Sep 21, 2018 at 2:46 PM Uhrig, Stefan 
mailto:stefan.uh...@sap.com>> wrote:

I don't have time to check the PR right now, but let me answer the quick and 
easy part :-)

We would like our module to become an officially supported one. I would act as 
module maintainer. Can you help us with the necessary steps?

The steps are documented in these two documents:
http://docs.geotools.org/latest/developer/procedures/supported.html
http://docs.geotools.org/latest/developer/procedures/check.html

The instructions are a bit outdated, code coverage cannot be verified anymore 
with cobertura since the switch to Java 8,
but you can use Jacoco or you IDE of choice to deliver a report.

Cheers
Andrea

==

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 di Montramito 3/A 55054 Massarosa (LU) phone: +39 0584 962313 fax: 
+39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it 
http://twitter.com/geosolutions_it 
--- Con riferimento alla 
normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento 
generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza 
inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è 
un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo 
scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, 
ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene 
notizia. This email is intended only for the person or entity to which it is 
addressed and may contain information that is privileged, confidential or 
otherwise protected from disclosure. We remind that - as provided by European 
Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or 
the information herein by anyone other than the intended recipient is 
prohibited. If you have received this email by mistake, please notify us 
immediately by telephone or e-mail.
___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

[Geotools-devel] Friendly reminder: JDBC Plugin for SAP HANA - Request for promotion to supported module

2019-07-29 Thread Uhrig, Stefan 
Hi all,

This is meant as a friendly reminder regarding my request to become a supported 
module.

The old link to the screenshots and the code coverage report expired, the new 
link is 
https://sap-my.sharepoint.com/:f:/p/stefan_uhrig/EpIpvp4ghn9Pthe020gkP5wBUp-4-RZohlklWQboX7LqVg.

SAP has also published a blog article that explains how to use the plugin with 
GeoServer in the meantime: 
https://blogs.sap.com/2019/03/28/how-to-connect-sap-hana-with-geoserver/. 
However, it would be nice if we could provide the plugin directly via 
http://geoserver.org/release/stable/.

If you have any questions or something else is missing, don’t hesitate to 
contact me.

Thanks and best regards,
Stefan


From: Uhrig, Stefan
Sent: Tuesday, December 18, 2018 1:04 PM
To: 'Geotools-Devel list' 
Subject: JDBC Plugin for SAP HANA - Request for promotion to supported module

Hi all,

I’d like to request that the JDBC Plugin for SAP HANA 
(modules/unsupported/jdbc-ng/jdbc-hana) gets promoted to a supported module.

With regards to the Gold Star Quality Assurance Check (Plugin QA Test) I’ve 
taken the following steps:

  *   I’ve added module status files (NOTICE.txt, README.md, REVIEW.md) with 
https://github.com/geotools/geotools/pull/2213, but review is still missing, of 
course.
  *   There are no known bugs.
  *   I’ve uploaded some screenshots of the plugin being used in GeoServer to 
https://sap-my.sharepoint.com/:f:/p/stefan_uhrig/EpIpvp4ghn9Pthe020gkP5wBQXyte0U1kNpFt2hXt1t-6Q?e=tprHsR.
 I created some layers on OpenStreetMap data of the county I’m living in. I’m 
not much of a cartographer, but it should hopefully suffice to show that the 
plugin can work with larger amounts of data.
  *   Documentation is available from 
http://docs.geotools.org/latest/userguide/library/jdbc/hana.html. I will act as 
module maintainer and answer questions regarding the module.

There is also a Jacoco coverage report at 
https://sap-my.sharepoint.com/:f:/p/stefan_uhrig/EpIpvp4ghn9Pthe020gkP5wBQXyte0U1kNpFt2hXt1t-6Q?e=tprHsR
 (I tried to attach it to the mail, but the mailing list rejected attachments). 
Unit-test line coverage is about 89%, branch coverage about 81%.

I’d appreciate if someone could do the IP check. Are there any further steps 
that are necessary to become a supported module? If you have any questions, 
don’t hesitate to ask.

Thanks and best regards,
Stefan


From: Andrea Aime 
mailto:andrea.a...@geo-solutions.it>>
Sent: Freitag, 21. September 2018 15:34
To: Uhrig, Stefan mailto:stefan.uh...@sap.com>>
Cc: Geotools-Devel list 
mailto:geotools-devel@lists.sourceforge.net>>
Subject: Re: [Geotools-devel] JDBC Plugin for SAP HANA

On Fri, Sep 21, 2018 at 2:46 PM Uhrig, Stefan 
mailto:stefan.uh...@sap.com>> wrote:

I don't have time to check the PR right now, but let me answer the quick and 
easy part :-)

We would like our module to become an officially supported one. I would act as 
module maintainer. Can you help us with the necessary steps?

The steps are documented in these two documents:
http://docs.geotools.org/latest/developer/procedures/supported.html
http://docs.geotools.org/latest/developer/procedures/check.html

The instructions are a bit outdated, code coverage cannot be verified anymore 
with cobertura since the switch to Java 8,
but you can use Jacoco or you IDE of choice to deliver a report.

Cheers
Andrea

==

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 di Montramito 3/A 55054 Massarosa (LU) phone: +39 0584 962313 fax: 
+39 0584 1660272 mob: +39 339 8844549 http://www.geo-solutions.it 
http://twitter.com/geosolutions_it 
--- Con riferimento alla 
normativa sul trattamento dei dati personali (Reg. UE 2016/679 - Regolamento 
generale sulla protezione dei dati “GDPR”), si precisa che ogni circostanza 
inerente alla presente email (il suo contenuto, gli eventuali allegati, etc.) è 
un dato la cui conoscenza è riservata al/i solo/i destinatario/i indicati dallo 
scrivente. Se il messaggio Le è giunto per errore, è tenuta/o a cancellarlo, 
ogni altra operazione è illecita. Le sarei comunque grato se potesse darmene 
notizia. This email is intended only for the person or entity to which it is 
addressed and may contain information that is privileged, confidential or 
otherwise protected from disclosure. We remind that - as provided by European 
Regulation 2016/679 “GDPR” - copying, dissemination or use of this e-mail or 
the information herein by anyone other than the intended recipient is 
prohibited. If you have received this email by mistake, please notify us 
immediately by telephone or e-mail.
___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

Re: [Geotools-devel] Exception in SAP-HANA-datastore when using a HANA-view

2020-05-12 Thread Uhrig, Stefan 
Hi Paul,

Thanks for reporting, investigating and fixing that. I assume you are working 
with a HANA 1 instance. The SRID derivation was removed in HANA 2 because it 
caused too many issues.

So, the fix won’t work for HANA 2, but it won’t do any harm either (if the SRID 
is omitted in HANA 2, it is assumed to be 0).

I’ll have a look at your PR. We can use it for the time being, but we should 
leave the JIRA issue open until there is a solution for HANA 2 as well. I can 
take the issue then and think what we can do about it.

Best regards,
Stefan


From: Biskup, Paul 
Sent: Tuesday, May 12, 2020 9:34 AM
To: Jody Garnett ; Uhrig, Stefan 
Cc: geotools-devel@lists.sourceforge.net
Subject: AW: [Geotools-devel] Exception in SAP-HANA-datastore when using a 
HANA-view

Hi,

I have created an issue and a PR:

Jira-issue:
https://osgeo-org.atlassian.net/browse/GEOT-6587

Pull request:
https://github.com/geotools/geotools/pull/2926

Regards,
Paul

Von: Jody Garnett mailto:jody.garn...@gmail.com>>
Gesendet: Dienstag, 12. Mai 2020 02:49
An: Biskup, Paul 
mailto:paul.bis...@fit.fichtner.de>>; Uhrig, 
Stefan mailto:stefan.uh...@sap.com>>
Cc: 
geotools-devel@lists.sourceforge.net<mailto:geotools-devel@lists.sourceforge.net>
Betreff: Re: [Geotools-devel] Exception in SAP-HANA-datastore when using a 
HANA-view

Thanks for joining the developer list with a fix, yes please create an issue in 
jira, and a PR with your fix. Checking the plugin 
pom.xml<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fgeotools%2Fgeotools%2Fblob%2Fmaster%2Fmodules%2Fplugin%2Fjdbc%2Fjdbc-hana%2Fpom.xml=02%7C01%7CPaul.Biskup%40fit.fichtner.de%7Cc85b4b889cce4009e5dd08d7f60e65fd%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637248414005411269=3dWt1qoFf3nzlIS%2BASbHfftbykV5gY3wEe7fLsl1Z0U%3D=0>
 shows Stefan is the module maintainer and should be in a position to review.

Stay safe!
--
Jody Garnett


On Mon, 11 May 2020 at 08:42, Biskup, Paul 
mailto:paul.bis...@fit.fichtner.de>> wrote:
Hi all,

I’ve been recently using the SAP HANA-datastore in GeoServer, which works great.
Unfortunately there is a problem if you publish a HANA-view as a layer and try 
to do a spatial GetFeature-request on this layer. The view can be published and 
displayed as WMS, but if you do a GetFeature-request, you get this exception:

java.lang.RuntimeException: java.io.IOException
java.io.IOExceptionSAP DBTech JDBC: [8]: invalid argument: The given SRID (-1) 
does not match the field's SRID (31466) at function __st_geomfromwkb__() (at 
pos 134) 

The SRID from the geometry-column in my view is EPSG:31466.

I have already analyzed the problem and have also found a fix for this problem.
The problem is, that in a spatial GetFeature-request the code tries to get the 
SRID from the layer by executing this SQL-Statement:

"SELECT SRS_ID FROM PUBLIC.ST_GEOMETRY_COLUMNS 
WHERE SCHEMA_NAME = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?");

https://github.com/geotools/geotools/blob/b1be2cef1c06058f726bce94ac665ee50b571db6/modules/plugin/jdbc/jdbc-hana/src/main/java/org/geotools/data/hana/HanaDialect.java#L193<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fgeotools%2Fgeotools%2Fblob%2Fb1be2cef1c06058f726bce94ac665ee50b571db6%2Fmodules%2Fplugin%2Fjdbc%2Fjdbc-hana%2Fsrc%2Fmain%2Fjava%2Forg%2Fgeotools%2Fdata%2Fhana%2FHanaDialect.java%23L193=02%7C01%7CPaul.Biskup%40fit.fichtner.de%7Cc85b4b889cce4009e5dd08d7f60e65fd%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637248414005411269=HxfLFNA5IUyQi58IFiHORZDgJnzpsBYwaG580Ojnvxk%3D=0>

But the PUBLIC.ST_GEOMETRY-view in HANA is only filled for tables and not for 
views. That is why in my request the SRID of the layer can not be found and a 
„-1“ is returned.
Further have I analyzed, that the received SRID is passed in the 
„prepareGeometryValue“-function, which is used to create the sql-statement.
https://github.com/geotools/geotools/blob/b1be2cef1c06058f726bce94ac665ee50b571db6/modules/plugin/jdbc/jdbc-hana/src/main/java/org/geotools/data/hana/HanaDialect.java#L732<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fgeotools%2Fgeotools%2Fblob%2Fb1be2cef1c06058f726bce94ac665ee50b571db6%2Fmodules%2Fplugin%2Fjdbc%2Fjdbc-hana%2Fsrc%2Fmain%2Fjava%2Forg%2Fgeotools%2Fdata%2Fhana%2FHanaDialect.java%23L732=02%7C01%7CPaul.Biskup%40fit.fichtner.de%7Cc85b4b889cce4009e5dd08d7f60e65fd%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637248414005421260=j9zzFk3oBN0A3FKzSD7rdnxdJkpXLYS7qvspSZ9eZUk%3D=0>

There is an easy fix for this problem:
If you change the „prepareGeometryValue“-code like this, the SRID will only be 
passed to the „ST_GeomFromWKB“-function, if it is „>1“:

public void prepareGeometryValue(
Class gClass,
int dimension,
int srid,
Class binding,
StringBuffer sql) {
String pattern = null;

Re: [Geotools-devel] Exception in SAP-HANA-datastore when using a HANA-view

2020-07-22 Thread Uhrig, Stefan 
Hi Paul,

I haven’t had the time to look into it yet, but thinking about a solution is 
part of our current development sprint that started on Monday. There are some 
items I have to work on before, but I assume that I can start next Monday at 
the latest.

Can you maybe send me instructions to reproduce the issue? That would save me 
some time…

Best regards,
Stefan


From: Biskup, Paul 
Sent: Tuesday, July 21, 2020 1:56 PM
To: Uhrig, Stefan 
Cc: geotools-devel@lists.sourceforge.net
Subject: AW: [Geotools-devel] Exception in SAP-HANA-datastore when using a 
HANA-view

Hi Stefan,

we have migrated our SAP HANA DB HANA 2. As you already said, my fix doesn’t 
work in HANA 2. So we are having the same problem again.

Did you already think about a solution for this problem in HANA 2? Thank you in 
advance.

Best regards,
Paul

Von: Biskup, Paul 
mailto:paul.bis...@fit.fichtner.de>>
Gesendet: Mittwoch, 13. Mai 2020 13:43
An: Uhrig, Stefan mailto:stefan.uh...@sap.com>>
Cc: 
geotools-devel@lists.sourceforge.net<mailto:geotools-devel@lists.sourceforge.net>
Betreff: Re: [Geotools-devel] Exception in SAP-HANA-datastore when using a 
HANA-view

Hi Stefan,

thank you for merging my PR into the master-branch.

You are right: We are working with a HANA 1-instance, but are planning to 
migrate to HANA 2 in the next few months.
So it would be great, if you could think about a solution for HANA 2.

Best regards,
Paul

Von: Uhrig, Stefan mailto:stefan.uh...@sap.com>>
Gesendet: Dienstag, 12. Mai 2020 09:52
An: Biskup, Paul 
mailto:paul.bis...@fit.fichtner.de>>; Jody Garnett 
mailto:jody.garn...@gmail.com>>
Cc: 
geotools-devel@lists.sourceforge.net<mailto:geotools-devel@lists.sourceforge.net>
Betreff: RE: [Geotools-devel] Exception in SAP-HANA-datastore when using a 
HANA-view

Hi Paul,

Thanks for reporting, investigating and fixing that. I assume you are working 
with a HANA 1 instance. The SRID derivation was removed in HANA 2 because it 
caused too many issues.

So, the fix won’t work for HANA 2, but it won’t do any harm either (if the SRID 
is omitted in HANA 2, it is assumed to be 0).

I’ll have a look at your PR. We can use it for the time being, but we should 
leave the JIRA issue open until there is a solution for HANA 2 as well. I can 
take the issue then and think what we can do about it.

Best regards,
Stefan


From: Biskup, Paul 
mailto:paul.bis...@fit.fichtner.de>>
Sent: Tuesday, May 12, 2020 9:34 AM
To: Jody Garnett mailto:jody.garn...@gmail.com>>; 
Uhrig, Stefan mailto:stefan.uh...@sap.com>>
Cc: 
geotools-devel@lists.sourceforge.net<mailto:geotools-devel@lists.sourceforge.net>
Subject: AW: [Geotools-devel] Exception in SAP-HANA-datastore when using a 
HANA-view

Hi,

I have created an issue and a PR:

Jira-issue:
https://osgeo-org.atlassian.net/browse/GEOT-6587<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fosgeo-org.atlassian.net%2Fbrowse%2FGEOT-6587=02%7C01%7Cpaul.biskup%40fit.fichtner.de%7C3990afa01b3a4439175108d7f7401875%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637249726977613241=jeBpOyk%2BVn2lt5XuSgA3vLOYMlGt4mPu2%2Be205S0%2BL4%3D=0>

Pull request:
https://github.com/geotools/geotools/pull/2926<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fgeotools%2Fgeotools%2Fpull%2F2926=02%7C01%7Cpaul.biskup%40fit.fichtner.de%7C3990afa01b3a4439175108d7f7401875%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637249726977613241=%2BN5xa0MpEv6lcv6zB%2FWsR7Bh85NPTUH%2FWK6JcI%2FJn0o%3D=0>

Regards,
Paul

Von: Jody Garnett mailto:jody.garn...@gmail.com>>
Gesendet: Dienstag, 12. Mai 2020 02:49
An: Biskup, Paul 
mailto:paul.bis...@fit.fichtner.de>>; Uhrig, 
Stefan mailto:stefan.uh...@sap.com>>
Cc: 
geotools-devel@lists.sourceforge.net<mailto:geotools-devel@lists.sourceforge.net>
Betreff: Re: [Geotools-devel] Exception in SAP-HANA-datastore when using a 
HANA-view

Thanks for joining the developer list with a fix, yes please create an issue in 
jira, and a PR with your fix. Checking the plugin 
pom.xml<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fgeotools%2Fgeotools%2Fblob%2Fmaster%2Fmodules%2Fplugin%2Fjdbc%2Fjdbc-hana%2Fpom.xml=02%7C01%7Cpaul.biskup%40fit.fichtner.de%7C3990afa01b3a4439175108d7f7401875%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637249726977623235=PWy9PF2Zt9Oi6pxzXYniF0%2BMLLvM0KkZgzgHfC79rvg%3D=0>
 shows Stefan is the module maintainer and should be in a position to review.

Stay safe!
--
Jody Garnett


On Mon, 11 May 2020 at 08:42, Biskup, Paul 
mailto:paul.bis...@fit.fichtner.de>> wrote:
Hi all,

I’ve been recently using the SAP HANA-datastore in GeoServer, which works great.
Unfortunately there is a problem if you publish a HANA-view as a layer and try 
to do a spatial GetFeature-request on this layer. The view can be published and 
displayed as WMS, but if you do a GetFeature-request, you get this exc

[Geotools-devel] Windows GitHub CI build failing on 23.x

2020-07-30 Thread Uhrig, Stefan 
Hi all,

I'm currently trying to backport a fix for the HANA JDBC plugin to branch 23.x 
(https://github.com/geotools/geotools/pull/3096). However, the Windows GitHub 
CI build is failing with the following error:

[ERROR] Failed to execute goal 
org.apache.maven.plugins:maven-install-plugin:2.4:install (default-install) on 
project xmlcodegen: Failed to install metadata 
org.geotools.maven/maven-metadata.xml: Error updating group repository 
metadata: in epilog non whitespace content is not allowed but got s (position: 
END_TAG seen ...\ns... @11:2) -> [Help 1]

Is that a known issue? Mac OS CI and Travis CI builds are fine, and I can't 
imagine that my change could break anything in project xmlcodegen.

Thanks and best regards,
Stefan

___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

Re: [Geotools-devel] Exception in SAP-HANA-datastore when using a HANA-view

2020-07-31 Thread Uhrig, Stefan 
Hi Paul,

I’ve provided fixes for codelines 24.x, 23.x and 22.x. See 
https://osgeo-org.atlassian.net/browse/GEOT-6672 and 
https://github.com/geotools/geotools/pull/3089 for details.

Let me know if it works for you. It might be necessary to re-create the layer 
based on the view though. And it only works if there is at least one geometry 
in the view column.

Best regards,
Stefan

From: Biskup, Paul 
Sent: Thursday, July 23, 2020 4:16 PM
To: Uhrig, Stefan 
Cc: geotools-devel@lists.sourceforge.net
Subject: AW: [Geotools-devel] Exception in SAP-HANA-datastore when using a 
HANA-view

Hi Stefan,

I’m glad to hear, that you soon will be starting to work on this issue.
You can reproduce the issue following these instructions:

-- 1. if you haven't installed the 31466-spatial reference system in your HANA 
DB, you can install it using the GeoSpatial Metadata installer: 
https://blogs.sap.com/2017/01/11/did-you-know-you-can-add-spatial-reference-systems-to-hana/
--alternatively you can install it with this command:

CREATE SPATIAL REFERENCE SYSTEM "DHDN / 3-degree Gauss-Kruger zone 2"
IDENTIFIED BY 31466
LINEAR UNIT OF MEASURE "meter"
TYPE PLANAR
ORGANIZATION "EPSG" IDENTIFIED BY 31466
COORDINATE X BETWEEN 0 AND 600
COORDINATE Y BETWEEN 300 AND 700
DEFINITION 'PROJCS["DHDN / 3-degree Gauss-Kruger zone 
2",GEOGCS["DHDN",DATUM["Deutsches_Hauptdreiecksnetz",SPHEROID["Bessel 
1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[598.1,73.7,418.2,0.202,0.045,-2.455,6.7],AUTHORITY["EPSG","6314"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4314"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",6],PARAMETER["scale_factor",1],PARAMETER["false_easting",250],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AUTHORITY["EPSG","31466"]]'
TRANSFORM DEFINITION '+proj=tmerc +lat_0=0 +lon_0=6 +k=1 +x_0=250 +y_0=0 
+ellps=bessel +towgs84=598.1,73.7,418.2,0.202,0.045,-2.455,6.7 +units=m 
+no_defs';

-- 2. create a table with a EPSG:31466 (Gauß-Krüger-2-projection) 
geometry-column
CREATE COLUMN TABLE "SCHEMA"."TEST_LAYER" ("NAME" NVARCHAR(255),"GEOM" 
ST_GEOMETRY(31466) CS_GEOMETRY);

--3. add a point to the table
INSERT INTO "SCHEMA"."TEST_LAYER" VALUES('Testpoint', NEW 
ST_POINT('POINT(2571654 5463139)',31466));

-- 4. create a view from the table
CREATE VIEW "SCHEMA"."V_TEST_LAYER" as (select * from "SCHEMA"."TEST_LAYER");

-- 5. publish the view in the GeoServer-admin interface as a layer and set the 
projection of the layer to "EPSG:31466"

-- 6. make a GetFeature-request with a spatial-filter against this layer (you 
have to customize this request to your GeoServer-Config (workspace, 
FeatureNS,...):
--POST-Body:
http://www.opengis.net/wfs;
  service="WFS"
  version="1.1.0"
  outputFormat="json"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance;
  xsi:schemaLocation="http://www.opengis.net/wfs 
http://schemas.opengis.net/wfs/1.1.0/wfs.xsd<http://www.opengis.net/wfs%20http:/schemas.opengis.net/wfs/1.1.0/wfs.xsd>"
  id="ext-element-38">
http://NAMESPACE.com;
 id="ext-element-36">
http://www.opengis.net/ogc;
  id="ext-element-37">

http://www.opengis.net/ogc;
  id="ext-element-40">
GEOM
http://www.opengis.net/gml;
   srsName="EPSG:31466">


2571564.7647672324 
5463213.694324999 2571564.7647672324 5463053.531473128 2571756.1458020946 
5463053.531473128 2571756.1458020946 5463213.694324999 2571564.7647672324 
5463213.694324999









--> Exception text: java.lang.RuntimeException: java.io.IOException
java.io.IOExceptionSAP DBTech JDBC: [2048]: column store error: search table 
error:  [1600604] The geometries passed to function ST_Intersects have 
different SRIDs. The SRID of the first geometry is 31466, the SRID of the 
second geometry is 0.;object=DEV::SCHEMA:TEST_LAYERen


Best regards,
Paul


Von: Uhrig, Stefan mailto:stefan.uh...@sap.com>>
Gesendet: Mittwoch, 22. Juli 2020 12:27
An: Bisku

Re: [Geotools-devel] Build failed in Jenkins: geoserver-2.18.x-java11 #39

2020-11-09 Thread Uhrig, Stefan 
Doesn’t it say comparison failure?

org.junit.ComparisonFailure: expected: but 
was:


From: Ian Turton 
Sent: Monday, November 9, 2020 3:13 PM
To: Geotools-Devel list 
Cc: geoserver-bui...@lists.sourceforge.net
Subject: Re: [Geotools-devel] Build failed in Jenkins: geoserver-2.18.x-java11 
#39

Looking further it's throwing an error on what looks like a timing error?


[INFO] Running org.geoserver.wms.wms_1_1_1.DimensionsVectorGetFeatureInfoTest

org.geoserver.platform.ServiceException: Failed to run GetFeatureInfo on layer 
sf:TimeElevationStacked

at org.geoserver.wms.GetFeatureInfo.execute(GetFeatureInfo.java:88)

at org.geoserver.wms.GetFeatureInfo.run(GetFeatureInfo.java:38)

at 
org.geoserver.wms.DefaultWebMapService.getFeatureInfo(DefaultWebMapService.java:260)

at jdk.internal.reflect.GeneratedMethodAccessor661.invoke(Unknown 
Source)

at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.base/java.lang.reflect.Method.invoke(Method.java:566)

at 
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)

at 
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)

at 
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at 
org.geoserver.ows.util.RequestObjectLogger.invoke(RequestObjectLogger.java:28)

at 
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at 
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)

at com.sun.proxy.$Proxy60.getFeatureInfo(Unknown Source)

at jdk.internal.reflect.GeneratedMethodAccessor660.invoke(Unknown 
Source)

at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.base/java.lang.reflect.Method.invoke(Method.java:566)

at org.geoserver.ows.Dispatcher.execute(Dispatcher.java:877)

at 
org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:265)

at 
org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:177)

at 
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:52)

at 
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)

at 
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)

at 
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)

at 
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)

at 
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)

at 
org.geoserver.test.GeoServerSystemTestSupport$2.service(GeoServerSystemTestSupport.java:1633)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:770)

at 
org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:167)

at 
org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:134)

at 
org.geoserver.test.GeoServerSystemTestSupport.dispatch(GeoServerSystemTestSupport.java:1662)

at 
org.geoserver.test.GeoServerSystemTestSupport.dispatch(GeoServerSystemTestSupport.java:1575)

at 
org.geoserver.test.GeoServerSystemTestSupport.getAsServletResponse(GeoServerSystemTestSupport.java:1041)

at 
org.geoserver.test.GeoServerSystemTestSupport.getAsServletResponse(GeoServerSystemTestSupport.java:1024)

at 
org.geoserver.wms.wms_1_1_1.DimensionsVectorGetFeatureInfoTest.getFeatureAt(DimensionsVectorGetFeatureInfoTest.java:79)

at 
org.geoserver.wms.wms_1_1_1.DimensionsVectorGetFeatureInfoTest.testSortTimeElevationDescending(DimensionsVectorGetFeatureInfoTest.java:467)

at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at 
java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at 
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.base/java.lang.reflect.Method.invoke(Method.java:566)

at 
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)

at 
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at 
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)

at

Re: [Geotools-devel] Automated tests for GeoTools SAP HANA store

2022-02-16 Thread Uhrig, Stefan via GeoTools-Devel 
Hi Andrea,

Thanks for the heads-up!

I will prepare a pull request to enable online tests for the SAP HANA data 
store. It might take some weeks until it's done, but I think it will be done 
long before September 2022.

Cheers
Stefan

From: Andrea Aime 
Sent: Tuesday, February 15, 2022 7:36 PM
To: Uhrig, Stefan ; Geotools-Devel list 

Subject: Automated tests for GeoTools SAP HANA store

Hi Stefan (hello GT developers!),
I'm contacting you about the GeoTools SAP HANA data store, because you're the 
developer
that did (from the commits I see at least) most of the work on it.

In GeoTools we are discussing dropping down to unsupported any data store that:

  *   Requires online testing
  *   Does not have online tests for it running in Github actions
The rationale is simple: we keep on making changes to GeoTools and we have no 
idea
if those stores are bit-rotting because of them. If a change breaks a stores, 
we want
to know right away.

Currently, most JDBC stores have a Github action workflow testing them:
https://github.com/geotools/geotools/tree/main/.github/workflows<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fgeotools%2Fgeotools%2Ftree%2Fmain%2F.github%2Fworkflows=04%7C01%7Cstefan.uhrig%40sap.com%7Cbe03c4c5bb704e6fa85608d9f0b216a3%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637805469936852787%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000=APW3%2BmndmiwJ2AL%2FR0MdOy3%2FzNDDcIZLnuizoHof1rg%3D=0>

The two that do not are Teradata and HANA.

Teradata is going to be immediately pushed down to unsupported because it has 
not
received new commits in a long while, and people that maintained it are gone: 
it looks
dead for all intents and purposes.

HANA seems to be more active and we have someone to contact instead (hence this 
mail).
You don't need to add a Github workflow right now, we decided to re-evaluate the
HANA position is six months so ... plenty of time, no rush.

If in September 2022 we'll find the module has no builds running its tests, then
we'll demote the module to unsupported status.
Hope you'll understand.

Cheers
Andrea

___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

[Geotools-devel] Migrate tests in gt-jdbc from JUnit3 to JUnit4

2022-05-06 Thread Uhrig, Stefan via GeoTools-Devel 
Hi all,

I'd like to migrate the tests in gt-jdbc (and all derived tests) from JUnit3 to 
JUnit4 (or at least attempt it). Are there any objections against that?

Background is that I'd like to enable parallel test execution for the HANA JDBC 
Plugin. That seems to work only for JUnit4 tests.

Best regards,
Stefan

___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

Re: [Geotools-devel] Migrate tests in gt-jdbc from JUnit3 to JUnit4

2022-05-10 Thread Uhrig, Stefan via GeoTools-Devel 
Hi Andrea,

Thanks for the hint. That's helpful. I considered migrating OnlineTest first, 
but not only the JDBC tests derive from that class. Using OnlineTestSupport as 
base for JDBCTestSupport reduces the amount of code that has to be migrated. 
It's still a lot, but not as much as with the OnlineTest migration...

I'll create a Jira item and will then prepare a pull request.

Cheers,
Stefan

From: Andrea Aime 
Sent: Friday, May 6, 2022 6:52 PM
To: Uhrig, Stefan 
Cc: GeoTools Developers 
Subject: Re: [Geotools-devel] Migrate tests in gt-jdbc from JUnit3 to JUnit4

Hi Stefan,
personally I don't have objections, as long as the tests for all databases
are still working after the migration. I believe Ben some years ago prepared an 
OnlineTest replacement JUnit4 .. here:
https://github.com/geotools/geotools/blob/73051a745a647c134ae7c2be26978e0968bfbb03/modules/library/sample-data/src/main/java/org/geotools/test/OnlineTestSupport.java#L44<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fgeotools%2Fgeotools%2Fblob%2F73051a745a647c134ae7c2be26978e0968bfbb03%2Fmodules%2Flibrary%2Fsample-data%2Fsrc%2Fmain%2Fjava%2Forg%2Fgeotools%2Ftest%2FOnlineTestSupport.java%23L44=05%7C01%7Cstefan.uhrig%40sap.com%7C0fd9e8e08cdf44c4d67808da2f80ba2d%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637874527169357345%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=CM0uXxk7H5qBVQe8fvQlyLRqL9hAph5wyuVoU0Ju9Nc%3D=0>

Only 3 classes seem to be extending it... not sure if it's gonna help or 
hinder, just letting you know it's there.

Cheers
Andrea


On Fri, May 6, 2022 at 6:16 PM Uhrig, Stefan via GeoTools-Devel 
mailto:geotools-devel@lists.sourceforge.net>>
 wrote:
Hi all,

I'd like to migrate the tests in gt-jdbc (and all derived tests) from JUnit3 to 
JUnit4 (or at least attempt it). Are there any objections against that?

Background is that I'd like to enable parallel test execution for the HANA JDBC 
Plugin. That seems to work only for JUnit4 tests.

Best regards,
Stefan

___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net<mailto:GeoTools-Devel@lists.sourceforge.net>
https://lists.sourceforge.net/lists/listinfo/geotools-devel<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.sourceforge.net%2Flists%2Flistinfo%2Fgeotools-devel=05%7C01%7Cstefan.uhrig%40sap.com%7C0fd9e8e08cdf44c4d67808da2f80ba2d%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637874527169357345%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=GnBF2oMMr8sBz8uBabz64yDWnyr%2B9WCfxS2mTOAHz2g%3D=0>


--

Regards,

Andrea Aime

==
GeoServer Professional Services from the experts!

Visit 
http://bit.ly/gs-services-us<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fbit.ly%2Fgs-services-us=05%7C01%7Cstefan.uhrig%40sap.com%7C0fd9e8e08cdf44c4d67808da2f80ba2d%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637874527169357345%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=ml1FzOwvVP89BujhBkK94WcVRr2OIZPIesE3e5u5UU4%3D=0>
 for more information.
==

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions Group
phone: +39 0584 962313

fax: +39 0584 1660272

mob:   +39  333 8128928


https://www.geosolutionsgroup.com/<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.geosolutionsgroup.com%2F=05%7C01%7Cstefan.uhrig%40sap.com%7C0fd9e8e08cdf44c4d67808da2f80ba2d%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637874527169357345%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=o0%2Bk4bQNtOv2gVwxR9OJZIii3dBSbM19HBw79kDq3o0%3D=0>

http://twitter.com/geosolutions_it<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Ftwitter.com%2Fgeosolutions_it=05%7C01%7Cstefan.uhrig%40sap.com%7C0fd9e8e08cdf44c4d67808da2f80ba2d%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C637874527169357345%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=u3AccyjoscLKEexhWDIiT07Bm%2BDLzMrJ3NQfQFDshsM%3D=0>

---

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 
2016/679 - Regolamento generale sulla protezione dei dati "GDPR"), si precisa 
che ogni circostanza inerente alla presente email (il suo contenuto, gli 
eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i 
destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per 
errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei 
comunque grato se potesse darmene notizia.

This email is intended only for the person or entity to which it is addressed 
and may contain information that is privileged, confidential or otherwise 
protected from

Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual tables

2022-12-28 Thread Uhrig, Stefan via GeoTools-Devel 
Hi Andrea,

Thanks for your reply. Your proposal sounds good to me. I'll open a Jira item 
and prepare a pull request.

Cheers,
Stefan

From: Andrea Aime 
Sent: Tuesday, December 27, 2022 11:52 AM
To: Uhrig, Stefan 
Cc: Jody Garnett ; GeoTools 

Subject: Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual 
tables

On Wed, Dec 14, 2022 at 2:45 PM Uhrig, Stefan 
mailto:stefan.uh...@sap.com>> wrote:

If you don't have objections, I'd like to add a way to let the SQLDialect 
decide. However, I'm not sure what the best way would be. I considered the 
following options:


  1.  Ask the SQLDialect via a method whether hints should be added, e.g. 
boolean handleSelectHintsOnVirtualTableQueries() returning false by default.
  2.  Add a new method to add "general" hints independent of the query type, 
e.g. handleGeneralHints(SimpleFeatureType featureType, Query query, 
StringBuffer sql), doing nothing by default.
  3.  Pass along the information on the type of query to handleSelectHints(). 
That would be an incompatible change though. Maybe overloading the method would 
be an option.

Yup, I'm all for letting the dialect decide. Maybe a shorter method name would 
help (we're using 4 chars indents and 100 columns, need to try and be short and 
to the point).
What about "boolean applyHintsOnVirtualTables()", defaulting to false?

Cheers
Andrea

==

GeoServer Professional Services from the experts!

Visit 
http://bit.ly/gs-services-us<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fbit.ly%2Fgs-services-us=05%7C01%7Cstefan.uhrig%40sap.com%7C240fcecb30c64bb630e008dae7f87684%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C638077351574610055%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=s9T%2FhdxYNOYBiVbeGCWWppQpVrKOzZIovf%2BNCTxJREQ%3D=0>
 for more information.
==

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions Group
phone: +39 0584 962313

fax: +39 0584 1660272

mob:   +39  339 8844549


https://www.geosolutionsgroup.com/<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.geosolutionsgroup.com%2F=05%7C01%7Cstefan.uhrig%40sap.com%7C240fcecb30c64bb630e008dae7f87684%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C638077351574610055%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=oHusSxypU4GGO8anTMUmbG9%2FWVDPDl8%2BJUJYpbVAIZE%3D=0>

http://twitter.com/geosolutions_it<https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Ftwitter.com%2Fgeosolutions_it=05%7C01%7Cstefan.uhrig%40sap.com%7C240fcecb30c64bb630e008dae7f87684%7C42f7676cf455423c82f6dc2d99791af7%7C0%7C0%7C638077351574766778%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C=eWHuHyaD7aKWTETEMaoS93oLhawcQKk4zDErrpPtKIo%3D=0>

---

Con riferimento alla normativa sul trattamento dei dati personali (Reg. UE 
2016/679 - Regolamento generale sulla protezione dei dati "GDPR"), si precisa 
che ogni circostanza inerente alla presente email (il suo contenuto, gli 
eventuali allegati, etc.) è un dato la cui conoscenza è riservata al/i solo/i 
destinatario/i indicati dallo scrivente. Se il messaggio Le è giunto per 
errore, è tenuta/o a cancellarlo, ogni altra operazione è illecita. Le sarei 
comunque grato se potesse darmene notizia.

This email is intended only for the person or entity to which it is addressed 
and may contain information that is privileged, confidential or otherwise 
protected from disclosure. We remind that - as provided by European Regulation 
2016/679 "GDPR" - copying, dissemination or use of this e-mail or the 
information herein by anyone other than the intended recipient is prohibited. 
If you have received this email by mistake, please notify us immediately by 
telephone or e-mail
___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel

Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual tables

2022-12-14 Thread Uhrig, Stefan via GeoTools-Devel 
Hi Andrea, hi Jody,

Thanks for your responses and insights.

Looking at the SQLServerDialect class, I understand the reasons for blocking 
search hints on virtual tables.

Unlike in SQL Server, hints can only be added at the very end of a query or 
subquery in HANA. For example, it is not possible to put a hint after a table 
name in a query as long as other query elements appear after the table name 
(like ORDER BY or GROUP BY), i.e.

-- Okay
SELECT col1, SUM(val1) FROM tab GROUP BY col1 WITH HINT(MAX_CONCURRENCY(1))

-- Not okay
SELECT col1, SUM(val1) FROM tab WITH HINT(MAX_CONCURRENCY(1)) GROUP BY col1

In Hana, it's always okay to append a hint at the end of the query.

The motivation to add hints to GeoServer view queries is described in 
https://stackoverflow.com/questions/73647691/how-to-add-sql-hints-to-geoserver-layers.
 The OP wants to add hints for the HANA optimizer. He can add the hint to the 
query he enters in GeoServer:

select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT
WITH HINT OPTIMIZATION_LEVEL (RULE_BASED)

But then the hint is only applied to the subquery when the actual query is 
generated:

SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC"
FROM
(select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT
WITH HINT OPTIMIZATION_LEVEL (RULE_BASED)
) as "vtable"
WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), 
ST_GeomFromWKB(?, 3067)) = 1 LIMIT 100 OFFSET 0

As a result, the optimizer applies the hint only to the subquery, but not on 
the whole query. What the OP would like to achieve is the following query:

SELECT "ID","DISTRICT_ID","DISTRICT_NAME","GEOLOC".ST_AsBinary() as "GEOLOC"
FROM
(select ID,DISTRICT_ID,DISTRICT_NAME,GEOLOC FROM MG_SPATIAL.V_GIS_DISTRICT
) as "vtable"
WHERE "GEOLOC".ST_IntersectsRectPlanar(ST_GeomFromWKB(?, 3067), 
ST_GeomFromWKB(?, 3067)) = 1 LIMIT 100 OFFSET 0
WITH HINT OPTIMIZATION_LEVEL (RULE_BASED)

Hence, it would be great if a SQLDialect could decide itself whether it wants 
to add hints to a query with an embedded VirtualTable query.

If you don't have objections, I'd like to add a way to let the SQLDialect 
decide. However, I'm not sure what the best way would be. I considered the 
following options:


  1.  Ask the SQLDialect via a method whether hints should be added, e.g. 
boolean handleSelectHintsOnVirtualTableQueries() returning false by default.
  2.  Add a new method to add "general" hints independent of the query type, 
e.g. handleGeneralHints(SimpleFeatureType featureType, Query query, 
StringBuffer sql), doing nothing by default.
  3.  Pass along the information on the type of query to handleSelectHints(). 
That would be an incompatible change though. Maybe overloading the method would 
be an option.

Maybe you have better ideas.

What do you think?

Thanks and best regards,
Stefan

From: Andrea Aime 
Sent: Wednesday, December 14, 2022 10:20 AM
To: Uhrig, Stefan 
Cc: GeoTools 
Subject: Re: [Geotools-devel] JDBCDataStore blocks search hints on virtual 
tables

HI Stefan,
a virtual table can be built around any resultset returning statement (e.g., a 
stored procedure), and the JDBCDataStore machinery
makes no attempt to understand the SQL being provided, it just uses it as a 
subquery in the from clause.

Query hints, at least in the databases that I'm familiar with, must be provided 
in very specific positions, which requires
an understanding of the SQL involved. If you check the SQLServerDialect 
handleSelectHints, it makes assumptions
on how the query is built to find the exact point where a hint can be added 
(it's pretty much added mid-query).
The assumption is fair because we control how a select against a table is 
built, the same cannot be said for virtual tables,
where anything goes in the user provided query (a table might not be involved 
at all).

Is SAP HANA so freeform that one can put query hints pretty much anywhere, 
regardless of what is found in the
user provided query? Even if it was, you'd have to modify the API to allow 
other database (SQLServer) to tell apart
actual table vs virtual table.

Cheers
Andrea

On Tue, Dec 13, 2022 at 9:36 PM Uhrig, Stefan via GeoTools-Devel 
mailto:geotools-devel@lists.sourceforge.net>>
 wrote:
Hi all,

Recently, a group of GeoServer users on HANA requested the possibility to 
append hints to SQL queries. Luckily, there was already 
SQLDialect#handleSelectHints(). So, I could just override the method in 
HanaDialect and append the hints to the query, which I did in [GEOT-7230] 
(https://github.com/geotools/geotools/pull/4064<https://eur03.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fgeotools%2Fgeotools%2Fpull%2F4064=05%7C01%7Cstefan.uhrig%40sap.com%7Cb28354be92e248229e1008daddb458d3%7C42f7676cf

[Geotools-devel] JDBCDataStore blocks search hints on virtual tables

2022-12-13 Thread Uhrig, Stefan via GeoTools-Devel 
Hi all,

Recently, a group of GeoServer users on HANA requested the possibility to 
append hints to SQL queries. Luckily, there was already 
SQLDialect#handleSelectHints(). So, I could just override the method in 
HanaDialect and append the hints to the query, which I did in [GEOT-7230] 
(https://github.com/geotools/geotools/pull/4064).

Now the group of users who requested the feature noticed that it works for 
common GeoServer layers, but not for GeoServer views, which are based on 
GeoTools' VirtualTable. I investigated and found that JDBCDataStore 
deliberately blocks the addition of search hints 
(https://github.com/geotools/geotools/blob/29394a11ce3128a625f5251abeb9b0b09cc0105b/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L3505).

I found that the SQLDialect#handleSelectHints() has been introduced with 
"[GEOT-4707] Add a flag to force spatial index usage in sql server" 
(https://github.com/geotools/geotools/pull/371/files). Adding search hints to 
VirtualTable queries was blocked from the start.

Does someone know why search hints are not added to VirtualTable queries?

In the HANA use case, I could not identify any reason yet why the addition of 
search hints should be blocked. I might oversee something though. If there is 
no urgent reason to block search hints in VirtualTable queries, what would be a 
good way to support search hints on VirtualTable queries in a SQLDialect? Would 
the introduction of an additional function like 
appendSearchHintsToVirtualTableQueries() be acceptable?

Thank you and best regards,
Stefan


___
GeoTools-Devel mailing list
GeoTools-Devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geotools-devel