Database Oracle Pathway Training 2011

FME Desktop ® Database (Oracle) Pathway Training

FME 2011 Edition

Safe Software Inc. makes no warranty either expressed or implied, including, but not limited to, any implied warranties of merchantability or fitness for a particular purpose r egarding these materials, and makes such materials available solely on an “as-is” basis. In no event shall Safe Software Inc. be liable to anyone for special, collateral, incidental, or consequential damages in connection with or arising out of purchase or use of these materials. The sole and exclusive liability of Safe Software Inc., regardless of the form or action, shall not exceed the purchase price of the materials described herein. This manual describes the functionality and use of the software at the time of publication. The software described herein, and the descriptions themselves, are subject to change without notice.

Copyright © 1994 – 2011 Safe Software Inc. All rights are reserved.

Revisions Every effort has been made to ensure the accuracy of this document. Safe Software Inc. regrets any errors and omissions that may occur and would appreciate being informed of any errors found. Safe Software Inc. will correct any such errors and omissions in a subsequent version, as feasible. Please contact us at: Safe Software Inc. Suite 2017, 7445 – 132 nd Street Surrey, BC Canada V3W1J8 www.safe.com Safe Software Inc. assumes no responsibility for any errors in this document or their consequences, and reserves the right to make improvements and changes to this document without notice.

Trademarks FME is a r egistered trademark trademark of Safe Software Inc. All brand or product names mentioned mentioned herein may be trademarks or registered trademarks of their respective holders and should be noted as such.

Documentation Information Document Name: FME Desktop Database (Oracle) Pathway Training Manual Version: FME 2011 Updated: June 2011

Safe Software Inc. makes no warranty either expressed or implied, including, but not limited to, any implied warranties of merchantability or fitness for a particular purpose r egarding these materials, and makes such materials available solely on an “as-is” basis. In no event shall Safe Software Inc. be liable to anyone for special, collateral, incidental, or consequential damages in connection with or arising out of purchase or use of these materials. The sole and exclusive liability of Safe Software Inc., regardless of the form or action, shall not exceed the purchase price of the materials described herein. This manual describes the functionality and use of the software at the time of publication. The software described herein, and the descriptions themselves, are subject to change without notice.

Copyright © 1994 – 2011 Safe Software Inc. All rights are reserved.

Revisions Every effort has been made to ensure the accuracy of this document. Safe Software Inc. regrets any errors and omissions that may occur and would appreciate being informed of any errors found. Safe Software Inc. will correct any such errors and omissions in a subsequent version, as feasible. Please contact us at: Safe Software Inc. Suite 2017, 7445 – 132 nd Street Surrey, BC Canada V3W1J8 www.safe.com Safe Software Inc. assumes no responsibility for any errors in this document or their consequences, and reserves the right to make improvements and changes to this document without notice.

Trademarks FME is a r egistered trademark trademark of Safe Software Inc. All brand or product names mentioned mentioned herein may be trademarks or registered trademarks of their respective holders and should be noted as such.

Documentation Information Document Name: FME Desktop Database (Oracle) Pathway Training Manual Version: FME 2011 Updated: June 2011

FME Desktop Database Training Manual

Introduction ....................................................................................................................................... 5 Database Database Pathway Pathway ........................... ......................................... ............................ ............................ ............................ ............................ ............................ ............................ .................... ...... 5 FME Version Version ........................... ......................................... ............................ ............................ ............................ ............................ ............................ ............................ ............................ ................ 5 Sample Data ........................... ......................................... ............................ ............................ ............................ ............................ ............................ ............................ ............................ ................ 5 Supported Database ................................................................................................................................ 5

Connecting to a Spatial Database .................................................................................................... 6 Basic Connection Parameters ................................................................................................................. 6 Connecting to Oracle ............................................................................................................................... 6

Updating Features .................................. ................. .................................... .................................... .................................... ................................... ................................... ................... 9 Database Format Attributes ..................................................................................................................... 9

Time and Date Attributes in Spatial Databases ............................................................................. 19 Formatting Date Attributes with Transformers ....................................................................................... 19

Creative Feature Reading .............................................................................................................. 20 Where Clause ........................... ......................................... ............................ ............................ ............................ ............................ ............................ ............................ ......................... ........... 20 Search Envelope......... Envelope....................... ............................ ............................ ............................ ............................ ............................ ............................ ............................ ......................... ........... 21 “Records to Fetch Fetch at a Time” Parameter..................... .......... ........... ..................... .......... ........... ........... ..................... .......... ........... ..................... .......... ........... . 21 Concatenated Parameters ..................................................................................................................... 23 FeatureRead FeatureReader er .......................... ........................................ ............................ ............................ ............................ ............................ ............................ ............................ ......................... ........... 25

Coordinate System Granularity in Spatial Databases .................................................................... 28 Supported Supported Formats Formats .......................... ........................................ ............................ ............................ ............................ ............................ ............................ ............................ .................. .... 28

Multiple Geometries ....................................................................................................................... 29 Multiple Geometry Writing ...................................................................................................................... 29 Multiple Geometry Reading ................................................................................................................... 30

Database Transformers Transforme rs................................... ................... ................................... .................................... .................................... ................................... ........................ ........ 38 SQLExecutor SQLExecutor ............................ .......................................... ............................ ............................ ............................ ............................ ............................ ............................ ......................... ........... 38 SQLCreator SQLCreator........................... ......................................... ............................ ............................ ............................ ........................... ........................... ............................ ............................ ................ .. 38

Transactions ................................................................................................................................... 42 Transaction Interval ............................................................................................................................... 42 Start Transaction Transaction At ............................ .......................................... ............................ ............................ ........................... ........................... ............................ ............................ ................ .. 42

Session Review ................................... ................... .................................... .................................... ................................... .................................... .................................... ................... 48 What You Should Have Learned from this Session ............................................................................... 48

Introduction

Page 3


Page 4

Introduction


Introduction

This training material is part of the FME Training Pathway system.

Database Pathway

This training material is part of the FME Training Database Pathway. It contains advanced content and assumes that the user is familiar with all of the concepts and practices covered by the FME Database Pathway Tutorial, and the FME Desktop Basic Training Course. FME Version

This training material is designed specifically for use with FME2011. You may not have some of the functionality described if you use an older version of FME. Sample Data

This sample data required to carry out the examples and exercises in this document can be obtained from: www.safe.com/fmedata

Supported Database

For the purposes of simplicity, this training includes documented steps for Oracle Spatial 10g only. In particular it relates to Oracle XE. Other Oracle products may differ slightly in their use.

Introduction

Page 5


Connecting to a Spatial Database

Connecting to the database is the one step all FME operations must perform.

Connecting to a database is slightly different to selecting a file for a file/folder-based format. The operation relies much more on format specific parameters. Basic Connection Parameters

The basic connection parameters are: • •

• • •

Host Name Database Name Username Password Network Port Number

These parameters may differ slightly for each format, but will always be found in any Reader/Writer dialog by clicking on the “Parameters…” button. Connecting to Oracle

Connecting to Oracle requires a client to be installed. Connection is possible through either tnsnames.ora or a direct connection. tnsnames.ora

tnsnames.ora is a file that usually resides in the Oracle client installation folder. It is a text file that consists of a number of service definitions of the form: = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = <1521>)) ) (CONNECT_DATA = (SERVICE_NAME = ) ) )

Oracle Direct Connection

Direct Connection is when a single string is supplied that includes all the parameters required to connect to the database. The connection string is of the form: user/password@//hostname:port/sid For example: training/training@//myComputerName:1521/myComputerID Although you might be able to use simply: training/training@myComputerID

Page 6



Example 1: Connection Parameters Scenario

FME user; City of Interopolis, Planning D epartment

Data

City Parks (MapInfo TAB)

Overall Goal

Test connection parameters by writing City Parks data to Oracle

Demonstrates

Connection parameters and database writing

Finished Workspace

C:\FMEData\Workspaces\PathwayManuals\DatabaseOracle1-Complete.fmw

Follow these steps to test the connection to your Oracle database: 1) Open Database Home Page Select Start > Programs > Oracle Database > Go To Database Home Page

NB: The Home Page is unique to the XE version of Oracle For non-XE users, use SQLPlus with training/training@hostname

2) Enter Authentication Details

When the database home page opens, it will prompt for a login. Enter your authentication details, and then either click Login or press the Return key. In FME training, the authentication parameters are usually: Username Password

training training

Thisauthenticationprocessisreallyjustatestoftheusernameandpassword. SuccessatlogginginheredoesnotguaranteeasuccessfulconnectionfromFME. Thebesttestistouseatnsoptioninathird-partyapplicationlikeSQLDeveloper.

3) Browse for Database

In the Home window, click the Object Browser button to see a list of tables in the database. Depending on whether the database has been used previously, you may or may not see a list of existing tables.


Page 7

FME Desktop Database Training Manual 4) Start FME Workbench

Start FME Workbench, and open the Generate Workspace dialog. Set up a translation as follows: Reader Format Reader Dataset

MapInfo TAB (MFAL)

Writer Format

Oracle Spatial Object

C:\FMEData\Data\Parks\city_parks.tab

Click the writer parameters button. In the parameters dialog enter the database connection parameters. In FME training this will usually be: Service Username Password

XE training training

Click OK, and then OK again, to create the workspace. 5) Run Workspace

Run the workspace. The foot of the log file will report success as follows: STATS |Final Output Nuker(TeeFactory): Cloned 0 input feature(s) into 0 output feature(s) INFORM|Executing SQL statement `CREATE INDEX CITY_PARKS_SG23 ON CITY_PARKS(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX' to create spatial index on Oracle table STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=STATS | Features Read Summary STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=STATS |city_parks 22 STATS |============================================================================== STATS |Total Features Read 22 STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=STATS | Features Written Summary STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=STATS |CITY_PARKS (CITY_PARKS) 22 STATS |============================================================================== STATS |Total Features Written 22 STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=INFORM|Closing native MapInfo reader INFORM|Translation was SUCCESSFUL with 5 warning(s) (22 feature(s) output)

If the log reports a message like one of these: • •

ORA-01017: invalid username/password; logon denied ORA-12154: TNS:could not resolve the connect identifier specified

…then you should check the connection parameters entered into the dialog and, if necessary, recheck these against the parameters entered into the Oracle Database Home Page.

Page 8



Updating Features

Updating entire tables is simple enough, but updating individual features is a task that requires a little more finesse.

Once information is stored in a database, it’s unlikely it will stay static. Changes will occur. Sometimes the update will involve reloading an entire set of data, totally replacing the existing data. Sometimes the table will also be replaced, and sometimes just emptied and refilled. Feature Type parameters such as ‘Drop Table’ and ‘Truncate Table’ can help with this task. However, most often only certain features need to be updated; the source data may indicate which features require updates, but sometimes it is necessary to go through a process of change detection to decide what to update. Database Format Attributes

To carry out specific actions on specific features, format attributes must be used. You’ll recall that operations on a reader or writer (i.e. at the database level) are carried out using Reader/Writer Parameters, located in the Navigator window. Operations on a feature type (i.e. at the database table level) are carried out using Feature Type Parameters, located both in the Navigator window and Feature Type Properties dialog. However, operations on individual features are carried o ut using Format Attributes. For databases there are two particular format attributes that control updates on individual features.

Updating Features

Page 9

FME Desktop Database Training Manual fme_db_operation

fme_db_operation is a format attribute whose value denotes how a database writer should handle that feature. It may take the value DELETE, INSERT or UPDATE. fme_where

fme_where is a format attribute whose value denotes a match that identifies which database record(s) this feature should update. The structure is usually:

For example a where statement of MyField = 4 says to update features where the database column named “MyField” has a value of 4. To match with the value of an attribute on a feature, the preferred method is to construct the where clause using a StringConcatenator transformer. For example: Concatenator Constant Concatenator Attribute

“MyField = ” MyAttribute

That’s equivalent to saying (for the FME experts) “MyField = @Value(MyAttribute)” If the attribute is a string, then that part has a quote around it, for example MyField = ‘abc’, or: Concatenator Constant Concatenator Attribute Concatenator Constant

“MyField = ‘” MyAttribute “‘”

‘Asanalternativetousingthefme_whereformatattribute,youcanusethe “UpdateKeyColumns”parameteronthewriterfeaturetype.Oneadvantage thereisthatyoucaneasilyselectmultiplecolumns’

Page 10

Updating Features


Example 2: Feature Updates Scenario


Data

Address Data (GeoMedia Access Warehouse)

Overall Goal

Load address data and updates

Demonstrates

Feature-level updates

Finished Workspace


The city has a set of address data, plus a set of updates. This example will show how to load the data and apply the updates, but also highlight some of the pitfalls and problems that can occur during the process. 1) Create Loader Workspace

Start Workbench if necessary and open the Generate Workspace dialog. Set up a translation as follows: Reader Format Reader Dataset

Intergraph GeoMedia Access Warehouse C:\FMEData\Data\Addresses\roadAllowancesAndAddressPoints.mdb

Click the reader parameters button. Click the table list button:

Choose ADDRESS_POINTS as the source table to read.

Writer Format


Click the writer parameters button and enter the database connection parameters as before.

Once you have used them a few times, and are sure the connection parameters are correct, then choose the “Save as My Defaults” option at the foot of the parameters dialog. This will save the parameters and prevent them having to be entered again and again.

Click OK, and OK again, to create the workspace.

Updating Features

Page 11

FME Desktop Database Training Manual 2) Check Parameters

Check that the writer parameter ‘Writer Mode’ is set to INSERT. Also check that the feature type parameters ‘Create Table’ and ‘Drop Existing Table’ are set to yes. 3) Save and Run Workspace

Save the workspace, and then run the workspace. The process will take about 10 seconds to load 12,292 features. Use the FME Universal Viewer to inspect the contents of the newly written table. Notice that the attribute PRIMARYINDEX is a unique ID number for each address. 4) Inspect Updates File

In a text editor open the file C:\FMEData\Data\Addresses\AddressUpdates\AddressUpdates.txt Inspect the contents. This file contains updates to the main address database. It has almost exactly the same schema, but has an additional field called UPDATE_TYPE, which records the type of edit to carry out: [I]nsert this record to the database as a new address [D]elete this record from the database as an old address [U]pdate this record as a changed address

I D U

5) Create Updating Workspace

Back in FME Workbench create a workspace to translate the updates file into Oracle.

Makesureyouhavesavedacopyoftheprevious(loader)workspace,asyouwill needitagainshortly.Infact,thebestoptionisprobablytoopenasecondinstanceof FMEWorkbenchtodevelopthefollowingsection,keepingtheoriginalopen.

Reader Format Reader Dataset Reader Parameters

Comma Separated Value (CSV)

Writer Format


C:\FMEData\Data\Addresses\AddressUpdates\AddressUpdates.txt

File Has Field Names = Yes Lines to Skip (Header) = 1

Check the option to “Import Feature Type Definitions” and click OK.

Page 12

Updating Features

FME Desktop Database Training Manual 6) Select Source of Feature Types

When prompted, set the format and dataset where the feature types can be imported from. By default this should be the Oracle database, so the fields may not nee d much editing:

Click the Parameters button, and use the table selection tool to select the newly created ADDRESS_POINTS table (it will probably be named TRAINING.ADDRESS_POINTS):

Click OK and then OK again. A workspace will be created that looks like this:

7) Assign Operation Type

Different values now need to be set for fme_db_operation in order to carry out the different actions for each value of UPDATE_TYPE. Set up the workspace to assign the values in this table: UPDATE_TYPE

fme_db_operation

I D U

INSERT DELETE UPDATE

There are two methods that might be used: Here the user has chosen to divide the data using an AttributeFilter , with an AttributeCreator to create/set fme_db_operation differently in each case.

This user has chosen to use an AttributeValueMapper transformer, which can both create and set fme_db_operation. However, it’s still important to divide the data up (for the following steps) which is done with an AttributeFilter.

Updating Features

Page 13

FME Desktop Database Training Manual 8) Assign Where Clause

Features of type “U” (Update) and “D” (Delete) need a where clause to define which features to update. Set up a StringConcatenator transformer to create an attribute called fme_where for these types of feature.

The where clause should be where the field (PRIMARYINDEX) matches the value of the attribute (PRIMARYINDEX). In the StringConcatenator it would look like this: The part with quotes around is entered through the Constants field. 9) Create Point Features

Incoming features don’t have geometry – they are just NULL features – but geometry is required in the output. This isn’t a problem where the records are to be DELETED or UPDATED, but features to be INSERTED will need a proper spatial point feature. Add a 2DPointReplacer transformer, to turn the incoming I (Insert) features, into point features. The attributes X_COORD and Y_COORD contain the information required to do this.

10) Set Last Update Field

So it’s possible to recognize the updated data, use an AttributeCreator transformer to create LA_UPD_BY (Last Updated By) and set its value to be a constant such as your own name. Connect the AttributeCreator to the output feature type and your workspace now looks like this:

Page 14

Updating Features

FME Desktop Database Training Manual 11) Check Parameters

The workspace is now complete, but the parameters need attention. At this point the Writer (database) parameters probably look like this:

…and the Feature Type (table) parameters like this:

Obviously we don’t want to either drop or re-create the table, merely update selected records, so set Create Table to No (when you do this, Drop Existing Table is automatically greyed-out). The question now is one of Writer Mode. From the above screenshots you can see there are parameters on both the Writer and the Feature Type. Currently: Writer: Writer Mode Feature Type: Writer Mode

INSERT INHERIT FROM WRITER

We have already defined writer mode (through fme_db_operation) to be a separate action (INSERT, UPDATE, DELETE) for indivdual features. According to the FME Readers and Writers Manual, this will work “unless the parameter at the feature type level is set to INSERT”! The current setup – with feature type mode set (via the writer mode) to INSERT – is not going to give the results we need. We should change the mode to be UPDATE. There are two options here: •

•

Leave the writer:writer mode parameter as-is, and set the feature type:writer mode parameter to UPDATE. Set the writer:writer mode parameter to UPDATE, and leave the feature type:writer mode parameter as-is.

Choose one of these methods to change writer mode to UPDATE. Are there any obvious benefits to choosing one over the other?

Updating Features

Page 15

FME Desktop Database Training Manual 12) Save and Run Workspace

Save the workspace, and then run it. The workspace will take about 50 seconds to complete.

Check the feature counts on the workspace connections, particularly where they emerge from the AttributeFilter :

Notice there are: INSERTS DELETES UPDATES

284 604 577

Two calculations are important: Original Features (12,292) + INSERTS (284) – DELETES (604) = 11972 There should now be 11,972 records in the database. INSERTS (284) + UPDATES (577) = 861 There should be 861 records now tagged with LA_UPD_BY = 13) Inspect Data

In the FME Universal Viewer, open the Oracle ADDRESS_POINTS table. In query mode, drag an envelope around the data to check how many features exist in the dataset: If you neglected to set the writer mode to UPDATE in step 11, then there will be 12,576 features (because no DELETES will have taken place)

But even when you set the writer mode correctly, something odd is going on:

A total of 11,395 features is not correct either; it is 577 short. But is the problem in writing the data, or simply that we are viewing it incorrectly? Page 16

Updating Features

FME Desktop Database Training Manual 14) Query Database

Open up a query tool for the database, and query the number of records in the table using: select count(*) from ADDRESS_POINTS

So there are the correct number (11,972) of records in the database, but apparently not in the FME Viewer. Return to the FME Viewer and choose the toolbar option to “Select No Geometry”: So that is the problem! The UPDATE features have no geometry! But why?

15) Re-Run Loader Workspace

Return to the original loader workspace and re-run it. This will revert the table back to how it was before we started to make updates. 16) Check Update Parameters

Now return to the update workspace. Check the Feature Type parameters. Do you notice any particular parameter that might cause a problem?

Yes! The problem is that UPDATE features are getting their geometry updated too, and we don’t necessarily want this. Set this parameter to No and re-run the workspace.

Updating Features

Page 17


17) Inspect Data

Now repeat the same query in the FME Universal Viewer (don’t forget to reopen the data!)

Yes! This time there are 11,972 features.

Advanced Tasks

Can you use the FME Viewer to check whether there are now 861 records tagged with your name as the LA_UPD_BY? Can you use a transformer to set a value for LA_UPD_DAT as the update date/time? What would happen if the xcoord or ycoord attributes were the part that had changed in a U (Updated) record? How could you fix this?

Lessons to Learn!

Besides how to apply updates to a database, there are two important lessons to take from this exercise: •

•

Always check your writer parameters very carefully. Then check them again. And again. Don’t assume because the translation was successful, that the data was written correctly! Otherwise you would have stopped at step 12 and not known about the UPDATES problem. Always inspect the output to make sure it is what you were expecting.

Page 18

Updating Features


Time and Date Attributes in Spatial Databases

Time and Date Attributes are among the more tricky to get into, and out of, a database.

Time and date attributes are complicated territory because each different database format may have its own unique structure for dates. Oracle

Oracle expects DATE values in the format YYYYMMDDHHMMSS even though when you display a date field from an Oracle table it shows something like this: 01-JAN-08 12:00:00 Microsoft SQL Server

DateTime fields represent date and time data from January 1, 1753 to December 31. For example, a value of 20061231235959 represents 11:59:59PM on December 31, 2006. When writing to the database, the writer expects the date attribute to be in the form YYYYMMDDHHMMSS SmallDateTime fields represent date and time data from January 1, 1900 to June 6, 2079. For example, a value of 20060101101000 represents 10:10:00AM on January 1, 2006. When writing to the database, the writer expects the date attribute to be in the form YYYYMMDDHHMMSS. IBM Informix

The Informix reader returns two attributes for each DATE field. The first attribute has the name of the database column, and the form YYYYMMDD. The second attribute has a suffix of .full and is of the form YYYYMMDDHHMMSS. For example, if the date field is called UPDATE_DATE, the attributes are UPDATE_DATE and UPDATE_DATE.full The Informix writer looks for both attributes when a DATE or DATETIME column is being output. Either may be specified. If both attributes are specified, then .full takes precedence. IBM DB2

As per the IBM Informix reader/writer except it supports DATE, TIME and TIMESTAMP types. ESRI ArcSDE

As per the IBM Informix reader/writer Formatting Date Attributes with Transformers

To write dates to a database DATE or DATETIME field you can use the TimeStamper or DateFormatter transformer to get the date into the correct format. A format string of ^Y^m^d^H^M^S will return a date-time in the form YYYYMMDDHHMMSS A format string of ^Y^m^d will return a date in the form YYYYMMDD

Time and Date Attributes in Spatial Databases

Page 19


Creative Feature Reading

Rather than a plain reader, there are some quite creative ways by which database features can be read using Workbench.

Using FME to read from a database should be carefully planned and considered. Frequently not every feature in every table is required, and yet that is what a user might be doing. The fewer features that are read from the database, the quicker the read will be, the less system resources are used, and the faster the overall translation will be. Chef Bimm says…

‘Thinkofadatabaselikearestaurant.Asensiblepersonwouldbrowse themenu,andorderjustthedishesthattheywant.Afoolishperson wouldordereverythingandwastethefoodtheydidn’treallyneed. Likearestaurant,it’sveryexpensiveandtime-consumingtoorderall datafromadatabasejusttodiscardmostofit.Farbettertoorderonly thedatayouintendtoconsumeinthecurrentsession!’ There are a number of items of functionality that can improve the performance of database reading in this way: Where Clause Search Envelope “Records to Fetch at a Time” parameter Concatenated Parameters FeatureReader

• • • • •

Where Clause

Most database readers will have a “where clause” parameter. Here a query can be set, so that only features that pass the query will be returned to FME. This employs database query tools – which in turn make use of database indices – and is a lot more efficient than reading an entire table and then filtering it with a Tester transformer.

Page 20


FME Desktop Database Training Manual Search Envelope

Similar to a where clause, “search envelope” parameters set a spatial query; only features that fall inside the specified extents will be returned to FME. Again, this employs native database functionality, and is more efficient than reading the entire table and then clipping it with a Clipper transformer.

An optional “Clip to Search Envelope” parameter defines whether features will be clipped where they cross the defined extents, or be allowed to pass completely where at least a part of them falls inside the extents. Of course the limitation here is that the four parameters only define a rectangular envelope.

For convenience, each reader also has a parameter called “Search Envelope Coordinate System”. This allows the search envelope coordinates to be provided in a different coordinate system to that of the actual data. For example, the coordinates could be provided in Lat/Long format (degrees) to use as a search envelope for data stored in State Plane (metres/feet).

Touseasearchenveloperequiresaspatialindextoexistforthattable;FMEcan’t doaspatialquerywithoutone.Ifaformatdoesn’tsupportaspatialindexbeing createdonaview,thenFMEwillnotbeabletodospatialqueriesonthatview.

“Records to Fetch at a Time” Parameter

This parameter defines how many records will be fetched at a time from the database. Tweaking it allows the user to tune the performance of the reader. Too low a number and FME will spend excessive time making read requests. Too high a number and database performance might be slowed down for other users. NB: In an Oracle reader this parameter is called “Rows to Read at a Time”


Page 21


Example 3: Where Clause Scenario


Data

Oracle Spatial Object (Address Point Data)

Overall Goal

Read address data with a query

Demonstrates

Where Clause parameters

Finished Workspace


To test the previous example, it’s time to read back some data – but only for a specific zipcode. 1) Start FME Workbench

Start Workbench if necessary and begin with an empty workspace. 2) Add Reader

Add a Reader using Readers > Add Reader. Set it up as follows: Reader Format


Click the reader parameters button and enter the database connection parameters as before. Click the Table List button and select the ADDRESS_POINTS table. Click OK, and OK again to close the dialogs and add the new reader. 3) Add Visualizer

Connect a Visualizer transformer to the reader feature type. 4) Save and Run Workspace

Save and run the workspace. Note how many features were read and how long it took. 5) Set Where Clause

Locate the Where Clause parameter in the Navigator Window, and double-click it. In the Edit Parameter box, enter: ZIPCODE=78723

6) Re-run Workspace

Re-run the workspace. Note how many features were read now and compare how long it took.

Page 22


FME Desktop Database Training Manual Concatenated Parameters

The problem with the where clause – 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 ( = ), when often only the part is required as input. This is where a concatenated parameter comes in. It is a parameter that is built of a constant string (the part) and a user-defined value (the part). NB: Scripted Parameters are a similar tool, but would be used in more scenarios where the value needs more complex processing that requires using a Python or Tcl script. For example, a scripted parameter would be useful for manipulating the search envelope parameter values.

Example 4: Concatenated Parameter Scenario


Data

Oracle Spatial Object (Address Point Data)

Overall Goal

Read address data with a user-defined query

Demonstrates

Concatenated parameters for a database

Starting Workspace

C:\FMEData\Workspaces\PathwayManuals\DatabaseOracle4-Begin.fmw

Finished Workspace


Continue on from the previous example. 1) Start FME Workbench

Start Workbench if necessary and open the workspace from the previous example. 2) Add Parameter

Right-click on “Parameters” in the Navigator window. Choose Add Parameter.

When prompted, choose a parameter of type text. Set the name to UserZipCode and the prompt to ZipCode:


Page 23

FME Desktop Database Training Manual 3) Add Parameter

Right-click the Oracle Reader Where Clause parameter and choose Create Parameter. Leave the type as Text, and set the Where clause default value to: ZIPCODE=$(UserZipCode) Turn off the Published flag, so the user is not prompted to set this concatenated parameter.

4) Run Workspace

Run the workspace using prompt and run. When prompted, enter a zipcode into the field provided. Valid values are:

• • • • • • •

78722 78723 78724 78751 78752 78753 78754

Each different zipcode will change the database where clause and return a different set of data. Advanced Task

If you have a little time, recreate the UserZipCode parameter to be a choice, rather than a text parameter. The choices should reflect the above list of valid zipcodes for Interopolis. But if you did have to find the list of unique zipcodes in the data, how would you do it?!

Mr. Saif-Investor says…

‘AWHEREclauseonthereaderappliesthesamequerytoALLtables beingread.Thiscanbeuseful,butalsoinefficient. BeawarethatthereisalsoasimilarWHEREclauseparameteroneach featuretype,sodatacanbequeriedonlyonindividualtables.’

Page 24


FME Desktop Database Training Manual FeatureReader The FeatureReader transformer is one that acts – as the name suggests – as a reader in itself.

It will read data, from a database, and use incoming features as the basis for a spatial or nonspatial query. Incoming features are known as triggers. Each causes a single query to be carried out through a reader. The query can use the geometry of the incoming feature as a base against which to test a spatial predicate, and the reader returns one or more features as the result of the query. For example, an incoming line feature (maybe a road) can be used to define the base for an intersection query against linear database features such as rivers or rail. Or a polygon feature could be used as a boundary within which to select point features. NB: This transformer is designed to replace both the OracleQuerier and ArcSDEQuerier.

Example 5: FeatureReader Scenario


Data

Census Data (ESRI Shape) Address Point Data (Oracle Spatial Object)

Overall Goal

Read address data within a user-defined census area

Demonstrates

FeatureReader Transformer

Finished Workspace


1) Start FME Workbench


Add a reader (using Readers > Add Reader) to read the following dataset: Reader Format Reader Dataset

ESRI Shape C:\FMEData\Data\GovtBoundaries\TravisCounty\CensusTracts.shp

3) Add Tester Add a Tester transformer connected to the Shape dataset. Open the Parameters dialog for the Tester .

Set the Tester as follows: Left Value Operator

TRACTCE00 =


(Select attribute)

Page 25

FME Desktop Database Training Manual 4) Publish Parameter

Right-Click in the Right Value field, and choose the option Create Parameter.

When prompted, create a new parameter: Type Name Published Optional Prompt Configuration Default Value

Choice CensusTract Yes No Census Tract: 1811, 1812, 1813 1811

Click OK.

5) Add Reprojector

The Shape data is in a Lat/Long coordinate system, but the database data is in TX83-CF. Therefore the Shape data must be reprojected to match the database. Add a Reprojector transformer connected to the Tester:PASSED port. Open the properties dialog. Set: Source Coordinate System Destination Coordinate System

Read from feature TX83-CF

6) Add FeatureReader

Add a FeatureReader transformer connected to the Reprojector:REPROJECTED port.

Page 26


FME Desktop Database Training Manual 7) Set Parameters

Open the parameters wizard and set the parameters as follows: Panel 1: Reader Format


Click the reader parameters button and enter the database connection parameters as before. Click the Table List button and select the ADDRESS_POINTS table. Panel 2: Feature Types Where Clause

Query the Feature Types specified on the previous page

AnicefeaturehereisthattheFeatureTypes(tables)toreadcanbeobtainedfrom thecontentsofanattribute.Forexample,youcouldgetthelistoftablesfroma publishedparameter,andextractthatintoanattributeforusehere.

Panel 3: Spatial Test

ORACLE:INSIDE

Notethatthefirstsetofpredicates(thoseprefixedbyORACLE:)willbeprocessed usingaquerytotheunderlyingdatabase.Thosewithouttheprefixareinternal predicatesthatuseFMEfunctionality.

Panel 4: Attribute Handling Geometry Handling

Result Attributes Only Result Geometry Only

8) Add Visualizers

Add Visualizers to the FeatureReader output ports. 9) Run Workspace

Run the workspace using File > Prompt and Run. When prompted, select a census tract of your choice. The workspace will read addresses from the Oracle database, only where they fall inside the chosen census tract. The data for tract 001811 will look like this:


Page 27


Coordinate System Granularity in Spatial Databases

Granularity refers to the level at which different features can be written to different coordinate systems

In FME2010 or older, all data sent to a writer would be written in the same coordinate system. This means that different tables would be forced into the same coordinate system, if they were part of the same writer. FME2011 improves on this by allowing different tables to have different coordinate systems. Old Behaviour

The old behaviour was for the first feature to enter the writer to set the coordinate system for all subsequent features, regardless of which table they were written to. New Behaviour

The new behaviour is that the first feature to be written to a table sets the coordinate system for all subsequent features in that table. Therefore each table may have a different coordinate system. Supported Formats

This functionality is supported in the following database formats: • •

• • •

Geodatabase and SDE SQL Server Informix Teradata IBM DB2

It is not (yet) supported in:

• • •

Oracle GeoMedia SQL Server Warehouse GeoMedia Access Warehouse

Page 28

Databases

/Coordinate System Granularity in Spatial


Multiple Geometries

Multiple Geometries are permitted where supported by the database, usually in the form of multiple geometry columns per table

Most databases include the ability to have multiple geometry columns per table, and FME does too. However, the table must exist beforehand – FME cannot create multiple geometry tables. Multiple Geometry Writing

There are two multiple-geometry writing scenarios: • •

Reading AND Writing multiple geometries Reading single geometry features and converting them to multiple geometries

In a Multiple -> Multiple translation, the writing is handled automatically. However, when converting single geometries to multiple, the key is in how to identify two features that are related, and how to assign each of them to the appropriate geometry column. The functionality used to do this involves geometry names and aggregates. Because FME doesn’t (*yet*) support multiple geometries within Workbench, the setup for each database record to be written is a little contrived. It will be composed of two or more features, each of which contributes its geometry to the final record. A geometry name is applied to each feature (with a GeometryNameSetter ) and this identifies the geometry column(s) to write to. The features are grouped together as an aggregate - usually with an Aggregator transformer – and this identifies which features form a particular database record. An IndividualGeometrySetter transformer is used to tell the writer to treat each feature in the aggregate as a different geometry for the same record.

Multiple Geometries

Page 29

FME Desktop Database Training Manual Multiple Geometry Reading

Similar to writing, multiple geometry reading involves aggregates.

Each multiple geometry feature that FME reads is an aggregate. The Deaggregator transformer can be used to split up the record into individual geometries, and an attribute (_geometry_name) used to determine which geometry came from which column.

ThedefaultFMEbehaviouristoreadandwritesinglecolumnsonly.Multiplecolumn behaviourisonlyactivatedbyaHandleMultipleSpatialColumnsparameter.

Page 30

Multiple Geometries


Example 6: Multiple Geometry Writing Scenario


Data

City Parks (MapInfo TAB, Oracle Spatial Object)

Overall Goal

Write to multiple geometry columns

Demonstrates

Multiple Geometry Writing

Finished Workspace


In this example, FME will be used to create multiple geometries where the two geometries are different representations (point and polygon) of the same park objects. A workspace will then be created to read the data back, and filter it (either points or polygons) depending on the scale required for the output. 1) Start FME Workbench

Start FME Workbench, and open the Generate Workspace dialog. Set up a translation as follows: Reader Format Reader Dataset

MapInfo TAB (MFAL)

Writer Format


C:\FMEData\Data\Parks\city_parks.tab

Click the writer parameters button. In the parameters dialog enter the database connection parameters. Click OK, and then OK again, to create the workspace. 2) Create Table

FME isn’t (yet) able to create tables with multiple geometry columns. You may either use an existing table with multiple geometry columns, or can use a script within the FME translation to create one. The latter method – an FME-executed SQL script – is how this exercise will create its multiple geometry table. The goals of the script are to: • • • •

Check if the table already exists and, if so, drop (delete) it Delete any existing metadata records for the table Create the new table Insert new metadata records for the table

Multiple Geometries

Page 31

FME Desktop Database Training Manual Locate and double-click the writer parameter ‘SQL Statement to Execute Before Translation’. When prompted, enter the following script (or copy it from a text file in the resources folder): FME_SQL_DELIMITER ! DECLARE cnt NUMBER; BEGIN SELECT COUNT(*) INTO cnt FROM ALL_TABLES WHERE TABLE_NAME = 'CITY_PARKS' AND OWNER = 'TRAINING'; IF ( cnt > 0 ) THEN EXECUTE IMMEDIATE 'DROP TABLE CITY_PARKS'; EXECUTE IMMEDIATE 'DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME=''CITY_PARKS'' '; END IF; END; ! CREATE table CITY_PARKS ( "PARK_ID" INTEGER, "NAME" CHAR(64), "NAME_ALT" CHAR(64), "PARKS_POINTS" SDO_GEOMETRY, "PARKS_POLYGONS" SDO_GEOMETRY )! INSERT into USER_SDO_GEOM_METADATA (table_name,column_name,diminfo,srid) VALUES ('CITY_PARKS','PARKS_POINTS', sdo_dim_array(sdo_dim_element('X',3000000,10000000,0.005),sdo_dim_element('Y',32 00000,10100000,0.005)), 2277)! INSERT into USER_SDO_GEOM_METADATA (table_name,column_name,diminfo,srid) VALUES ('CITY_PARKS','PARKS_POLYGONS', sdo_dim_array(sdo_dim_element('X',3000000,10000000,0.005),sdo_dim_element('Y',32 00000,10100000,0.005)), 2277)!

Notes:

The script is divided into four different sections: o An FME keyword to define the SQL delimiter o A PL/SQL script to drop an existing table and delete its metadata o A SQL command to create the CITY_PARKS table o Two insert statements to create metadata •

•

•

•

The FME_SQL_DELIMITER keyword specifies a character to separate multiple SQL statements. Usually you would use a semi-colon (;) but here we have a section of PL/SQL that already uses semi-colon delimiters within itself, and so this script uses the exclamation mark (!) instead. The PL/SQL section is used in order to check if the table exists before dropping it. It is equivalent to – though not as elegant as – the SQL Server “IF EXISTS” function. In the line that deletes existing metadata records, CITY_PARKS is bracketed by two single quote characters (’’CITY_PARKS’’). This is a requirement of the FME parser. There are two lines to create metadata (one for each geometry column). Without these records FME would not be able to find the table to read it back!

Commits are performed automatically, and do not need to be included. Page 32

Multiple Geometries

FME Desktop Database Training Manual Alternate Script:

An alternative to the above script is as follows: FME_SQL_DELIMITER ; -DROP TABLE CITY_PARKS; -DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME='CITY_PARKS'; CREATE table CITY_PARKS ( "PARK_ID" INTEGER, "NAME" CHAR(64), "NAME_ALT" CHAR(64), "PARKS_POINTS" SDO_GEOMETRY, "PARKS_POLYGONS" SDO_GEOMETRY ); INSERT into USER_SDO_GEOM_METADATA (table_name,column_name,diminfo,srid) VALUES ('CITY_PARKS','PARKS_POINTS', sdo_dim_array(sdo_dim_element('X',3000000,10000000,0.005),sdo_dim_element('Y',32 00000,10100000,0.005)), 2277); INSERT into USER_SDO_GEOM_METADATA (table_name,column_name,diminfo,srid) VALUES ('CITY_PARKS','PARKS_POLYGONS', sdo_dim_array(sdo_dim_element('X',3000000,10000000,0.005),sdo_dim_element('Y',32 00000,10100000,0.005)), 2277);

Notes:

The difference here is, rather than trap errors in the script, a specific FME device is used to ignore them. Notice the hyphen character that precedes the DROP and DELETE commands. This prompts FME to ignore any errors from these commands, such as would occur when trying to drop a table that does not exist. Pros Cons

A shorter, simpler script Non-standard SQL

3) Edit Schema

Notice that the new table (as defined in the script) has a c olumn PARK_ID which is not defined in the workspace. Edit the destination feature type and add the new field. Remove the unnecessary fields ROTATION, HEIGHT and TEXTSTRING

Multiple Geometries

Page 33

FME Desktop Database Training Manual 4) Add Counter

To create a park ID, add a Counter transformer. Open the properties dialog and make Count Output Attribute write to PARK_ID Change the Count Start parameter to start at 1 (instead of 0)

5) Add CenterPointReplacer

Now to create the multiple geometries: The park features are originally polygons; to create points insert a CenterPointReplacer transformer, connected to a second output stream from the Counter. The transformer has no parameters (except name) to worry about. The workspace will now look something like this:

6) Add GeometryNameSetter

Now there are two sets of geometries, and they must be given different names. The names should match the geometry columns names in the database: PARKS_POINTS and PARKS_POLYGONS Place two GeometryNameSetter transformers and use them to name the two sets of geometry.

Page 34

Multiple Geometries

FME Desktop Database Training Manual 7) Add Aggregator

The next step is to identify the matching features. This is done with an Aggregator transformer. Place an Aggregator transformer and connect both streams of data to it. In the parameters, set group_by to use park_id ; this is how the two sets of features are paired off and aggregated together.

8) Add IndividualGeometriesSetter

Add an IndividualGeometriesSetter transformer. This little-known transformer tags the aggregate features as actually being multiple individual geometries. The parameter Set to Contain Individual Geometries, should be set to Yes.

At this point you should also make sure that the attributes on the output are properly mapped. Because there is a different in case, they may have become disconnected!

9) Set Multiple Geometry Parameter

In the Navigator window, locate the writer parameter Handle Multiple Spatial Columns, and set it to Yes.

10) Set Create Table to No

As a final task, open the writer Feature Type properties, and set the parameter “Create Table” to No.

11) Save and Run Workspace

Save the workspace and then run it. The City Park features will be written to Oracle with multiple geometries.

Multiple Geometries

Page 35


Example 7: Multiple Geometry Reading Scenario


Data

City Parks (Oracle Spatial Object, KML)

Overall Goal

Read and filter multiple geometry columns

Demonstrates

Multiple Geometry Reading

Finished Workspace


In this example, FME will be used to read the previous data back, and filter it (either points or polygons) depending on the scale required for the output. NB: Workbench will be used to read the data back, as the FME Universal Viewer does not yet support the selection of geometry column.

1) Start FME Workbench

Start FME Workbench, and open the Generate Workspace dialog. Set up a translation as follows: Reader Format


In the Reader parameters dialog enter the database connection parameters, then select city_parks as the table to read. Writer Format Writer Dataset

Google Earth KML C:\FMEData\Output\DemoOutput\Parks.kml

Click OK to create the workspace. 2) Set Multiple Geometry Parameter

In the Navigator window, locate the reader parameter Handle Multiple Spatial Columns, and set it to Yes. 3) Add Deaggregator

Add a Deaggregator transformer. This will divide the data into its two geometry types. Notice that one of the parameters is for Geometry Name Attribute. 4) Add Tester

Add a Tester transformer. Set up a test to check if the geometry name attribute (by default _geometry_name) has a value of PARKS_POINTS To prove that the workspace is doing what is expected (so far) disconnect the output feature type (so no data is written), attach a Visualizer transformer to each Tester output port, and run the translation. Points and polygons should get separated out.

Page 36

Multiple Geometries

FME Desktop Database Training Manual 5) Add Bufferer

Because it’s no longer database related, you can skip the rest o f the steps if you desire. Otherwise, continue work to make the KML display the different features at different zoom levels. Firstly add a Bufferer transformer to buffer the point features by 1000ft. This just makes it easier to turn them on/off as we zoom. 6) Add KMLRegionSetters

Add two KMLRegionSetter transformers; one for the buffered points, one for the polygons. The buffered points version should be set to: Bounding Box: Calculate: Minimum Display Size Maximum Display Size

Yes 10 30

The polygons version should be set to: Bounding Box: Calculate: Minimum Display Size Maximum Display Size

Yes 50 -1

7) Add KMLStylers

Add two KMLStyler transformers; one for the buffered points, one for the polygons. Assign the buffered points a red fill color, and the polygons green. The workspace will now look like this:

8) Set Coordinate System

Set the source coordinate system for the reader to TX83-CF 9) Save and Run Workspace

Save the workspace and then run it. If you like, open the output in Google Earth (it may be slow on a Virtual Machine) The ‘point’ geometry will show when the view is zoomed out; as you zoom into the view, the points will disappear to be replaced by the actual polygons.

Multiple Geometries

Page 37


Database Transformers

Besides the FeatureReader there are a number of database-related transformers for submitting SQL statements directly to the database.

There are two methods to submit SQL statements to a database. Firstly, SQL statements can be entered into parameters to run before and after a translation. Secondly, there are SQL-related transformers. Such statements might be used to: • • • •

Create, drop, modify or truncate a database table Carrying out a database join Drop constraints prior to data loading Any other function that is usually carried out using a SQL statement.

This section will focus on the use of transformers to run SQL statements. SQLExecutor

The SQLExecutor is a transformer for executing SQL statements against a database. Each incoming feature triggers the SQL statement that has been defined. Although not new, it has been extensively revised for FME2011. The original remains in FME – for the moment – as the ClassicSQLExecutor transformer. If the SQL is a query, and if features are returned from the database, those features form the output from the transformer. There will be a feature output for each row of the results. This is a key difference to the ClassicSQLExecutor, which returned only a single feature with a list attribute containing feature attributes rather than the features themselves. The new form of this transformer also exposes result attributes, and does not need to be followed by an AttributeExposer . SQLCreator

The SQLCreator transformer is similar to the SQLExecutor, but does not rely on incoming features to trigger it. Instead, the statement is executed once only. Like the SQLExecutor, there will be a feature output for each row of the results.

Page 38


FME Desktop Database Training Manual Uses SQLExecutor can also be used for:

Using a Sequence Number

A sequence number can either be read or written using the syntax .NextVal For example, to write a sequence ID you could use: insert into people (TEAMID, TEAMLEADER, TEAMNAME, TEAMPASSWORD) values (teamid.NextVal,'@Value(TeamLeader)','@Value(TeamName)','@Value(TeamPassword)')

Carrying out a Join The SQLExecutor is able to carry out multiple table joins more efficiently even than the Joiner

transformer. For example: SELECT parcel.*, Landuse.LANDUSE, Landuse.USES FROM parcel INNER JOIN Landuse ON parcel.LULU = Landuse.Code WHERE PARCEL_ID = '@Value_key_field)';

This would require two Joiner transformers but can be done with a single SQLExecutor

AnOraclesequencenumbercanalsobespecifiedintheFeatureType Propertiesdialog:


Page 39


Example 8: SQLExecutor Scenario


Data

Address Data (Oracle Spatial) Emergency Facilities (Access Database)

Overall Goal

Carry out a join using the SQLExecutor

Demonstrates

SQLExecutor transformer

Finished Workspace


The city has a database of emergency facilities in Microsoft Access format. In this example, FME will be used to update the address database, to flag addresses that are an emergency facility. This will be done by using a SQLExecutor transformer to do a database join. 1) Inspect Source Data

Use the FME Viewer to open the source file: Reader Format Reader Dataset

Microsoft Access C:\FMEData\Data\Emergency\e911_facilities.mdb

Click the ‘Select No Geometry’ button on the toolbar to view the text records. 2) Start FME Workbench


Add a Reader using Readers > Add Reader. Set it up as follows: Reader Format


Click the reader parameters button and enter the database connection parameters as before. Click the Table List button and select the ADDRESS_POINTS table. Also, because we don’t want to work with inactive addresses, set a WHERE Clause: STAT != ‘INAC’

Click OK, and OK again to close the dialogs and add the new reader. Q) Does setting a WHERE clause here affect the Reader or just a Feature Type? Why might this matter, and how could you change it should you need to? 4) Add Visualizer Connect a Visualizer transformer to the reader feature type and run the workspace.

If the reader and where clause are functioning correctly, you should read 11,723 p oint features.

Page 40


FME Desktop Database Training Manual 5) Add StringConcatenator

If you noticed in the Access source data, the address is stored in a single string, rather than a set of separate attributes. To do a join requires that attributes from the ADDRESS_POINTS table should be concatenated to match the Access MDB structure. Add a StringConcatenator transformer to the workspace. Open the parameters dialog and set it up to concatenate: ADDRESS STREET_NAM STREET_TYP

The destination attribute can be called fulladdr or something similar. Re-run the workspace if desired, to prove that the concatenation is working correctly. 6) Add SQLExecutor

Add a SQLExecutor transformer after the StringConcatenator . The RESULT output port is the one to connect to the output.

7) Set Parameters Open the SQLExecutor parameters dialog. Set up the parameters as follows: Reader Format Reader Dataset

Microsoft Access

SQL Statement Attributes to Expose Combine Attributes

select * from e911_facilities where ADDRESS = '@Value(fulladdr)' NAME Keep Trigger Attributes if Conflict

C:\FMEData\Data\Emergency\e911_facilities.mdb

NB: If you wish to populate Attributes to Expose from the SQL Query, you will first need to set the SQL statement without the “where” clause, and only add that part once the list is populated. 8) Add AttributeCopier

Add an AttributeCopier transformer to copy NAME to COMMENT_ This will populate the name of the emergency facility in the comments field of the address table. 9) Save and Run Workspace

Save the workspace and then run it. Inspect the output to confirm the COMMENT_ field now includes the contents of the emergency data NAME attribute.


Page 41


Transactions

Transactions are a way to make a workspace more efficient, and to handle cases where a translation fails mid-write.

There are two key parameters that relate to transactions. Transaction Interval

Transaction Interval specifies the number of features to be written to the database at a time. Features sent to a database writer are cached in memory until this number of features is reached, and then pushed out to the database. Increasing the interval can help performance, because it takes fewer database transactions to actually load data. However, after a certain point the amount of features cached in memory is such that system resources become low and performance is adversely affected.

AnydatawrittenbytheSQLExecutoris NOTconsideredtobepartofthesame transactionasthatwrittenbyawriter.

Start Transaction At

Whenever a translation fails mid-write, FME will report in the log window how many transactions had been applied up to that point. Features written but not yet committed will be rolled back. If the problem can be easily fixed, then the translation can be re-run, but this time specifying which transaction to start at. The process is then easier and quicker because the entire database doesn’t need to be rolled-back because of the original failure, and no data needs recommitting. If the “Transaction to Start Writing At” parameter is set to zero – the default – then all data is written as usual.

“FeaturestoBulkWrite”isslightlydifferent.ItrelatestoCHUNKSIZE–thenumber ofrowsreadorwrittenatatime.Intheabovescreenshotthewriterwouldwrite200 rowsatatimeandthen,after1000features,wouldcommitthetransaction.

Somewritersalsosupportaformatattributecalledfme_db_transactionthatcanbe usedtocontrolcommitsandrollbacksatthefeaturelevel.SeetheReadersand Writersmanualformoredocumentationonthisfunctionality. Page 42

Transactions


Example 9: Transactions Scenario

FME user; City of Interopolis, Planning Department

Data

Address Data (Oracle Spatial) Emergency Facilities (Access Database)

Overall Goal

Write data in a Lat-Long coordinate system

Demonstrates

Import Feature Types, Transactions

Finished Workspace


Now the address data has been updated, it can be written back to the address table. But here’s the twist: the end users have decided they want the data to be written to lat/long coordinates – rather than in a state plane coordinate system. So rather than update the existing table, we’ll create a whole new one. 1) Start Workbench

Start Workbench if required and open the workspace from the previous example. 2) Add Writer

Use Writers > Add Writer to add a new Oracle Spatial writer. Writer Format


Click the writer parameters button and enter the database connection parameters. Click OK, and then OK again, to add the writer. When prompted to add a new feature type, click No.

3) Import Feature Types

Since the new table will be the same as the previous one, simply use Writers > Import Feature Types, to import the schema of the existing ADDRESS_POINTS table.

Transactions

Page 43

FME Desktop Database Training Manual 4) Set Feature Type Properties

Open the Feature Type properties dialog. Change the table name from ADDRESS_POINTS to ADDRESS_POINTS_LL

Click the Parameters tab. Set “Create Table” and “Drop Existing Table” to Yes, to ensure that the table is created anew, and that any existing table of that name is dropped first. Set the Min/Max X/Y values to: Minimum X Minimum Y

-98 30

Maximum X Maximum Y

-97 31

5) Set Coordinate System

Set the relevant coordinate systems (TX83-CF and LL 83) in the Navigator window, so that the data will be reprojected.


Ensure the data is connected up to the writer Feature Type, then save and run the workspace.

Page 44

Transactions

FME Desktop Database Training Manual 7) Create Index

Now the table is created (and has data in it) there is a request to add an index to it. Open the database home page and use the object browser to browse to the newly created table. Click on the tab marked Indexes.

Click on the option Create.

Click Next

Set the uniqueness parameter to Unique, and select PRIMARYINDEX as the Index Column. Click Next.

Click Finish and the index will be created. NB: The equivalent SQL statement for this index would be something like:

create UNIQUE index "ADDRESS_POINTS_IDX1" on "ADDRESS_POINTS" ("PRIMARYINDEX") / 8) Inspect Data

Inspect the data using the FME Universal Viewer. There should be 12,000+ features…. oh! When we mapped the data we forget to connect the Trigger features too!

Transactions

Page 45

FME Desktop Database Training Manual 9) Finalize Workspace

Create a connection between the SQLExecutor:Trigger port and the writer feature type.

Before the workspace can be run, the Feature Type parameters need to be changed – to avoid dropping the table and losing the index we just created. So, open the Feature Type properties dialog. Set “Create Table” to No (since it already exists and we wish to keep it) Set “Truncate Existing Table” to Yes (since we are about to re-write existing data, and this would violate the uniqueness constraint of the index).

10) Update Transaction Interval

Purely for the sake of this demonstration(!) locate the parameter “Features to Write Per Transaction” in the Navigator window, and change it to 25.

Page 46

Transactions

FME Desktop Database Training Manual 11) Save and Run Workspace

Save the workspace, and then run it. The translation will run for a while and then fail. The error message will be something along the lines of this: ERROR |Execution of statement `INSERT INTO ADDRESS_POINTS_LL (…)did not succeed; Error was `ORA-00001: unique constraint (ADDRESS_POINTS_LL_IDX1) violated'. ERROR |Database Writer: Translation aborted – rerun specifying "ORACLE8I_1_START_TRANSACTION 8"

So there is a problem. The problem is that the output of the SQLExecutor:TRIGGER port includes the six features that also appear from the SQLExecutor:RESULT port. This results in a duplicate record that violates the uniqueness constraint on the index. 12) Adjust Workspace

OK, the key is to filter out the six trigger features that have a matching result. Fortunately there is a _matched_records attribute to allow us to do just that. Add a Tester transformer between the SQLExecutor:TRIGGER port and the writer feature type. Set it up to filter out (i.e. remove) data where _matched_records > 0 (or keep data where _matched_records = 0)

12) Set Transaction Parameter

Now re-check the log window. One of the messages will say something like: ERROR |Database Writer: Translation aborted – rerun specifying "ORACLE8I_1_START_TRANSACTION 8"

Make a note of the transaction number specified. In the Navigator window, locate the writer parameter Transaction to Start Writing at . Set the parameter to the value mentioned in the log window (in this case, 8).


Save and re-run the workspace. This time the process will skip over the first 7 transactions, because they were already successfully committed. Features will only be committed from transaction 8 onwards. The log file will report: INFORM|Oracle 8 Spatial Writer: Starting at transaction 8 with a transaction interval of 25

And the data will now have been successfully written!

Transactions

Page 47

Database Oracle Pathway Training 2011

Recommend Documents