User DSN versus System DSN

[ajsmith]

Hi,

I've got an interesting issue with my connection to our PostGIS enabled PostgreSQL Database. I have a user DSN set up on my PC which is working perfectly when I use it in designer input tools, in particular it pulls through the geom column as a spatial object.

However, when I execute the same query to the same database as the same user as a connection string instead, the geom column is pulled through as binary and cannot be converted to a spatial object. Casting to WKT and geoJSON in the query also don't work.

Does anyone have any ideas what may be causing this? Unfortunately can't post workflows as this is all about database connections.

[JohnGD]

Hi @ajsmith  !

Do you mind sharing the following so I can try to reproduce internally :

- version of Alteryx

- version of your DB

- Sample table structure in postgresql + sample data

- Sample workflow reading in

Thank you in advance,

[ajsmith]

Hi John,

Alteryx - 2019.1.6.58192

postgres - 9.6

postgis - 2.3

table structure - gid serial PK, census_cod charvar(9), region charvar(48), geom geometry(multipolygon,27700)

table data - 1, E12000001, London, [geometry]

The workflow is difficult because it contains connection details to our server. It is worth noting that this is a consistent problem with the connection type and not a single table.

I have one input tool that pulls in the data using an DSN from the Windows ODBC tools and the geometry correctly arrives as a spatial object. I have a second that uses the same details as a connection string and pulls through the geometry as a binary.

[ajsmith · Accepted solution]

I have revisited this issue with some assistance from another source.

To work around the problem you can cast your geometry to geojson in the query and then convert with a select tool. This was not working previously as the geometries are rather complicated and the resulting text string was being truncated. Adding the MaxLongVarcharSize setting to the connection string at a very large value corrected this. It is worth noting that there is still a max string size and sufficiently complex geometries therefore cannot be pulled through.

While this fixes the problem, I still have no idea why a DSN worked while a connection string didn't!