Wrap All Your Points In A Bin

With the magic of the Qlik Sense editor, you can wrap your large number of points in a bin to better consume the data. 

At QlikMaps, we’ve been hearing a lot more questions about bins on a map. I’d like to spend some time discussing what we’ve learned, but let’s make sure we are all on the same page and know what bins are.

The topic of bins usually comes up shortly after somebody says something like:

“I need to map 150,000 points on map and the native Sense map can’t do it. Can QlikMaps?”

Well, that question comes with a lot potential answers but there’s only one right one…

You are asking the wrong question.

Too many points on a map break the insight possible through the visualization. Look at the example below.

 

The map shows 500,000 bridges in the US but you only see a small portion of them. Why? At this zoom level points sit on top of points, hiding many locations under the surface. This problem disappears as you zoom, but how do you know what areas are worth exploring? The deluge of points is simply too much information for the visualization.

Fortunately, there is a better way.

Binning is a technique that telecom has been using for a long time. The idea is to roll up many points to an area and color code the area based on the density of points it represents. The source points are not lost, but hidden behind the Bins until a zoom level is reached where individual points are distinguishable.

Take a look at the image below.

Using the same 500,000 data points, I have created square bins that better represent the points.

In this map, no shapes overlap and all points have representation in the visualization by shaping the color of each square. The higher the density of points, the darker the color. This makes identifying high density areas that need to be drilled into much simpler to identify.

So, how do you make Bins?

In the past, we at QlikMaps would suggest you use an outside tool like we did in this video.

But now the QlikMaps development team made the process much simpler. It turns out that creating square bins is a simple matter of math that can be done in the Qlik Sense Script Editor. I’ve included the script below but a .qvf is also available on my Google Drive folder.

 

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

/////////////////////////// TAB //////////////////////////////////

/*

This script takes a table with lat and long fields and:

1. Builds a WKT string that creates square bins over all areas where a lat/long is located
2. Identifies which lat/long points fall inside the created square bins.

Why would you do this?

When working with a large number of points, it is often difficult to pull meaningful analysis from a map due to overlapping points. Binning allows you to aggregate points into geographical areas based on proximity instead of geographical territories (States, Zips, Province, etc.).

For a video showing how binning can benefit mapping needs, see QlikMaps in action at https://vimeo.com/157730654

How do I use this script?

1. Place the load script for the data source containing your lat and longs in the User Data folder. An inline table has been placed there as an example.

2. Update the vDimTable, vDimID, vDimLat and vDimLng variables on the User Variables tab.

3. Review the vLatInc and vLngInc variabls on the User Variables tab.

4. Click reload.

*/

/////////////////////////// TAB //////////////////////////////////

/*
//////////////////////////////////////////////

Place your load script below. The Inline table below just serves an example.

  //////////////////////////////////////////////
*/

Salesperson:
LOAD * INLINE [
SalespersonName, SalespersonLat, SalespersonLng, Zip, County
Andrea Hayes, 32.32215129, -95.28064553, 75701, SMITH
Anne Burke, 32.19426342, -95.40404025, 75762, SMITH
Cheryl Shaw, 32.30869378, -95.38088927, 75709, SMITH
Denise Williamson, 32.34085688, -95.31485059, 75701, SMITH
Douglas Davis, 32.32629003, -95.17029457, 75707, SMITH
Emily Watson, 32.44102106, -95.31201215, 75706, SMITH
Evelyn Spencer, 32.19883143, -95.43494675, 75762, SMITH
Harold Bowman, 32.29225023, -95.1914807, 75707, SMITH
Jason Williams, 32.23493317, -95.33442338, 75703, SMITH
Jerry Cook, 32.24311323, -95.31968725, 75703, SMITH
Jimmy Washington, 32.29360826, -95.16187958, 75707, SMITH
Johnny Lawrence, 32.21145182, -95.23770333, 75791, SMITH
Julia Coleman, 32.36870552, -95.31960172, 75702, SMITH
Lawrence Hart, 32.1168258, -95.35333511, 75757, SMITH
Lawrence Ramirez, 32.32289032, -95.39034168, 75709, SMITH
Louis Morris, 32.42278599, -95.45248748, 75704, SMITH
Michael Miller, 32.23873407, -95.33297743, 75703, SMITH
Norma Crawford, 32.32864232, -95.31006406, 75701, SMITH
Roger Webb, 32.39243832, -95.23533429, 75708, SMITH
Roy Willis, 32.39216682, -95.19966536, 75708, SMITH
Ruth Powell, 32.13585839, -95.34402054, 75757, SMITH
Sarah Young, 32.48159757, -95.28838903, 75706, SMITH
Teresa Gonzalez, 32.32054783, -95.37549644, 75709, SMITH
Willie Jackson, 32.43941297, -95.22809598, 75708, SMITH
];

/////////////////////////// TAB //////////////////////////////////

/*
//////////////////////////////////////////////

  Variables below set provide information about your lat/long table. Complete the information below. Then, paste the load script for your lat/long table in the User Data tab.

  //////////////////////////////////////////////
*/

Set vDimTable = 'Salesperson'; // Name of the table with your Lat/Longs
Set vDimID = 'SalespersonName'; // Name of the identifier in your Lat/Long table
Set vDimLat = 'SalespersonLat'; // Lattitude field in Lat/Long table
Set vDimLng = 'SalespersonLng'; // Longitude field in Lat/Long table

/*
//////////////////////////////////////////////

Variables below set the size of the Bins. Standard size of .3 for both vLatInc and vLngInc are average sizes for the US. Larger numbers can be used to create larger squares suitable for larger areas. Smaller numbers can be used to create smaller squares suitable for smaller areas.

  //////////////////////////////////////////////
*/

Let vLatInc = .3;
Let vLngInc = .3;

/////////////////////////// TAB //////////////////////////////////

/////////////////////////////////////////////////////
/////////////////////////////////////////////////////

/*
//////////////////////////////////////////////

Clean up User Data table and move to variable based names to make script more portable.

  //////////////////////////////////////////////
*/

NoConcatenate

coord:
Load
$(vDimID),
Floor($(vDimLat), $(vLatInc)) as binLat, // flooring lat to set vertical height of bin
Floor($(vDimLng), $(vLngInc)) as binLng, // flooring long to set horizontal width of bin
Floor($(vDimLat), $(vLatInc))
& '|' &
Floor($(vDimLng), $(vLngInc)) as binId // provide a unique ID for the bin
Resident $(vDimTable);

Drop Table $(vDimTable); // source table is no longer necessary

/////////////////////////////////////////////////////
/////////////////////////////////////////////////////

/*
//////////////////////////////////////////////

Sets the range for which to build bins. By default the range is based on the lat/longs provided from the tabel under the User Data tab.

If a different range is required, you may substitute in a lat and long in the max and min in the coordRange table. Do not do this unless you know exactly what you are doing.

  //////////////////////////////////////////////
*/

coordRange:
Load
Min(binLat) as minLat, // southwest lat
Min(binLng) as minLng, // southwest lng
Max(binLat)+$(vLatInc) as maxLat, // northeast lat
Max(binLng)+$(vLngInc) as maxLng // northeast lng
Resident coord;

/*
// Variables below pull from the coordRange table.  //
// Do not change variables, but instead replace //
// coordRange table if different range is required. //
*/

Let vSeedLat = Peek('minLat');
Let vSeedLng = Peek('minLng');
Let vLatRange = Peek('maxLat') - Peek('minLat');
Let vLngRange = Peek('maxLng') - Peek('minLng');
Let vLatRangeCount = $(vLatRange) / $(vLatInc);
Let vLngRangeCount = $(vLngRange) / $(vLngInc);

/////////////////////////////////////////////////////
/////////////////////////////////////////////////////

/*
//////////////////////////////////////////////

  Build the 4 points that make the corners of
the bins based on information provided above.

  Do not change anything below this unless you
know exactly what you are doing.

  //////////////////////////////////////////////
*/

binCoords:
Load
RecNo() as latIdx,
$(vSeedLat) + (RecNo()-1) * $(vLatInc) as binLat,
$(vSeedLat) + RecNo() * $(vLatInc) as binLatN
AutoGenerate $(vLatRangeCount);

OUTER JOIN (binCoords)

Load
RecNo() as lngIdx,
$(vSeedLng) + (RecNo()-1) * $(vLngInc) as binLng,
$(vSeedLng) + RecNo() * $(vLngInc) as binLngE
AutoGenerate $(vLngRangeCount);

/////////////////////////////////////////////////////
/////////////////////////////////////////////////////

/*
//////////////////////////////////////////////

  Build WKT string and associated back to user
data table.

  //////////////////////////////////////////////
*/

Bins:
Load
binLat & '|' & binLng as binId,
'POLYGON((' &
binLng  & ' ' & binLat  & ', ' &
binLngE & ' ' & binLat  & ', ' &
binLngE & ' ' & binLatN & ', ' &
binLng  & ' ' & binLatN &
'))' as binWKT
Resident binCoords
Where
Exists(binId, binLat & '|' & binLng);

/////////////////////////////////////////////////////
/////////////////////////////////////////////////////

/*
//////////////////////////////////////////////

 Clean up

  //////////////////////////////////////////////
*/
Drop Tables coordRange, binCoords;

 

 

This article originally appeared on the Unconventional QlikView blog. 

Trey is a Senior Sales Engineer at Analytics8. He specializes in application development in a variety of business applications, including QlikView, Qlik Sense, AWS and SharePoint. He's very interested in projects that he can carry from solution research to a business-changing product.

 

Contact Us

National Office Telephone | Mon-Fri 8:30am-5:30pm CT