New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Give Users Map & Legend Binning Control (enhancement) #9208

Open
MarcGJA opened this Issue Jan 3, 2019 · 0 comments

Comments

Projects
None yet
2 participants
@MarcGJA
Copy link

MarcGJA commented Jan 3, 2019

Forego one-size-fits-all Map & Legend Binning and give developers/users the ability to define Map & Legend Bin-size themselves for as many Bins as desired. For SQL based Questions it is doable.

In a simple reference map, for example, allow developers/users to set Bin-size =1 and associate the field representing sub-geographies to a secondary numerical or descriptive field allowing as many Legend categories as there are unique values in the secondary field, e.g. sales territories, Congressional districts, etc.

In a typical map with a range of values, for example, allow developers/users to define Bin-size as a filter variable of type Number or Field Filter just as is currently done for an SQL based Question letting the SQL determine the number of Legend categories. The developer sets up the DB, SQL, & Filter details and the end-user simply types in or selects a Maximum, Minimum, and Bin-size of their choice. Flexible, clean, and very end-user friendly as shown in the sample Histogram & accompanying SQL below that is entirely adaptable to Map Questions once the developer is allowed to make the logical data associations:

JPEG see Metabase Discussion


DECLARE -- THE FOLLOWING IS WRITTEN FOR SQL SERVER AND CAN BE ADAPTED TO POSTGRESQL, MYSQL, ETC.
@C_CURRENT SMALLINT =(SELECT [CURRENT] FROM [database].[dbo].[YEAR]), -- SINGLE RECORD CONTROL TABLE
@BIN INT = 10, -- INITIAL BIN SIZE VALUE
@MinPrice REAL= 0,
@MaxPrice REAL = 0,
@T [dbo].[Table] -- USER DEFINED TABLE TYPE

DELETE FROM @T -- TABLE
INSERT INTO @T (ID, PRICE, QUANTITY)
SELECT ID, PRICE, QUANTITY
FROM [database].[view].[TableView] WITH (NOEXPAND INDEX ([IX_UserDefinedIndex]))
WHERE REPORT_YEAR=@C_CURRENT
AND PRICE>=(CASE WHEN {{MNP}} = 0 THEN PRICE ELSE {{MNP}} END) -- MINIMUM PRICE VARIABLE
AND PRICE<=(CASE WHEN {{MXP}} = 0 THEN PRICE ELSE {{MXP}} END) -- MAXIMUM PRICE VARIABLE
ORDER BY PRICE ASC
SET @MaxPrice=CEILING((SELECT MAX(PRICE) MAXPRICE FROM @T))+1 -- CEILING ROUNDS UP FROM ANY DECIMAL AND ADDS 1 SO THE INEQUALTY DOESN'T ACTUALLY END UP EXCLUDING THE LOCAL MAXIMUM PRICE ITSELF
SET @BIN=(CASE WHEN {{BIN}} = 0 THEN [database].[dbo].[fPrcCat] (@MinPrice, @MaxPrice) ELSE {{BIN}} END) -- IF BIN SIZE VARIABLE =0 THEN SET BIN SIZE TO USER DEFINED FUNCTION WITH MINIMUM & MAXIMUM PRICE INPUTS ELSE BIN VARIABLE
SELECT
'$'+CAST(L AS VARCHAR(10))+'-'+CAST(U AS VARCHAR(10)) PRICE_CATEGORY, -- X-AXIS LABELS CORRESPONDING TO A SET OF SUCCESSIVE BINS SPELLED OUT IN TEXT FORM THAT ACT AS A UNIQUE ID FIELD FOR THE FINAL DATASET
ROUND(SUM(QUANTITY),1) QUANTITY -- ROUNDING NECESSARY OTHERWISE FALSE SERIES OF DECIMALS APPEAR IN THE RESULTS
FROM
(
SELECT T.PRICE, T.QUANTITY, -- QUANTITY DATATYPE 'REAL' ROUNDED AS NESTED QUERIES OTHERWISE INTERPRET MORE DECIMAL PLACES THAN ACTUALLY EXIST
@BINFLOOR(PRICE/@BIN) L, -- LOWER BOUNDARY i.e. FROM, INCLUSIVE
@BIN
FLOOR(PRICE/@BIN)+@BIN U -- UPPER BOUNDARY, i.e. TO, EXCLUSIVE
FROM @T T
) T
GROUP BY L, '$'+CAST(L AS VARCHAR(10))+'-'+CAST(U AS VARCHAR(10))
ORDER BY L ASC

Bottom line, let developers/users define Map & Legend Bin-size themselves for as many Bins as desired and solve several issues at the same time:

  1. restrictive Bins -- 5, 10, 15, and perhaps even up to 20 bins is insufficient,
  2. arbitrary Bin size -- predetermined Legend category bookends are painfully awkward, and
  3. the current Binning regime obliges Metabase to maintain Binning code/algorithms that do not currently work as intended, cannot satisfy every use case, and lock users into a frustratingly rigid box not of their own choosing:

Map Legend Binning Incorrect #7486 (April 2018)
Map & Legend Mismatch (April 2018)
Customising bucket ranges in Map Visualization? (January 2018)
Legend on the map questions (July 2016)

JPEG see Metabase Discussion

United States: Metabase in-built map w/dataset -- Note how the value 18 is incorrectly coded as the darkest blue associated with the legend category "20+"

Abbreviations, STATE, AGE
AL, Alabama, 18.6
AK, Alaska, 19
AZ, Arizona, 15.5
AR, Arkansas, 22.8
CA, California, 18
CO, Colorado, 18
CT, Connecticut, 15.8
DE, Delaware, 23
FL, Florida, 17.8
GA, Georgia, 21.8
HI, Hawaii, 11.5
ID, Idaho, 19.1
IL, Illinois, 17.9
IN, Indiana, 20.2
IA, Iowa, 15.2
KS, Kansas, 20.7
KY, Kentucky, 20.7
LA, Louisiana, 17.8
ME, Maine, 18.8
MD, Maryland, 23.9
MA, Massachusetts, 10.9
MI, Michigan, 18
MN, Minnesota, 17.6
MS, Mississippi, 15
MO, Missouri, 21
MT, Montana, 12.1
NE, Nebraska, 18.3
NV, Nevada, 17.3
NH, New Hampshire, 13
NJ, New Jersey, 17.1
NM, New Mexico, 16.3
NY, New York, 18.1
NC, North Carolina, 22
ND, North Dakota, 14
OH, Ohio, 18
OK, Oklahoma, 20
OR, Oregon, 18.2
PA, Pennsylvania, 17
RI, Rhode Island, 15
SC, South Carolina, 12.6
SD, South Dakota, 14.7
TN, Tennessee, 17
TX, Texas, 8
UT, Utah, 17.4
VT, Vermont, 19
VA, Virginia, 18.2
WA, Washington, 17
WV, West Virginia, 15
WI, Wisconsin, 12.6
WY, Wyoming, 14.7

JPEG see Metabase Discussion

California: CA Counties GitHub GeoJson w/dataset -- Note how the value 17.8 is incorrectly coded as the darkest blue associated with the legend category "20+"


COUNTY_D, AGE
Alameda, 18.6
Amador, 19
Calaveras, 15.5
Colusa, 22.8
Contra Costa, 18
El Dorado, 18
Fresno, 15.8
Glenn, 23
Humboldt, 17.8
Kern, 21.8
Kings, 11.5
Lake, 19.1
Los Angeles, 17.9
Madera, 20.2
Mariposa, 15.2
Mendocino, 20.7
Merced, 20.7
Monterey, 17.8
Napa, 18.8
Nevada, 23.9
Placer, 10.9
Riverside, 18
Sacramento, 17.6
San Benito, 15
San Bernardino, 21
San Diego, 12.1
San Joaquin, 18.3
San Luis Obispo, 17.3
San Mateo, 13
Santa Barbara, 17.1
Santa Clara, 16.3
Santa Cruz, 18.1
Shasta, 22
Siskiyou, 14
Solano, 18
Sonoma, 20
Stanislaus, 18.2
Sutter, 17
Tehama, 15
Trinity, 12.6
Tulare, 14.7
Tuolumne, 17
Ventura, 8
Yolo, 17.4
Yuba, 19

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment