In this tutorial we will read CSV data, create points, and write them to PostGIS. We will
also examine some of the PostGIS writer parameters for table handling, specifically the create
drop and truncate table options.
Here is our source CSV data of downtown public art locations which is delimited. We will
use the latitude and longitude columns to create points. We will write the points to
a PostGIS database on an Amazon EC2 machine into a schema called fmedata2015.
Create a new workspace in FME workbench. Drag the CSV file onto the canvas. The format and
dataset are automatically set.
Click on parameters and double check that the file preview is correct. Setting the longitude
and latitude to an X and Y coordinate type allows FME to create points as it reads the data.
Also within the reader dialogue set a coordinate system for the points. Select a lot long WGS84
coordinate system.
Now let's add a PostGIS writer. From the writer's menu select add writer. Select PostGIS for
the format. If you already have a database connection setup you may use it, otherwise,
you can select embed connection parameters and type them in. Notice the default table
creation parameters regarding the spatial column and the attribute case. We will use
these default values. Click OK to dismiss the dialog boxes.
Connect the source and destination.
Now open the PostGIS destination feature type properties. We will change the table name
to downtown public art. We also need to set the table qualifier to our schema FMEdata2015.
Click on the format parameters tab. A feature operation, "insert" and table handling,
"create if needed" is required. This means if the table does not exist then FME will
create it. If it does exist FME will write to the existing table. We will try the drop
and truncate options shortly.
It's obvious that the attributes are not mapped because of the red arrows on the destination
attributes. A quick way to deal with these is to right-click on the connection and select
the replace link with attribute manager option.
Inside the properties of the attribute manager, map the mixed case to the lowercase attributes.
Go ahead and run the workspace.
There are 114 features that have been written to a new table in PostGIS. Right-click on
the destination and choose inspect.
Data is read directly from the PostGIS table by the data inspector and we can confirm that
we have points in the table.
If we ran this workspace again FME would load another 114 points into the same table because
our table handling brighter parameter was set to create if needed. If we need to completely
reload data into the same table we would select the truncate existing table handling option.
Go ahead and run the workspace.
We still have the data inspector open so just hit the refresh button to read from the table
again. We can confirm that we have 114 points and not 228. This confirms that the truncate
did in fact happen. We can also confirm that the truncate took place in the log file. If
we decided to rename our longitude and latitude columns to long and lat we could change it
here in the user attributes and also in the attribute manager. The schema no longer matches
the existing table in PostGIS so dropping the existing table and creating a new one
makes sense.
Go ahead and run the workspace again.
The log file confirms that FME has dropped and created the table.
Inspecting the table again confirms that the table was created with the new schema.