SNOW-1834293: Error reading data using SnowflakeType GEOMETRY

[tdaggers10]

1. What version of JDBC driver are you using? 3.13.3

2. What operating system and processor architecture are you using? Windows Server 2019 standard: version 1809, x64-processor

3. What version of Java are you using? Java2d 11.0.22

4. What did you do?

When using the snowflake jdbc plugin in an application, we were able to visualize spatial data of SnowflakeType GEOGRAPHY. However, when importing data of SnowflakeType GEOMETRY we run into the following error:

code=’internalError’Rendering process failed. Layers: SUBSIDIEGRENS_TEST9net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 2 at position 20
Invalid argument types for function ‘ST_INTERSECTS’: (GEOGRAPHY, GEOMETRY)

In our work it is necessary for us to have the data in EPSG:28992. However, reprojecting the data in FME automatically results in a SnowflakeType GEOMETRY.

5. What did you expect to see?

6. Can you set logging to DEBUG and collect the logs?

Caused by: java.io.IOException
at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:650)
at org.geotools.data.store.ContentFeatureSource.getReader(ContentFeatureSource.java:636)
at org.geotools.data.store.ContentFeatureCollection.features(ContentFeatureCollection.java:174)
... 134 more
Caused by: java.sql.SQLException: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 2 at position 20
Invalid argument types for function 'ST_INTERSECTS': (GEOGRAPHY, GEOMETRY)
at org.geotools.jdbc.JDBCFeatureReader.runQuery(JDBCFeatureReader.java:282)
at org.geotools.jdbc.JDBCFeatureReader.(JDBCFeatureReader.java:153)
at org.geotools.jdbc.JDBCFeatureSource.getReaderInternal(JDBCFeatureSource.java:620)
... 136 more
Caused by: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error: error line 2 at position 20
Invalid argument types for function 'ST_INTERSECTS': (GEOGRAPHY, GEOMETRY)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:126)
at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:66)
at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:434)
at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:338)
at net.snowflake.client.core.SFStatement.executeHelper(SFStatement.java:482)
at net.snowflake.client.core.SFStatement.executeQueryInternal(SFStatement.java:194)
at net.snowflake.client.core.SFStatement.executeQuery(SFStatement.java:131)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:737)
at net.snowflake.client.core.SFStatement.execute(SFStatement.java:635)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal(SnowflakeStatementV1.java:238)
at net.snowflake.client.jdbc.SnowflakeStatementV1.executeQuery(SnowflakeStatementV1.java:133)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.geotools.jdbc.JDBCFeatureReader.lambda$new$0(JDBCFeatureReader.java:153)
at org.geotools.jdbc.JDBCFeatureReader.runQuery(JDBCFeatureReader.java:271)
... 138 more

Before sharing any information, please be sure to review the log and remove any sensitive
information.

[sfc-gh-dszmolka]

hi - thank you for filing this issue with us. JDBC driver 3.13.3 is unsupported as of now. Furthermore I think GEOMETRY support only was added with 3.13.28.

So for debugging this further, could you please

• install a supported version of the Snowflake JDBC driver (recommended is the latest)
• if the issue reproduces even with a supported driver version, can you please provide a minimal reproduction setup which when executed, exhibits the behaviour you're observing?

Let me know please.

[tdaggers10]

Hello David, Thank you for your response. We are going to update the JDBC-driver and do some further tests. I will let you know the outcome. Met vriendelijke groet, Tisja Daggers Geo-informatiekundige ***@***.*** Gemeente Nijmegen 06-52904563 ***@***.*** bereikbaar op maandag, dinsdag, donderdag en vrijdag Van: David Szmolka ***@***.***> Verzonden: dinsdag 17 december 2024 09:41 Aan: snowflakedb/snowflake-jdbc ***@***.***> CC: Tisja Daggers ***@***.***>; Author ***@***.***> Onderwerp: Re: [snowflakedb/snowflake-jdbc] SNOW-1834293: Error reading data using SnowflakeType GEOMETRY (Issue #1977) U ontvangt niet vaak e-mail van ***@***.*** Ontdek waarom dit belangrijk is<https://aka.ms/LearnAboutSenderIdentification> hi - thank you for filing this issue with us. JDBC driver 3.13.3 is unsupported<https://docs.snowflake.com/en/release-notes/requirements> as of now. Furthermore I think GEOMETRY support only was added with 3.13.28<#1237>. So for debugging this further, could you please * install a supported version of the Snowflake JDBC driver (recommended is the latest) * if the issue reproduces even with a supported driver version, can you please provide a minimal reproduction setup which when executed, exhibits the behaviour you're observing? Let me know please. - Reply to this email directly, view it on GitHub<#1977 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BNJQMXQW6ORNOMHVKYABHFT2F7PSNAVCNFSM6AAAAABSWXTSZ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKNBXHAYTQNRQGM>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[sfc-gh-dszmolka]

hi @tdaggers10 did you have a chance to re-test with an upgraded driver? do you still need assistance on this one or can be closed?

[tdaggers10]

Hello David, Unfortunately I didn't have the opportunity to re-test. We decided to engage an external party to advise us on the various requirements a data warehouse should meet for the city of Nijmegen. Therefore, it is a bit too early to further look into this. I think it is best to close the issue for now and if we need assistance in the future, I will open a new one. Thank you for your response. It was useful anyway for us to know that there is an update of the jdbc-driver available. Met vriendelijke groet, Tisja Daggers Geo-informatiekundige ***@***.*** Gemeente Nijmegen 06-52904563 ***@***.*** bereikbaar op maandag, dinsdag, donderdag en vrijdag Van: David Szmolka ***@***.***> Verzonden: woensdag 15 januari 2025 11:54 Aan: snowflakedb/snowflake-jdbc ***@***.***> CC: Tisja Daggers ***@***.***>; Mention ***@***.***> Onderwerp: Re: [snowflakedb/snowflake-jdbc] SNOW-1834293: Error reading data using SnowflakeType GEOMETRY (Issue #1977) U ontvangt niet vaak e-mail van ***@***.******@***.***>. Ontdek waarom dit belangrijk is<https://aka.ms/LearnAboutSenderIdentification> hi @tdaggers10<https://github.com/tdaggers10> did you have a chance to re-test with an upgraded driver? do you still need assistance on this one or can be closed? - Reply to this email directly, view it on GitHub<#1977 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BNJQMXTCAV2QJQWBKNSL5QD2KY433AVCNFSM6AAAAABSWXTSZ6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDKOJSGMYTCNBZGI>. You are receiving this because you were mentioned.Message ID: ***@***.******@***.***>>

[sfc-gh-dszmolka]

Hello Tisja,

Thank you for your response - closing this issue out for now. Exactly as you mentioned; we're here to help if you encounter any issues with this library in the future, feel free to open an Issue with us.