This is a cross post from
my SCN document on the same subject. I intend to add some more "experimental" :) features on this page as I go along and more "stable" ones on the SCN site.
After following this blog post the data from the US Census quickfacts
site can be shown on a custom US Counties Choropleth map in SAP Lumira.
An example map is here. It is based on the free personal edition of SAP
Lumira which can be
downloaded for free from this page.
Background
The custom US Counties map Lumira extension is based on the following
1) Document from Manfred Schwarz
Lumira Geoextension with datamaps.js and topojson.js
, however no datamaps.js is used in this custom map. The great work by
Manfred in regards to setting up require.js to use D3.v3 and topojson
was used in this extension.
2) The custom US counties map is based on the same code found in a blog by Robert Russell (me:))
Custom Choropleth Maps with VizPacker
The custom topojson map of the US counties is based on an original county map found here
http://nationalatlas.gov/mld/countyp.html No restrictions have been placed on the original US counties map
TOPOJSON was used to convert the map and simplify it.
Version Information
Install Vizpacker extension
Download the extension from the link provided “Version Information” section above.
1) Quit SAP Lumira to install the extension.
2) Extract the downloaded file into the Lumira extensions folder (administrator authorisation is required)
C:\Program Files\SAP Lumira\Desktop\extensions
After extracting the file you should have the following folder
C:\Program Files\SAP Lumira\Desktop\extensions\bundles\viz\ext\rjruscountiesv1\
With the following files required for the US counties choropleth map
- d3.v3.js
- domReady.js
- rjruscountiesv1-bundle.js
- topojson.v1.min.js
Download US Census QuickFacts Data
Download the following files from the quickfacts census site
http://quickfacts.census.gov/qfd/download_data.html
- DataSet.txt
- DataDict.txt
- FIPS_CountyName.txt
Download all files to a local drive.
There
is a requirement to convert the data into excel files. This is required
to ensure that leading zeros are not dropped from the FIPS county
codes.
On the quickfacts download page there is a description of
how to format the data. Some screenshots are below to highlight the
necessary steps for the FIPS data and variations found in loading the
data.
1) Convert the DataSet.txt to an Excel format
Essential to format the FIPS column as
Text as this is to prevent the leading 0 being dropped from some of the county FIP codes.
Save as a new Excel file
2) Convert the DataDict.txt file to an Excel format
Following options taken when splitting the DataDict file which is different to how it is described on the download page.
Save as a new Excel file
3) Convert the FIPS_CountyName.txt file to an Excel format
Again it is important to load the FIPS data as follows
Essential to use Text as column name as shown.
Split the United States Column (Column B)
Select only Column B select Data and split into Columns as shown below
ADD column header names
Save as new Excel file
Format Data
Insert two rows at the top of the new Excel file
DataSet.xslx
Follow the section to add descriptive headings to the DataSet file on the download page.
“Copy headings from DataDict to the top of the DataSet worksheet”
The final DataSet.xslx file should look like this.
Save the
DataSet.xslx file and the rest of the work is in Lumira.
Lumira will be used to add the county names as follows.
Load the FIPS_CountyName.xslx file and DataSet.xslx and take the option to merge the data.
The result should be a 100% match on the FIPS columns in both files.
Add
a filter to exclude the “blank” States in the State CODE column, so
only US counties will be available in Lumira visualisations.
Select the Custom chart “
US Counties – SCN demo” as shown below.
Select Measure required and the
FIPS dimension as per the example shown below.
