Viewing and Inspecting PostGIS Data

Introduction

Connecting to a database is slightly different from selecting a file for a file/folder-based format. The operation relies much more on format-specific parameters. In this article, we'll learn how to create a database connection, then view and inspect data stored in PostGIS or PostgreSQL using FME Workbench Visual Preview.
 
Note: This example will use PostGIS in the text and screenshots, but it is also applicable to the PostgreSQL format.
 

Video

This video was recorded using FME 2016.1, the data is different from the current article, but the concepts are still the same.

Connecting to a Database

Connecting to a database with FME is a bit different than reading from a database. In order to retrieve data from a database, a database connection needs to be set up, regardless of if the database is on-premise or in the cloud. Database connections provide a convenient and secure way to store and reuse connection parameters. 

Database connections are by default private and are only available for the user. If a public connection is desired, please see Making Database and Web Connections Public for more information. 

Before creating a connection, obtain the following for your PostgreSQL or PostGIS database: 

• Host (Server) Name
• Network Port Number
• Database (Service) Name
• Username
• Password

Safe Software provides a READ-ONLY PostgreSQL/PostGIS database to use while working through training or articles. Since this database is read-only, instructions have been provided on how to create and set up your own training database that allows writing access as well. For instructions on how to create that database, see Creating a PostgreSQL/PostGIS Training Database with FME. 

Safe Software read-only PostgreSQL training credentials: 

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

Connection Methods

There are two main ways to set up a database connection in FME. The first way is through the FME Options, which allows you to set up the database connection before creating a workspace. The second more common way is to create the database connection while adding a reader or writer.
 

FME Options

In FME Workbench or FME Data Inspector, on the top menu bar go to Tools > FME Options (Windows), FME Workbench > Preferences (macOS and Linux). 


When the FME Options dialog opens, select Database Connections from the side menu. Database Connections will show you all of your connections. This is where you can add, edit, delete, or test connections. 
To create a new database connection, click on the plus (+) sign at the bottom. Then proceed to Add Credentials and Testing.

Reader or Writer

The other option for creating a Database Connection is from within the Add Reader/Writer dialog in FME Workbench or FME Data Inspector. After selecting either PostgreSQL or PostGIS as the Format, the dialog will change from Dataset to Connection. Click on the drop-down section of the Connection dialog and select Add Database Connection. This drop-down will also show other database connections for this format you may have already created. Proceed to Add Credentials and Testing. 

 

Add Credentials and Testing 

In the Add Database Connection dialog, select PostgreSQL as the Database Connection, then give the connection a meaningful name. If you are connecting to the Safe Software provided training database, fill in the credentials as follows, otherwise, use your own credentials:

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

Default credentials from Creating a PostgreSQL/PostGIS Training Database with FME:

• Host: localhost
• Port: 5432
• Database: FMETraining
• Username: postgres
• Password: <user created>

Click Test, if the test is successful, click Save, and the database connection is ready to use. 

 
If the Test fails, confirm that the credentials were filled in correctly. If the Test still fails, ensure that the database is turned on, or if you are using a cloud-based database ensure that your computer or network IP has been set as an Authorized Network (Google Cloud example documentation). If you are still having trouble, see the Troubleshooting section for PostgreSQL or PostGIS in our documentation. 

 

Viewing Data

In FME Workbench, data can be viewed directly in the FME Workbench window using Visual Preview. Data can also be viewed using FME Data Inspector, for more information see Getting Started with FME Desktop: Introduction to FME Data Inspector.
 
1. Open FME Workbench
In a blank FME Workbench canvas, add a PostGIS reader. If you haven’t already set up your database connection, click on the drop-down next to Connection and then follow the steps listed in Connecting to a Database. Once you have selected or connected the database connection, click on the Parameters button.

 
In the Parameters dialog, click on the ellipsis next to Table List to open up the PostGIS/PostgreSQL database. Select which tables you would like to read in. Once the table has been selected, click OK twice to add the reader to the canvas. For this example, read in public.AddressPoints

 
2. Open Visual Preview
Once the reader feature type has been added to the canvas, click on it to open the popup menu. On the popup menu, click on the View Source Data button to open Visual Preview.

 
Now you can view the data in Visual Preview. Continue your workflow as desired. 

Data Attribution

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