ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How to: Write Spatial Data to PostgreSQL

khoward85
Alteryx
Alteryx
Created

How To: Write Spatial Data to PostgreSQL 

Spatial data is stored in a PostgreSQL database a little differently than one might expect -- the information is stored as strings! Luckily, Alteryx Designer already has all the tools needed to flip the spatial data field into a string field and format it properly in order to be stored in PostgreSQL.  
 

Prerequisites

  • Product - Alteryx Designer (tested on 2020.2+)
  • PostgreSQL database (suggested resource: PG Admin - https://www.pgadmin.org)
  • ODBC Connection to PostgreSQL database
  • PostGIS is NOT being used for this exercise/application

Procedure

Spatial objects need to be written to PostgreSQL fields as strings using the following syntax depending on the spatial data type:

Field TypeDescriptionSyntax
pointPoint on a plane(x,y)
lineInfinite line{A,B,C}
lsegFinite line segment((x1,y1),(x2,y2))
boxRectangular box((x1,y1),(x2,y2))
pathClosed Path((x1,y1),...)
pathOpen path[(x1,y1),...]
polygonPolygon((x1,y1),...)
circleCircle<(x,y),r> (center point and radius)
per the documentation here: https://www.postgresql.org/docs/13/datatype-geometric.html
 
Capture.PNG
 Things to Keep In Mind:
  • When reading this data back into Designer using INPUT tools, the spatial field will have to be parsed and the geography/geometry rebuilt in order to re-construct the spatial objects.
  • Table name is Case Sensitive
    • odbc:DSN=Postgres;UID=ds_admin;PWD=__EncPwd1__|||test_point_NoUID (works)
    • odbc:DSN=Postgres;UID=ds_admin;PWD=__EncPwd1__|||test_point_Nouid (errors)
  • Ensure that your field mappings are correct – seems to work best when your spatial (now string type field) field is the same name as the spatial field in PostgreSQL. Field mappings are a little easier if the naming convention is the same between the output and the target table.
  • In the OUTPUT settings in Designer, you will not be able to specify the spatial object field (line 12) because your spatial data coming out of Designer will now be STRING format.
  • There is a LINE and a LSEG datatype… Line follows a mathematical model that is functionally useless for most applications of geography (represents an infinite line).
  • There is a BOX type as well that takes pairs of coordinates (opposite corners of the box).  My example above for polygon is technically a box, but seems to work fine as a polygon…
  • The UID (primary key/unique ID) is not necessary, but recommended
Attachments