I want to replicate oracle tables with Esri ST_GEOM data types to a Snowflake data warehouse without any transformations before. Just a straight up replication of the complete table, including the ST_GEOM data field. Can Safe FME do that?

Hi @miguel​ ,

Are you wanting to store the ST_GEOM data in Snowflake in an appropriate column (data type), untouched?

What is the use case here? I can only think that Esri (ArcObjects) would be able to read it back to do something with it.

The ST_GEOM contains a BLOB? (see here)? This might be an issue in selecting the best data type in Snowflake to handle this with the other mixed data... VARIANT? VARBINARY? I'm not sure. You might have to explore... maybe even CAST the source data before it can be stored in one of these data types.

If so just be mindful of the storage limitations for Snowflake.

8MB for binary and 16 MB for geometry and geography (in reality less).

More here: https://docs.snowflake.com/en/sql-reference/data-types-geospatial

Once this data is stored in Snowflake - you won't be able to make use of their native geom/geog spatial functions.

Also keep in mind, larger complex polygons and even linear features with a lot of vertices/detail will blow up on insert. In my testing I've been able to load about 300,000 vertices... and some (like the link below) report 500,000 vertices as that limit... I think it comes down to hitting the 16MB limit.

NOTE: I saw your other post... it sounded like you wanted to start the SDO_GEOMETRY from Oracle 'as is' in Snowflake... not sure why you'd want to do that. If however, as Dan has answered, certainly FME can read from Oracle and transform the spatial data into a suitable GEOMETRY or GEOGRAPHY data type to be stored in Snowflake.

https://community.snowflake.com/s/question/0D5Do00000D7f23KAB/geography-size-limits