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 Bayne's photo
Senior Sales Engineer
Trey works to enable partners and guide the future development of QlikMaps. Trey has worked with Qlik for nearly a decade. See more from Trey on Twitter (www.twitter.com/QVgorilla) and on his blog, Unconventional QlikView (http://unconventionalqlikview.tumblr.com).

 

Contact Us

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