Sep. 21-22, 2006

1. v FME Worldwide User Conference – Vancouver Sep. 21-22, 2006 FME and ArcSDE / Geodatabase Mark Stoakes, Safe Software, Vancouver, BC

2. Agenda • Supported ESRI databases • Differences between ArcSDE and Geodatabase • Writing data into ArcSDE and Enterprise Geodatabases • Reading data from ArcSDE and Enterprise Geodatabases • Raster Support • ArcSDEQuerier • What’s coming in FME 2007

3. SupportedESRI Databases • ArcSDE A spatial data gateway providing spatial database functionality on top of relational databases (e.g. Oracle, SQL Server). • Multi-User Geodatabase (Enterprise) Enterprise Geodatabase is a layer of functionality and structure built on top of ArcSDE. • Individual Geodatabases • Personal Geodatabase • Personal geodatabase stored in a Microsoft Access database. • File-Based Geodatabase • ArcGIS 9.2 geodatabase stored as a set of files in a directory tree.

4. ArcSDE vs. Geodatabase

5. ArcSDE vs Geodatabase

6. Why File-Based Geodatabase?

7. Writing ArcSDE / Geodb • Spatial Domain • The most common reason for data load errors • setting: data resolution / precision data extents (origin) coordinate system • Use ArcCatalog to determine the best domain parameters

8. Writing ArcSDE / Geodb Step 1 • Determine Spatial Domain for each table • Coordinate Reference System (e.g. LL84) • Smallest coordinate in x,y of entire area to be loaded • Largest coordinate in x,y of entire area to be loaded Step 2 • Calculate the scale factor • Larger of (xExtent) / 231 or (yExtent) / 231. Scale is the reciprocal of this number Step 3 • Specify -1 (ArcSDE) as the grid size so that you can load the data in quickly

9. Writing ArcSDE / Geodb Determining the Spatial Domain Within ArcCatalog: • Double click the feature class • Select the 'Fields' tab on the Feature Class Properties dialog • Select the Geometry field • In the Field Properties table at the bottom of the dialog, press the ellipsis button (correspondingto the Spatial Reference entry) • Select the 'X/Y Domain' tab on the resulting Spatial ReferenceProperties dialog

10. Writing ArcSDE / Geodb • Versions • Supported by both ArcSDE and Geodatabase • Writer can perform RECONCILE and POST operations • Feature Datasets (Geodb only) • All feature classes in a feature dataset must have the same spatial domain • FME writer spatial domain is used when writing to a feature dataset • Subtypes & Domains (Geodb only) • Load subtype codes or descriptionsuse geodb_subtype_name to load subtype descriptions • Create coded and range domains

11. Writing ArcSDE / Geodb Feature Dataset & Spatial Domain Feature Dataset Forces the writer spatial domain parameters to be used Spatial Domain

12. Writing ArcSDE / Geodb Subtypes & Domains (Geodb only) • Domain/Subtype creation in the feature type definition Parameter TAB • Template files will simplify creation of subtypes & domains

13. Writing ArcSDE / Geodb • Annotation (Geodb only) • Feature-linked annotation • Annotation scale & units • Relationship Classes (Geodb only) • FME currently cannot create a relationship class – but can write to them • Building relationships requires workspace configuration to determine the OBJECTID of the child tables • Data Validation • Catches features rejected by ArcSDE / Geodb and the underlying database • ArcSDE has rejected feature pipeline – cleans & resubmits features • Geodb can send failed features to FFS file

14. Writing ArcSDE / Geodb Geometry Consistency • Every geometry that is loaded is validated • Slower loading and some geometry is rejected • Can perform queries with confidence . self intersecting Bad Geometries! Good Geometries

15. Writing ArcSDE / Geodb Geometry Consistency • Things are not always so simple • Coordinates in low precision are stored in database as 31 bit integers • Self-intersections can be introduced by the ArcSDE itself! • High precision • Headaches go away or are reduced!

16. Writing ArcSDE / Geodb How does FME Help? ArcSDE 1. FME tries to write feature 4. FME tries to write feature onelast time 3. Pipeline is applied to try and fix feature ArcSDE 2. Invalid feature sent to ffs or rejected feature pipeline Geodb – writes failed features to FFS file

17. Writing ArcSDE / Geodb more validation options… • Continue on bad data • ArcSDE writer ignores the bad data and continues the translation • Roll back then continue • Rolls back to the previous commit and then continues the translation • Ignore failed features • Geodb writer ignores the bad data and continues the translation

18. Writing ArcSDE / Geodb • Paths • Geometries containing lines & arcs as a single entity • Now supported by FME rich geometry • Measures • Use the geodb_measures or sde30_measures attributessde30_measures = 20,35,65,105,145 • Use the MeasureGenerator transformer • Metadata • FME does not read / write Metadata, but… • FME can process metadata XML once it is exported from ArcGIS

19. Writing ArcSDE / Geodb • Transactions & Commits • ArcSDE transactions can be controlled at the writer level or per feature • Writer parameters such as: • Transaction Type (geodb only) • Transaction Interval • Feature attribute (ArcSDE only): • fme_db_transaction = COMMIT_BEFORE | COMMIT_AFTER | ROLLBACK_AFTER | IGNORE • allows groups of related features to be committed or rolled back

20. Writing ArcSDE / Geodb • Inserts, Updates & Deletes • Can be controlled at the writer level or per feature • Writer parameter: • Writer Mode = INSERT | UPDATE | DELETE (geodb) • restricts the writer to a single mode • Feature attribute (ArcSDE only): • fme_db_operation = INSERT | UPDATE | DELETE • allows each feature to control it’s destiny! • Updates and Deletes easier with the OBJECTID • Journal Tables (JTX): • track the changes (add, modify or delete) to allow incremental updates • FME can read the JTX XML (GMLFeatureReplacer) and extract feature changes

21. Writing ArcSDE / Geodb • Tips on Performance • Use the ArcSDE writer if possible • Do not build the spatial index when creating new layers • Use load_only mode - set the grid size to -1 • Build the spatial index after the data load END_TCL • Use Transactions if possible. Versioning is slower • Increasing the transaction interval / size • Set SIMPLIFY_GEOM to NO • Writing annotation features is slow • Use two writers – Geodb for annotation, ArcSDE for other features • Avoid user-defined fields on annotations, if possible

22. Other Writing Tips • Use Import Feature Type definitions • If writing to existing ArcSDE this saves time • Put FME on the same machine as the ArcSDE Server • Network latency is significant when loading • Use High Precision ArcSDE Tables • This can save you headaches down the road • Much easier to get data into

23. Reading ArcSDE / Geodb • Versions • supported by both ArcSDE and Geodatabase readers • can either read from existing version or create new version • more on this later • Spatial Query • allows a spatial predicate (INTERSECTS, TOUCHES, etc) and search envelope • WHERE clause • to be expanded in 9.2 for a full SELECT statement • Domains & Subtypes • resolve domains & subtype descriptions, i.e.:STATUS & STATUS_resolved

24. Reading ArcSDE / Geodb • Networks • returns geometry elements and element ID, from / to junction IDs and junction geometry • Annotation & Labels (geodb only) • supports feature linked annotation • Metadata • not supported, but…FME can process metadata XML once it is exported from ArcGIS • Measures • loads the geodb/sde30_measures attributes

25. Working with Raster Data • Reading from ArcSDE Raster • Reads mosaics (Raster Maps) and Catalogs • Raster maps can be queried using spatial extents • Writing to ArcSDE Raster • Writes both Raster Maps & Raster Catalog • Creates the FOOTPRINT (bounding box vector) • Control color compression, raster compression and pyramiding • Resolution set by the first raster loaded (as per SDE convention) • FME Universal Viewer can view rasters

26. Rasters & FME Universal Viewer

27. Raster formats and more… Focus is on loading raster to database and database to raster extract Working with Raster Data

28. ArcSDEQuerier • ArcSDEQuerier is an ArcSDE reader /writer embedded in a transformer • Enables user to access ArcSDE data in the middle of a workspace data flow • ArcSDEQuerier allows: • QUERIES | UPDATES | DELETES • supports spatial & attribute queries • Applications • more complex ArcSDE spatial queries involving multiple input features • transaction table support – JTX tables • multiple attribute only (WHERE) queries using several input features

29. ArcSDEQuerier The ArcSDEQuerier enables data to be retrieved from the ArcSDE anywhere in a workspace. FME FeatureData Stream FME FeatureData Stream ArcSDEQuerier 2. Data retrieved from ArcSDE 1. Query Featuresare routed to ArcSDEQuerier 3. Retrieved data is output to rest of FME data flow ArcSDE

30. Other Issues • The Geodatabasereader/writer is only available on Windows platforms • And requires an ArcGIS license be available • Will be available on other platforms in ArcGIS 9.2 • The ArcSDEreader/writer is available on Windows and UNIX platforms • And requires no additional licenses • The ArcSDEreader/writer is faster because it avoids an entire layer of software • But has somewhat reduced functionality as a result • Many clients doing large scale data migrations use the ArcSDEwriter for speed and portability • Running FME for loading on the same machine as ArcSDE Server makes a noticeable difference

31. What’s in the Pipeline … • File Geodatabase • File Geodatabase is supported in ArcGIS 9.2 • FME Geodatabase Writer Template File • Pass writer a reference geodatabase file and all settings will be used • Domains, relationships, feature datasets, feature classes, etc. • Rich Geometry Support • Rich geometry support will be enhanced • Writing Networks to Geodatabase

32. What’s in the Pipeline … • High Precision Support • Full support for both low and high precision ArcSDE and Geodatabase files • History/Archiving Support • Probably not for FME 2007 • ArcGIS Server • Use geodatabase reader/writer to connect to and read maps provided by ArcGIS Servers • Rasters • Improved raster support & functionality

33. ArcSDE/Geodb and Server FME Spatial ETL Server ArcSDE/Geodb

34. Summary • ArcSDE and Geodatabase provide very comprehensive database capabilities • A moving target with new features with each ArcGIS release from ESRI • Safe Software continues to focus on improving the usability of these formats • Let us know what you need when working with these formats • Even if it is just what should be easier!