Snowflake spatial objects come in as strings

[blyons]

When I connect and Input tool to Snowflake tables with geospatial fields, they all come show in Alteryx as string type rather than spatial. I've tried both Snowflake's ODBC SnowflakeDSIIDriver 2.25.04.00 and the Simba Snowflake ODBC Driver 2.25.01.1018 from the Alteryx downloads site. Both are 64 bit. Both produce the same result. Am I doing something wrong?

I know with SQL Server, I had to use an ODBC driver instead of OleDb to input spatial fields properly. However I can't find any equivalent for Snowflake.

Any ideas or suggestions would be appreciated!

[apathetichell]

What type of Snowflake spatial data is your field? This probably isn't Alteryx specific but ties into the DSII driver - here are some notes from Snowflake on how the Snowflake Spatial data goes through the ODBC output:

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

ValueDescription

GeoJSON (default)	The GEOGRAPHY result is rendered as an OBJECT in GeoJSON format.
WKT	The GEOGRAPHY result is rendered as a VARCHAR in WKT format.
WKB	The GEOGRAPHY result is rendered as a BINARY in WKB format.
EWKT	The GEOGRAPHY result is rendered as a VARCHAR in EWKT format.
EWKB	The GEOGRAPHY result is rendered as a BINARY in EWKB format.

is this a WKB or EWKB? If not - I would expect the result would be a string in Alteryx.

[blyons]

Thank you so much for your quick response.

The field data type is GEOGRAPHY, and it contains polygons and multi-polygons. This is the field definition in the table create statement: POLYGON_GEOGRAPHY GEOGRAPHY.

I had not seen that GEOGRAPHY_OUTPUT_FORMAT setting before. Indeed, ours was using the default, which is GeoJSON.

However, I tried changing the session parameter to WKB and EWKB, by putting alter session set GEOGRAPHY_OUTPUT_FORMAT = 'WKB' (or 'EWKB') in the Pre-SQL Statement of the Input tool. and in both cases, the type came in as Blob. I tried all 4 options, and they came in as either string or blob, rather than SpatialObj. I did this on both the Simba driver and the Snowflake driver, and both had the same result.

Any more ideas? Do others get SpatialObj type for geography fields on input from Snowflake?

[apathetichell]

Good news & bad news - BLOB means that your conversion worked - that is how they should appear. Blob would be the correct file type. 

Bad news: Your next issue isn't how to get an Alteryx spatial file type out of Snowflake - it's how to convert a WKB or EWKB file into an Alteryx spatial format. See this post - and note - I would keep your Geography as GeoJSON. 1) It's way smaller. 2) it's easier to convert (try the select tool).

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Handling-polygon-GeoJSON-files-in-Alte...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/WKT-spatial-object-read/td-p/139670

[blyons]

Those references all seem like kludgy workarounds, not a proper solution. In SQL Server with Alteryx, I output spatial objects directly, and input them directly, all without any conversion or manipulation. I expected the same behavior with Snowflake, and don't understand why Alteryx doesn't seem to support it the same way.

In my table, due to my tests and experiments, I have 2 fields for the same object. One is a string field, which contains GeoJSON in text, the other is GEOGRAPHY. I populated both fields with Alteryx using Snowflake's driver and bulk upload.

If I use the string field, and change the type to SpatialObj with the Select tool, it works on every record, and the mapped output in the Browse tool looks great. If I use the geography field, with the GEOGRAPHY_OUTPUT_FORMAT = 'GeoJSON', and change the type to SpatialObj with the Select tool, only 200 of the 210 records convert. The ones that don't all appear to have "{ "type": "MultiPolygon", "coordinates"..." at the beginning. Theoretically, both fields should be rendered GeoJSON, but Geography isn't.

Everything I read says to store GeoJSON as a Geography field in Snowflake for speed, efficiency, support of functions, etc. However, that doesn't appear to be a working option.

[apathetichell]

Dude. I think you need to reconsider your expectations for messageboard help. Your problem is solved. You literally just convert in a multi-field formula or a select tool. Talk to Snowflake about their driver vs SQLServer.

[blyons · Accepted solution]

Ignoring the snarkiness, the problem as it was was not "solved," because there was no universal or predictable solution. But the part about talking to Snowflake about their driver was well taken. These problems were affecting both Alteryx and Tableau. In Tableau, some objects were null, and others were rendered improperly. I did a bunch more research and testing. I am documenting this here in case others encounter similar problems.

The one thing that the problem records had in common was their size. It turns out that Snowflake's ODBC driver has a maximum read length limitation of roughly 3.6 to 7 million characters for a geography field, depending on the configuration, causing this problem in both Alteryx with the ODBC driver and Tableau with their native driver.  Oddly, their database can store the values, and the ODBC driver can bulk-load them, it just can't return them. The solution was to "simplify" the geography using Snowflake's ST_SIMPLIFY() function, which essentially reduces the detail of the geography, and therefore the size of the objects. By doing this, it resolved all of the problems in both Alteryx and Tableau, and has the added benefit of increasing the performance significantly.

Simplifying does have one negative side-effect. Complex geographies, like those that follow a very complex natural boundary, like a river or ridge, may have gaps between them or overlapping sections. When doing geospatial matching to a single point, that point could not be in either geography, or be in two geographies at once. Consequently, we still store the complex version to do geospatial matching calculations.

However, the ODBC driver still returns the fields, even simplified ones, as strings. This is easily solved in Alteryx by changing the type to SpatialObj in the Select tool. In Tableau, the native connector is the only solution.

I confirmed all of these issues with Snowflake's engineers, and Snowflake now has an issue open to resolve them. In the meantime, I have a satisfactory solution, and hopefully others can benefit from this experience as well.

[Samantha_Jayne]

I have just wrote a blog series introducing this, will link here when published. It's not obvious that its just a quick switch in a select tool. Even though its a simple thing to do, it would be great to have this option on the driver. i.e. bring all geography as spatialobjs.