WIXLIB

FME Desktop Spatial DatabasePathway TutorialFME 2013-SP2 Edition

Safe Software Inc. makes no warranty either expressed or implied, including, but not limited to, anyimplied warranties of merchantability or fitness for a particular purpose regarding these materials, andmakes 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 consequentialdamages in connection with or arising out of purchase or use of these materials. The sole and exclusiveliability of Safe Software Inc., regardless of the form or action, shall not exceed the purchase price of thematerials described herein.This manual describes the functionality and use of the software at the time of publication. The softwaredescribed herein, and the descriptions themselves, are subject to change without notice.Copyright 1994 – 2013 Safe Software Inc. All rights are reserved.RevisionsEvery effort has been made to ensure the accuracy of this document. Safe Software Inc. regrets anyerrors and omissions that may occur and would appreciate being informed of any errors found. SafeSoftware Inc. will correct any such errors and omissions in a subsequent version, as feasible. Pleasecontact us at:Safe Software Inc.Suite 2017, 7445 – 132nd StreetSurrey, BCCanadaV3W1J8www.safe.comSafe Software Inc. assumes no responsibility for any errors in this document or their consequences, andreserves the right to make improvements and changes to this document without notice.TrademarksFME is a registered trademark of Safe Software Inc.All brand or product names mentioned herein may be trademarks or registered trademarks of theirrespective holders and should be noted as such.Documentation InformationDocument Name: FME Desktop Database Pathway TutorialFME Version: FME 2013-SP2 (Build 13499) 32-bitOperating System: Windows 7 SP-1, 64-bitDatabase: Oracle Database Express 11g Release 2 (11.2), 32-bitDatabase: SQL Server 2012Database: PostGIS 2.0 (v2.0.1) for PostGreSQL x64 v9.2Updated: July 2013

FME Desktop Spatial Database TutorialIntroduction . 4Spatial Database Pathway . 4FME Version . 4Sample Data . 4Supported Databases . 4Introduction to FME . 5What is FME? . 5Spatial Database Basics . 6Spatial Database Operations . 6Supported Database Formats . 6Important Database Terminology . 7Connecting to a Spatial Database . 8Basic Connection Parameters. 8Connecting to PostGIS . 9Connecting to Oracle . 10Connecting to SQL Server . 12Connecting to an Enterprise Geodatabase . 14Writing to a Spatial Database . 16Writing Data . 16Controlling Database Writing. 19Writer Parameters . 19Feature Type Parameters . 19Importing Table Schemas . 24Import Feature Types. 24Reading from a Spatial Database . 28Reading Data . 28Controlling Database Reading . 30Reader Parameters. 30Feature Type Parameters . 30Basic Database Updates . 35FME Update Modes . 35Troubleshooting . 42Connection Problems. 42Reading Problems . 43Writing Problems. 43What’s Next? . 44Next Step . 44IntroductionPage 3

FME Desktop Spatial Database TutorialIntroductionWelcome to the FME Desktop Spatial Database Pathway TutorialSpatial Database PathwayThis tutorial is an introduction to usingspatial databases with FME. It is thefirst part of the FME Training SpatialDatabase Pathway.It is assumed that you will already befamiliar with the concepts andtechniques described in the FMEDesktop Tutorial.NB: You can find the FME DesktopTutorial online. It includes both PDFdocuments and a set of movies thatcover each chapter.FME VersionThis tutorial covers the use of FME Desktop 2013 edition, specificallyFME2013-SP2. Older versions of FMEmay not have some of the functionalitydescribed.Sample DataThe sample data required to carry out the examples in this document can be obtained from:http://www.safe.com/fmedataSupported DatabasesThis tutorial was tested on, and includes documented steps for, the following databases: PostGIS 2.0 for PostgreSQL 9.2Oracle Express 11g (11.2)SQL Server 2012Enterprise GeodatabaseFME Format: PostGISFME Format: Oracle Spatial ObjectFME Format: Microsoft SQL Server SpatialFME Format: Esri Geodatabase(ArcSDE Geodatabase)For the purposes of simplicity, a PostGIS connection will be described and illustrated in all thefollowing examples. Where other formats deviate strongly from the described steps, or where thestep is specific to PostGIS, it will be highlighted with a tag:PostGISIntroductionOracleGeodatabaseSQL ServerPage 4

FME Desktop Spatial Database TutorialIntroduction to FMEHere’s a quick one-page reminder on what FME is.What is FME?FME is a spatial data transformation platform that helpsorganizations more easily overcome a range of spatial datainteroperability challenges. It is available in both desktop andserver solutions.FME is classified as a Spatial ETL (Extract-Transform-Load)tool, designed to help users master more spatial datatransformation challenges than any other technology. Extract is the ability to read any format of spatial data. Transform is the ability to manipulate data during thetranslation process. Load is the ability to write the data in any other format.With Data Transformation, the output from an FME process can be tailored to match a requiredstructure, and can even be greater than the sum of the inputs.The key FME Desktop application is FME Workbench, an intuitive point and click interface forgraphically defining translations and transformations as a flow of data.FME Quick Translator is an application for carrying out basic, non-customized translations.FME Data Inspector is an application for visually inspecting both spatial and non-spatial data.Introduction to FMEPage 5

FME Desktop Spatial Database TutorialSpatial Database BasicsHere are some basic facts and information about Spatial Databasesand their relationship with FMESpatial Database OperationsSpatial databases are almost exclusively used for long-term data storage, rather than short-termdata transfers, so the key operations are getting data into and out of that store.The three key operations that occur with a spatial database are: Data ImportsData UpdatesData DistributionAll of these actions involve not just transforming the data into the correct format, but alsotransforming data into the correct schema (data model).Supported Database FormatsFME supports many database formats, including spatial database formats (vector and raster) andnon-spatial database formats.Some of the notable formats supported by FME include:ESRIArcSDE, ArcSDE Raster, Geodatabase, Geodatabase RasterAutodeskFDO ProvidersGoogleGoogle Fusion Tables, Google SpreadsheetIBMDB2 Non-Spatial, DB2 Spatial, Informix, Informix SpatialIntergraphGeoMedia SQL Server WarehouseMapInfoSpatialWareMicrosoftSQL Server Non-Spatial, SQL Server Spatial, AzureNetezzaNetezza, Netezza SpatialOracleOracle Non-Spatial, Spatial Object, Spatial Point Cloud, Spatial GeoRasterPostgresPostgreSQL, PostGISSmallworldSmallworld 3, Smallworld 4TeradataTeradata Non-Spatial, Teradata SpatialFind the complete list of supported formats at www.safe.com/fme/format-search/.Spatial Database BasicsPage 6

FME Desktop Spatial Database TutorialImportant Database TerminologyIt’s important to clarify some of the basic database terms that are used by FME, as they maydiffer from what is used in a particular database package.SchemaAlso known as Data Model.Coordinate SystemAlso known as Spatial Reference System or Spatial Reference Identifier (SRID).Translation ComponentsAn FME translation is made up of various components. When handling spatial databases, it isimportant to know what these components are, and to get a good grasp of the related FMEterminology. Readers and Writers are thecomponents that read and write data.They represent the database in atranslation. Feature Types are the components thatdefine data structures (or schemas). Theyrepresent the tables in a database. Features represent the individual recordsin a database table.Spatial Database BasicsPage 7

FME Desktop Spatial Database TutorialConnecting to a Spatial DatabaseDatabase connections have slightly different parameters according tothe format of data being used.Connecting to a database is slightly different to selecting a file for a file/folder-based format. Theoperation relies much more on format specific parameters.Basic Connection ParametersThe basic connection parameters are: Host (Server) NameDatabase (Service) NameUsernamePasswordNetwork Port NumberThese parameters may differ slightly for each format, but will always be found in any datasetselection dialog by clicking on the “Parameters ” button.TroubleshootingIf a connection problem occurs, refer to the section titled Troubleshooting, at the end of thisdocument.Connecting to a Spatial DatabasePage 8

FME Desktop Spatial Database TutorialPostGISConnecting to PostGISConnecting to a PostGIS database requires all five basic connection parameters.Follow these steps to test the connection to your PostGIS database:1) Start FME Data InspectorSelect File Open Dataset from the menu bar to open the dataset selection dialog.2) Define DatasetWhen the dialog opens fill in the readerformat field as follows:Reader Format:PostGISClick the Parameters button to openthe parameters dialog.Fill in the host, port, database, username,and password parameters.3) Select TablesClick on the browse button to the right of the Table List parameter. If the connection was successful, and there is already data in the database, then a list oftables will be presented. Select one and click OK (and then click OK on subsequentdialogs) to view the data. If the connection was successful, but there is no data in the database, then a dialog willopen with a warning to this effect:readSchema resulted in 0 schema features being returned FATAL:If the connection was unsuccessful, then a dialog will appear with an error reporting thenature of the problem:password authentication failed for user "postgres"Connecting to a Spatial DatabasePage 9

FME Desktop Spatial Database TutorialOracleConnecting to OracleConnecting to Oracle requires a client to be installed.Connection is possible through either tnsnames.ora or a direct connection.tnsnames.oratnsnames.ora is a file that usually resides in the Oracle client installation folder. It is a text file thatconsists of a number of service definitions of the form: net service name (DESCRIPTION (ADDRESS LIST (ADDRESS (PROTOCOL TCP)(HOST hostname )(PORT 1521 )))(CONNECT DATA (SERVICE NAME oracle sid )))Follow these steps to test the connection to your Oracle database using tnsnames.ora.1) Start FME Data InspectorStart the FME Data Inspector.Select File Open Dataset from the menu bar to open the dataset selection dialog.2) Define DatasetWhen the dialog opens fill in the readerformat field as follows:Reader Format:Oracle SpatialObjectClick the Parameters button to open theparameters dialog.Fill in the service, username, and password.3) Select TablesClick on the browse button to the right of the Table List parameter. If the connection was successful, and there is already data in the database, then a list oftables will be presented. Select one and click OK to view the data. If the connection was unsuccessful, then a dialog will appear with an error reporting thenature of the problem:Error connecting to Oracle database: message was ORA-12154: TNS:could not resolve the connect identifier specified'.Connecting to a Spatial DatabasePage 10