Advanced Mapping Techniques Austin Dahl, Dirk Karis, Robert Morton Tableau Software All rights reserved. © 2009 Tableau Software Inc. Agenda • Big Picture • Review of 4.0 Features • Custom Geocoding Geographic Hierarchies Creating new role: Regions Augmenting an existing role: UK Postal Codes Adding a new hierarchy: FIPS Codes • WMS – Web Mapping Service • Leveraging Geospatial Capabilities of Databases All rights reserved. © 2009 Tableau Software Inc. Big Picture Map Latitude, Longitude Place Name Geocoding All rights reserved. © 2009 Tableau Software Inc. Mapping Service Big Picture, Example 47.35° N, 120.88° W Map Washington Geocoding All rights reserved. © 2009 Tableau Software Inc. Mapping Service + Data Map Scatter Plot Geocoding All rights reserved. © 2009 Tableau Software Inc. Mapping Service Demo Basics All rights reserved. © 2009 Tableau Software Inc. Review of 4.0 Features Basic Mapping and Challenges • Creation Double click on any geocoded field to get a map Use Show Me • Resolving Ambiguity • LOD Best Practices Put full hierarchy on LOD e.g. Country, State, Zip • 2008 Conference talk by Austin Dahl has lots more http://conference.tableausoftware.com/2008/sessions/training All rights reserved. © 2009 Tableau Software Inc. Agenda • Big Picture • Review of 4.0 Features • Custom Geocoding Geographic Hierarchies Creating new role: Regions Augmenting an existing role: UK Postal Codes Adding a new hierarchy: FIPS Codes • WMS – Web Mapping Service • Leveraging Geospatial Capabilities of Databases All rights reserved. © 2009 Tableau Software Inc. Custom Geocoding Map Latitude, Longitude Place Name Geocoding All rights reserved. © 2009 Tableau Software Inc. Mapping Service Geographic Hierarchies • Countries have States • States have Counties • Counties have Cities • Cities have Neighborhoods • Neighborhoods have Buildings • Buildings have Rooms • Rooms have Chairs • … • Countries have Area Codes • Area Codes have Exchanges • Exchanges have Numbers All rights reserved. © 2009 Tableau Software Inc. Built In Geocoding Existing Hierarchies Country (Name, FIPS 10, ISO 3166-2, ISO 3166)  CMSA  Area Code  State (Name Abbreviation)  County  Zip Code All rights reserved. © 2009 Tableau Software Inc. Hierarchy Examples C:\Program Files\Tableau\Tableau 5.0\Local\data\ZipCode.tds C:\Program Files\Tableau\Tableau 5.0\Local\data\AreaCode.tds Country State Zip Code Country Area Code All rights reserved. © 2009 Tableau Software Inc. Custom Geocoding • What you can do Add new hierarchies e.g. Sales Regions, Airports, Store Locations Add new sub-levels to existing hierarchies e.g. Cities Add new locations to an existing level e.g. UK Postal Codes • What you cannot do Put existing levels under your new levels Add additional alternate names to existing levels Change the location for an existing entry • But, you can duplicate existing levels and do all of the above All rights reserved. © 2009 Tableau Software Inc. What You Need • CSV File with: • Unique ID (no duplicates) • Optional: IDs of containing levels (a.k.a. foreign key) • Latitude • Longitude • Optional: Schema.ini file • IDs should be Text • Even numeric fields like zip code • Latitude and Longitude should be Double See http://msdn.microsoft.com/enus/library/ms709353(VS.85).aspx for details on Schema.ini All rights reserved. © 2009 Tableau Software Inc. Simple Geocoding Little League World Series All rights reserved. © 2009 Tableau Software Inc. LLWS Region Encoding CSV File All rights reserved. © 2009 Tableau Software Inc. Two Locations SELECT [LLWS#csv].[Runner-Up Region] AS [Runner-Up Region], [LLWS#csv].[Runner-Up Score] AS [Runner-Up Score], [LLWS#csv].[Winner Region] AS [Winner Region], [LLWS#csv].[Winner Score] AS [Winner Score], [LLWS#csv].[Year] AS [Year] FROM [LLWS#csv] All rights reserved. © 2009 Tableau Software Inc. Two Locations SELECT [LLWS#csv].[Runner-Up Region] AS [Region], [LLWS#csv].[Runner-Up Score] AS [Score], [LLWS#csv].[Year] AS [Year], 'Runner-Up' AS [Winner - Runner-Up] FROM [LLWS#csv] UNION SELECT [LLWS#csv].[Winner Region] AS [Region], [LLWS#csv].[Winner Score] AS [Score], [LLWS#csv].[Year] AS [Year], 'Winner' AS [Winner - Runner-Up] FROM [LLWS#csv] All rights reserved. © 2009 Tableau Software Inc. Augmenting the Built In Geocoding UK Postal Codes All rights reserved. © 2009 Tableau Software Inc. UK Postal Codes Clean Up All rights reserved. © 2009 Tableau Software Inc. UK Postal Codes Schema.ini All rights reserved. © 2009 Tableau Software Inc. Adding Geocoding Hierarchies FIPS Codes Desired Hierarchy Country (Name, FIPS 10)  FIPS State (Abbreviation, 2 Digits) FIPS County (3 Digits, 5 Digits) All rights reserved. © 2009 Tableau Software Inc. Adding Geocoding Hierarchies FIPS Geocoding Files FIPS State All rights reserved. © 2009 Tableau Software Inc. FIPS County Adding Geocoding Hierarchies FIPS Schema.ini All rights reserved. © 2009 Tableau Software Inc. Custom Geocoding Best Practices • Clean the data Remove blank or duplicate rows Check the data types Format for leading zeros if needed • Use Schema.ini to enforce types and column names • Store in a common directory Use subfolders for different locations or roles Keep your CSV files • Order the fields with the most readable name first • Use Tableau to connect to it as a sanity check All rights reserved. © 2009 Tableau Software Inc. Sharing Your Custom Geocoding • One-off: Save to TWBX and send Import from TWBX • Group Wide One person imports everything Distribute My Tableau Repository\Local Data (Advanced) It is possible to have TDS files in Local Data reference in-house databases • On Tableau Server Publish and custom geocoding will get sent if needed. All rights reserved. © 2009 Tableau Software Inc. Agenda • Big Picture • Review of 4.0 Features • Custom Geocoding Geographic Hierarchies Creating new role: Regions Augmenting an existing role: UK Postal Codes Adding a new hierarchy: FIPS Codes • WMS – Web Mapping Service • Leveraging Geospatial Capabilities of Databases All rights reserved. © 2009 Tableau Software Inc. WMS Map Latitude, Longitude Place Name Geocoding All rights reserved. © 2009 Tableau Software Inc. Mapping Service WMS Basics 1 2 3 All rights reserved. © 2009 Tableau Software Inc. WMS Examples http://wms.jpl.nasa.gov/wms.cgi? All rights reserved. © 2009 Tableau Software Inc. WMS Examples http://apps1.gdr.nrcan.gc.ca/cgi-bin/worldmin_en-ca_ows All rights reserved. © 2009 Tableau Software Inc. WMS Examples http://wms1.ccgis.de/cgibin/mapserv?map=/data/umn/germany/germany.map&& All rights reserved. © 2009 Tableau Software Inc. Agenda • Big Picture • Review of 4.0 Features • Custom Geocoding Geographic Hierarchies Creating new role: Regions Augmenting an existing role: UK Postal Codes Adding a new hierarchy: FIPS Codes • WMS – Web Mapping Service • Leveraging Geospatial Capabilities of Databases All rights reserved. © 2009 Tableau Software Inc. Tapping Into Geospatial Databases • Contains • Intersection • Distance AllAll rights reserved. ©© 2009 Tableau Software Inc. rights reserved. 2009 Tableau Software Inc. Summary • Custom Geocoding Add your locations, augment Tableau locations • WMS In house via ArcGIS and others; numerous public servers Different layers than the Tableau built-in • Geospatial Databases Calculations of Distance, Intersection, Containment Geographic Types All rights reserved. © 2009 Tableau Software Inc. Questions ? • Austin Dahl adahl@tableausoftware.com • Dirk Karis dkaris@tableausoftware.com • Robert Morton rmorton@tableausoftware.com All rights reserved. © 2009 Tableau Software Inc. Resources • Geospatial Data Sets National Atlas - http://www.nationalatlas.gov/atlasftp.html NOAA - http://csc-s-maps-q.csc.noaa.gov/hurricanes/download.jsp • Upload Shapefiles to SQL Server 2008 Shape2SQL - http://www.sharpgis.net/page/SQL-Server-2008Spatial-Tools.aspx • Edit and Manipulate Shapefiles OpenJUMP - http://www.openjump.org/ All rights reserved. © 2009 Tableau Software Inc.