Wrap All Your Points In A Bin

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.