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?
Solved! Go to Solution.
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
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
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.
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
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
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.