Tableau Software
Download
Report
Transcript Tableau Software
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
[email protected]
• Dirk Karis
[email protected]
• Robert Morton [email protected]
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.