How to: Write Spatial Data to PostgreSQL

[khoward85]

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 Type	Description	Syntax
point	Point on a plane	(x,y)
line	Infinite line	{A,B,C}
lseg	Finite line segment	((x1,y1),(x2,y2))
box	Rectangular box	((x1,y1),(x2,y2))
path	Closed Path	((x1,y1),...)
path	Open path	[(x1,y1),...]
polygon	Polygon	((x1,y1),...)
circle	Circle	<(x,y),r> (center point and radius)

per the documentation here: https://www.postgresql.org/docs/13/datatype-geometric.html
 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