Spatial mis-communication w. SQL Server: Image, Blob and Geom

[hilton]

I am having difficulty getting Alteryx and MS SQL Server to communicate properly with spatial data. I know that Alteryx has no problem writing spatial data fields, i.e. [SpatialObj], to SQLServer and reading them back again. As it does this, it saves the data to an Image field type in the SQL DB. This works if you need to write to the DB and read from it again using Alteryx only.

However, as far as I can tell, the native spatial data type of SQL Server is a Geometry field type. When spatial data is in a geometry field, a spatial preview tab is available in SQL Management Studio.

My issue is that I cannot find a way to read the Geometry field type stored in a SQLServer DB into Alteryx as a spatial field. It is read as a Blob instead. And using the Blob Conversion tool doesn't convert it into any text format that Alteryx recognises as spatial.

Likewise in reverse, the Image field type that Alteryx writes to the DB isn't recognised by SQL, and cannot be converted into Geometry.

I have found a workaround that involves first converting the geometry field in the DB into a text field, using the following SQL:

GeomCol.STAsText()

This results in the following text: 

"POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))"

This can then be retrieved by Alteryx and parsed into a polygon with the Poly Build tool etc. Surely there is a better way? Why can't Alteryx and SQL Server natively communicate?

For reference, this issue was obliquely referred to in this previous discussion.

[HenrietteH]

Hi hilton!

I'm not sure what type of a connection you are using, have you tried the Spatial Database connections yet?

That might do the trick for you...

I find that OleDB works better than ODBC (ODBC treats it as an image), it works well for the geography data type in sql (points in Alteryx), they communicate seamlessly.

[hilton]

Hi Henriette,

I vaguely recall trying that in the past, not sure. Thanks for the tip, I'll try that. Sounds like you've got it working as I had intended.

[HenrietteH · Accepted solution]

Hi Hilton,

To give you a little more detail.... I played around with the connectors a bit this morning:

1. You can only load one spatial field at a time

2. In ODBC, when reading in data, your spatial field has to be the last in the select statement (Doesn't matter where it is in the table, in the select statement, it has to be the last field before the FROM)

3. If you want to use geometry, you have to use the little checkbox:

[hilton]

Thanks Henriette! I managed to get both the read from and write to the spatial SQLServer DB working now.

Part of the answer lies in using the "New Spatial OleDB Connection..." in the Input tool, and part lies in using the "SQL Server Native Client" OleDB provider in the Data Link Properties dialog, rather than "Microsoft OLE DB Provider for SQL Server". That was also my mistake.

[nicho_x86]

Hi,

I'm experiencing a similar problem. Using an ODBC Spatial Database connection as my input I linked to a table on our SQL server which I originally wrote out to using Alteryx (9.5). On the SQL Server Management Studio the data shows up correctly but when I try to view it in Alteryx it shows none in the spatial field for every record. The field type on the SQL server that holds the spatial data (point) is a Geometry type (as required by other software we use). Is there something that I'm missing?

[Benjamin_Schalk]

This bugged my for quite some time now. Writing seems to work with the "normal" OleDB connection as well. 

If you leave the "Geomatry Spatial Type" unchecked, it creates a "Geography" type field. 

This can then also be used in a regular input tool (odbc) or also with Tableau, which doesn't seem to support the geometry type.