Converting from PostGIS

Introduction

With FME, connecting to a database is slightly different from selecting a file for a file/folder-based format. Various FME parameters exist to provide control when reading a database. These parameters can control the reader (database) and the feature types (tables) to improve database read performance. Not every feature in every table is required when reading from a database. To learn more about how to improve database reading performance, see the article Let the Database Do the Work: Reading

In this article, a WHERE Clause is utilized to read only the park features in the neighborhood of interest. A WHERE Clause is a specific query that only allows features that pass the query to be returned to FME. This employs database query tools (SQL, specifically), which in turn use database indices, and is much more efficient than reading an entire table and then filtering it with a Tester transformer.

Video

This video was recorded using FME 2016.1; the data differs from the current article, but the concepts remain the same.

Step-by-Step Instructions

1. Add PostGIS Reader

Start FME Workbench and open a new workspace.

From the Readers menu, select Add Reader. Select PostGIS for the format. For Connection, select the PostGIS Training connection created in Viewing and Inspecting PostGIS. If you do not have this named database connection, click on the drop-down and select Add Database Connection, then enter the following connection parameters:

• Name: Training Postgres
• Host: postgis.train.safe.com
• Port: 5432
• Database: fmedata
• Username: fmedata
• Password: fmedata

Next, click the Parameters button, then, for Table List, click the ellipsis |and choose public.Parks. We can set our WHERE Clause in the reader parameters, but we want to inspect our data first. The WHERE Clause can be added later. Click OK twice.

2. Inspect Data

Now that we’ve accessed the database successfully and added the Parks dataset, we can inspect the data. Click on the reader feature type to open the mini toolbar and then select View Source Data to open up Data Preview (formerly Visual Preview).

Here we have a dataset of all the parks in Vancouver, BC, but we are only interested in those in the West End neighborhood. We will use a WHERE Clause to filter these out.

Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.

3. Set WHERE Clause

To set the WHERE Clause after the reader has been added, we need to use the Navigator pane. In the Navigator pane, expand the Training Postgres [POSTGIS] reader, then expand Parameters. Double-click on the WHERE Clause to open the parameters window.

In the Edit Training Postgres [POSTGIS] dialog, click on the ellipsis next to WHERE Clause to open the text editor.

In the text editor, add the following, taking note of the single vs. double quotation marks. Click OK twice to add the WHERE Clause.

"NeighborhoodName" = 'West End'

On the left, NeighborhoodName signifies the attribute name, and on the right, West End signifies the attribute value. In standard SQL, double quotation marks indicate an attribute/field name, while single quotation marks indicate an attribute/string value.

4. Add File Geodatabase Writer

Now we need to write out the data. From the Writers menu, select Add Writer. Select Esri Geodatabase (File Geodb Open API) for the format. For the Dataset, click the Open Folder Browser button and enter a name (WestEndParks.gdb) and location (wherever you like) for the .gdb file. Set the Feature Class or Table Definition to Automatic and then click OK.

The Feature Type dialog will appear. Set the Feature Class or Table Name to WestEndParks. Then set the Geometry to geodb_polygon, and click OK.

Once the writer feature type is added to the canvas, connect the public.Parks reader feature type to the WestEndParks writer feature type.

5. Run Workspace

Save and then run the workspace. To view the data, click on the writer feature type to open the mini toolbar and then click on View Written Data.

Preserving the attributes from the source dataset, the File Geodatabase writer writes only the 16 parks in the West End Neighborhood to WestEndParks.gdb.

Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.

Advanced Exercise

Using Concatenated Published Parameters

The problem with the WHERE Clause parameter, as with similar parameters, is that it is difficult to get user input and apply it to the clause. Simply publishing the parameter is not useful because the user would have to enter the full clause (<field> = <value>), whereas often only the <value> part is required. This is where a concatenated parameter comes in. It is a parameter composed of a constant string (the <field> part) and a user-defined value (the <value> part).

1. Add a Published Parameter

Before beginning, ensure that you have completed the previous steps up until Step 4. In the Navigator pane, right-click on User Parameters and select Manage User Parameters.

In the top-left corner, click on the green plus (+) sign and select Text as the parameter type. 

Next, on the right-hand side of the dialog, enter the following parameter properties:

• Parameter Identifier: NeighborhoodName
• Prompt: Neighborhood Name
• Published: Enabled
• Optional: Enabled
• Disable Attribute Assignment: Disabled
• Default Value: ‘West End’ 

Ensure that there are single quotes around ‘West End’ and then click OK.

2. Convert WHERE Clause to a Private Parameter

Now that we have the published parameter, we can update the WHERE Clause. In the Navigator pane, right-click the PostGIS reader feature type WHERE Clause parameter and select Create User Parameter.

Uncheck the Published box, so the user is not prompted to set this parameter when the workspace is run; this is called a private parameter. Then set the Value to:

“NeighborhoodName” = $(NeighborhoodName)

This published parameter references the previously created parameter, indicated by $(NeighborhoodName). Click OK to add the new private parameter.

3. Run the Workspace Using Prompt for User Parameters

Before running the workspace, click the Run drop-down and enable Prompt for User Parameters. This option will prompt the user to select a neighborhood at runtime.

Now run the workspace and when prompted, enter a valid NeighborhoodName. Choose from:

• 'Downtown'
• 'Fairview'
• 'Kitsilano'
• 'Mount Pleasant'
• 'Strathcona'
• 'West End'

Note that single quotation marks indicate an attribute/string value and are required for attribute values. 

Since the geodatabase is set up to insert new content, the new neighborhood will be added to the existing West End.

Map tiles by Stamen Design, under CC-BY-3.0. Data by OpenStreetMap, under CC-BY-SA.

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.