Single edits PostgreSQL: Update a Database Using an FME Server App

Introduction

FME Server can create FME Server Workspace Apps that allow users to run a workspace and enter parameters without the need to log in. These apps allow users to update datasets on the fly while out in the field using their mobile phones. In this article, we show you how to set up PostGIS and FME Server to respond to single-feature database edits. If a large number of edits are being made, a bulk insert can also be set to push out large quantities of data with a single call. This is described in the following article Bulk Edits PostgreSQL: Update a Database Using an FME Server App.

Requirements

• PostgreSQL database with write permissions; see Creating a PostgreSQL/PostGIS Training Database with FME for instructions on how to create a local database. 

Step-by-Step Instructions

Part 1: FME Desktop

Before we begin, we will need to open the two workspaces in FME Desktop to change the parameters to our own PostGIS database. If you do not have a PostGIS database, please follow the instructions in Creating a PostgreSQL/PostGIS Training Database with FME before continuing. 

1. Open FME Desktop

Note: If you completed the Bulk Edits PostgreSQL article, you can skip to Part 1 - Step 3 as you will already have the table loaded into your database.

First, we need to create a table in our database. Open CreateTableInPostGIS.fmwt and expand the PostGIS writer in the Navigator window. Double-click on Connection to open the connection parameters.

In the Edit ‘PostGIS Training [PostGIS]’ dialog, click the drop-down next to Connection and either add a new database connection or use the FMETraining connection created in Creating a PostgreSQL/PostGIS Training Database with FME.

2. Run the workspace

Save the workspace and then run it. This will create a new table in your PostGIS database called Tree_Survey.

Go into your PostGIS database, or inspect the output in FME to confirm that the table was written correctly.

3. Open Second Workspace

Close the first workspace and then open PostGIS-TreeSurvey.fmw. This workspace will read the database and then based on the user parameters, either INSERT, UPDATE, or DELETE the data in the database.

There are a variety of user parameters created to allow user input when we upload this workspace to FME Server.

Let’s go through what this workspace does:

a. Reads in the Tree_Survey database table created with CreateTableInPostGIS.fmwt

b. Tests the user parameter $(Tree_ID), if the Tree_ID exists, it will move on to the UPDATE or DELETE section. If it doesn’t exist, it will move to the INSERT section.

c. Has the tree been removed? This is based on the $(Removed) user parameter. If yes, then it triggers the DELETE workflow. If no, then it moves to UPDATE.

d. Joins the $(Tree_ID) user parameter with the Tree_ID attributes in the database to see if the value input for the user parameter exists in the database. If it doesn’t, it moves onto the INSERT workflow

e. Tests the new Tree_ID and if the tree is new. If it is new, then it continues the INSERT workflow; if it is not new, the workspace ends.

f. Sets the additional user parameters to include in the UPDATE or INSERT

g. Sets the database operation with the FME specific attribute fme_db_operation. Additionally, for DELETE and INSERT it uses a WHERE clause to identify exactly which record to DELETE or INSERT.

h. Writes out to the same PostGIS database table as the FeatureReader, and switches the database operation based on the previous AttributeCreators.

i. Cleans up attributes in preparation to write out to a log file.

j. Writes out the Database Operation, Date and which attributes were written to the database in a Microsoft Excel file. This file can be used to monitor changes to the database.

4. Update FeatureReader

In this second workspace, open the FeatureReader parameters and change the Connection to the PostGIS connection you created in the previous workspace. Additionally, click the ellipsis next to Feature Types to Read and select the Tree_Survey in your database. Click OK to close the Feature Reader.

When you update the FeatureReader, ensure that the output port public.Tree_Survey is connected to the Tester_2.

5. Update PostGIS Writer

Next, we will need to update the connection parameters for the PostGIS Writer. In the Navigator window, change the PostGIS connection to your connection the same way as the previous workspace.

6. Test the Workspace

Before we publish this workspace to FME Server, it is a good idea to test it locally. Ensure that Prompt for User Parameters is enabled and then run the workspace.

In the Translation Parameter Values dialog enter the following:

• Tree ID: 59152
• Tree Health: EXCELLENT
• Measure the Diameter in Inches: 52
• Has the Tree Been Inspected: Yes
• Civic Number: 1800
• Type in Street: Beach Av
• Has dead tree been removed?: No
• Is this tree new for this year?: No
• Uncheck: Save As User Parameter Default Values

Once the parameters are entered, click Run. This will trigger an UPDATE in the database. You can inspect the database to confirm this update. As well, you can also view the Microsoft Excel TreeLog, which will include the parameters we just set, the database operation UPDATE and today’s date and time.

If you want to test DELETE and INSERT, rerun the workspace and change the following parameters:

For DELETE:

• Has dead tree been removed?: Yes

For INSERT

• Tree ID: 1482749
• Is this tree new for this year?: Yes

Note: For INSERT, due to the current published parameters, not all of the attributes will have a value such as LATITUDE, LONGITUDE, or CURB. If you would like to add values to these attributes, create a published parameter for each and set them in the AttributeManager_2 in bookmark f.

7. Publish to FME Server

Once you have confirmed that this workspace runs successfully with your database connection, it is time to publish it to FME Server.

Before publishing to FME Server, you need to decide if you want your Excel log file to be available to the user running the Server App. If you do not, you can either leave the Excel file to be updated locally on your machine OR you can set it up to update in the FME Server Resources folder.

To update the file to save it to FME Server, expand the TreeDatabase-LogReport writer in the Navigator window. Then double-click on the Destination Microsoft Excel File parameter to open up the edit dialog. In the edit dialog, click on the drop-down next to Destination Microsoft Excel File, then choose User Parameter > FME Server Parameters > FME_SHAREDRESOURCE_DATA. Then after $(FME_SHAREDRESOURCE_DATA) type in \ServerApps\TreeDatabase-LogReport.xlsx

$(FME_SHAREDRESOURCE_DATA)\ServerApps\TreeDatabase-LogReport.xlsx

Click OK. Save the workspace, and then click on the Publish icon in the top toolbar.

In the Publish to FME Server dialog, create an FME Server connection if you do not already have one. Then ensure this workspace is saved to a Repository called ServerApps. Be sure to upload your PostGIS connection. Then register with the Job Submitter service if you want your log file private and/or the Data Download service if you want the log file to be public. The Data Download service will download the log Excel file every time the app is run.

Part 2: FME Server

With the workspace published, we can create an FME Server Workspace App. A Workspace App allows users to run an FME Server workspace without logging in. It also allows the user to run the workspace on their mobile phone. For more information on FME Server Apps, please see the article Getting Started with FME Server Apps.

1. Log into FME Server

Log into FME Server with an account that has privileges to create FME Server Apps. Once logged in, expand the Sever Apps section then select Build Workspace App.

2. Create FME Server Workspace App

On the Create Workspace App page, give your app a name and short description. Then select the Repository and Workspace.

Choose which Service you want to use. Remember if you want your log file public, use the Data Download service, if you want it private, use the Job Submitter.

Next, check the Expiration date. By default, apps are set to expire in 10 years. It might be a good idea to set this expiration date to a shorter time. This expiration time can be changed at any time.

Now expand the Parameters section, and double-check the defaults for the app. If your parameters have values next to them, click on the Reset button. Setting all the values to blank will ensure that the user doesn’t miss a parameter when they enter their own values. Also, ensure that all of the desired parameters are shown in the app, which is indicated by the green checkmark next to each parameter.

Finally, you can expand the Customize Appearance section and add color, text, or images to your final app page.

Once you are satisfied with your app, click OK. You will get a URL that links to your app.

3. Test App

Click on the URL that was provided at the end of the app setup.

Note: To share the FME Server App with anyone, the FME Server must be accessible externally, not internally using localhost.

If you navigated away from the page with the URL, you can access it again in the Manage Workspace Apps section. 

To test the Workspace App, enter the following parameters:

• Tree ID: 113783
• Tree Health: POOR
• Measure the Diameter in Inches: 10
• Has the Tree Been Inspected: Yes
• Civic Number: 784
• Type in Street: Thurlow St
• Has dead tree been removed?: No
• Is this tree new for this year?: No

You can also change the parameters to trigger a DELETE or INSERT.

Data Attribution

The data shown here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.