span8
span4
span8
span4
In this example,theSQLExecutortransformer 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.
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.
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 anAddressfield.This field will be used to match to the PostalAddress table'sPostAddressfield.
FireHall.gml viewed with Data Inspector
PostalAddressTable
Inspect the source PostalAddress table in Data Inspector by using the foll
owing parameters after setting the Dataset toEmbed Connection Parameters:
Host:postgis.train.亚搏在线safe.com
Port: 5432
Database: fmedata
Username: fmedata
Password: fmedata
Set Schemas for Table Listing tofmedata2015,then pick the table PostalAddress.
ThePostalAddressfield which will be used to match to the Fire Hall GML'sAddressfield.
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.
The followingSQL Statementwill 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 thequote charactersaround 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 theNameattribute from the Fire Halls GML feature to OwnerName.
4.Examine the Output In Data Inspector
SQLExecutor results in Data Inspector.
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.
Performing native spatial queries on database tables using the SQLExecutor
Using the SQLExecutor or SQLCreator to issue commands to a database
Executing a Stored Procedure on Microsoft SQL Server with FME
Performing spatial queries on database tables using the FeatureReader
Splitting SQL statements using the FME_SQL_DELIMITER directive
The SQLCreator and SQLExecutor Transformers
Writing custom data types to an Oracle table
© 2019 亚搏在线Safe Software Inc |Legal