Writing Multiple Geometry Types to an Existing PostGIS Table

Introduction

A PostGIS database can have multiple spatial columns, but due to limitations, it is a multiple-step process to write to these columns in FME. If you are writing to Oracle or SQL, please see Writing to Database Tables that contain Multiple Geometry Columns.
 

Step-by-step Instructions

1. Create Database Table
Before you begin, please ensure you have created your personal training database by following the directions in Creating a PostgreSQL/PostGIS Training Database with FME.  
 
2. Open Workspace
To save time, open the CreateTable-City-Parks.fmw workspace. This workspace will create a table called City_Parks in the FMETraining database with all of the attributes that will be needed, as well as a Geometry column called geom. 



3. Edit Connection and Run Workspace
Edit the database connection in the Navigator pane, by right-clicking on FMETraining and selecting Edit Connection. Enter the credentials that were set up in the Creating a PostgreSQL/PostGIS Training Database with FME article, in most cases, only the password needs to be updated. 


Run the workspace, and confirm that the database table was created. 
 
4. Add Geography Column in pgAdmin
Open pgAdmin and then right-click on the newly created City_Parks table under the public schema and select Properties. 

Screenshot from pgAdmin

In the City_Park properties, switch to the Columns tab, then click on the plus sign (+) in the upper right corner to create a new column. Create a new column called geog, and assign it the geography data type. Click Save to save the column. 


 
5. Open Write-Multiple-Geometries.fmwt Workspace
Return to FME Workbench, and open the Write-Multiple-Geometries.fmwt workspace. This workspace reads from a MapInfo TAB dataset containing park polygons. We will use the attributes and the polygons to INSERT data into the City_Parks table and the geom column will contain the polygon information. 
The second bookmark in the workspace is taking the center point of the park polygon and converts it into a point. We will only use the ParkId to UPDATE the City_Parks table to include the geog column, which will contain the point information. 

 
6. Add a PostGIS Writer
Add a PostGIS writer to the canvas and connect to the FMETraining database. Since we already created the database table structure, we want to import that. Set the Table Definition to Import from Dataset, then click OK. 


In the Import Writer Feature Types, ensure the Format is set to PostGIS and the Connection is set to FMETraining, then open the Parameters. 


In the parameters, click on the ellipsis next to Tables, then select the City_Parks table. Click OK three times to finish adding the writer. 


Connect the public.City_Parks writer feature type to the Parks reader feature type. This writer will write all of the Parks data. 

 
Double-click on the public.City_Parks writer feature type to open the parameters. Change the Table Handling to Truncate Existing, as we want to ensure that the geog column that we added in pgAdmin will still be there when the data is written. Note that the Spatial Column is set to geom, this will contain the polygon data. 

 
7. Add a Second Writer
Add a second PostGIS writer to the canvas, and connect to the FMETraining database. Repeat the previous steps to import the City_Parks table. This time connect the public.City_Parks writer feature type to the AttributeKeeper. 


Open the parameters for the public.City_Parks writer feature type that is connected to the AttributeKeeper. This writer will UPDATE the table by adding in the point data which is stored in the geog column. 
Set the Feature Operation to Update then the Table Handling to Use Existing. Now select parkid for Match Columns. 
Since we want the point data to be written to the geog column that we created in pgAdmin. Change the Spatial Type to Geography then change the Spatial Column to geog. 

 
One final step is to switch to the User Attributes tab and change the Attribute Definition to Automatic, so that only the parkid is being written as an attribute. Click OK to close the second writer parameters. 

 
8. Set Connection Runtime Order
To ensure that the park polygon data gets written out first, we need to set the Connection Runtime Order. Right-click on the grey connection triangle coming from the Parks reader feature type, then select Set Connection Runtime Order.

 
In the Runtime Order dialog, click on the Parks -> public.City_Parks connection, then using the arrows at the bottom, move it to the top so that this connection runs first. Click OK. 

 
9. Run Workspace and View Output in pgAdmin
Run the workspace, then view the output data in pgAdmin. Since the PostGIS readers and writers don’t support multiple geometries, only the first geometry type will be shown in Visual Preview.

 

Reading Multiple PostGIS Geometries

FME will read in the first geometry column, then ignore the remainder. To read in additional geometry columns, an SQL SELECT statement needs to be used to read in the columns as text. 

After adding the PostGIS reader to the canvas, open the reader feature type parameters, and enter in the following SELECT Statement:

SELECT *, ST_AsText(geom)
FROM "public"."City_Parks";

Then after running the workspace, the geog attribute will be shown in the Feature Information window in Visual Preview, and the geom data will be listed as st_astext. 

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.