Writing Multiple Geometry Columns to an Existing PostGIS Table

Files

WriteMultipleGeometries.zip
- 100 KB
- Download

Introduction

A PostGIS database supports multiple geometry columns within a single table, which is especially useful when a feature requires more than one spatial representation. By using multiple geometry columns, you can conveniently store all related geometries together in one place. For example:

Use Case: an area has a polygon boundary and a representative point
Geometry Column names: geom_poly, geom_point

Requirement

Read/Write access to personal PostGIS database
Basic knowledge of FME and read/write to PostGIS database
- For detailed instructions, please refer to the article, Tutorial: Getting Started with PostGIS and PostgreSQL
Any sample polygon spatial dataset or use "Parks" dataset from

Safe Software read-only PostgreSQL training credentials:

Host: postgis.train.safe.com
Port: 5432
Database: fmedata
Username: fmedata
Password: fmedata

to follow the exact steps in this tutorial.

Step-by-step Instructions

In this tutorial, we will:

Create a polygon table named "Parks" in PostGIS with a default geometry column named "geom"
Add an additional geometry column named "geom_park_centroid" to the existing Parks table

1. Create a New Workspace and add a PostGIS Reader

In a blank workspace, there are a few ways to accomplish adding a reader.

In the Search for Transformer window, start typing PostGIS and select PostGIS [Reader]. In the Add reader dialog, select your PostgreSQL database connection. If you do not have this, please see the credentials in the Introduction section.

Click on Parameters and set the following:

Database Connection:
- Connection: Training
- Schemas for Tables: public
  - Click on the ellipses to open the 'Select Schemas for Tables' dialog. You can select the individual tables from here
Constraints:
- Tables: public.Parks
  - Click on the ellipses to open the 'Select Tables' dialog. You can select the individual items from here

Click OK to accept the PostGIS Parameters. Click OK again to finish adding the reader to your canvas.

2. Add a FeatureWriter

As with readers, there are multiple ways to add a writer to the canvas.

In the Search for Transformer or Format window, start typing FeatureWriter and click on it to add it to the canvas. Connect the public.Parks reader to the transformer. Double-click on the FeatureWriter to open the parameters and set the following:

Writer:
- Format: PostGIS
- Connection: Training

Keep the default geometry column as "geom" under "Spatial Definition" (Geometry Definition in 2026.1+).

As of FME 2025.1, the Geometry parameter in Reader and Writer feature types is now configured within the Spatial or Geometry Definition Table under the User Attributes tab. For more information, including details about the change, please see Introducing Spatial and Geometry Definition Tables.

2. Add an additional Geometry Column to the table "Parks"

Our PostGIS database has a "Parks" table with a default geometry column "geom" for park boundaries. Now, we'll add a new geometry column geom_park_centroid to store each park's center point.

FME can only create the first geometry column. Below are some of the options to add more geometry columns:

In FeatureWriter Parameters, select "Parameters...", then "Advance," and paste the SQL below into "SQL To Run After Write."
Or run the SQL below in PostGIS
Or use SQLCreator/SQLExecutor transformer

The tutorial will walk through the first option using the FeatureWriter.

Click on the Parameters button in the FeatureWriter Parameters window. Next, find the Advanced drop-down menu and click on it to expand it. Paste the following SQL statement

SQL to Run After Write: ALTER TABLE public."Parks" ADD COLUMN geom_park_centroid GEOMETRY(Point, 26910);

Click OK

3. Read Updated Table

Now we need to read our new data back in. To do this, we will use a FeatureReader. Add FeatureReader to the canvas and connect it to the FeatureWriter.

Double-click on the FeatureWriter and set the following parameters:

Format: PostGIS
Connection: Training

Click on Parameters to select the tables:

Schemas for Tables: Public
- Click on the ellipses to open the 'Select Schemas for Tables' dialog. You can select the individual tables from here

Click OK to accept the Parameters. Back in the FeatureReader Parameters, continue setting the following:

Constraints:
- Feature Types to Read: public.parks
  - Click on the ellipses to open the 'Select Tables' dialog. You can select the individual items from here

Click OK to finish adding the FeatureReader to your canvas.

4. Update geom_park_centroid Column with Center Point Data

Add CenterPointReplacer to get the centroid of the parks
Add PostGIS Writer

5. Write Updates to Table

If you are using the Safe PostgreSQL database you will not be able to write new data to it.

Add a PostGIS writer to the canvas. Writers can be added in the same ways as readers and transformers can.

Set the following Parameters:

Format: PostGIS
Table Handling: Use Existing
Row Selection:
- WHERE Clause: @Value(ParkId) = parkid

By default, PostGIS stores column names in lowercase.

Set User Attributes:

Attribute Definition: Manual
Spatial Definition (Geometry Definition in 2026.1+): geom_park_centroid

Advanced Option Using the FeatureMerger and AttributeKeeper to Improve Performance

This tutorial on geometry columns also applies to geography columns. A column must exist in the database before writing to it. To add a geography column named geog_park_centroid to the "Parks" table, use the following SQL

ALTER TABLE public."Parks" ADD COLUMN geog_park_centroid GEOGRAPHY(Point, 4326);

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.