Connecting Alteryx server to SQL database and losing spatial objects

[WKiehn]

Hi Team,

I connected  Alteryx Server to a Postgres database and then loaded data from the postgres database via MyGallery into Alteryx Designer. It works but the type of all spatial objects was changed to string. On the other hand it isn't possible to write spatial object into the database.

If I directly connect Alteryx Designer to a Postgres database it works. I can upload and write spatial objects. 

I used the same ODBC driver for connecting Alteryx to the Postgres database and Alteryx Designer and Alteryx Server are on the same machine.

Could you please help me?

[David-Carnes]

@WKiehn

The Spatial Object actually is a string.  For example, I can change SpatialObj to V_String with the Select tool and vice versa.  Text should look like this:
{ "type": "Point", "coordinates": [  -80.844401, 35.273093 ] }

As far as your issue with not being able to write spatial objects with Alteryx Server, my first thought is something permissions related.  I'm not experienced in Postgres but maybe there are some log files to review?

Best,

David

[WKiehn]

Thank you for your support, David. We use the select tool to export spatial object as WKT or geojson.

Another software should have access to the SQL database, too and it needs spatial objects therefore.

Using the select tool is a good idea for building a workaround. Changing spatial object into WKT, write it into the SQL database and write a "Post create  SQL statement" to change WKT in spatial objects in the database. If it works, it would help for the moment. 

First we checked permissions. They are okay.

Thanks,

Werner

[jm99]

Solution for the first problem

You have to convert the geometries to geojson in the Postgresql-Database. In Alteryx you can then use the Input Data tool and the Select tool to convert the column to the Alteryx Spatial format.

[MichaelAd]

Hi @WKiehn,

You might find https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-Write-Spatial-Data-to-Postgr...of interest.

Please note that if you are using PostGIS there will be different steps and there should be shortly an article covering them.

Thanks,

Michael

[jm99 · Accepted solution]

To upload geodata via Alteryx server

In Alteryx I would convert the geoformat to V-String (= geojson) and use Post Create SQL statement in the Postgresql to convert it to the PG geofomat

Post Create SQL statements points: 

SELECT AddGeometryColumn ('public','table_name','geom',4326,'POINT',2);
update table_name set geom = ST_GeomFromGeoJSON(geojson);
ALTER TABLE table_name DROP COLUMN geojson;

in some cases it is necessary to adjust the connect_string of the Postgresql connection in the server - parameter MaxLongVarcharsize

BR

Christoph

[JonathanAllenby]

Is this solution required even if Alteryx Designer can read the data as spatial data automatically, as described in the initial post?

We have the same issue here where when Designer connects directly it reads it in as spatial data, whereas Designer using the same connection through the Gallery instead reads the data as peculiar looking strings.