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 Knowledge Base

Definitive answers from Designer Desktop experts.

Reading and Writing Spatial data from/to Amazon Redshift

ntobon
Alteryx
Alteryx
Created

Environment Details


Environment/Details

Can not load geospatial data (field type GEOMETRY ) from Alteryx to Amazon Redshift directly with an Output Data Tool. 
Reading Spatial data from Amazon Redshift is not represented/displayed in Alteryx as Spatial Data.
 
  • Alteryx Designer
  • All Versions
  • Amazon RedShift

EXAMPLE 1:

a. WRITE Spatial Data from Alteryx workflow to Redshift:




Although the records are written, the GEOMETRY data (SpatialObject field) does not get written out to Redshift, and you get a warning message: 
 
Warning: Output Data (4): Unsupported SpatialObj field will be ignored: SpatialObj






Put a Browse Tool after an Input Data tool and you will only see the Label field:




EXAMPLE 2:

a. WRITE Spatial Data from Redshift table into another Redshift table:
Although the records are written you get a warning message: 
 
Warning: Output Data (3): Unsupported SpatialObj field will be ignored: geo




The GEOMETRY data did not get written out to Redshift since there is the message stating “Unsupported Blob field will be ignored: geo”

b. READ Spatial data from Redshift:
If you put a Browse Tool after the Input Data tool you can not see the Spatial data in Alteryx because it is reading as a blob.



The Metadata shows:



 

Cause


This is currently not supported. Amazon Redshift launched native spatial data processing support in November 2019.

Resolution


1. Please submit an Idea here: Product Ideas to allow support for field type GEOMETRY in Amazon RedShift.

2. WORKAROUND: 

- You can load GeoJSON into Reshift using a Select tool. Spatial objects (all types) can easily be converted into a GeoJSON format simply by changing the datatype of the object from Spatial Object to VString.



 You don't get a warning message:



- This is a pretty simple polygon. Depending on the complexity of your spatial objects, the number of nodes stored in the object could blow up the length of the string of the GeoJSON. You can manually re-set the size of the string field to 6355, which seems to be the Redshift column size default. You should check the length of the string version of your spatial objects. Once converted to a string, you just ran a simple Length function to see how long this string value was to verify the Redshift defaults would accommodate this object. If your spatial objects are enormous, you may need to either 1) talk to your Redshift admin to increase the size of those columns or 2) potentially integrate an Alter Table SQL statement to modify the column size of the GeoJSON field (info here: https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html). This statement might require certain permissions on that Redshift database to actually execute the changes. 

- You can read the GeoJSON object back into Designer and reconstruct the spatial object. All we need to do is use a Select tool to change the data type of the GeoJSON string to a Spatial object. 



- This workaround was tested using the ODBC connection to Redshift, not the Bulk Loader, but this process should  work for both upload options.