topic Re: Connecting Alteryx server to SQL database and losing spatial objects in Alteryx Server Discussions

Connecting Alteryx server to SQL database and losing spatial objects

WKiehn — Wed, 05 Sep 2018 07:06:50 GMT

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?

Re: Connecting Alteryx server to SQL database and losing spatial objects

David-Carnes — Fri, 07 Sep 2018 15:06:30 GMT

@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

Re: Connecting Alteryx server to SQL database and losing spatial objects

WKiehn — Thu, 13 Sep 2018 11:07:08 GMT

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

Re: Connecting Alteryx server to SQL database and losing spatial objects

jm99 — Fri, 22 Jan 2021 21:48:05 GMT

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.

Re: Connecting Alteryx server to SQL database and losing spatial objects

madler_alteryx — Wed, 27 Jan 2021 09:59:15 GMT

Hi @WKiehn,

You might find https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-to-Write-Spatial-Data-to-PostgreSQL/ta-p/677170 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

Betreff: Connecting Alteryx server to SQL database and losing spatial objects

jm99 — Wed, 27 Jan 2021 16:01:27 GMT

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

Christoph

Re: Connecting Alteryx server to SQL database and losing spatial objects

JonathanAllenby — Tue, 13 Jul 2021 13:35:48 GMT

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.