How to tranform spatial field from progresql/postgis in Alteryx Designer ?

[kantawee]

I want to render field geom in alteryx designer ,when i connect postgreSQL.
How to solve this problem.? please..

[CharlieS · Accepted solution]

It's my understanding that this data is stored as Well Known Binary (WKB) or hex-encoded extended WKB. This post on stack exchange suggests using the 'ST_asText' function to output a more comprehensible WKT that can be easily parsed in Alteryx.

https://gis.stackexchange.com/questions/146266/understanding-the-format-wkb-from-wkt-and-how-to-conv...

I would try something like this, but hopefully someone with access to a postgreSQL database can help further.

SELECT 

grid

,postgis_fi

,lat_y_dd

,long_y_dd

,status

,nest_id

,ST_asTest(geom) as geom_WKT

FROM public.baea_nests

ORDER BY grid ASC

Otherwise it should be possible to parse the binary in Alteryx, but that seems like a Plan B route if the field can't be converted in the query. 

https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary

[kantawee]

@CharlieS

Very nice, excellent for explain solution.

Thanks 

[gregkaleka]

@kantawee can you share how you made this work? I was able to select the field as text, but still have not been able to get Alteryx to recognize the field as a geometric field.