Safe Software's FME

FME Training

1. download training manual from http://cdn.safe.com/training/course-materials/Training-Workbook-Exercises.pdf
2. download the training pack from http://cdn.safe.com/training/course-materials/FME_Training_Self_Study_Pack_v2012.zip
3. download training data from http://cdn.safe.com/training/tutorials/FME-Sample-Dataset-Full.zip

FME Support

• ryan.cragg@safe.com gives the online training for FME workbench
• FME Talk group to ask questions instead of using tech support

FME Workbench

• 
• workbench files store fixed path but if you save the workspace in the same directory as the data then it will relative paths.
• Workbench Interface
• 
• Reference: Workbench Introduction on ESRI's ArcGIS Data Interoperability Demos ArcGIS Data Interoperability FAQ
• FME Transformer Reference Guide
• Mark Ireland Blog - www.fmeevangelist.com

Documenting FME Workbench

• Bookmarks
• Comments

FME Server

• http://fmeserver.com/userweb/sharper/playground/index.html

FME Universal Viewer

• can reorder layers
• will not reproject layers, will only overlay if in the same coordinate system, not like ArcMap
  • Navigator Pane → Coordinate System - to reproject data
  • Reprojection Transformer - to immediately reproject data, not just at the end of the workspace
• FME view space saves (.fmv) absolute paths, not relative paths so this is a problem is giving the file to someone else. The .fmv file is an XML document
• cannot view the symbology of a Google Earth KML styler or ArcMap layers files (.lyr)
• cannot view 3D but need to use the FME Data Inspector (beta)
• can add a WMS/WFS server connection as the background
• Cannot add Bing Maps or Google Maps as a base map
• Appears you cannot label features like you can in ArcMap

Background on FME and Google Earth KML files

• Background info on ArcGIS, FME and Google Earth

Using FME to create static KML/KMZ files for Google Earth and Map

1. Creating KML Features
   • Steps to create a KML/KMZ using FME Workbench for Polygons, Lines and Points
   • Projections - Stateplane to Lat/Long
   • Using KML Network Links and Regions
2. Symbolizing and Labeling KML Features
   • Random Color Generator using KMLStyler
   • Adding a Company Logo as a Screen Overlay
   • How to Label Features in a KML file
   • How to Symbolize KML files using the FME KMLStyler Transformer
   • The FME KMLStyler
3. Display KML Attributes
   • How to display GIS attributes/fields in the KML Balloon Descriptor
   • KML Extended Data and Hyperlinks
   • Create Hyperlinks within a KML Balloon Descriptor
   • KML Folders - How to group features in a KML
     • Example, I have various engineering studies such as traffic studies, drainage studies and geotechnical studies. I want to group them together so I can easily turn on/off all the traffic studies.
   • KML Document Name
   • Reference:
     • Setting KML Document Attributes, KML Attributes currently
     • supported by FME, and the FME
     • Google Earth KML Reader/Writer
     • FME Online Learning Documentation
     • KML Section of the Advanced FME Training Manual
4. Edit KML Features
   • Merge several kml/kmz files into a single KMZ using Google Earth
5. Using FME to convert TBC to KML
6. FME Extras
   • FME KML Writer
   • Batch Deploy
   • FME Tech Support Info

FME Coordinate Transformations

• FME and ESRI World File
• FME Shapefile Transformation - Grid to Ground

FME and ESRI World File - Grid to Ground

• Problem
  • Autodesk products such as Map3D and Civil3D do not recognize grid to ground coordinate transformations like ESRI ArcMap does with a World File (.wld).
  • Can manually transform in Map3D and Civil3D using the ALIGN or ADETRANSFORM commands but would like to automate this process.
• Solution: use FME workbench to transform GIS layers such as Shapefiles into ground coordinates
• Procedure
  • Step 0: obtain datasets
    • GIS Shapefile in Grid Coordinates (Stateplane Coordinates - Nevada East Zone, Units Feet, Datum NAD83) pcl04135.zip
    • CAD drawing in Ground Coordinates (Arbitrary XY coordinates with origin of 5000,5000) HeyerGubler.dwg
    • ESRI World File - HeyerGubler.wld
  • Step 1: Download the Dave Campanas custom transformer called WLDWarper
  • Step 2: Unzip WLDWarper in your transformers directory, for example C:\Program Files\FME\transformers\WLDWarper.fmx
  • Step 3: refresh the FME Workbench Transformers window
    • 
  • Step 4: assign WLDWarper Parameters
    • In this example we are just going shapefile to shapefile but doing a coordinate transformation, so setup the FME Workbench Source Data and Destination Data as shapefiles.
    • Browse to the location of the ESRI World File which has a minimum of two coordinate pairs which will be used to tranform the coordinates from GIS Grid to CAD Ground.
    • Remember .wld format is first the CAD ground coordinates then the GIS grid coordinates. So using the WLDWarper.fmx, forward is from CAD to GIS and reverse is from GIS to CAD
    • 
  • Step 5: view files in Civil3D
    • Open CAD .dwg file
    • MapImport the shapefile which was transformed
    • Should look like this
    • 

FME Shapefile Transformation - Grid to Ground

ArcGIS, FME and Google Earth

• List of software applications/scripts which can create KML/KMZ files with ArcGIS (see Google KML Developer Support Discussion Group - Looking for help to convert Arc projects file mxd to KML
• Transformers supported by ArcGIS Data Interoperability

Steps to create a KML/KMZ using FME Workbench

• Step 0: open FME Workbench, File -> New, then click Create a blank workspace
• Step 1: Source Data -> Add Dataset...
  • Step 1a: Format, click the ... button and in the search box at the lower left corner, type shp (if converting a shapefile) or mdb (if converting a geodatabase)
  • Step 1b: specify path and filename of the GIS dataset. For a Geodatabase, will also need to select a Feature class.
  • Step 1c: keep the Coordinate System setting to Read from feature
• Step 2: Destination Data -> Add Dataset...
  • Step 1a: Format, click the ... button and in the search box at the lower left corner, type kml or kmz
  • Step 1b: specify path and filename for the output kml file to be viewed in Google Earth
  • Step 1c: keep the Coordinate System setting to Read from feature
• Step 3: add a Reprojection Transformer in the GIS dataset is NOT in LL84 coordinate system.
• Step 4: draw a line between the two boxes to connect them, then Run Translation (green play button on the toolbar)
• 

Projections - Stateplane to Lat/Long

• Google Earth only supports a geographic coordinate system of Lat/Long with the WGS84 datum (see Google Earth Users Guide - What Is a Datum)
• Use the FME Workbench Reprojector Transformer to change the output destination to LL84 projection.
• 
• References: FME Workbench Reprojector Transformer

Using KML Network Links and Regions

• Problem: Google Earth and Map cannot display very large datasets. To get around this, need to create Regions (not supported in Google Earth) and Network Links. This is very similar to tiled aerial photo images (e.g. Las Vegas NV will tile aerial photos by one square mile and use an index - raster image catalog - to give the impression the images are mosaicked together into one seamless image).
• Then you need a link to the individual subset kml files, this is similar to a raster image catalog. Note Google Map doesn't support Regions as of June 2008 but the team is working on it.
• RBNLs require 2 KML files: 1) the KML index which contains the level of detail and bounding box info and 2) the tiled data which doesn't have any view/zoom/display restrictions so if you add one of these tiled kml files, will be visable at any scale.
• Region-Based Network Links
  • Course Level - Entire Clark County
  • Medium Level - Assessor Books
  • Fine Level - Assessor Sections
  • Google Earth Plugin will zoom to the Course Level. Zoom in, then the Course Level will disappear and the Medium Level will appear. Zoom in further and the Medium Level will disappear and the Fine Level will appear. To display GIS data, will need to tile the data by section. Then will need an ability to display multiple kml files at this Fine Level. For example, kml file of drainage studies, kml file of water lines, kml file of school attendance zones, etc.
• Region Based Network Links (RBNLs)
  • Step 1: create KML Index Dataset
    • KML will contain the bounding box for the tiled features/images: kml_latlonaltbox_east, north, south and west
    • This file is just like a raster image catalog
    • Necessary KML Attributes
      • kml_id
      • kml_link_href
      • kml_latlonaltbox_altitude_mode
      • kml_latlonaltbox_min_altitude and max_altitude
      • kml_lod_min_lod_pixels and max_lod_pixels
      • kml_lod_min_fade_extent and max_fade_extent
      • kml_link_refresh_mode = onRegion
      • kml_latlonaltbox_east, north, south and west
  • Step 2: create KML Tile Folder
    • If features are in one shapefile, will need to break up or tile into smaller sizes
    • KML contains all the styling and symbology
    • does NOT contain any level of detail or bounding box tags
• To create smaller subset of KML files, use the feature type NetworkLink and fanout by feature type
  • 
• The master index must include the NetworkLink bounding box. NetworkLink destination features must be named NetworkLink in the Destination Data properties, Feature Type Name field
  • 
• "When your data is projected onto the screen, it must occupy an area of the screen that is greater than <minLodPixes> and less than <maxLodPixels> in order to be visible. Once the projected size of the Region goes outside of these limits, it is no longer visible, and the Region becomes inactive" (Working with Regions)
• For maxLodPixels, it specifies -1, which has the special meaning "active to infinite size." Without this specification, the entire hierarchy might never be triggered. Prepare the KML files under Preparing Your Data for a Super-Overlay
• Can use relative paths with NetworkLinks
• kml_link_href = http://faculty.unlv.edu/jensen/kml/regions/16201.kml
• kml_id = 16201 (BOOKSEC)
• kml_latlonaltbox_altitude_mode = relativeToGround
• kml_latlonaltbox_min_altitude = 0
• kml_latlonaltbox_max_altitude = 3000
• kml_lod_min_lod_pixels = 1200
• kml_lod_max_lod_pixels = -1
• kml_lod_min_fade_extent = 20
• kml_lod_max_fade_extent = 20
• kml_link_refresh_mode = onRegion
• kml_latlonaltbox_east = ELongXmax field (create using ArcMap field calculator)
• kml_latlonaltbox_north = NLatYmax field (create using ArcMap field calculator)
• kml_latlonaltbox_south = SLatYmin field (create using ArcMap field calculator)
• kml_latlonaltbox_west = WLongXmin field (create using ArcMap field calculator)
• Examples
  • ccsectsubdfeaturebbox.html will show the subdivisions in Clark County NV. Problem is when you zoom in, features will disappear even though the max level of detail is set to -1 (zoom to infinite). The problem could be 1) the shapefile used to create the kml was a multipart feature (one record for several features, e.g. Hawaii islands), 2) the bounding box was set to the extent of the feature which might be to restrictive. Possible work arounds is to use the bounding box for the entire section, see if this helps.
    • Solution - problem is the bounding box was to restrictive. It is not a good idea to have a bounding box for each and every feature. Better to have a RBNL KML index for an area such as Township Range and Section, which then points to a tiled KML dataset which contains many features. Thus resulting in a smaller RBNL KML index and better organization of the tiled kml features.
  • Problem - when using the random color generator for subdivisions, will have multiple parcels in the subdivision and want to color the same. When there are hundreds of subdivisions, the only solution I can think of is to create multipart features. Need to check if this will work. Example of the problem is the Ten Oaks subdivision at the NW corner of Oakey and Tenaya.
    • Solution - FME and KML will support multipart features. So when using a random color to symbolize polygons, if the Hawaii islands are a multipart feature (one record for many polygons) then FME KMLStyler will use the same color for the Hawaii islands.

Random Color Generator using FME KMLStyler Transformer

• The KMLStyler Transformer can be used to random assign a color to every feature in the GIS layer.
• "Label, Icon, Line, and Polygon colorization support a 'normal' mode, and a 'random' mode. In the normal mode, Google Earth will use the color as specified; in random mode, Google Earth will use the specified color as the maximum value for randomly generating colors. For example, if the color specified is "1,1,1", Google Earth will generate colors that can have any RGB value; if the color specified is "1,0,0", then the generated colors will be limited to various shades of red.
  Opacity values are percentages expressed as floating point numbers. For example, "0.8" corresponds to 80% opacity.
  Scale values are percentages expressed as floating point numbers. For example, "1.6" corresponds to a scaling factor of 160%. If the scale value is set to 0, the element will not be displayed.
  Line width expresses the line width in pixels."
  (See FME Workbench Help - KMLStyler)
• 

Adding a Company Logo as a Screen Overlay

• Step 0: need a company logo (e.g. ExcellenceMgmt.com, logo.png). Ideally the background of the image is transparent.
  • Can use Adobe Photoshop to create a .png file with a transparent background.
  • Option 1: Use the Quick Selection Tool (keyboard shortcut is W, use Shift-W toggle between the Magic Wand Tool). See Adobe Video Workshop - Making and refining selections by Michael Ninness
  • Option 2: Select -> Color Range... to select the background color. Change Fuzziness to 15, Click the Eyedropper Tool and select any part of the background image you want to select, then Edit -> Cut to remove the background, finally File -> Save As... to save the image as .png
  • Option 3: Use the Background Eraser Tool (keyboard shortcut is E, use Shift-E to toggle between the Eraser Tool and the Magic Eraser Tool. Use Window -> Tools to bring up the Tools toolbar.
    • press the [ key on the keyboard to decrease the size of the brush ( [ is the keyboard shortcut)
    • press the ] key on the keyboard to increase the size of the brush ( [ is the keyboard shortcut)
• Step 1: Set the Watermark Name, Snippet Text, and Overlay Icon in the Advanced Parameters of the output dataset in the Navigator pane in FME Workbench. Watermark Overlay Icon can be either a URL to an image or local filename.
• 
• Note, I'm having troubles assigning a logo in FME outside of the Advanced Parameters in the Navigator. That is, I'm unable to assign constant values to the FME elements: kml_icon_href, kml_overlay_xy_x, kml_overlay_xy_y, and kml_screen_xy_x as discussed in FME KML Reader Writer page 32
• A watermark can easily be added or removed the the KML file. For example the evaluation version of FME Workbench will automatically add the FME logo. Just open the doc.kml and remove the following lines of XML.

  • <ScreenOverlay id="kml_1"> <name>Powered By FME</name> <Snippet><![CDATA[Get demos, tutorials, and more at <a href='http://safe.com/kml'>safe.com/kml</a>]]></Snippet> <Icon> <href>images/powered_by_fme_color.png</href> </Icon> <overlayXY x="0" y="0" yunits="fraction" xunits="fraction"/> <screenXY x="0" y="0" yunits="fraction" xunits="fraction"/> </ScreenOverlay>

  • If you just delete the image file which is zipped with the KMZ file, Google Earth will display a large red X to show the image is missing
  • 
• Reference: KML Watermarks

How to Label Features in a KML file

• Use the KML <name> element to display test as a label for an object (Placemark, Folder, or NetworkLink). FME name is kml_name
• Use the FME Workbench AttributeCopier Transformer to copy a GIS attribute field (e.g. SCHNAME) to the New Attribute called kml_name
• "All elements derived from Object can have an id assigned to them. This id is used by the KML update mechanism for files loaded with a NetworkLink. It is also used by shared styles. The id is a standard XML ID." KML Reference
• kml_id will also behave similar to kml_name and will label features, but it is recommended to use kml_name
• There is no label for polygons or lines in Google Earth. Can only label points (i.e. Placemarks).
• Workaround is to add a placemark with no icon (icon scale = 0) and use the placemark's label. Note, will not be able to click the placemark to bring up the Balloon Descriptor if the icon scale is 0 (smallest size I was able to get to work is 0.2). You can still use the ctrl+left mouse click to bring up the Balloon Descriptor for a feature, just note this sometimes behaves radically, where it will bring up the Balloon Descriptor for a feature you didn't click.
• kml_name <name> is the attribute which is used to label points.
• kml_label is used to stylize the kml_name
• See How can I label polygon and polyline kmls in KML Developer Support discussion by ManoM in Google Groups
• Information Point Icons - are a mechanism that allows polygons to be "clickable" within the Google Earth interface. By default, polygons displayed in Google Earth are only selectable via the left-hand navigation tree (or ctrl+left mouse click on the polygon) to bring up the balloon descriptor. If, however, the polygon is part of an aggregate that in turn contains a point geometry element, Google Earth will display an icon that the user can select to pop up the description bubble.
• 

How to Symbolize KML file

• Desired Outcome: 1) symbolize a point shapefile with different marker symbols based on an attribute value in the shapefile database, 2) group features by an attribute value, 3) support hyperlinks and 4) label the point feature
• FME stores the marker symbols in C:\Program Files\FME\icons\*.png and *.bmp
• Step 0: using the Clark County Offsites Point Layer (offstdy0508ll.shp), ensure using geographic coordinates, Lat/Long, WGS84.
• Step 1: use the FME wizard to setup the Published Parameters to convert from shapefile to KML. Then add the AttributeFilter Transformer followed by the KMLStyler Transformer. Assign a symbol and color based on the offsite type (Subdivision, Commercial, Parcel Map):
  • Subdivision (color=yellow)
    • with Full Offsites (symbol = D2 a square)
    • with Grading (symbol = D6 a square with an x in it)
    • with Paving (symbol = D5 a square with a smaller square in it)
  • Commercial (color=red)
    • with Full Offsites (symbol = D2 a square)
    • with Grading (symbol = D6 a square with an x in it)
    • with Paving (symbol = D5 a square with a smaller square in it)
  • Parcel Map (color=brown)
    • with Full Offsites (symbol = D2 a square)
    • with Grading (symbol = D6 a square with an x in it)
    • with Paving (symbol = D5 a square with a smaller square in it)
• Step 2: use Concatenator Transformer to modify the URL
• Step 3: Link attributes to the KML fields of kml_name and kml_description_raw_text
• Solution
  • 
  • Download shapefile and FME workbench (.fmw) dataset: shape2kml21offsitesSymbols.zip

The FME KMLStyler

• list of Google Maps point icons by Fabio Vergani
• Multigeometry - Create both Polygon and Information Point Icon
  • FME KML Doc The KML writer will create an information point icon if the feature has an icon specified. This can be done one of two ways: 1) The icon name of path can be specified as a defined parameter (Feature Type Properties in Workbench) or 2) The icon name or path can be specified using the kml_icon attribute.
  • 
• Transparency
  • fme_fill_opacity is a floating value between 0.0 to 0.1. Will be converted to a kml_polystyle_color attribute, IF there is a corresponding fme_color attribute
  • 
• FME Workspace Transformer which reads an Excel spreadsheet, convert to spatial data and finally write a Google KML file.

Descriptor Balloon

• kml_name - title of the ballon
• kml_description_raw_text - ballon text
• kml_balloon_color (KMLStyler - Balloon Background Color)
• kml_balloon_opacity (KMLStyler - Balloon Background Opacity)
• kml_balloon_text_color (KMLStyler - Balloon Text Color)
• kml_balloon_text_opacity (KMLStyler - Balloon Text Opacity)
• kml_balloon_style_text (KMLStyler - Balloon Text (Plain))
• kml_balloonstyle_raw_text (KMLStyler - Balloon Text (HTML)))
• kml_balloonstyle_bgcolor <BalloonStyle><bgColor>
• kml_balloonstyle_text_color <BalloonStyle><textColor>
• "To prevent the driving directions links from appearing in a balloon, include the <text> element with some content, or with $[description] to substitute the basic Feature <description> (see KML Reference)
• 

KML Extended Data and Hyperlinks

• How to display GIS attributes (fields) in the KML balloon descriptor using FME
• Option 1: FME KML conversion, by default FME converts all the GIS attribute data into an Extended Data - <Schema> <SimpleField> <SimpleData>
  • Problems: how do you control the parsing of URLs, unknown how to create a friendly URL link and keep Google Earth from incorrectly parsing the URL
• Option 2: Extended Data - Untyped Name/Value pairs
  • Step 1: use the Attribute_Creator transformer to create 3 named fields:
    • kml_data{0}.name = HTE
    • kml_data{1}.name = STUDY
    • kml_data{2}.name = URL
    • 
  • Step 2: use the Attribute Copier transformer to assign values from the attribute fields
    • kml_data{0}.value = HTE shapefile attribute field
    • kml_data{1}.value = StudyName shapefile attribute field
    • kml_data{2}.value = URL_DSNET2 shapefile attribute field
    • 
  • Step 2a: if you want to display a friendly name such as UNLV instead of the exposing the entire URL, such as www.unlv.edu, use the concatenator transformer by enclosing the URL in the <A HREF=""></A> tags
    • 
  • Step 3: in the output Feature Type Properties, Format Attribute tab, expose the kml_data{}.name and kml_data{}.value fields. Note will need to type in the value 3 next to kml_data{}.name and .value to expose all 3 elements
    • 
  • The KML Balloon Descriptor should appear similar to this, as displayed in Google Earth Pro
    • 
    • Balloon descriptor with a friendly link name
    • 
  • FME denotes a list structure with curly braces, e.g. kml_data{0} or kml_data{1}
  • Example from Dave Campanas with Safe Software Inc. m_shp2kml21offsites-namevalue2.zip
• Option 3: HTML and kml_description_raw_text
• References: FME and KML Extended Data, Google Developer Guide - Extended Data
• Balloon Descriptor
  • To include To here - From here driving directions in the balloon, use the $[geDirections] tag. To prevent the driving directions links from appearing in a balloon, include the <text> element with some content, or with $[description] to substitute the basic Feature <description>. (see KML Reference - BalloonStyle)

Create Hyperlinks within a KML Balloon Descriptor

• Goal: create a KML file which supports hyperlinks. Using the basic shp to kml conversion in FME, if the URL is rather complex, FME will incorrectly parse the link. Also if you want to display a friendly link name, will need to perform the steps below:
• KML description_raw_text = FME kml_description_raw_text
• Use the FME Workbench Transformer - AttributeCreator to create a new field called kml_description_raw_text (optional step)
• Use the FME Workbench Transformer - Concatenator to append <A HREF> tags to the kml_description_raw_text field
• 
• FME Workbench: shape2kml21offsites.fmw

KML Folders - How to group features

• FME Workbench has 2 options to put features into separate folders
• Option 1 - Fanout Attribute
  • Bring up the Feature Type Properties for the output kml/kmz file by right clicking ... button. Under the General tab, check Fanout by Attribute and then specify a field name you want to use to group the features.
  • 
• Option 2 - Create Folder Features
  • Step 1 - create a destination feature type with the same KML document called Folder
    • 
  • Step 2 - set the KML dataset name to kml_document
    • 
  • Step 3- FME workbench will look similar to this
    • 
  • See FME FAQ - KML Folders

KML Document Name

• Using FME Workbench, on the output KML/KMZ file, set the Root Document name in the Navigator pane -> output kml -> Parameters -> Advanced -> Root Document name:
• 
• References: FME Workbench KML Dataset Structure

Merge KML Files

Merge several kml/kmz files into a single KMZ using Google Earth

• Benefits, if sharing the .kmz file on the internet using the Google Earth Plugin, will only have to load a single KMZ file. Example is schools.html (requires the Google Earth Plugin to be installed). Will show 3 different layers (School Points layer, School Parcels layer, and Major Street in Clark County NV).
• Step 0: add the kml or kmz files to Google Earth
• Step 1: On the Google Earth Places panel, right click Temporary Places and choose Save Places As
• 

Merge several kml/kmz files into a single KMZ using FME Workbench

• If you add multiple KML writer output sources to your workspace, then multiple KML files will be written. Idea is to have a single KML writer but use multiple destinations within it. Using the FME Workbench, Destination Data menu -> Add Feature Type Definition. This will allow one to get multiple features written through a single kml writer and thus a single kml file. Each Destination Data will appear as a separate entry in the Google Earth Navigator panel, but under a single file.
• Step 0: Add multiple GIS files to be converted to a single KML
• Step 1: Add a single Destination Data set, call the output something like all.kmz. Duplicate this for as many GIS files your converting.
• Step 2: Each GIS file will be linked a single copy of the Destination Data set.
• Step 3: In the Feature Type Properties for the Destination Data set, General Tab, enter a Feature Type Name. This will be the Document name displayed in the Google Earth Navigation panel.
• 

Using FME to convert TBC to KML

• Background
• Problem, when using Trimble Business Center (TBC) to convert to Google Earth KML, I do not have the ability to add a COLLADA model to a point. For example, I want to create a KMZ file of trees but each tree is represented by a COLLADA model from the Google 3D Warehouse.
• Input File: HardyRV-PLatLongZD.csv
• FME Workspace: csv2ogckml.fmw
• TBC Export Points File - Lat/Long
• 1. Start FME Workbench, File → New
• 2. Input File
• 
• 3. Export File - OGCKML
• Navigator Pane → Coordinate System LL84
• Navigator Pane → Advanced → Move To Local Coordinate System: No
• output filename extension, use .kmz instead of .kml
• 
• 4. add 2DPointAdder transformer to create gemetry/point features from ASCII points table/file
• Need to use either the 2DPointAdder or the 3DPointAdder
• 
• 5. download COLLADA model from the Google 3D Warehouse
• Google 3D Warehouse Trees by Surreal 3D
• 3D Warehouse - Canary Island Date Palm
• 6. add COLLADA reader
• Readers → Add Reader...
• Move to world coordinate system: No
• 
• 7. FMEpedia - Place 3D COLLADA models at locations associated with input points
• 
• FME Talk - looking for help on getting collada of tree to show up in multiple locations in google earth

FME and SQL Server

Options to load GIS data into SQL Server

• FME from Safe Software
• ESRI
• Shape2SQL by Morten Nielsen

Licensing

• current FME ESRI Edition 2011 SP1, license does not support Microsoft SQL Server Spatial
• need FME Desktop Microsoft SQL Server edition

How to Connect FME to Microsoft SQL Server installed on Godaddy ISP

• FME Workbench → File → New... to open the Create Workspace dialog box/window. Click New → Generate workspace...
• Reader: ESRI Shape
• Writer: Microsoft SQL Server Spatial (MSSQL_SPATIAL) (requires special license)
  • Parameters
  • name of server: gisengineering.db.6396153.hostedresource.com
  • name of database: gisengineering
  • username/login: gisengineering
  • password: Normal1
  • spatial type: geometry (planar vector spatial data) or geography (geodetic vector spatial data)
    • "The geography spatial data type, geography, is implemented as a .NET common language runtime (CLR) data type in SQL Server. This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates." (MSDN SQL Server 2008 R2 - Getting Started with the geography Data Type
    • "The planar spatial data type, geometry, is implemented as a common language runtime (CLR) data type in SQL Server. This type represents data in a Euclidean (flat) coordinate system." (MSDN SQL Server 2008 R2 - Getting Started with the geometry Data Type
  • name of spatial column
  • Table List:
• Documentation
  • http://www.safe.com/microsoft
  • FME Video - Solutions for Databases using Microsoft SQL Server
  • Demo: Loading Data into SQL Server
  • FME Desktop 2011 - Spatial Database Tutorial

SQL Server 2008 Spatial

• Multipart Features is support in SQL Server 2008 and is known as a Geometry Collection.
• "When you define a single object that contains several individual geometries in this way, it is called a Geometry Collection. A Geometry Collection may contain any number of any type of geometries. In the specific case in which a Geometry Collection contains only multiple elements of the same type of geometry, it is referred to as a MultiPoint, MultiLineString, or MultiPolygon geometry." (Beginning Spatial with SQL Server 2008 by Alastair Aitchison, p. 8)
• References
  • Spatial Ed
  • Isaac at MSDN
  • www.sharpgis.net

FME and MySQL

• MySQL Spatial Reader/Writer or download the complete FME Readers and Writers Manual
• FME MySQL FAQ

MySQL Background

• Support
  • MySQL Campus Tour 2009 - Duleepa "Dups" Wijayawardhana (dups@sun.com). Dups on Rails
  • formus.mysql.com (username jefferyjjensen@gmail.com and password is normal)
  • PHP 6 and MySQL 5 by Larry Ullman - Companion Web Site and forum (username jjensen and password normal)
• ESRI Products do not support MySQL (tabular or spatial)
  • Types of Geodatabases Release 9.3
  • System Requirements for ArcSDE
• Autodesk and MySQL
  • MySQL database compatibility with AutoCAD Map 3D
  • The Essential FDO
  • Free Your FDO - Autodesk University 2007
  • Using MySQL with FDO in AutoCAD Map 3D 2008

How to Connect Autodesk Civil 3D to MySQL spatial database

• Step 0: Install a local version of MySQL database
  • My Internet Service Provider - Bluehost.com uses MySQL version 5.0.67, so I will download and install MySQL Community Server 5.0 install Windows Essentials (x86). This will install libmySQL.dll (C:\Program Files\MySQL\MySQL Server 5.0\bin\libmySQL.dll). This works with both Civil 3D 2008 and 2009
• Step 1: check providers.xml includes MySQL snippet
  • C:\Program Files\AutoCAD Civil 3D 2008\FDO\bin\providers.xml

  • <FeatureProvider>
    <Name>OSGeo.MySQL.3.2</Name>
    <DisplayName>OSGeo FDO Provider for MySQL </DisplayName>
    <Description>Read/write access to MySQL-based data store. Supports spatial data types and spatial query operations. </Description>
    <IsManaged>False</IsManaged>
    <Version>3.2.1.0</Version>
    <FeatureDataObjectsVersion>3.2.1.0</FeatureDataObjectsVersion>
    <LibraryPath>MySQLProvider.dll</LibraryPath>
    </FeatureProvider>
    

  • C:\Program Files\AutoCAD Civil 3D 2009\FDO\bin\providers.xml

  • <FeatureProvider>
    <Name>OSGeo.MySQL.3.3</Name>
    <DisplayName>OSGeo FDO Provider for MySQL </DisplayName>
    <Description>Read/write access to MySQL-based data store. Supports spatial data types and spatial query operations. </Description>
    <IsManaged>False</IsManaged>
    <Version>3.3.0.0</Version>
    <FeatureDataObjectsVersion>3.3.0.0</FeatureDataObjectsVersion>
    <LibraryPath>MySQLProvider.dll</LibraryPath>
    

• Step 2: libmysql.dll
  • Civil 3D 2008 - copy libmysql.dll into C:\Program Files\AutoCAD Civil 3D 2008\FDO\bin directory
  • Civil 3D 2009 - copy libmysql.dll into C:\Program Files\AutoCAD Civil 3D 2009\FDO\bin directory
  • FME 2008 comes with libmysql.dll (C:\Program Files\FME\plugins\mysql\libmysql.dll) but this does NOT work with Civil 3D 2008 or 2009. Beta FME 2009 also comes with libmysql.dll According to Robyn Rennie with Safe Software, the version of libmysql.dll included with FME is 4.1.9.0.
  • MySQL GUI Tools 5.0 comes with libmySQL.dll (C:\Program Files\MySQL\MySQL Tools for 5.0\libmySQL.dll). Note this is NOT compatible with Civil 3D 2008 or 2009.
  • MySQL Community Server 5.0 install Windows Essentials (x86). This will install libmySQL.dll (C:\Program Files\MySQL\MySQL Server 5.0\bin\libmySQL.dll). This works with both Civil 3D 2008 and 2009
  • If Civil 3D 2009 is running and you copy the libmysql.dll into the correct directory, FDO should work without having to restart Civil 3D.
• Step 3: AutoCAD map workspace
  • Start AutoCAD Civil 3D 2008
  • type MAPWSPACE, Task Pane On. Then click Data -> Connect to Data
  • Add MySQL Connection
  • Service name: ksalv.com, User Name: ksalvcom_NVSOS, Password: NVSOS1234
• Troubleshooting
  • The specified credentials are not valid or the provider is unable to establish a connection
    • Issue is with the libmysql.dll file not being compatible with Civil 3D and/or bluehost.
    • 

MySQL Load Data Infile

• LOAD DATA [LOCAL] INFILE 'c:/gisdata/filename.csv'; # mysql on windows uses forward slashes for the pathname
• LOAD DATA
  INFILE 'C:/gisdata/crnvsos/2009-01/complete/Corporations.CrprtStc.1.010709132850.txt'
  INTO TABLE nvsos.stocks
  FIELDS TERMINATED BY ','
  /* having problems with this ENCLOSED '"' */
  ESCAPED BY '\\'
  LINES TERMINATED BY '\r\n'
  (col1, col2, col3, @col4)
  SET col4 = str_to_date(@col4, '%m/%d/%Y');
  
• Note: If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r' (Reference: load data)
• when entering the FIELDS parameter from the LOAD DATA, there is no need to repeatly type in the FIELDS for each option. For example:
  BAD LOAD DATA
  
  LOAD DATA INFILE '/tmp/teams.txt'
  INTO TABLE team
  FIELDS TERMINATED BY '\t' # tab is the default delimited for MySQL
  FIELDS OPTIONALLY ENCLOSED BY '"'
  # no need to type FIELDS again FIELDS ESCAPED BY '\\'
  # no need to type FIELDS again LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES
  (Name, ID)
  
  
  GOOD LOAD DATA
  
  LOAD DATA INFILE '/tmp/teams.txt'
  INTO TABLE team
  FIELDS TERMINATED BY '\t' # tab is the default delimited for MySQL
      OPTIONALLY ENCLOSED BY '"'
      ESCAPED BY '\\'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES
  (Name, ID)
  
  see Core MySQL by Leon Atkinson, p. 247
• How to add an autonumber field, such as the record number when importing a text file which doesn't have such a field.
  • when creating the table, add a field with the AUTO_INCREMENT field, then on import, do NOT specify the column in the LOAD DATA INFILE
  • "An auto-increment column intercepts default values and uses sequential numbers in that column. The column must be an integer, and should be unsigned. It must also have an index. Use the AUTO_INCREMENT flag to create an auto-increment column. You may only have one auto-increment column on a table. It's customary to make the auto-increment column the primary key." (Core MySQL by Leon Atkinson, p. 226)
  • see comment from Jay Alverson in MySQL Forums :: Newbie :: LOAD DATA INFILE issue
• Reference: load data
• Problems import int(4) when blank, works if I use varchar(10) for the Corporations table
• Importing Dates
  • Problem: I have a date field in MM/DD/YYYY but MySQL wants its dates in YYYY-MM-DD format.
  • Solution from Jay Alverson - load data date fields, basically import the date as a varchar then add a new field with type DATE.
  • Ramam Pullella also discusses converting strings into dates while loading data using LOAD DATA INFILE.

MySQL Command Line Tool and Scripts

• To run a MySQL script file (.sql) from the mysql> command prompt, use the SOURCE command, for example
  
• MySQL 5.1 Reference Manual - The MySQL Command-Line Tool
• Executing SQL Statements from a Text File

MySQL GUI Tools

• Download MySQL GUI Tools
• Useful to test connection to the MySQL database
• MySQL GUI Tools Manual

MySQL Comments

• "MySQL ignores test after a # or two dashes followed by a space "-- ". MySQL also ignores text inside /* and */. (Core MySQL by Leon Atkinson p. 214)
• SELECT NOW(); # get current time
• SELECT NOW(); -- get current time
• /*
  ** get current time
  */
  SELECT NOW();
• 

MySQL Databases

• CREATE DATABASE [IF NOT EXISTS] name
• CREATE DATABASE nvsos
  • "Databases are simply subdirectories to MySQL, so this command creates an empty directory." (Core MySQL by Leon Atkinson p. 224)
  • square brackets [ ] show otional clauses
  • name is the database name you want to create,
  • Note, SQL statements are case-insensitive
  • 
• DROP DATABASE [IF EXISTS] name;
  • "Use DROP DATABASE to remove a database from the system. All tables and data are permanently removed. The IF EXISTS flag suppresses errors when the database doesn't exist. Databases are implemented as directories containing files for maintaining the data and indexes in tables. A DROP DATABASE statement deletes all files in the directory that are clearly MySQL files. Other files are left. If the directory is empty, it is removed as well. Placing your own file inside the directory prevents it from being deleted. Therefore, it's possible to drop a databse, deleting all tables, but the database itself remains. You can also remove a database completely by deleting its directory on the file system. InnoDB tables must be dropped individually before dropping a database." (Core MySQL by Leon Atkinson p. 236)

MySQL Create and Drop Tables

• CREATE TABLE nvsos.stocks ( /* Columns/Fields */ stockid int(4) unsigned not null, corporationid int(4) unsigned not null, sharecount bigint(8), sharevalue double, /* Indexes */ PRIMARY KEY (stockid), /* this is not working - FOREIGN KEY (corporationid) */ );
• SHOW FULL TABLES FROM db_name; # will return all tables in the db_name MySQL database
• MySQL Data Types
• DESCRIBE NVSOS.STOCKS;
  • will show all the columns in the table
  • 
• DROP TABLE
  • DROP TABLE removes all files related to a table. You may specify multiple tables by separating them with commas. (Core MySQL by Leon Atkinson p. 237)
  • DROP TABLE nvsos.stock;

MySQL variables

• SHOW VARIABLES will return all the MySQL variables
• SHOW VARIABLES LIKE 'max%'; will return only those variables starting with max
• Reference: MySQL 5.0 Reference Manual - 12.5.5. SHOW Syntax

MySQL on bluehost issues

• Bluehost does NOT support LOAD DATA INFILE
  • LOAD DATA INFILE '~/public_html/MYSQLDB/gisdata/CorporationOfficers.csv' REPLACE INTO TABLE gisengin_sosombud.CorporationOfficers FIELDS TERMINATED BY ',' ENCLOSED BY '"';
• How to import and export a MySQL Database from the command line (Bluehost Support Center Article ID 000126)

How to Export MySQL database

• C:\> mysqldump --user=root --password=gisengineering --database=nvsos >nvsosall.sql
  • Had problems with Windows XP and mysqldump, the following worked
  • C:\> "C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump.exe --user=root --password=Normal ccrec >ccrecall.sql
• using Windows Explorer, compress nvsosall.sql (1.1GB) to nvsosall.zip (169MB)
• ftp to ftp.gisengineering.com/gisdata/crnvsos/2009-01/nvsosall.zip
• login to bluehost SSH shell account: unzip nvsosall.zip
• ensure you have a blank database called gisengin_nvsos, if not, bluehost only lets you create MySQL databases from the Control Panel.
• bluehost doesn't allow you to create a database at the command prompt, can only create using the Control Panel. Also can only delete a MySQL database from the Control Panel. The directory where the MySQL database is stored is above the directory you access with SSH login.
  • Comment the following lines in nvsosall.sql by adding a # symbol in front
  • # CREATE DATABASE /*!32312 IF NOT EXISTS*/ `nvsos` /*!40100 DEFAULT CHARACTER SET latin1 */;
  • # USE `nvsos`;
• mysql --user=root --password=gisengineering --database=gisengin_nvsos <nvsosall.sql
  • Bluehost needs gisengin added to the username, for example
    mysql --user=gisengin_root --password=GISJ???1 --database=gisengin_nvsos <nvsosall.sql
• Reference:
  • mysqldump - A Database Backup Program
  • How to import and export a MySQL Database from the command line (Bluehost Support Center Article ID 000126)

Connecting to MySQL installed on Bluehost ISP

• SSH Secure Telnet Shell Method
  • # mysql --user=gisengin_root --password=gisengineering --host=gisengineering.com --database=gisengin_test
  • How do I import an .sql file
    • mysql --user=gisengin_root --password=gisengineering --host=gisengineering.com --database=gisengin_test Notes: it appears you must already create the database you want to import. So if your .sql file was created with mysqldump of the entire database and its tables, then you will have to make the database first before you can import records.
    • How to import and export a MySQL Database from the command line (Bluehost Support Center Article ID 000126)
• Step 0: login to the bluehost cPanel, e.g. using a web browser visit http://www.ksalv.com/cpanel then will be prompted for a username (ksalvcom) and password (Normal)
• Step 1: Under the Databases heading, click MySQL Databases module
• Step 2: Create a New Database
• Step 3: Add New User
• Step 4: Add User to the New Database
• Step 5: at the cPanel homepage, Under the Databases heading, click Remote MySQL module. Enter the WAN IP address in the Add Access Host
  • home: 67.77.42.39
  • UNLV: 131.216.19.%
• Step 6: then click the Customer Profile link, then in the Customer Preferences, will see the MySQL Whitelisted IPs and add the WAN IP address there as well.
  • bluehost Support Center - Whitelist IP Address
• If you don't do these steps, will get the following error message from the MySQL GUI tools:
  Could not connect to the specified instance. MySQL Error Number 2003. Can't connect to MySQL server on 'ksalv.com' (10060). If you want to check the network connection, please click the Ping button
• BlueHost Tutorials - Setting up MySQL Databases

Google and Bluehost Notes

• Name Servers
  • This will forward all web traffic from godaddy.com (or whatever company you have your domain registered with) to Bluehost.com
  • NS1.BLUEHOST.COM
  • NS2.BLUEHOST.COM
  • example GoDaddy.com Name Servers (NS51.DOMAINCONTROL.COM and NS52.DOMAINCONTROL.COM)
• Total DNS Control and MX Records
  • CNAMES (Aliases)
    • mail - ghs.google.com
    • docs - ghs.google.com
    • calendar - ghs.google.com
    • sites - ghs.google.com
    • start - ghs.google.com
• Bluehost IP - 74.220.219.63
• Godaddy IP - 68.178.232.100
• VegasWiFi IP - 66.116.107.3
• Managing Your Domain Names
• Canonical Name (CNAME) see What are zone files and zone records?
• Manage GISEngineering.com Domain on Google, http://www.google.com/a/gisengineering.com, username: jjensen, password: Normal
• Google custom image logo should be PNG or GIF format scaled to 143 x 59 pixels (see How do I change the logo?)
• Edit Google Web Pages at http://pages.google.com/a/www.gisengineering.com
• Google Sites Video Tutorial

MySQL Query Browser - MySQL GUI Tools

• Used to enter and display SQL query results
• Example SQL Query: select * from ksalvcom_NVSOS2008.Corporations where CorporationID between 1000 and 1010
• 
• Inner Joins

  • SELECT * FROM t1.table1 AS DatabaseName.table1 INNER JOIN t2 AS DatabaseName.table2 ON t1.id = t22.id WHERE t2.name = 'somevalue'

  • "When selecting from multiple tables, you must use the dot syntax (table.column) if the tables named in the query have columns with the same name. This is normally the case when dealing with relational databases because a primary key from one table will have the same name as a foreign key in another." (PHP 6 and MySQL 5 by Larry Ullman, p. 173 - Companion Web Site)
• Outer Joins

  • SET SQL_BIG_SELECTS=1;
    SELECT a.*, b.* FROM gisengin_ccrec.instrument a
    LEFT OUTER JOIN gisengin_ccrec.names b ON a.instrno=b.instrno;
    

How to import CSV files into MySQL using FME Workbench

• Step 0: obtain csv file. This demo using the NV Secretary of State database (Corporations.CrprtStc.1.100708123054.txt)
• Step 1: create a MySQL database on bluehost. Database ksalvcom_NVSOSOct2008, username ksalvcom_NVSOS password NVSOS1234 with all privileges
• Step 2: using the FME Workbench wizard, select the .csv file you want to create as a MySQL table (Corporations.CrprtStc.1.100708123054.txt)
  • 
  • 
• Step 3: select output destination format as MySQL Non-spatial. In the settings, specify the host name (if your MySQL database is installed locally, then enter localhost, if its a remote MySQL database, enter the domain name such as ksalv.com)
  • 
  • 
• Step 4: optional. Import Feature Type Definitions. If the fields are already defined in another database, such as Microsoft Access, can just import them.
  • 
  • Then after FME has read the data, will be prompted to navigate to the database which you want to import the table/field definitions from
  • 
  • 
• Step 5: Connect the features
  • Should have green triangle in the FME Workbench, implies its ready to run the translation
  • 
• Notes
  • How do I control if the .csv records will overwrite or append records when the table already exists? It appears, FME will do an overwrite.
  • How

MySQL Questions

• How do you find the number of records in a table?
  • Option 1:

    SELECT COUNT(a.namesid) FROM gisengin_ccrec.names a

    • This should count the number of records in the table. It appears null values are also included in the count, so this is helpful, don't have to worry if your data field contains null values, will get an accurate count.
  • Option 2:

    SHOW TABLE STATUS FROM gisengin_ccrec;

    • "The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count. The Rows value is NULL for tables in the INFORMATION_SCHEMA database."
    • this will list all the tables in the gisengin_ccrec database, type of engine (MyISAM, InnoDB), number of rows and other things.
    • See MySQL 5.1 Reference Manual, 12.5.5.38. SHOW TABLE STATUS syntax
  • Option 3:

    SELECT MAX(a.namesid) FROM gisengin_ccrec.names a

    • If you have an AUTOINCREMENT (AUTONUMBER) field, just list the MAX record to get the total number of records in the table.
• How do you create a template table, that is column/fields are predefined along with the indexes?
• How do I view the column/field properties for a table?
  • SHOW FULL COLUMNS FROM ksalvcom_NVSOSOct2008.Corporations;
  • FULL will display the Comment column/field
  • see MySQL 5.1 Reference Manual - 12.5.5.6 SHOW COLUMNS Syntax
• How do you move tables between databases?
  • "As long as two databases are on the same filesystem, you can use RENAME TABLE to move a table from one database to another."

    RENAME TABLE current_db.tbl_name TO other_db.tbl_name; (see MySQL 5.0 Reference Manual, 12.1.19. RENAME TABLE Syntax)

  • Move table from one db to another on same server - how?
  • 
• How do you setup relationships (one-to-many, enforce referential integrity, ...) in MySQL?
• How do you define a column/field as a foreign key in an existing table?
  • ALTER TABLE ksalvcom_NVSOSOct2008.CorporationActions ADD FOREIGN KEY (CorporationID) REFERENCES ksalvcom_NVSOSOct2008.Corporations(CorporationID);
• How do you define a column/field as a primary key in an existing table?
  • ALTER TABLE ksalv_NVSOSOct2008.CorporationActions ADD CONSTRAINT PK_ActionID PRIMARY KEY (ActionID);
  • I'm not sure what the PK_ActionID is
  • DESCRIBE ksalvcom_NVSOSOct2008.CorporationActions;
    • Use to confirm the table has been altered and the primary key was added
    • "The Key field indicates whether the column is indexed. A value of PRI indicates that the column is part of the table's primary key. UNI indicates that the column is part of a UNIQUE index. The MUL value indicates that multiple occurrences of a given value are allowed within the column.
    • 
• Microsoft Access and MySQL
  • Migrating from Microsoft SQL Server and Access to MySQL by Mike Hillyer with MySQL AB
  • Does MySQL have an autonumber data field similar to Access?
  • MySQL Microsoft Access Forum
• How to Build MySQL on Windows

FME and AutoCAD DWG

• AutoCAD Civil 3D 2010 or newer is NOT supported by FME, see Support for AutoDesk AutoCad Civil 3D 2010 and 2011
• FME and Autodesk AutoCAD Civil 3D
• Creating AutoCAD Blocks with FME
• Autodesk AutoCAD DWG/DWF Reader/Writer or download the complete FME Readers and Writers Manual
• Unlock the Potential of Your AutoCAD Map 3D Object Data - Webinar and watch the recorded webinar
• Geospatial Solutions - Third-Party and Open Source FDO Providers
• SL-King Spatial Solutions - FDO provider for MapGuide and Autodesk Map 3D
• Autodesk University 2008 GV210-1 AutoCAD Map 3D and ESRI ArcGIS Interoperability

FME Geocoding

• I think I need to use the HTTPFetcher to use geocoding services
• ProxixGeocoder need access to a Proxix PxPoint web service, see www.proxix.com but this goes to www.corelogin.com instead. Geocoding from within FME
• Geocoding with FME Using Gisgraphy
• Geocoding: Extracting Value from Non-Spatial Data
• "Geocoding is the process of assigning geographic coordinates to addresses" (see Python Google Geocoder)
• Yahoo! PlaceFinder
• The Google Geocoding API
• ArcGIS North American Address Locator (ArcGIS 10 style)
• CoreLogic ParcelPoint Technology
• Mapping APIs: Bringing Your Spatial Data info Bing, OpenLayers, and Google
• Google Geocoding Utility

FME Geocoding with Google Maps API

1. http://maps.googleapis.com/maps/api/geocode/xml?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&sensor=false
2. Add a Creator Transformer
3. User Parameters → Published Parameters → right click Add Parameter
• Type: Text, Name: BaseURL, Prompt: BaseURL, Default Value: http://maps.googleapis.com/maps/api/geocode/xml?
• Type: Text, Name: Address, Prompt: Address, Default Value: 1600 Amphitheatre Parkway
• Type: Text, Name: City, Prompt: City, Default Value: Mountain View
• Type: Text, Name: State, Prompt: State, Default Value: CA
• Type: Text, Name: Sensor, Prompt: Sensor, Default Value: false
4. Add an AttributeCreator Transformer
• BaseURL: http://maps.googleapis.com/maps/api/geocode/xml?
• Address: 1600 Amphitheatre Parkway
• City: Mountain View
• State: CA
• Sensor: false
5. Add a StringConcatenator Transformer
• Transformer Name: StringConcatenator
• Destination Attribute: GeoCodingURL
• Attribute Value: BaseURL
• Constant: address=
• Attribute Value: Address
• Constant: ,
• Attribute Value: City
• Constant: ,
• Attribute Value: State
• Constant: &sensor=
• Attribute Value: Sensor
6. Reading Complex XML or GML using the XMLFlattener

FME Geocoding with Gisgraphy

1. Create a .csv file with addresses you want to geocode such as geocodeaddresses.csv
   • ID,StreetAddress,City,Country 1,6655 W Sahara Ave,Las Vegas,US 2,2701 S Rainbow Blvd,Las Vegas,US 3,4505 S Maryland Pkwy,Las Vegas,US
2. Download FME Workbench example Geocoding with FME Using Gisgraphy and open in FME Workbench (can just double click the *.fmw file)
3. Readers → Add Reader
   • Format: Comma Separated Value (CSV)
   • Dataset: geocodeaddresses.csv
   • Parameters button
     • check File Has Field Names
   • OK, leave the rest as default
4. Connect the reader to the StringConcatenator
5. Modify the StringConcatenator
   • 
   • change
     String Type = from Attribute Value to Constant
     String Value from BaseURL to http://services.gisgraphy.com/geocoding/geocode?
6. Delete "Parameters to make an address Record" bookmark with the Creator and AttributeCreator transformers
7. Add KML Writer
   • Writers → Add Writer
   • Google Earth KML
   • Dataset: geocodeaddresses.kml
   • OK
   • "The new writer has been added. Would you like to add a new feature type to the writer?" Yes.
   • Feature Type Name: geocodedaddresses on the General tab
   • connect to the AttributeKeeper output transformer
8. Delete the MapInfo writer
9. Run the workspace
   • Having problems, addresses are not being properly geocoded

FME Batch Deploy

• File -> Batch Deploy
• creates a .bat and .tcl translation file, both are required to run batch.
• execute by either typing filename.bat or fme filename.tcl
• Would be nice if FME could be used to create a standalone .exe file which does the translation

FME Shapefile Transformation - Grid to Ground

• Problem: AutoCAD Map3D and Civil3D cannot perform a coordinate transformation when import or connecting (FDO) to Shapefiles which is needed for Grid to Ground transformations.
• Solution: Use FME Workbench and the AffineWarper Transformer
• Step 1: Use FME New Wizard to setup the Source Types and Destination Types as Shapefiles
• Step 2: Insert the AffineWarper transformer. Have the Source shapefile which is in a Grid based Coordinate System (e.g. Stateplane) connect to the Observed stream. Then have the Corrected stream connect to the Destination shapefile which will be a Ground based Coordinate System (i.e. local coordinates such as origin of 5000,5000).
• Step 3: Add a Control Feature stream
  • "Two sets of features must be routed into this transformer. Features entering via the CONTROL port represent the control features used to compute the corrections. Features entering via the OBSERVED port are the features which will be corrected. The modified OBSERVED features are output via the CORRECTED port. Each CONTROL feature represents a control vector whose start point is at some location in the original OBSERVED data space, and whose end oint is at the corresponding location in the desired output data space." (FME 2009 Workbench Help - AffineWarper)
  • Need to create 2 vectors/lines that start at the Grid coordinate system (OBSERVED) and end at the Ground coordinate system (CORRECTED).
  • Use Creator Transformer
  • "Must have at least 4 control points to perform an AFFINE transformation"