Spatial Data from Redshift Database

[Christopher Poss]

Amazon has recently added a Geometry data type to Redshift. I'm working with a simple "Hello Wolrd" Redshift dataset example that has a simple POINT geometric object called location (picture below). I've followed the guidance outlined here and here regarding modifying the Information Link SQL, setting the data type as BLOB, as well as setting the Properties mapchart.columntypeid = Geometry and ContentType = application/x-wkb.

In addition to using ST_AsBinary, I've also tried ST_AsEWKB (extended well known binary) in the Information Link SQL. Redshift spatial functions listing.

In all cases, I'm unable to view the data from the Information Link. I consistantly get the error:Failed to get data: [Amazon][JDBC](10400) Invalid type for data - column: 3, type: BinaryStream. (HRESULT: 80131500). I've seen this error in both Spotfire 7.11 and Spotfire 10.3

Has anyone been able to read Geometry data from Redshift If so, can you describe the steps you took to read the Geometry datatype into Spotfire

 

SELECT

t1."address_id" AS "ADDRESSID",

t1."depot_site_ind" AS "DEPOTSITEIND",

ST_AsBinary( t1."location") AS "LOCATION"

FROM

"" t1

WHERE

[Khushboo Rabadia]

seen on left seems to berelated to path. Can you check if the path is valid and exists on database.

This error is thrown from jdbc driver connected to redshift database. If you run same query on redshift database without spotfire, does it work

[Dave Leigh]

I had a quick look at this and it looks like Redshift does not have a Binary column data type:

https://docs.aws.amazon.com/redshift/latest/dg/c_Supported_data_types.html

Executing a simple select * returns the Geometry column as a string representation.

Executing ST_AsBinary on my Geometry column returns the hexadecimal well-known binary (WKB) representation of an input geometry using ASCII hexadecimal characters (09, AF):

https://docs.aws.amazon.com/redshift/latest/dg/ST_AsBinary-function.html

So telling the Information Link to expect a Binary data type will throw an error (as you are reporting) however you configure the query.

I was able to import the ASCII representation of the WKB object using an Information Link but I don't see an easy way of converting that into a Binary column.

One way I can think of would be to create an IronPython script that writes out the imported data table contents to an STDF/SBDF file, converting the WKB string data into a Binary column as it goes, and then re-import that data. It might also be possible to do some conversion with the TERR engine.

If I find an easier way, I will let you know.