FME Server as Geospatial addon for Business Intelligence

FME Server as Geospatial add-on for Business Intelligence Querying Jeffrey Smith GIS Developer Farallon Geographics, Inc. Gerald Gloskin Director, Data Warehouse ISO Innovative Analytics

Business Challenge § ISO Innovative Analytics needs a way to spatially visualize the output from Viador, a non-spatial BI tool. § Viador offers many visualization/reporting widgets, and ISO desires to add a map widget into the environment they are familiar with.

Technical Challenges § The schema of data exported from Viador changes depending on the query. § There are several spatial datasets that data from Viador may need to join with (States, Census Block Groups, etc).

Viador Capabilities to Leverage § Ability to create HTML forms that support querying § Export queries to formats including CSV and XML

Step 1 From a custom HTML form within Viador a query is made and the results are saved as a CSV file onto a network drive (a location accessible by FME Server)

Step 2 From the HTML form within Viador a URL to a custom ASP. NET MVC web application is constructed and opened within a results page

Step 2 -1 The URL contains 5 querystring parameters which will eventually be used as published parameters passed to FME Server Query. String Param Source. Dataset_CSV CSVJoin. Attr Spatial. Join. Att Feature. Type Thematic Description Path to CSV file exported from Viador Attribute in CSV file to use for joining Attribute in SQL Server to use for joining Spatial layer in SQL Server to join CSV with Attribute in CSV to use for thematic rendering Example URL: http: //iiageot 1. iso. com/Main? Source. Dataset_CSV=\equatorrmgCompany_DWP_State_wcolo rs. csv&CSVJoin. Attr=State_Code&Spatial. Join. Attr=state_abbr&Feature. Type=STATES&Thematic=Filing_Status

Step 2 -2 Example CSV file schema

Step 2 -3 In SQL Server we have written a view that unions all of the potential spatial datasets. This creates a single view we can use as a Feature. Type in FME (the Feature. Type querystring param is used as a WHERE clause on the TYPE column) TYPE GEOM JOIN 1 JOIN 2 JOIN 3 … STATES POLYGON CA 4065 6…. STATES POLYGON HI 4061 3…. STATES POLYGON OR 4067 5… CENSUS_BLKGRP POINT 83847888 as 88 fdv …. CENSUS_BLKGRP POINT 99344898 vf 66 wsd … CENSUS_BLKGRP POINT 93900957 hf 87 dds …

Step 3 The custom web application takes these querystring parameters and uses them to build a URL for FME Server. This URL is embedded into the web page that is returned to the clients browser.

Step 4 On load of the webpage we have some Java. Script code that uses the embedded URL to make a request to FME Server

Step 5 FME Server then reads in the CSV file exported from Viador as well as the appropriate spatial data from SQL Server and joins these datasets together in effect spatially enabling the nonspatial data from the CSV file

Step 6 JSON data is returned from FME Server, and then rendered on a map using Open. Layers

The Solution

The Web Application

The Web Application At this point the user can click on features on the map to view a popup with attribute information.

The Web Application Notice that the data is rendered thematically, and the application has a legend in the left corner

The Web Application Data can be exported to KML or Shapefile format

The Web Application Data can be viewed on top of either Google or Bing basemap data

Cool FME Functionality Used § CSV reader’s ability to read in a file with any schema into the translation § Generic writer’s ability to write multiple formats § Generic writer’s ability to handle any schema we throw at it

Thank you! Jeffrey Smith jsmith@fargeo. com Farallon Geographics, Inc. www. fargeo. com 415. 227. 1140 Questions? Gerry Gloskin GGloskin@iso. com ISO Innovative Analytics