Tutorial: Getting Started with PostGIS and PostgreSQL

Introduction

This tutorial is an introduction to using PostGIS and PostgreSQL with FME. Databases are useful for long-term data storage, rather than short-term data transfers, so the key operations are getting data into and out of that storage. For more information about how to work with databases in FME, as well as specific FME terminology for databases, see the Working with Databases & Data Warehouses and FME article.

The three key operations that occur with a database are:

• Data imports
• Data updates
• Data distribution

All of the activities above involve not only transforming the data into the correct format but also transforming the data into the correct schema (data model).
The tutorial series will cover connecting to a PostGIS/PostgreSQL database, reading from PostGIS, and writing to PostGIS. All of these examples are also applicable to using the PostgreSQL format. 

Database Access

Safe Software has provided a PostgreSQL training base to read data from within FME. However, this database is read-only. For the tutorials where you will be writing to PostgreSQL, you will need to have PostgreSQL installed on your computer or have access to a cloud-based version. Please see Creating a PostgreSQL/PostGIS Training Database with FME for instructions on how to create your own training database. 
 

Articles

Viewing and Inspecting PostGIS Data

How to connect to a PostGIS or PostgreSQL database and have the connection persist through different FME sessions.

Converting from PostGIS

Read data from PostGIS, involving some common database reader parameters, then write out to Esri File Geodatabase.

Creating a PostgreSQL/PostGIS Training Database with FME

Migrate data from the Safe Software provided PostGIS training database into your own database to prepare for the articles that write to PostGIS/PostgreSQL. 

Converting to PostGIS: Create, Drop or Truncate a Table

How to write to a new PostGIS table.

Converting to PostGIS: Write to an Existing Table

Details how to write to a database table that already exists through importing table schemas.

Updating a PostgreSQL Database Using UPSERT

Learn how UPSERT data into a PostgreSQL database in FME 2022.0 or newer.

Writing Multiple Geometry Types to an Existing PostGIS Table

PostGIS can have multiple geometry columns, learn how to write multiple columns in FME. 

Additional Information

• Tutorial: Updating Databases
• Tutorial: Let the Database Do the Work
• Working with Databases & Data Warehouses and FME
• How Do I Do That in FME
• PostGIS Reader/Writer documentation
• PostgreSQL Reader/Writer Documentation