Converting to PostGIS: Write to an Existing Table | Shapefile to PostGIS

Introduction

Often, a new FME workspace creates database tables that are a duplicate of the incoming data. However, sometimes a user will wish to write to a database table that already exists.
When a table already exists, the workspace should reflect the table schema (not the source), and this is achieved by importing the schema of that table from the database. When adding a database writer, the import option allows us to use the schema of a database table that has been previously created.
 

Step-by-Step Instructions

1. Create the Training Database
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. Inspect Database Table
Start FME Workbench and create a blank workspace. Add a PostGIS reader to the canvas and connect to the FMETraining Database that was created in step 1. 


In the parameters, click on the ellipsis next to tables, then select the BikePaths table from the public schema. Click ok three times to finish adding the reader. 


3. Inspect PostGIS
This reader is only temporary, we are only using it to view the table. Optionally, this can be done in FME Data Inspector. 
Click on the public.BikePaths reader feature type to open the popup menu. Then click on View Source Data to view the data in Visual Preview. 


In Visual Preview, you can see there are multiple bike paths throughout the city of Vancouver that are all L (large) path types. The important part of viewing the data, is not the data itself but the structure (schema) of the dataset. To ensure the new data can be added successfully to this table, we need to import the schema. 

 
4. Read in Shapefile Data
The PostGIS reader can be deleted if desired, as we won’t be using it anymore. Next, add an Esri Shapefile reader to the canvas. Browse to the BikePaths_S.shp dataset; this dataset is available in the Files section of this article. Click OK to finish adding the reader. 

 
5. Inspect Data
Before we write out the data, let’s inspect the shapefile data to see what the starting schema is. On the BikePaths_S reader feature type, click on View Source Data to open Visual Preview. 
In Visual Preview, you can see that the schema is similar, but there are capitalization differences in the attribute names. The database is using all lowercase, whereas this dataset has CamelCase. 

 
6. Add Writer
We can resolve the schema case conflict by importing the desired schema when we add the writer. 
Add a PostGIS writer to the canvas and connect to the FMETraining database. To import the schema from the existing table, change the Table Definition to Import from Dataset, then click OK. 

 
After clicking OK, an Import Writer Feature Types dialog will appear, this dialog can be used to import feature types from any source. Since we already read in the Training PostGIS database earlier in this tutorial, the parameters should already be set. Confirm that the Format is set to PostGIS and the connection is FMETraining. Then open the Parameters. 


The parameters are the same as when the table was read in earlier. Click on the ellipsis next to Tables and select public.BikePaths, click OK three times to finish importing the schema. 

 
The schema definition of the selected table is now added to the workspace. Notice that the writer feature type is a copy of the existing database table schema (and not the reader feature type, as a standard workspace would create). You can tell by inspecting the newly added writer feature type, as it already has the BikePaths data in it. Additionally, all the attributes are the same.

 
7. Set Feature Operation and Table Handling
Double-click on the public.BikePaths writer feature type to open the parameters. Check that the Feature Operation parameter is set to Insert and then change the Table Handling to Use Existing. This will allow us to add more data to the table without overwriting it or removing it first. For more information on the database operations available in FME, please see the Feature Operation documentation.

 
8. Map Schema
The reader and writer feature types now need to be connected. Connect the BikePaths_S reader feature type to the public.BikePaths writer feature type. If you click the drop-down arrow next to each, you will notice that the arrows are yellow and red, which means that the attributes are not correctly mapped, this is due to the lowercase and CamelCase attribute names. 

 
To quickly correct this, right-click on the connection line and select Auto Connect Attributes. If you were planning on modifying the attributes or further transforming your data before writing it out, it is recommended that you use the Replace Link with AttributeManager option instead.


 
 
9. Run Workspace and Inspect Results
Save and run the workspace. Once the workspace has finished running, click on the public.BikePaths writer feature type to open the popup menu. On the popup menu click on the View Written Data button to open Visual Preview. 

 
The BikePaths table should now have both large and small bike paths within Vancouver. Note that if the workspace is run more than once, then new data will be added repeatedly!

 

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.