Converting from CSV to PostgreSQL and PostGIS or SQLite and SpatiaLite

Introduction

You have been assigned the task to update a PostgreSQL database with census data and library locations for different neighborhoods in the City of Vancouver. The census data needs to be split up by age range as well as neighborhood. The library dataset is missing neighborhood information, so using a spatial overlay you will add in the neighborhood information before writing out to the database.

The Safe Software provided PostgreSQL training database is read-only. Please follow the instructions in Creating a PostgreSQL/PostGIS Training Database with FME to create an instance with write permissions. Optionally, you can use SQLite and SpatiaLite writers instead to write locally without installing anything.

Step-by-step Instructions

Part 1: CSV to Tabular PostgreSQL or SQLite

1. Start FME Workbench and Add CSV Data

Start FME Workbench, and click on New to open a blank workspace. 

First, we need to add a source dataset. Click on the Add Reader button. In the Add Reader dialog, enter the following:

• Format: CSV (Comma Separated Value)
• Dataset: Census2016.csv
  • Click on the ellipsis and browse to the downloaded dataset
• Parameters:
  • Feature Type Name(s): From File Name
  • Field Names Line: 5

Then click OK.

CSV datasets often begin with additional information prior to when the data actually starts. In the parameter Preview section, you can see that there is some metadata about where the dataset originated from before the field names begin on Line 5. Updating the Field Names Line parameter to 5 will update the preview and read the data in correctly. While in the parameters, change the Feature Type Name(s) to From File Name to have the file name shown on the canvas. 

2. Filter Data

Now we need to filter the census data based on the different age range of potential visitors to the library. Inspect the source dataset using Data Preview (formerly Visual Preview) by running the workspace or using the View Source Data button. In Data Preview, note how there are multiple ranges within a larger age range. We are only interested in 

• ID 2 = Ages 0 to 14
• ID 6 = Ages 15 to 64
• ID 17 = Ages 65 and over

Add a Tester transformer to the canvas and connect it to the reader feature type. In the parameters, set the following tests:

• Clause 1:
  • Left Value: ID
  • Operator: =
  • Right Value: 2
• Clause 2:
  • Logic: OR
  • Left Value: ID
  • Operator: =
  • Right Value: 6
• Clause 3:
  • Logic: OR
  • Left Value: ID
  • Operator: =
  • Right Value: 17

Then click OK. 

3. Write to PostgreSQL

Now we are ready to write out the census data to the PostgreSQL destination. If you do not have access to a PostgreSQL database with write permissions, use a SQLite writer instead. 

Click the Writer button and in the Add Writer dialog, enter the following:

• Format: PostgreSQL
• Connection: <your PostgreSQL database connection>
  • See the PostgreSQL: Add Database Connection documentation for instructions

Then click OK. 

In the Feature Type parameters, set the following:

• Table Name: CensusByNeighborhood
• Table Qualifier:  public
  • Your database may have a different table qualifier.
• Table Handling: Drop and Create

Then click OK. 

After clicking OK, connect the CensusByNeighborhood writer feature type to the Tester Passed output port.  

In FME 2025.0, the PostgreSQL writer now automatically maps attributes of any case or if their are spaces to lowercase or underscores that PostgreSQL requires. If using FME 2024.2 or older, right-click on the connection line between the Tester and PostgreSQL writer feature type and click Auto Connect Attributes.

If you do not have access to a PostgreSQL database with write permissions, use SQLite:

• Format: SQLite
• Dataset: .\CensusByNeighborhood.sqlite

 In the Feature Type parameters, set the following:

• Table Name: CensusByNeighborhood
• Table Handling: Drop and Create

Then click OK. 

4. Run the Workspace

Run the workspace and view the output PostgreSQL table in either PG Admin or Data Preview. 

For more information about writing to PostgreSQL, see Converting to PostGIS: Create, Drop or Truncate a Table. 

If you used a SQLite writer instead of PostgreSQL, the output attributes will maintain their original case and spacing.  

For more information about writing to SQLite, see the SQLite documentation. 

Part 2: CSV to Spatial PostGIS or SpatiaLite

1. Start FME Workbench and Add CSV Data

As of FME 2025.2, the Coordinate System parameter is now configured within the Parameters dialog of each reader/writer format. For more information, including details about the change and affected transformers, please see Coordinate System Parameter Location Change.

• In a new workspace, click the Add Reader icon in the Toolbar and add a reader with the following parameters:

    • Format: CSV (Comma Separated Value)

    • Dataset: /libraries.csv

  
  

  Click the Parameters button to set the coordinate system:

    • Feature Type Name(s): From File Name(s)

    • Attribute Definition: Manual

  Name	Type
  LONGITUDE	x_coordinate
  LATITUDE	y_coordinate

    • Create Point Geometry from Attributes: Enabled

    • Coordinate System: LL84

  

  
  

  Click OK to add the reader to the workspace.

• In a new workspace, click the Add Reader icon in the Toolbar and add a reader with the following parameters:

    • Format: CSV (Comma Separated Value)

    • Dataset: /libraries.csv

    • Coordinate System: LL84

  
  

  Click the Parameters button and set the feature type name:

    • Feature Type Name(s): From File Name(s)

  Before adding the reader to the canvas, confirm that the LONGITUDE and LATITUDE attributes are set to x_coordinate and y_coordinate, respectively. If they are not, change the Attribute Definition to Manual, then manually set. FME will automatically create points from these attributes.

  

  
  

  Click OK to add the reader to the workspace.

   

If continuing from Part 1, the Field Names Line will need to be set back to 1 to correctly read this dataset. 

2. Add Neighborhood Boundaries

Now we need to add in the neighborhood boundaries. Add another reader to the canvas.

• Format: Esri Shapefile
• Dataset: local_area_boundary.shp

 Then click OK. 

3. Reproject the Neighborhood Boundaries

If you inspect the local_area_boundary shapefile, you will notice that it is in the UTM83-10 coordinate system. We will need to reproject this into LL84 to match our libraries CSV file. 

Add a Reprojector transformer to the canvas and connect it to the local_area_boundary feature type. In the parameters, set:

• Destination Coordinate System: LL84

4. Retrieve the Attributes from Neighborhoods and Add them to the Libraries

Each library falls within a Vancouver neighborhood, to add this neighborhood attribute to the libraries, we will use a PointOnAreaOverlayer transformer. Add a PointOnAreaOverlayer transformer to the canvas, then connect the Point input port to the libraries reader feature type, and the Area input port to the local_area_boundaries reader feature type. 

In the PointOnAreaOverlayer parameters, set:

• Attribute Accumulation:
  • Merge Attributes: Checked

5. Clean up the Attributes

We need to clean up the attributes before we write out to the database. Add an AttributeManager transformer to the canvas and connect it to the Point output port on the PointOnAreaOverlayer. In the parameters, set:

• Update Attribute:
  • Input Attribute: NAME
    • Output Attribute: NEIGHBORHOOD
• Remove Attributes:
  • Input Attribute: MAPID
    • Action: Remove
  • Input Attribute: _overlap
    • Action: Remove

6. Write to PostGIS

Now we are ready to write out the data to the PostGIS destination. If you do not have access to a PostGIS database with write permissions, use a SpatiaLite writer instead. 

Click the Writer button and in the Add Writer dialog, enter the following:

• Format: PostGIS
• Connection: <your PostGIS database connection>
  • See the PostGIS: Add Database Connection documentation for instructions
• Coordinate System: LL84

Then click OK. 

In the Feature Type parameters, set the following:

• Table Name: Libraries
• Table Qualifier:  public
  • Your database may have a different table qualifier.
• Table Handling: Drop and Create

Then click OK. 

After clicking OK, connect the Libraries writer feature type to the AttributeManager.  

In FME 2025.0, the PostgreSQL writer now automatically maps attributes of any case or if their are spaces to lowercase or underscores that PostgreSQL requires. If using FME 2024.2 or older, right-click on the connection line between the Tester and PostgreSQL writer feature type and click Auto Connect Attributes.

If you do not have access to a PostGIS database with write permissions, use SpatiaLite:

• Format: SpatiaLite
• Dataset: .\Libraies.sqlite

 In the Feature Type parameters, set the following:

• Table Name: Libraries
• Table Handling: Drop and Create

Then click OK. 

7. Run Workspace

Run the workspace and view the output in Data Preview. 

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.