Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

hilton
7 - Meteor

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.

6 REPLIES 6
HenrietteH
Alteryx
Alteryx

Hi hilton!

 

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

 

3-30-2016 4-58-53 PM.png

 

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.

Henriette Haigh
Technical Product Manager - Data Connectors
Alteryx, Inc.

hilton
7 - Meteor

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
Alteryx
Alteryx

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:

4-1-2016 10-51-38 AM.png

Henriette Haigh
Technical Product Manager - Data Connectors
Alteryx, Inc.

hilton
7 - Meteor

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
6 - Meteoroid

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
7 - Meteor

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. 

Benjamin_Schalk_0-1645098082390.png

 

Labels