Question

Snowflake geometry types honoured by FME?

  • 20 September 2021
  • 4 replies
  • 17 views
helmoetz
Rank
Badge +10

Hi, is it possible that FME does not honour geography types for SnowFlake? SnowFlake should be able to store geometry as WKT / WKB / EWKT / EWKB or GeoJSON. Currently (FME(R) 2020.2.3.0 (20210129 - Build 20820 - WIN64) only GeoJSON seems to be supported. When I try to specify a different format, FME keeps on writing GeoJSON format geometry.

  • I tried specify it in the JDBC connection parameter GEOGRAPHY_OUTPUT_FORMAT
  • I tried specify it in a pre-write SQL statement ALTER SESSION SET GEOGRAPHY_OUTPUT_FORMAT='WKT'

Any ideas? Or file a support case?

Love, Helmoet.

4 replies

chrisatsafe
Rank
Badge +2

Just for testing, if you create a separate Snowflake database connection with WKT in the JDBC connection parameters, does your data write out in WKT or still defaults to GeoJSON?

 

Or after altering the database connection to set the geography output format to WKT, can you close and re-open FME to see if it writes correctly? Are you writing to an existing table (drop and create) or a new table?

 

I was testing this out last night and noticed when I edited an existing Snowflake connection the geometry would always default back to the previous format I had set...

helmoetz
Rank
Badge +10

Hi, I was writing using Drop and Create on the feature type. I use a separate Snowflake database connection with WKT in the JDBC connection parameters. Currently I cannot retest, since there was a change in role assignment which prevents me from writing at all. I used a Snowflake database connection with GEOGRAPHY_OUTPUT_FORMAT = WKT, and the data writes out in GeoJSON. Restarting FME Desktop does not seem to influence things. I got around it by creating a varchar attribute MGT_GEOM and put the output of a GeometryExtractor (in WKT mode) into it.

steveatsafe
Rank
Badge +11

Snowflake stores geometry in both the GeoJSON and an internal binary representation of the value.

The approach you took is a solution to store this value in WKT.

 

You can check out exercise 3.2 in the Snowflake Training to see if you might have other solutions.

Go here: https://www.safe.com/training/recorded/connect-data-even-geospatial-snowflake/

And review the Training Manual for Exercise 3.2.

Worth reviewing: https://docs.snowflake.com/en/sql-reference/data-types-geospatial.html#geospatial-object-types

Hope this helps!

SteveatSafe
helmoetz
Rank
Badge +10

Hi, I found out that Snowflake stores geometries in GeoJSON (or probably some other internal) format. Setting the alter session set GEOGRAPHY_OUTPUT_FORMAT='WKT'; will format geometry output to WKT. I apparently misunderstood the use of the GEOGRAPHY_OUTPUT_FORMAT parameter in the JDBC connect string.

Reply


Community Stats

28,724
Posts
109,518
Replies
36,564
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.