Using the SQLExecutor to do a SQL Join

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2017.x

Introduction

In this example, the SQLExecutor transformer will be used to join records from a database table that relate to source features from a GML file. We have a dataset of Fire Halls in GML format, and a database table called PostalAddress. Both datasets have a common address field.

FME will be used to read in the Fire Halls, then select matching records in the PostalAddress table by using a SQLExecutor to do a database join based on the common address field.

Note: Though this example focuses on the PostGIS format, it is also applicable to other relational database formats such as Oracle and SQL Server.

 

Step-by-step Instructions

The download sqlexecutor-join-201.fmwt is the complete workspace. If you would like to create the workspace yourself, please download sourcegmldata.zip, and follow the steps below to create it.

sqlexecutorworkspace.jpg

The completed workspace

1. Inspect Source Data

FireHalls GML

Add a GML Reader and set it to read the FireHalls.gml. Use the Data Inspector to inspect FireHalls.gml. Use the Table View window to view the text records. Notice that each Fire Hall facility has an Address field. This field will be used to match to the PostalAddress table's PostAddress field.

gml-source-2.jpg

FireHall.gml viewed with Data Inspector

PostalAddress Table

Inspect the source PostalAddress table in Data Inspector by using the foll

owing parameters after setting the Dataset to Embed Connection Parameters:

Host: postgis.train.safe.com

Port: 5432

Database: fmedata

Username: fmedata

Password: fmedata

Set Schemas for Table Listing to fmedata2015, then pick the table PostalAddress.

The PostalAddress field which will be used to match to the Fire Hall GML's Address field.

postaddresstableview.jpg

PostalAddress table viewed with Data Inspector

2. Set up the SQLExecutor

The SQLExecutor will be used to match the address record from the PostalAddress table in the database for each GML Fire Hall facility.

sqlexcecutortransformer.jpg

sqlexecutorparameters.jpg

The following SQL Statement will be used to join the GML record to the database record:

select * from fmedata2015."PostalAddress" where "PostalAddress" ='@Value(Address)' 

The SQL statement is most easily created by using the SQL Editor tool. Be sure to include the quote characters around the final @Value() part!

Use the Atributes to Expose parameter to make any attributes added by the SQL join visible in workbench, if you want.

3. Copy Attribute Values

An AttributeManager transformer may be used to copy or rename attributes. For example, rename the Name attribute from the Fire Halls GML feature to OwnerName.

4. Examine the Output In Data Inspector

SQLExecutor results in Data Inspector.

sqlexecutorresults.jpg

Results of the SQLExecutor JOIN viewed in Data Inspector

5. Edit the SQL Statement

The SQLExecutor has merged the data from a SQL SELECT statement onto the Firehall features. You can also undertake a SQL join with the SQL statement.

The Firehall data is missing the CivicNo attribute. This is in the AddressPoints table. Use the AddressID to join the the CivicNo attribute onto the Filehall records. You could either use a second SQLExecutor, or use a SQL join. Something like:

SELECT a.* , b."CivicNo"  FROM  fmedata2015."PostalAddress" a, "fmedata2015"."AddressPoints" b WHERE "a"."PostalAddress" = '@Value(Address)'  AND a."AddressId" = b."AddressId";

Whenever possible "let the database do the work"! Do as much as you can in a single SQL call.

 

 

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.