Converting to PostGIS: Create, Drop or Truncate a Table

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2022.0

Introduction

Simply writing data into a new table in a database is not much more complex than writing to a file format. There are a number of parameters for each feature type (table) that allow users to control writing to that table. The main thing to consider is whether or not the table already exists in the database, if it does do you want to erase it first and recreate it or update what is already there? For more information on the database operations available in FME, please see the Feature Operation documentation.
In this article, we will read data from Microsoft Excel and then write it out to PostGIS. In part 1, we will drop and create the table since it already exists in our database. In part 2, we will truncate the data. 
 

Source Data

The table that we will be working with is from the PostGIS training database. Please see Creating a PostgreSQL/PostGIS Training Database with FME before you begin to create your own version of this database to work with. 
IntialPostgres.png
Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.

The initial public.West_End table has four attributes (columns): name, title, longitude, and latitude. As well, there are only 14 rows (records). 
 

Step-by-step Instructions

Part 1: Drop and Create

1. Add a Microsoft Excel Reader
Start FME Workbench and begin with a blank canvas. Add a Microsoft Excel reader to the canvas and browse to the PublicArt.xlsx file that is available from the Files section on this article. Set the Coord. System to LL84  and then click the Parameters button.
AddReader.png
 
In the Parameters, we only need to read in the West End Excel spreadsheet. Uncheck all of the neighborhoods except West End. Next, double-check to ensure that the Longitude and Latitude attributes have the Type x_coordinate and y_coordinate, respectively. Depending on your version of FME this may or may not set automatically. If it is not set, click the drop-down to switch the Type to x_coordinate or y_coordinate, (you may need to set the Attribute Definition to Manual).
Params.png

Having the Type set to x_coordinate or y_coordinate tells the reader to create points from the Excel data as it reads the file. Click OK twice to add the reader.
 
2. Inspect the Data
Now let’s inspect the data to ensure the reader created the points. Click on the reader feature type to open the popup menu and then select View Source Data to open up Visual Preview.
ViewSource.png
 
There should be 14 different pieces of public art and there should also be points in the Geometry view.
VPPoints.png
Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.
 
3. Add PostGIS Writer
Now from the Writers menu, click Add Writer. For Format select PostGIS and for Connection, select the FMETraining connection created in Creating a PostgreSQL/PostGIS Training Database with FME.  
For Table Definition, select Copy from Reader, so that the source schema is used to define the PostGIS table, then click OK to add the writer.
PostWriter.png
 
4. Define Table
Once the writer is added to the canvas, double-click on the writer feature type to open the parameters.
Leave the Table Name as West_End. Set the Table Qualifier to public.
The Table qualifier is the same as the Schema for Table Listing in the PostGIS Reader parameters. It is specified per database table as one could write to multiple schemas using the same database writer.
WriterParams.png
 
Next, we need to define how the table is to be handled. Since this table was included when the FMETraining database was created in Creating a PostgreSQL/PostGIS Training Database with FME, we need to either update the data or recreate the data. For this example, let’s recreate. 
Set the Feature Operation to Insert, then set the Table Handling to Drop and Create. This will drop the existing public.West_End table, then create it again using the data from the Microsoft Excel spreadsheet. 
DropAndCreate.png

If we weren’t sure if this table already existed, and we were ok will appending more data on if the table did exist, we use Create If Needed. Now, if we wanted to remove the old data, but were confident that the table schema was correct, we can use Truncate Existing, which will be covered in Part 2.  
 
5. Map Attributes
The reader and writer feature types now need to be connected. Connect the West End reader feature type to the public.West_End 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 how PostGIS/PostgreSQL handles attributes; it has everything in lowercase whereas the attributes coming from the Excel file are in Title case.
Connection.png
 
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.
ReplaceLink.png
GreenConnections.png
 
6. Run Workspace and Inspect Output
Save and run the workspace. Then click on the public.WestEnd_PublicArt writer feature type to open the popup menu, then click the View Written Data button. 
ViewWritten.png
 
Dropping and creating the table is a quick solution if you are not concerned about losing data. The data from the Microsoft Excel spreadsheet very closely matched the data already in the database, so you will see minimal change. To really see a change, complete Part 2, then re-run Part 1 to reset the database table. 
Part1Vp.png
Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.
 

Part 2: Truncate a Table

Truncating a table removes all of the rows from the table, but leaves the attributes (columns). This is important to preserve any relationships between other tables. 
 
1. Continue In Part 1 Workspace
Continuing in the workspace from Part 1, add a Tester transformer between the reader and writer feature types. 
Tester.png
 
In the Tester parameters, set the test to Name Attribute is Missing. We want to replace the entire table in the database with only the art pieces that don’t have a name. 
TesterParams.png
 
2. Update PostGIS Writer Parameters
This database table is going to be used for a project to find names for various art pieces around the City of Vancouver. Since we know this table already exists, and we only want to change the data, we will use Truncate Existing. 
 
Double-click on the public.West_End writer feature type to open the parameters. Then change the Table Handling to Truncate Existing. 
Truncate.png
 
3. Run the Workspace
Run the workspace, then inspect the output in Visual Preview. The attribute names should still be the same, but there is only one record. 
TruncateVP.png
 
If you want to try truncating again. You can remove the Tester and rerun the workspace with the same parameters set for the writer. 


 
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. 

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.