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 <paul.bis...@fit.fichtner.de> Sent: Thursday, July 23, 2020 4:16 PM To: Uhrig, Stefan <stefan.uh...@sap.com> 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 6000000 COORDINATE Y BETWEEN 3000000 AND 7000000 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",2500000],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=2500000 +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: <GetFeature xmlns="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"> <Query typeName="WORKSPACE:V_TEST_LAYER" srsName="EPSG:31466" xmlns:WORKSPACE="http://NAMESPACE.com"; id="ext-element-36"> <Filter xmlns="http://www.opengis.net/ogc"; id="ext-element-37"> <And> <Intersects xmlns="http://www.opengis.net/ogc"; id="ext-element-40"> <PropertyName>GEOM</PropertyName> <Polygon xmlns="http://www.opengis.net/gml"; srsName="EPSG:31466"> <exterior> <LinearRing srsName="EPSG:31466"> <posList srsDimension="2">2571564.7647672324 5463213.694324999 2571564.7647672324 5463053.531473128 2571756.1458020946 5463053.531473128 2571756.1458020946 5463213.694324999 2571564.7647672324 5463213.694324999</posList> </LinearRing> </exterior> </Polygon> </Intersects> </And> </Filter> </Query> </GetFeature> --> 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 <stefan.uh...@sap.com<mailto:stefan.uh...@sap.com>> Gesendet: Mittwoch, 22. Juli 2020 12:27 An: Biskup, Paul <paul.bis...@fit.fichtner.de<mailto:paul.bis...@fit.fichtner.de>> 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, 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 <paul.bis...@fit.fichtner.de<mailto:paul.bis...@fit.fichtner.de>> Sent: Tuesday, July 21, 2020 1:56 PM To: Uhrig, Stefan <stefan.uh...@sap.com<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 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 <paul.bis...@fit.fichtner.de<mailto:paul.bis...@fit.fichtner.de>> Gesendet: Mittwoch, 13. Mai 2020 13:43 An: Uhrig, Stefan <stefan.uh...@sap.com<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 <stefan.uh...@sap.com<mailto:stefan.uh...@sap.com>> Gesendet: Dienstag, 12. Mai 2020 09:52 An: Biskup, Paul <paul.bis...@fit.fichtner.de<mailto:paul.bis...@fit.fichtner.de>>; Jody Garnett <jody.garn...@gmail.com<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 <paul.bis...@fit.fichtner.de<mailto:paul.bis...@fit.fichtner.de>> Sent: Tuesday, May 12, 2020 9:34 AM To: Jody Garnett <jody.garn...@gmail.com<mailto:jody.garn...@gmail.com>>; Uhrig, Stefan <stefan.uh...@sap.com<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&data=02%7C01%7CPaul.Biskup%40fit.fichtner.de%7C8b15dd7d451d49f7903a08d82e29b946%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637310105025808931&sdata=vXBIXSA7XTvqOudQZW%2FSD3Zh6HGHUm0S98ACz8pfRcM%3D&reserved=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&data=02%7C01%7CPaul.Biskup%40fit.fichtner.de%7C8b15dd7d451d49f7903a08d82e29b946%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637310105025808931&sdata=j07TEu9xrX%2F07sfNON1YPSgN53VXM5ObA%2FacyygsK1o%3D&reserved=0> Regards, Paul Von: Jody Garnett <jody.garn...@gmail.com<mailto:jody.garn...@gmail.com>> Gesendet: Dienstag, 12. Mai 2020 02:49 An: Biskup, Paul <paul.bis...@fit.fichtner.de<mailto:paul.bis...@fit.fichtner.de>>; Uhrig, Stefan <stefan.uh...@sap.com<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&data=02%7C01%7CPaul.Biskup%40fit.fichtner.de%7C8b15dd7d451d49f7903a08d82e29b946%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637310105025818927&sdata=ZC%2BNGwzr0ff77THKpNyJMUOmVXvmAYVpcVp8CQ59BBo%3D&reserved=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 <paul.bis...@fit.fichtner.de<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: <ows:ExceptionText>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) </ows:ExceptionText> 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&data=02%7C01%7CPaul.Biskup%40fit.fichtner.de%7C8b15dd7d451d49f7903a08d82e29b946%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637310105025818927&sdata=3mbgIWaHzCdYKyTSz4jf48TFIu7QKxFQrrkKR%2FKWsCM%3D&reserved=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&data=02%7C01%7CPaul.Biskup%40fit.fichtner.de%7C8b15dd7d451d49f7903a08d82e29b946%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637310105025828920&sdata=1UTCZcL9mfE77yjxlBESluhg3t6Z5bEZbXTaO7utDbw%3D&reserved=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<? extends Geometry> gClass, int dimension, int srid, Class binding, StringBuffer sql) { String pattern = null; if (srid > -1) { pattern = "ST_GeomFromWKB( ? ,{0})"; sql.append(MessageFormat.format(pattern, Integer.toString(srid))); } else { sql.append("ST_GeomFromWKB( ? )"); } } If no SRID is passed, the HANA-function assumes, that the SRID is in the same coordinate system as the passed coordinates. The fix is working in my locally build JDBC-HANA-Datastore and GeoServer. Could you please take a look at this and tell me if I should create an issue in your JIRA? Best regards, Paul _______________________________________________ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net<mailto:GeoTools-Devel@lists.sourceforge.net> https://lists.sourceforge.net/lists/listinfo/geotools-devel<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.sourceforge.net%2Flists%2Flistinfo%2Fgeotools-devel&data=02%7C01%7CPaul.Biskup%40fit.fichtner.de%7C8b15dd7d451d49f7903a08d82e29b946%7Cb43430ce7d754158ab7b1f39e6fe6b3f%7C0%7C0%7C637310105025828920&sdata=SaHv%2Fns6ZqfjZ1mx14GlzTkOppwSsW7%2BEWJhm4M8LHc%3D&reserved=0>
_______________________________________________ GeoTools-Devel mailing list GeoTools-Devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geotools-devel
