Spatial geometry from Postgis importing with latitudes and longitudes the wrong way around

[Amanda_Breeze]

Hi team,

I'm connecting to a Postgis database containing spatial polygons. I'm trying to import them into Alteryx and create centroids. It still recognises the geometry as polygons but when I try to create centroids as X and Y fields from them, they come out reversed. So I end up getting the Longitude as Y and Latitude as X. Of course when I try to build a centroid from this, it errors telling me my coordinates are out of range. I have to manually rename the fields so the latitudes and longitudes are actually correct and create points in order for them to generate a valid centroid.

I've attached a workflow containing a sample of the data as it imports into Alteryx. I can't for the life if me figure out why Lat and Long coordinates from a Postgis database are being interpreted by Alteryx the wrong way around.

Anyone have any idea what's going on and how I can fix this?

UPDATE: The format for coordinates in Postgis is Long/Lat but Alteryx is looking for Lat/Long. Not sure if this is a regional thing or an application thing. Is there a way to tell Alteryx which order to read the coordinates?

[Matt_D]

Hi @Amanda_Breeze I'm not sure if I'm thinking about this too simple but why not just point the Create Points Tool to the vice versa configuration? X is Y and Y is X 🤔

[Amanda_Breeze]

That is what I have to do. But the issue is, Alteryx used to import the data correctly (or rather not be bothered by the order of the lat/longs). Now I've upgraded to 2022.1.1 it's not. Also, if I want to keep the polygons, I can't as it throws MapServer errors because it can't render the geometries. 

[Amanda_Breeze · Accepted solution]

If anyone happens to stumble upon this thread, this is what I have discovered thanks to my friendly Alteryx Customer Support Engineer.

Alteryx are aware of a similar product defect that was introduced with version 2021.4.

Further to this, after some investigation we discovered that there are actually 2 parts to this problem.

The first part is the result of Alteryx indeed somehow reversing the position of the Lat and Long coordinates in the spatial object. So, it is effectively trying to read longitudes as latitudes and vice versa, effectively placing the coordinates out of range.

Alteryx have registered this as a serious defect TCM-429 which they are working on a resolution for. In the meantime, they have suggested 2 workarounds.

Workaround 1: If you are using a spatial input such as an Esri shape file (not a database connection), in some cases you can specify the projection in the input tool by manually entering "WGS84" into the Projection field as shown. DO NOT use the GUI (ellipses) to enter this information as apparently it doesn't work. Yes, this will convert your coordinates to WGS84 but this is the closest projection that currently works. This will unfortunately compromise spatial accuracy, especially if using GDA2020, for GDA2020 can be misaligned when projected into WGS84 by up to 1.8 metres. But if this level of accuracy isn't an issue it works fine for most datasets.

Run the workflow and hopefully the map renders the spatial objects.

Workaround 2: Not ideal, but if this is a real problem for you, you may want to revert to a previous version of Alteryx that doesn’t contain this issue (2021.3) and update when the issue is resolved.

I have a third workaround for those who aren’t fussed about converting to WGS84. If you must use GDA2020, see part 2 of the problem below.

Workaround 3:

Alternatively, you can deconstruct the spatial fields, obtain the lat and long coordinates, reverse them (rename Y to X and vice versa), create points and stitch them back together. A screenshot of this is below.

Attached is a zip file containing a workflow called Spatial conversion workflow.yxmd which does just this. It’s mainly for polyline and polygons and can be used where workaround 1 doesn’t work or if you wish to use a database connection, such as Postgres.

Simply replace the input file with a spatial file of your own (or database connection), update the fieldnames and correct the tool configurations as necessary. This will convert your geometries into WGS84, transform them into an Alteryx digestible format and output the result.

Again, this isn’t ideal, but it’s the best we can do for now.

Note however that as this converts the coordinates to WGS84, so if your original coordinates are in a different projection (such as GDA2020) be aware there may be some discrepancy between the converted coordinates and reality. For those using GDA2020, this may up to 1.8 metres.

The second part of the problem pertains specifically to GDA2020. And that is Alteryx does not currently support it. For those affected (and that's pretty much anyone using Alteryx to work with spatial data in Australia), converting coordinates can have a significant impact on the accuracy of their data, as stated above.

So, for this, I have logged an idea.

The more people who "like" my idea, the better chance it has at getting into the hands of the devs. More than 10 likes will give it traction. So, if this is an issue for you and would like to have them include support for GDA2020, please visit this link Include support for GDA2020 spatial projection - Alteryx Community and kindly upvote my idea.

[Amanda_Breeze · Accepted solution]

This issue has been resolved with version 2023.1 - thank you devs! 😀

Workaround above is if you're still running 2022.1.