Introduction

One of the biggest challenges for business intelligence developers is creating custom regions. Today, there are limited options for quickly and easily creating these custom regions. Using an open source GIS tool, Quantum GIS, we have created our own plugin to automate the region generation process. This tutorial will explain how to create your own custom regions.

Install Quantum GIS

(version 1.8 or higher)

Quantum GIS is an open source GIS (geographic information system) desktop tool viewing and editing geospatial data. The same way you use Photoshop for editing photos, GIS tools like Quantum GIS or ESRI ArcGIS allow you to accomplish a variety of tasks related to spatial data. Because QGIS is an open source initiative, there is no cost. You will need to install QGIS on your desktop only. No server software is required.

Downlaod Site. http://www.qgis.org/

Not familiar with Shapefiles? Here is some reading to get you up to speed: Working with Shapedata

Install CMaps Region Generator

STEP 1. Open QuantumGIS

STEP 2. Open the “Plugins” menu and click “Manage Plugins”

STEP 3. From the list, select “CMaps Region Generator”

STEP 4. Press Okay.

That’s it! You now have CMaps Region Generator available for use in the “Plugins” menu

CMaps Analytics Region Generator is an open source project and has been committed to the official QuantumGIS repository for anyone to obtain and contribute to.

FOR TABLEAU USERS

Access Region Generator Experimental Version which includes Tableau export format

When we post new versions of Region Generator we first release it as an experimental version for public beta / QA testing from the community. Once we have reached a threshold with no reported defects, we release into the public repository.

HOW TO ACCESS EXPERIMENTAL RELEASES:

In Quantum GIS, go to Plugins > Manage and Install Plugins.

Click on Settings

Check Show also experimental plugins

Now, you will see new CMaps Region Generator Plugins when available.

Data Evaluation and Preparation

1. Obtain definitions for your Custom Regions 

To create custom regions you need a 2 column Excel / CSV file containing your custom regions, and the administrative, geopolitical, or other standard boundaries  (countries, state, zip, etc). that belong to that region. This is a critical item to map and merge these standard geographies to your custom regions.

EXAMPLE: If your regions are a combination of states, you need a list of all states and the regions they belong to.

Download sample query result (State regions).

Non-standard administrative areas:

In some cases, regions are territories can be composed in a way where there is no standard administrative area (state, county, etc). For example, what if California is broken into north and south? These exceptions are easily managed in QGIS through various drawing and splitting tools. IF there are many exceptions where you have to manually edit your non-standard administrative areas it could increase the effort.

2. Obtain the Shapefile that you will Merge

You will need to download the shapefiles for the standard geography (country, state, zip, etc) that you can merge into your custom regions. Included with the shapefiles is a DBF (Database File), SHP (shapefile), and SHX.

Not familiar with Shapefiles? Here is some reading to get you up to speed: Working with Shapedata

Need Shapefiles not on our list? Contact us for access to premium data like zipcodes

3. Matching Administrative Areas in your Data with Shapefiles

Your business data should serve as primary key that defines how you merge your shapes.. In other words, if your BI data warehouse identifies “United States” and the Shapefile lists  “United States of America” you will need to modify the Shapefile to match your business data.

The good news is CMaps Analytics Region Generator latest experimental version (see install instructions) will now automatically identify locations from your business data that do not match with the Shapefile. This simplifies the process to modify your shapefile to fit your business data.

In future version we will automate this process for you, so stay tuned for new versions of region generator.

Create your Region Definition File

Prior to running CMaps Region Creator, you need to have your region definition file ready ( explained on #1 on the previous tab). This is a 2 column CSV (comma separated file).

Column 1: Your Custom Regions

A list of your organization’s regions or territories in your business data. This label should originate from your business data source (database) and can be what ever values you like.

Column 2: Standard Geography

A list of all locations that you are going to merge. These are the individual administrative areas (states, counties, zips). These are locations that are present inside of your Shapefile DBF (database file).

Notes on Region Definition File:

• To keep track, make sure you include the column title on the first row of your Excel / CSV file
• If your file contains locations that do not exist, they will be ignored.
• If you are missing locations that are inside of your shapefile, they will not be included in the final output region file.
• Your file should only have 2 columns of data.

With your data prepared, you are now ready to run CMaps Region Creator.

Run CMaps Region Creator

With Quantum GIS Open, CMaps Region Creator installed (view instructions), and your Region Definition File created (view instructions), you can run CMaps Region Creator Region generator.

1. Import a shapefile onto the canvas.

Select Layer>Add Vector Layer or click on the “Add Layer” icon  (V1)   (V2)

Do you need shapefiles to start with? Click here for our shape data repository

2. Click Plugins>CMaps Region Generator>CMaps Region Generator

3. With the Custom Region Generator window open you will enter the following information:

A: Shapefile Attribute for Merging: From your shapefile, you will have multiple columns of data from your DBF file. You need to choose the correct attribute (DBF file column) also mapped inside of your region definition file. Inside CMaps Region Creator, it will display the attribute name and the-the first record value. In this example, we chose the state name attribute. This indicates that the first column in our region definition file contains matching state names (highlighted in orange below)

B: Region Definition: Click “browse” to select the Region Definition File from your local file system.

C: First Row Contains Header: This property should be checked if your Region Definition CSV file contains row titles.

D: Standard Geography Column: This is the column that corresponds to the Shapefile Attribute for Merging (shown in orange below)

E: Your Region Definition Column: These are your custom regions and territories that map to standard geography. (shown in light green below)

F: Save New Region Output to: Click “browse” to select the file name and location for your new Shapefile.

G: Output File Prefix: Click “browse” to select the file name and location for your new files.

H: Load into canvas when finished: This property should be checked when you want to immediately display the newly created shapefile in Quantum GIS. If un-checked, you must manually import the shapefile to view it in Quantum GIS.

3. Click OK to generate your new custom shapefile

A new Shapefile (.SHP), Database File (.DBF), and SHX file will be generated, along with a CSV file and GeoJSON with attributes, and GeoJSON without attributes. Product specific output like Tableau will be prefixed accordingly.

Where to Use CMaps Analytics Region Generator Output

CMaps Analytics Region Generator outputs multiple formats for use with any GIS or mapping solution that accepts ESRI Shapefiles or GeoJSON. The following links illustrate how to use the output with various CMaps Analytics solutions:

CMaps Analytics Designer
Antivia DecisionPoint
CMaps Plugin for SAP Dashboards
CMaps Extension SAP Web Intelligence
CMaps Extension for SAP Lumira
SAP Design Studio
Tableau
Qlik
CartoDB
More tutorials coming soon!

Troubleshooting

The following is a list of known causes for CMaps Region Generator to possibly throw errors in the current version. If you encounter an error, for Centigon Solutions support to troubleshoot and resolve, we may require access to your shapefiles to check for errors.

1. Make sure your region names are in the second column and not the first within your region definition CSV. The first column should contain attribute names inside of your shapefile.

2. If you do not have header rows, make sure to un-check “First Row Contains Header”

3. Make sure that the CSV file region definition is not open inside of Excel.

4. Make sure you have valid attribute selected that is inside of your region definition CSV.

Resources

Working with Geo-Data

Tech-Tuesday: Creating and Analyzing your own regions

Free Shapefiles

Created in Collaboration with Lutra Consulting

CMaps Analytics Region Creator was built as a collaborative effort with Lutra Consulting. Lutra Consulting’s staff have delivered innovative solutions to their clients’ projects in the UK water engineering sector using their solid background in hydraulics and knowledge of software development and GIS. If you have GIS development projects, or need extensions built, the Lutra Consulting team is available to assist you. Learn more about Lutra Consulting