Creating a PostgreSQL/PostGIS Training Database with FME

Introduction

Safe Software provides a read-only PostgreSQL training database so that users can quickly get started. Since this database is read-only, we have provided instructions for you to seed and create your own PostgreSQL/PostGIS database that you can read and write to. By creating your own database, you can follow along with all of the articles without any interruptions. PostgreSQL is free and easy to install. 
 

Step-by-step Instructions

1. Download PostgreSQL
Download PostgreSQL, if you want to install PostGIS as well, ensure that Stack Builder is also installed. 

Note that this tutorial, and any tutorials that will use this training database, will assume default permissions and setup. Please see the PostgreSQL documentation for any information beyond defaults. 

Screenshot from PostgreSQL installer


Screenshot from PostgreSQL Stack Builder installer

This will download file PostGIS extension. Navigate to the download location, and then double-click on the postgis_x_x_pgx installer and complete the PostGIS installation process.

2. Launch pgAdmin
After PostgreSQL has been installed, open pgAdmin. You can use another method for accessing your database, but this tutorial will only cover pgAdmin. 

Log into pgAdmin with your master password, if this is the first launch, you will need to set up a master password. 

3. Create Database
On the right-hand side, expand Servers, then PostgreSQL, and right-click on Database, then Create a new Database.

Screenshot from PostgreSQL pgAdmin


Create a new database called FMETraining, then take note of the Owner, we will use this in FME. Click Save to create the database. 

Screenshot from PostgreSQL pgAdmin


4. Enable PostGIS Extension
Still in pgAdmin, within the FMETraining Database right-click on Extensions then Create Extension. 
. 
Screenshot from PostgreSQL pgAdmin

Then in the Create - Extension dialog, click the drop-down next to Name and select postgis. Click Save. 
 
Screenshot from PostgreSQL pgAdmin

The database may need to be refreshed, but the postgis extension should now be available under Extensions. 

Screenshot from PostgreSQL pgAdmin


5. Open FME Workbench
Open the CreatePostgresTrainingDatabase.fmw workspace in FME Workbench. This workspace is available from the Files section of this article. Note that this workspace will be modified as more tutorials are added to the FME Community. The workspace may have more or different datasets then what is shown in the following screenshots. 


This workspace reads from the publicly available postgis.train.safe.com PostgreSQL database that Safe Software provides. This workspace is read-only. 

In the Navigator pane, expand Database Connections, right-click on FMETraining and select Edit Connection. 


In the Edit FMETraining dialog, update the connection parameters with the parameters for your PostgreSQL instance. 


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. 


6. Run Workspace
Run the workspace to seed the new FMETraining database with data. After the translation was successful, the FMETraining database is now ready to use with any article on the Community that requires PostgreSQL reading or writing access. 

If at any time you wish to reset your personal FMETraining database, re-run this workspace and it will drop and create the table. 

Screenshot from PostgreSQL pgAdmin

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.