Sep' 2122, 2006

1
v
Sep. 21-22, 2006
FME Worldwide User Conference - Vancouver
Database Tips and Tricks Robyn Rennie, Safe
Software, Vancouver, BC
2
Database Tips and Tricks

• Begin/End_SQL
• Other SQL options
• Feature-Based Transactions
• SQL/WHERE Clause
• SQL/SELECT Clause

3
Database Tips and Tricks

• Database Performance Hints
• SDE/Geodatabase
• Spatial Indices
• Oracle SRID Exceptions Table
• Any Questions?

4
BEGIN/END_SQL

• Used to apply some kind of pre/post processing
• Acts before opening or after closing the table
• Allows multiple SQL statements
• MYSQL_DB_1_BEGIN_SQL0
• SET FOREIGN_KEY_CHECKS0
• ORACLE8I_DB_1_END_SQL0
• create view clip_area as select
  id,shape_area from clipper
• Tools Edit Header

5
Other SQL Options

• Use SQL Statement to Execute before/after
  translation
• UPDATE test_table SET date_mod 2006/09/14
• Available on some of the writers
• Table level SQL statement
• Used to modify features on writing
• UPDATE test_table SET plan_no prod_id
• WHERE customer 15

6
Other SQL Options
7
Feature-Based Transactions

• fme_db_operation
• INSERT
• UPDATE
• DELETE
• WRITER_MODE must be set to UPDATE
• Use TESTER to decide what happens to each feature
• Use ATTRIBUTECREATOR to assign values
• Identify KEY column for matching
• Use TRANSACTION_INTERVAL to force commits

8
Feature-Based Transactions
9
Feature-Based Transactions

• fme_db_transaction
• COMMIT_BEFORE
• COMMIT_AFTER
• ROLLBACK_AFTER
• IGNORE
• TRANSACTION_INTERVAL must be set to VARIABLE
• Use TESTER to decide what happens to each feature
• Use ATTRIBUTECREATOR to assign values
• Not supported for all writers yet
• Does not allow for nested transactions

10
SQL/WHERE Clause

• More pre-processing options
• Speeds up database reading
• Works at the dataset or table level
• Used to select features to process
• WHERE city_name Burnaby
• WHERE "REPLICATION.REPLICATION_DATE is null AND
  \
• REPLICATION.AREA_CD DIST_CENTER.AREA_CD AND"
  \
• "REPLICATION.IDENT REPLICATION_LAYERS.IDENT
  AND" \
• "DIST_CENTER.TOWN_CD (TOWN)

11
SQL/SELECT Clause

• Even more pre processing options
• Works at the table level
• Used to reduce attribute lists on reading
• SELECT Statement ID, NAME, TYPE, SITE from
  INPUT_TABLE
• Used to join tables prior to reading
• SELECT statement (SEIS_LINE_QUERY)
• Save as seis_line_query.fmi and include it in
  the Header
• Use Tools Edit Header

12
SQL/SELECT CLAUSE

• SQL Queries can be included here
• See the WB Tools-gtEdit Header
• These MACROS can be replaced with a GUI or
• command line input...
• MACRO MIN_LAT 50
• MACRO MAX_LAT 55
• MACRO MIN_LONG -117
• MACRO MAX_LONG -113
• MACRO SEIS_LINE_QUERY \
• select \
• a.SEIS_PT_LAT, \
• a.SEIS_PT_LONG, \
• a.ELEVATION, \
• a.SEIS_PT_NUM, \
• a.SEQ_NUM, \

13
Database Performance Hints

• Reading
• Let the database do the work!
• Use SQL commands to join tables or select
  features
• Attribute queries are faster than spatial queries
• SEARCH_ORDER
• SPATIAL_FIRST
• ATTRIBUTE_FIRST
• OPTIMIZE
• Make use of the Spatial Envelope option
• Set IGNORE_NETWORK_INFO to Yes for Geodatabase
  reader major performance gain!

14
Database Performance Hints

• Writing
• Let the database do the work!
• Increase the transaction interval
• Set SIMPLIFY_GEOM to NO for Geodatabase writer
• Build Spatial Index after the data load
• Use ArcSDE writer rather than Geodatabase writer
  if possible

15
SDE/GEODATABASE

• Geodatabase writer only allows one geometry
• SDE writer allows multiple geometry
• ArcGIS expects only one geometry
• SDE reader/writer is faster because it avoids an
  entire layer of software
• Geodatabase writer cannot drop table first
• Set X and Y origin to avoid Coordinates out of
  bounds message

16
Spatial Indicies

• Grid 0 0 for Geodatabase writer
• forces the FME to calculate the spatial index
• Grid 0 0 for SDE writer
• the FME loads data without creating a spatial
  index
• then use SDE, FME_END_TCL or FME_END_PYTHON to
  manually create the spatial index
• some performance gain
• In general
• use a Grid 0 1 or 10 for Lat/Long
• use a Grid 0 1000 for projected data
• affects the speed of a spatial query
• Lots of information in the FME Readers and
  Writers manual

17
Oracle SRID Exceptions Table

• Matches Oracle SRIDs to FME coordinate systems
• Oracle writer uses this table to extract SRID
  information
• Currently only about 750 matching coordinate
  systems
• ltFME_HOMEgt\Reproject\Exceptions\oracle.db
• Customizable
• ORACLEUTM10-8326910

18
Any Questions?

• Any questions?
• Please contact
• support_at_safe.com
• Robyn Rennie
• Professional Services