MapInfo Pro

Predominance is a form of mapping that can be used to compare multiple attributes within a dataset and show the largest (dominant) value on the map. This is a method of identifying and visualizing new patterns within datasets. My feeling is that MapInfo Pro could benefit from having this capability as part of the built-in capabilities. I would be interested to hear from other users as to if and how they produce these forms of maps in MapInfo Pro, whether they feel this development would be worthwhile, or if I am a solo voice in the distance.

I will try and provide a little more context and some explanation of how I am currently producing these outputs using MapInfo Pro below to assist the conversation.

With more and more data becoming available, the question of "which is the dominant attribute" from a row with multiple attributes, and then displaying this attribute on a map is a valuable method for analyzing data. The capability is provided as standard by a number of other spatial analysis tools.

A predominance map can be applied when there are multiple columns of data that share a common subject and unit of measurement. By using predominance to analyze the multiple columns of related data, a user can determine which column contains the highest, or predominant, value for each geographic feature or reference.

An election map (like the one below by ABC News for the 2020 US Election) is a simple example of a predominance map. On a State-by-State basis, it displays which of two or more candidates have the highest total vote count. 

 

 

Presently to use predominance in MapInfo Pro, the data needs to be prepared outside of the tool in some form of data analysis tool (can be as simple as Excel). Once the analysis is completed, the data can then be imported into MapInfo Pro and linked to the relevant geographic object to enable the result to be displayed. The relevant value for the dominant variable can also be incorporated to enable bivariate thematic mapping.

If the dominant value is noted as part of the data preparation, MapInfo Pro could allow the dominant value to be used in displaying a region object in a bivariate manner by using the dominant category to colour the object and the dominant value to be reflected in the colour density/transparency (if this capability was present).

The example below determines which Profession category is dominant based from a typical demographic group of variables - Census data in this case -  while the map displays the dominant variable category by region.  The red fields are generated in Excel.

 

 

 

I have provided some further examples below (base data - prior to preparation - and final map images) of the application of predominance developed using Excel and MapInfo.

Preparation of the data to determine predominance was done using Excel formulas (to deliver fields similar to the red fields in the Profession example above) and then this data was imported into MapInfo Pro and linked to the appropriate geographic object (point, line, or polygon) using a unique identifier.

These examples include:

• Determining which Age Band is dominant from a typical demographic dataset (map displays dominant category by region)

 

• Determining which Household Income Band is dominant (map displaying this as a bivariate thematic at a point level using the dominant category for the colour and the proportion value of the dominant category for the size of dot)

 

 

• Determining which brand/model of luxury SUV had the most sales (map displays dominant brand/model by region)

 

 

 

• Results from the Agriculture Census (example data shown) is another potential application where the dominant category of produce for an area could be determined and then displayed on a map

 

Given that I am sure all users of MapInfo Pro have examples of data that could benefit from the use of predominance analysis, I think it "Predominance" deserves consideration for inclusion in MapInfo Pro and would be a beneficial enhancement in the analysis and visualization capabilities available to users.

 

Look forward to comments from the Community.

------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia
------------------------------

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

Hi Stefan

Great response and certainly agree that the key fields are easy to generate if the data you start with is non-spatial and requiring preparation before being imported into MapInfo Pro.  These can then be joined to the geographies as per the examples above.

What I should have made more clear is that in the majority of examples above, the original variables data was acquired and already in MapInfo Pro as spatial data.  This meant that to produce the predominance required exporting the tabular data, conducting the analysis and then importing the key fields back into MapInfo Pro to do the spatial join before being able to produce the outputs.

Indeed, there are a number of datasets from Precisely and other suppliers/sources (Demographics, Consumer Spend, any Census data, etc) that come spatially enabled with a plethora of attributes that could benefit from predominance as an analysis output.  Not having to go through the export/prepare/import/join process would add another form of visualization into MapInfo Pro off the shelf.

Granted that data preparation is a significant element of any project, there are a number of users that I work with in the commercial sector where MapInfo Pro is only a part of their job description, that could struggle with the creation of the key variables in an external product.  I'm sure many users would not be aware of the ability to produce predominance in MapInfo Pro (which could be a good opportunity for Precisely to deliver a YouTube video demonstrating the capability or a MapInfo Monday article) regardless of whether the data to begin with is spatial or non-spatial.

If the data is acquired in spatial format it would be much more efficient and powerful to have the capability available without needing extra work to prepare.  Alternatively, if non-spatial data is imported without the key predominance already derived but a variety of attributes available, it would be good if MapInfo Pro could do the heavy lifting after joining the data with the geography.

As demonstrated in my original post, MapInfo Pro can certainly deliver the predominance outputs, but only if the data is appropriately prepared externally to allow it to be joined to an existing spatial object.  I just wanted to test the appetite of the Community for something like Predominance to be built in to a future MapInfo Pro release as an enhancement.  Benefit would be both in efficiency/useability (through not having to prepare data externally) and added analysis capability (always good for users paying their subscription fees to get new features).


Cheers





------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia
------------------------------

Original Message:
Sent: 12-07-2021 03:36
From: Stefan Hausmann
Subject: Predominance for MapInfo Pro

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

To generate a selection on top of this data structure, that calculates the maximum value of profession for each state is fairly easy, and this table can then be used to map those joined to the state geometries, - a functionality that MiPro already has.

So if you would use for all the cases you explained the raw data instead of the already aggregated form, - MiPro wouldn't need a new functionality.

..but as i said: only my two cents.

best regards
Stefan

Hi Tony,

Have you given some thought as to what you would need in Pro to make this easier?
I think it can be done already by using IIF() or Cond() functions.

You should be able to do the same thing you did in excel inside of Pro with a careful update statement to set the value of a new column.

You can also create an individual value theme without creating a new column:

For World Table shading a county by pop_male vs pop_female (similar to democrat vs republican)
theme on str$(iif(world.pop_male>world.pop_fem, "male", "female"))
we have to use str$ since iif can return any type.


For multiple columns you could use nested IIF() expressions

Perhaps we could add a new functions for example Predominance(expr, column1, label1, column2, label2, column3, label...)
expr would be some way to identify how to compare the values (Max, Min, not sure what else)

Or maybe you would just want some ui to select multiple columns and we build the expression for you.

Thoughts?

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team
------------------------------

Original Message:
Sent: 12-08-2021 00:13
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Stefan

Great response and certainly agree that the key fields are easy to generate if the data you start with is non-spatial and requiring preparation before being imported into MapInfo Pro.  These can then be joined to the geographies as per the examples above.

What I should have made more clear is that in the majority of examples above, the original variables data was acquired and already in MapInfo Pro as spatial data.  This meant that to produce the predominance required exporting the tabular data, conducting the analysis and then importing the key fields back into MapInfo Pro to do the spatial join before being able to produce the outputs.

Indeed, there are a number of datasets from Precisely and other suppliers/sources (Demographics, Consumer Spend, any Census data, etc) that come spatially enabled with a plethora of attributes that could benefit from predominance as an analysis output.  Not having to go through the export/prepare/import/join process would add another form of visualization into MapInfo Pro off the shelf.

Granted that data preparation is a significant element of any project, there are a number of users that I work with in the commercial sector where MapInfo Pro is only a part of their job description, that could struggle with the creation of the key variables in an external product.  I'm sure many users would not be aware of the ability to produce predominance in MapInfo Pro (which could be a good opportunity for Precisely to deliver a YouTube video demonstrating the capability or a MapInfo Monday article) regardless of whether the data to begin with is spatial or non-spatial.

If the data is acquired in spatial format it would be much more efficient and powerful to have the capability available without needing extra work to prepare.  Alternatively, if non-spatial data is imported without the key predominance already derived but a variety of attributes available, it would be good if MapInfo Pro could do the heavy lifting after joining the data with the geography.

As demonstrated in my original post, MapInfo Pro can certainly deliver the predominance outputs, but only if the data is appropriately prepared externally to allow it to be joined to an existing spatial object.  I just wanted to test the appetite of the Community for something like Predominance to be built in to a future MapInfo Pro release as an enhancement.  Benefit would be both in efficiency/useability (through not having to prepare data externally) and added analysis capability (always good for users paying their subscription fees to get new features).


Cheers





------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia
------------------------------


Original Message:
Sent: 12-07-2021 03:36
From: Stefan Hausmann
Subject: Predominance for MapInfo Pro

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

To generate a selection on top of this data structure, that calculates the maximum value of profession for each state is fairly easy, and this table can then be used to map those joined to the state geometries, - a functionality that MiPro already has.

So if you would use for all the cases you explained the raw data instead of the already aggregated form, - MiPro wouldn't need a new functionality.

..but as i said: only my two cents.

best regards
Stefan

Hi Bob

Thanks for taking the time to give this some consideration and respond.
Sounds like the IIF() expresions could deliver a partial result (dominant field) but might be a little painful if you've have multiple columns (which would more typically be the case).  What this process does not seem to deliver is the maximum value for the dominant field which can be used for the bivariate thematics.
Just for general information, the expressions I use in Excel to prepare predominance data for use are "=MAX(#2:#2)" to generate the dominant value and "=INDEX($#$1:$#$1,MATCH(MAX(#2:#2),#2:#2,0)) to generate the dominant variable heading.  These are based on when the first row of the data contained Field Headings and the variables commenced in Column 2.
As mentioned in my earlier post, a lot of the MapInfo Pro users that I work with in the commercial sector are only using it as a small part of their role and would generally not have the capability to build the expressions you suggest themselves.  This is why I think that an in-built tool would be beneficial as the potential results can certainly add to the business decision support outputs inherent within MapInfo Pro and grow/strengthen it's role as a mission critical tool.
If it was to be developed as an enhancement, my initial suggestion would be to build a tool with a simple UI that allowed the user to identify the fields to be considered and perhaps output a temporary table that included the map object, it's UID, and the dominant maximum value and dominant field heading as attributes.  The user could then use this result to do any thematic mapping or other analysis/visualization and/or could save the result as a permanent table if they felt it was going to be needed at some later stage.
As a long time MapInfo user/supporter, I felt that this could be a positive and relatively simple enhancement given most of the capability is there somewhere anyway, and the benefit would be both in efficiency/useability (through not having to prepare data externally or build complex expression statements in MapInfo Pro) and added analysis capability (always good for users paying their subscription fees to get new features).  Would also demonstrate nicely in a new release session and could highlight some of the Precisely Data Catalogue in doing so!

Cheers



------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia
------------------------------

Original Message:
Sent: 12-08-2021 11:59
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

Have you given some thought as to what you would need in Pro to make this easier?
I think it can be done already by using IIF() or Cond() functions.

You should be able to do the same thing you did in excel inside of Pro with a careful update statement to set the value of a new column.

You can also create an individual value theme without creating a new column:

For World Table shading a county by pop_male vs pop_female (similar to democrat vs republican)
theme on str$(iif(world.pop_male>world.pop_fem, "male", "female"))
we have to use str$ since iif can return any type.


For multiple columns you could use nested IIF() expressions

Perhaps we could add a new functions for example Predominance(expr, column1, label1, column2, label2, column3, label...)
expr would be some way to identify how to compare the values (Max, Min, not sure what else)

Or maybe you would just want some ui to select multiple columns and we build the expression for you.

Thoughts?

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team
------------------------------


Original Message:
Sent: 12-08-2021 00:13
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Stefan

Great response and certainly agree that the key fields are easy to generate if the data you start with is non-spatial and requiring preparation before being imported into MapInfo Pro.  These can then be joined to the geographies as per the examples above.

What I should have made more clear is that in the majority of examples above, the original variables data was acquired and already in MapInfo Pro as spatial data.  This meant that to produce the predominance required exporting the tabular data, conducting the analysis and then importing the key fields back into MapInfo Pro to do the spatial join before being able to produce the outputs.

Indeed, there are a number of datasets from Precisely and other suppliers/sources (Demographics, Consumer Spend, any Census data, etc) that come spatially enabled with a plethora of attributes that could benefit from predominance as an analysis output.  Not having to go through the export/prepare/import/join process would add another form of visualization into MapInfo Pro off the shelf.

Granted that data preparation is a significant element of any project, there are a number of users that I work with in the commercial sector where MapInfo Pro is only a part of their job description, that could struggle with the creation of the key variables in an external product.  I'm sure many users would not be aware of the ability to produce predominance in MapInfo Pro (which could be a good opportunity for Precisely to deliver a YouTube video demonstrating the capability or a MapInfo Monday article) regardless of whether the data to begin with is spatial or non-spatial.

If the data is acquired in spatial format it would be much more efficient and powerful to have the capability available without needing extra work to prepare.  Alternatively, if non-spatial data is imported without the key predominance already derived but a variety of attributes available, it would be good if MapInfo Pro could do the heavy lifting after joining the data with the geography.

As demonstrated in my original post, MapInfo Pro can certainly deliver the predominance outputs, but only if the data is appropriately prepared externally to allow it to be joined to an existing spatial object.  I just wanted to test the appetite of the Community for something like Predominance to be built in to a future MapInfo Pro release as an enhancement.  Benefit would be both in efficiency/useability (through not having to prepare data externally) and added analysis capability (always good for users paying their subscription fees to get new features).


Cheers





------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-07-2021 03:36
From: Stefan Hausmann
Subject: Predominance for MapInfo Pro

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

To generate a selection on top of this data structure, that calculates the maximum value of profession for each state is fairly easy, and this table can then be used to map those joined to the state geometries, - a functionality that MiPro already has.

So if you would use for all the cases you explained the raw data instead of the already aggregated form, - MiPro wouldn't need a new functionality.

..but as i said: only my two cents.

best regards
Stefan

Hi Tony,

I spent a few hours on friday trying this out.
I made two new mapbasic functions that take a variable number of parameters.
The first one - PredominantVal(expr1, expr1 [, exprn...]) returns the maximum of the values passed in.
The return value has the type of the first expression, so it works for ints, float, string, etc.

The second one tPredominantCol takes the table and the names of the columns as strings.
It returns the name of the column with the predominant value (for the current row)

fetch first from world
print world.Country
print PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)

print PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")

Then i created a tool using python to prompt for a table and then the columns you want.
It currently adds two temporary columns to the table but it could create a temp table instead as you suggested:
It takes the col type from the first col specified.
I could also have used Nested IFF() functions to get the same result without creating new functions.

add column world("MAX_Value" float) from world set to PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)
add column world("CAT_Dominant" char(31)) from world set to PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")



Finally, my other thought was to just extent Max() to take multiple values, then create something like WhichMax() to return the index of the max parameter. A little more work for the tool writer but maybe more generic. Could then do same for Min() and maybe some other functions as well.

Thoughts?

-Bob

ps. I am happy to share the python code if any one wants it. Like this thread if you would like to see it in the product...

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team
------------------------------

Original Message:
Sent: 12-09-2021 23:24
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Bob

Thanks for taking the time to give this some consideration and respond.
Sounds like the IIF() expresions could deliver a partial result (dominant field) but might be a little painful if you've have multiple columns (which would more typically be the case).  What this process does not seem to deliver is the maximum value for the dominant field which can be used for the bivariate thematics.
Just for general information, the expressions I use in Excel to prepare predominance data for use are "=MAX(#2:#2)" to generate the dominant value and "=INDEX($#$1:$#$1,MATCH(MAX(#2:#2),#2:#2,0)) to generate the dominant variable heading.  These are based on when the first row of the data contained Field Headings and the variables commenced in Column 2.
As mentioned in my earlier post, a lot of the MapInfo Pro users that I work with in the commercial sector are only using it as a small part of their role and would generally not have the capability to build the expressions you suggest themselves.  This is why I think that an in-built tool would be beneficial as the potential results can certainly add to the business decision support outputs inherent within MapInfo Pro and grow/strengthen it's role as a mission critical tool.
If it was to be developed as an enhancement, my initial suggestion would be to build a tool with a simple UI that allowed the user to identify the fields to be considered and perhaps output a temporary table that included the map object, it's UID, and the dominant maximum value and dominant field heading as attributes.  The user could then use this result to do any thematic mapping or other analysis/visualization and/or could save the result as a permanent table if they felt it was going to be needed at some later stage.
As a long time MapInfo user/supporter, I felt that this could be a positive and relatively simple enhancement given most of the capability is there somewhere anyway, and the benefit would be both in efficiency/useability (through not having to prepare data externally or build complex expression statements in MapInfo Pro) and added analysis capability (always good for users paying their subscription fees to get new features).  Would also demonstrate nicely in a new release session and could highlight some of the Precisely Data Catalogue in doing so!

Cheers



------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia
------------------------------


Original Message:
Sent: 12-08-2021 11:59
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

Have you given some thought as to what you would need in Pro to make this easier?
I think it can be done already by using IIF() or Cond() functions.

You should be able to do the same thing you did in excel inside of Pro with a careful update statement to set the value of a new column.

You can also create an individual value theme without creating a new column:

For World Table shading a county by pop_male vs pop_female (similar to democrat vs republican)
theme on str$(iif(world.pop_male>world.pop_fem, "male", "female"))
we have to use str$ since iif can return any type.


For multiple columns you could use nested IIF() expressions

Perhaps we could add a new functions for example Predominance(expr, column1, label1, column2, label2, column3, label...)
expr would be some way to identify how to compare the values (Max, Min, not sure what else)

Or maybe you would just want some ui to select multiple columns and we build the expression for you.

Thoughts?

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-08-2021 00:13
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Stefan

Great response and certainly agree that the key fields are easy to generate if the data you start with is non-spatial and requiring preparation before being imported into MapInfo Pro.  These can then be joined to the geographies as per the examples above.

What I should have made more clear is that in the majority of examples above, the original variables data was acquired and already in MapInfo Pro as spatial data.  This meant that to produce the predominance required exporting the tabular data, conducting the analysis and then importing the key fields back into MapInfo Pro to do the spatial join before being able to produce the outputs.

Indeed, there are a number of datasets from Precisely and other suppliers/sources (Demographics, Consumer Spend, any Census data, etc) that come spatially enabled with a plethora of attributes that could benefit from predominance as an analysis output.  Not having to go through the export/prepare/import/join process would add another form of visualization into MapInfo Pro off the shelf.

Granted that data preparation is a significant element of any project, there are a number of users that I work with in the commercial sector where MapInfo Pro is only a part of their job description, that could struggle with the creation of the key variables in an external product.  I'm sure many users would not be aware of the ability to produce predominance in MapInfo Pro (which could be a good opportunity for Precisely to deliver a YouTube video demonstrating the capability or a MapInfo Monday article) regardless of whether the data to begin with is spatial or non-spatial.

If the data is acquired in spatial format it would be much more efficient and powerful to have the capability available without needing extra work to prepare.  Alternatively, if non-spatial data is imported without the key predominance already derived but a variety of attributes available, it would be good if MapInfo Pro could do the heavy lifting after joining the data with the geography.

As demonstrated in my original post, MapInfo Pro can certainly deliver the predominance outputs, but only if the data is appropriately prepared externally to allow it to be joined to an existing spatial object.  I just wanted to test the appetite of the Community for something like Predominance to be built in to a future MapInfo Pro release as an enhancement.  Benefit would be both in efficiency/useability (through not having to prepare data externally) and added analysis capability (always good for users paying their subscription fees to get new features).


Cheers





------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-07-2021 03:36
From: Stefan Hausmann
Subject: Predominance for MapInfo Pro

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

To generate a selection on top of this data structure, that calculates the maximum value of profession for each state is fairly easy, and this table can then be used to map those joined to the state geometries, - a functionality that MiPro already has.

So if you would use for all the cases you explained the raw data instead of the already aggregated form, - MiPro wouldn't need a new functionality.

..but as i said: only my two cents.

best regards
Stefan

I like it, Bob

I had spent some time thinking about how to do this with pure MapBasic and had come to a similar process with two functions that could provide the maximum value and the column that holds that maximum value.

Doing it through MapBasic would be more tricky as you can declare a function that takes a varying number of parameters so I probably would have ended up using an array.

The only thing that would make your solution even better is if the user also could specify an "alias" in lack of a better word) for the column names returned. Instead of using "POP_0_14" use "Population 0-14 years old" as an example.

This could be done using a Cond() expression that replaces the column names with the friendly name. But it would be smoother if it was part of the Predominant Tool. Just a thought.

PS: You stole my typo. It not IFF() but IIf() :-)

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------

Original Message:
Sent: 12-13-2021 16:22
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

I spent a few hours on friday trying this out.
I made two new mapbasic functions that take a variable number of parameters.
The first one - PredominantVal(expr1, expr1 [, exprn...]) returns the maximum of the values passed in.
The return value has the type of the first expression, so it works for ints, float, string, etc.

The second one tPredominantCol takes the table and the names of the columns as strings.
It returns the name of the column with the predominant value (for the current row)

fetch first from world
print world.Country
print PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)

print PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")

Then i created a tool using python to prompt for a table and then the columns you want.
It currently adds two temporary columns to the table but it could create a temp table instead as you suggested:
It takes the col type from the first col specified.
I could also have used Nested IFF() functions to get the same result without creating new functions.

add column world("MAX_Value" float) from world set to PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)
add column world("CAT_Dominant" char(31)) from world set to PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")



Finally, my other thought was to just extent Max() to take multiple values, then create something like WhichMax() to return the index of the max parameter. A little more work for the tool writer but maybe more generic. Could then do same for Min() and maybe some other functions as well.

Thoughts?

-Bob

ps. I am happy to share the python code if any one wants it. Like this thread if you would like to see it in the product...

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team
------------------------------


Original Message:
Sent: 12-09-2021 23:24
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Bob

Thanks for taking the time to give this some consideration and respond.
Sounds like the IIF() expresions could deliver a partial result (dominant field) but might be a little painful if you've have multiple columns (which would more typically be the case).  What this process does not seem to deliver is the maximum value for the dominant field which can be used for the bivariate thematics.
Just for general information, the expressions I use in Excel to prepare predominance data for use are "=MAX(#2:#2)" to generate the dominant value and "=INDEX($#$1:$#$1,MATCH(MAX(#2:#2),#2:#2,0)) to generate the dominant variable heading.  These are based on when the first row of the data contained Field Headings and the variables commenced in Column 2.
As mentioned in my earlier post, a lot of the MapInfo Pro users that I work with in the commercial sector are only using it as a small part of their role and would generally not have the capability to build the expressions you suggest themselves.  This is why I think that an in-built tool would be beneficial as the potential results can certainly add to the business decision support outputs inherent within MapInfo Pro and grow/strengthen it's role as a mission critical tool.
If it was to be developed as an enhancement, my initial suggestion would be to build a tool with a simple UI that allowed the user to identify the fields to be considered and perhaps output a temporary table that included the map object, it's UID, and the dominant maximum value and dominant field heading as attributes.  The user could then use this result to do any thematic mapping or other analysis/visualization and/or could save the result as a permanent table if they felt it was going to be needed at some later stage.
As a long time MapInfo user/supporter, I felt that this could be a positive and relatively simple enhancement given most of the capability is there somewhere anyway, and the benefit would be both in efficiency/useability (through not having to prepare data externally or build complex expression statements in MapInfo Pro) and added analysis capability (always good for users paying their subscription fees to get new features).  Would also demonstrate nicely in a new release session and could highlight some of the Precisely Data Catalogue in doing so!

Cheers



------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-08-2021 11:59
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

Have you given some thought as to what you would need in Pro to make this easier?
I think it can be done already by using IIF() or Cond() functions.

You should be able to do the same thing you did in excel inside of Pro with a careful update statement to set the value of a new column.

You can also create an individual value theme without creating a new column:

For World Table shading a county by pop_male vs pop_female (similar to democrat vs republican)
theme on str$(iif(world.pop_male>world.pop_fem, "male", "female"))
we have to use str$ since iif can return any type.


For multiple columns you could use nested IIF() expressions

Perhaps we could add a new functions for example Predominance(expr, column1, label1, column2, label2, column3, label...)
expr would be some way to identify how to compare the values (Max, Min, not sure what else)

Or maybe you would just want some ui to select multiple columns and we build the expression for you.

Thoughts?

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-08-2021 00:13
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Stefan

Great response and certainly agree that the key fields are easy to generate if the data you start with is non-spatial and requiring preparation before being imported into MapInfo Pro.  These can then be joined to the geographies as per the examples above.

What I should have made more clear is that in the majority of examples above, the original variables data was acquired and already in MapInfo Pro as spatial data.  This meant that to produce the predominance required exporting the tabular data, conducting the analysis and then importing the key fields back into MapInfo Pro to do the spatial join before being able to produce the outputs.

Indeed, there are a number of datasets from Precisely and other suppliers/sources (Demographics, Consumer Spend, any Census data, etc) that come spatially enabled with a plethora of attributes that could benefit from predominance as an analysis output.  Not having to go through the export/prepare/import/join process would add another form of visualization into MapInfo Pro off the shelf.

Granted that data preparation is a significant element of any project, there are a number of users that I work with in the commercial sector where MapInfo Pro is only a part of their job description, that could struggle with the creation of the key variables in an external product.  I'm sure many users would not be aware of the ability to produce predominance in MapInfo Pro (which could be a good opportunity for Precisely to deliver a YouTube video demonstrating the capability or a MapInfo Monday article) regardless of whether the data to begin with is spatial or non-spatial.

If the data is acquired in spatial format it would be much more efficient and powerful to have the capability available without needing extra work to prepare.  Alternatively, if non-spatial data is imported without the key predominance already derived but a variety of attributes available, it would be good if MapInfo Pro could do the heavy lifting after joining the data with the geography.

As demonstrated in my original post, MapInfo Pro can certainly deliver the predominance outputs, but only if the data is appropriately prepared externally to allow it to be joined to an existing spatial object.  I just wanted to test the appetite of the Community for something like Predominance to be built in to a future MapInfo Pro release as an enhancement.  Benefit would be both in efficiency/useability (through not having to prepare data externally) and added analysis capability (always good for users paying their subscription fees to get new features).


Cheers





------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-07-2021 03:36
From: Stefan Hausmann
Subject: Predominance for MapInfo Pro

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

To generate a selection on top of this data structure, that calculates the maximum value of profession for each state is fairly easy, and this table can then be used to map those joined to the state geometries, - a functionality that MiPro already has.

So if you would use for all the cases you explained the raw data instead of the already aggregated form, - MiPro wouldn't need a new functionality.

..but as i said: only my two cents.

best regards
Stefan

Hi Bob & Peter

Thanks for taking the time to look into this a bit deeper.
Apologies for not responding sooner but have been away from the office for 3 days without access to my computer.
Will take a look into your responses and come back to you ASAP now that I'm back online.

Cheers

------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia
------------------------------

Original Message:
Sent: 12-14-2021 01:35
From: Peter Horsbøll Møller
Subject: Predominance for MapInfo Pro

I like it, Bob

I had spent some time thinking about how to do this with pure MapBasic and had come to a similar process with two functions that could provide the maximum value and the column that holds that maximum value.

Doing it through MapBasic would be more tricky as you can declare a function that takes a varying number of parameters so I probably would have ended up using an array.

The only thing that would make your solution even better is if the user also could specify an "alias" in lack of a better word) for the column names returned. Instead of using "POP_0_14" use "Population 0-14 years old" as an example.

This could be done using a Cond() expression that replaces the column names with the friendly name. But it would be smoother if it was part of the Predominant Tool. Just a thought.

PS: You stole my typo. It not IFF() but IIf() :-)

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------


Original Message:
Sent: 12-13-2021 16:22
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

I spent a few hours on friday trying this out.
I made two new mapbasic functions that take a variable number of parameters.
The first one - PredominantVal(expr1, expr1 [, exprn...]) returns the maximum of the values passed in.
The return value has the type of the first expression, so it works for ints, float, string, etc.

The second one tPredominantCol takes the table and the names of the columns as strings.
It returns the name of the column with the predominant value (for the current row)

fetch first from world
print world.Country
print PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)

print PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")

Then i created a tool using python to prompt for a table and then the columns you want.
It currently adds two temporary columns to the table but it could create a temp table instead as you suggested:
It takes the col type from the first col specified.
I could also have used Nested IFF() functions to get the same result without creating new functions.

add column world("MAX_Value" float) from world set to PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)
add column world("CAT_Dominant" char(31)) from world set to PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")



Finally, my other thought was to just extent Max() to take multiple values, then create something like WhichMax() to return the index of the max parameter. A little more work for the tool writer but maybe more generic. Could then do same for Min() and maybe some other functions as well.

Thoughts?

-Bob

ps. I am happy to share the python code if any one wants it. Like this thread if you would like to see it in the product...

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-09-2021 23:24
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Bob

Thanks for taking the time to give this some consideration and respond.
Sounds like the IIF() expresions could deliver a partial result (dominant field) but might be a little painful if you've have multiple columns (which would more typically be the case).  What this process does not seem to deliver is the maximum value for the dominant field which can be used for the bivariate thematics.
Just for general information, the expressions I use in Excel to prepare predominance data for use are "=MAX(#2:#2)" to generate the dominant value and "=INDEX($#$1:$#$1,MATCH(MAX(#2:#2),#2:#2,0)) to generate the dominant variable heading.  These are based on when the first row of the data contained Field Headings and the variables commenced in Column 2.
As mentioned in my earlier post, a lot of the MapInfo Pro users that I work with in the commercial sector are only using it as a small part of their role and would generally not have the capability to build the expressions you suggest themselves.  This is why I think that an in-built tool would be beneficial as the potential results can certainly add to the business decision support outputs inherent within MapInfo Pro and grow/strengthen it's role as a mission critical tool.
If it was to be developed as an enhancement, my initial suggestion would be to build a tool with a simple UI that allowed the user to identify the fields to be considered and perhaps output a temporary table that included the map object, it's UID, and the dominant maximum value and dominant field heading as attributes.  The user could then use this result to do any thematic mapping or other analysis/visualization and/or could save the result as a permanent table if they felt it was going to be needed at some later stage.
As a long time MapInfo user/supporter, I felt that this could be a positive and relatively simple enhancement given most of the capability is there somewhere anyway, and the benefit would be both in efficiency/useability (through not having to prepare data externally or build complex expression statements in MapInfo Pro) and added analysis capability (always good for users paying their subscription fees to get new features).  Would also demonstrate nicely in a new release session and could highlight some of the Precisely Data Catalogue in doing so!

Cheers



------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-08-2021 11:59
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

Have you given some thought as to what you would need in Pro to make this easier?
I think it can be done already by using IIF() or Cond() functions.

You should be able to do the same thing you did in excel inside of Pro with a careful update statement to set the value of a new column.

You can also create an individual value theme without creating a new column:

For World Table shading a county by pop_male vs pop_female (similar to democrat vs republican)
theme on str$(iif(world.pop_male>world.pop_fem, "male", "female"))
we have to use str$ since iif can return any type.


For multiple columns you could use nested IIF() expressions

Perhaps we could add a new functions for example Predominance(expr, column1, label1, column2, label2, column3, label...)
expr would be some way to identify how to compare the values (Max, Min, not sure what else)

Or maybe you would just want some ui to select multiple columns and we build the expression for you.

Thoughts?

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-08-2021 00:13
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Stefan

Great response and certainly agree that the key fields are easy to generate if the data you start with is non-spatial and requiring preparation before being imported into MapInfo Pro.  These can then be joined to the geographies as per the examples above.

What I should have made more clear is that in the majority of examples above, the original variables data was acquired and already in MapInfo Pro as spatial data.  This meant that to produce the predominance required exporting the tabular data, conducting the analysis and then importing the key fields back into MapInfo Pro to do the spatial join before being able to produce the outputs.

Indeed, there are a number of datasets from Precisely and other suppliers/sources (Demographics, Consumer Spend, any Census data, etc) that come spatially enabled with a plethora of attributes that could benefit from predominance as an analysis output.  Not having to go through the export/prepare/import/join process would add another form of visualization into MapInfo Pro off the shelf.

Granted that data preparation is a significant element of any project, there are a number of users that I work with in the commercial sector where MapInfo Pro is only a part of their job description, that could struggle with the creation of the key variables in an external product.  I'm sure many users would not be aware of the ability to produce predominance in MapInfo Pro (which could be a good opportunity for Precisely to deliver a YouTube video demonstrating the capability or a MapInfo Monday article) regardless of whether the data to begin with is spatial or non-spatial.

If the data is acquired in spatial format it would be much more efficient and powerful to have the capability available without needing extra work to prepare.  Alternatively, if non-spatial data is imported without the key predominance already derived but a variety of attributes available, it would be good if MapInfo Pro could do the heavy lifting after joining the data with the geography.

As demonstrated in my original post, MapInfo Pro can certainly deliver the predominance outputs, but only if the data is appropriately prepared externally to allow it to be joined to an existing spatial object.  I just wanted to test the appetite of the Community for something like Predominance to be built in to a future MapInfo Pro release as an enhancement.  Benefit would be both in efficiency/useability (through not having to prepare data externally) and added analysis capability (always good for users paying their subscription fees to get new features).


Cheers





------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-07-2021 03:36
From: Stefan Hausmann
Subject: Predominance for MapInfo Pro

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

To generate a selection on top of this data structure, that calculates the maximum value of profession for each state is fairly easy, and this table can then be used to map those joined to the state geometries, - a functionality that MiPro already has.

So if you would use for all the cases you explained the raw data instead of the already aggregated form, - MiPro wouldn't need a new functionality.

..but as i said: only my two cents.

best regards
Stefan

Hi Tony

Just in time for Christmas, I share a small tool with you.

It's still early days so it doesn't come with any user interface yet but you can use the features from the SQL Window and the MapBasic window.

The basic idea is to give you a couple of functions to use to get the name of the predominant column and the value from the predominant column too.

It works like this:
In the SQL Window enter these statements:
Dim sTab As String
Dim sCols As String

sTab = "MBI_CA_Census_Division_SocioDe"
sCols = "AGE_T0014,AGE_T1529,AGE_T3044,AGE_T4559,AGE_T60PL"

Update sTab
Set AGET_PRED_NAME = GetPredominanceColumnCSL(sTab, sCols),
AGET_PRED_MAX = GetPredominanceValueCSL(sTab, sCols)

You can see how it looks below:

Now let me explain the statements:
The first two lines create two variables to hold the table to update and the columns to compare.

The next two lines assign values to these two variables. First, you specify the name you want to update, and secondly, you specify a comma-separated list of columns whose values to compare.

The last statement uses an Update statement to get the name of the column with the maximum value and the maximum value. These values get inserted into two existing columns (AGET_PRED_NAME and AGET_PRED_MAX).

In my example, I'm comparing some socio-demographic data to find the predominant age group for each of the areas. In the browser window in the image above, you can see that the values have been assigned to the two columns.

I can now create a new thematic map that shows which age group is predominant in each region/census area.

I have attached a zip with the compiled application. Please give it a try and let me know if you find any issues. I'll try to build an interface around this but I'm not sure if I get to that this year.

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------

Original Message:
Sent: 12-16-2021 01:43
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Bob & Peter

Thanks for taking the time to look into this a bit deeper.
Apologies for not responding sooner but have been away from the office for 3 days without access to my computer.
Will take a look into your responses and come back to you ASAP now that I'm back online.

Cheers

------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia
------------------------------


Original Message:
Sent: 12-14-2021 01:35
From: Peter Horsbøll Møller
Subject: Predominance for MapInfo Pro

I like it, Bob

I had spent some time thinking about how to do this with pure MapBasic and had come to a similar process with two functions that could provide the maximum value and the column that holds that maximum value.

Doing it through MapBasic would be more tricky as you can declare a function that takes a varying number of parameters so I probably would have ended up using an array.

The only thing that would make your solution even better is if the user also could specify an "alias" in lack of a better word) for the column names returned. Instead of using "POP_0_14" use "Population 0-14 years old" as an example.

This could be done using a Cond() expression that replaces the column names with the friendly name. But it would be smoother if it was part of the Predominant Tool. Just a thought.

PS: You stole my typo. It not IFF() but IIf() :-)

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data


Original Message:
Sent: 12-13-2021 16:22
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

I spent a few hours on friday trying this out.
I made two new mapbasic functions that take a variable number of parameters.
The first one - PredominantVal(expr1, expr1 [, exprn...]) returns the maximum of the values passed in.
The return value has the type of the first expression, so it works for ints, float, string, etc.

The second one tPredominantCol takes the table and the names of the columns as strings.
It returns the name of the column with the predominant value (for the current row)

fetch first from world
print world.Country
print PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)

print PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")

Then i created a tool using python to prompt for a table and then the columns you want.
It currently adds two temporary columns to the table but it could create a temp table instead as you suggested:
It takes the col type from the first col specified.
I could also have used Nested IFF() functions to get the same result without creating new functions.

add column world("MAX_Value" float) from world set to PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)
add column world("CAT_Dominant" char(31)) from world set to PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")



Finally, my other thought was to just extent Max() to take multiple values, then create something like WhichMax() to return the index of the max parameter. A little more work for the tool writer but maybe more generic. Could then do same for Min() and maybe some other functions as well.

Thoughts?

-Bob

ps. I am happy to share the python code if any one wants it. Like this thread if you would like to see it in the product...

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-09-2021 23:24
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Bob

Thanks for taking the time to give this some consideration and respond.
Sounds like the IIF() expresions could deliver a partial result (dominant field) but might be a little painful if you've have multiple columns (which would more typically be the case).  What this process does not seem to deliver is the maximum value for the dominant field which can be used for the bivariate thematics.
Just for general information, the expressions I use in Excel to prepare predominance data for use are "=MAX(#2:#2)" to generate the dominant value and "=INDEX($#$1:$#$1,MATCH(MAX(#2:#2),#2:#2,0)) to generate the dominant variable heading.  These are based on when the first row of the data contained Field Headings and the variables commenced in Column 2.
As mentioned in my earlier post, a lot of the MapInfo Pro users that I work with in the commercial sector are only using it as a small part of their role and would generally not have the capability to build the expressions you suggest themselves.  This is why I think that an in-built tool would be beneficial as the potential results can certainly add to the business decision support outputs inherent within MapInfo Pro and grow/strengthen it's role as a mission critical tool.
If it was to be developed as an enhancement, my initial suggestion would be to build a tool with a simple UI that allowed the user to identify the fields to be considered and perhaps output a temporary table that included the map object, it's UID, and the dominant maximum value and dominant field heading as attributes.  The user could then use this result to do any thematic mapping or other analysis/visualization and/or could save the result as a permanent table if they felt it was going to be needed at some later stage.
As a long time MapInfo user/supporter, I felt that this could be a positive and relatively simple enhancement given most of the capability is there somewhere anyway, and the benefit would be both in efficiency/useability (through not having to prepare data externally or build complex expression statements in MapInfo Pro) and added analysis capability (always good for users paying their subscription fees to get new features).  Would also demonstrate nicely in a new release session and could highlight some of the Precisely Data Catalogue in doing so!

Cheers



------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-08-2021 11:59
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

Have you given some thought as to what you would need in Pro to make this easier?
I think it can be done already by using IIF() or Cond() functions.

You should be able to do the same thing you did in excel inside of Pro with a careful update statement to set the value of a new column.

You can also create an individual value theme without creating a new column:

For World Table shading a county by pop_male vs pop_female (similar to democrat vs republican)
theme on str$(iif(world.pop_male>world.pop_fem, "male", "female"))
we have to use str$ since iif can return any type.


For multiple columns you could use nested IIF() expressions

Perhaps we could add a new functions for example Predominance(expr, column1, label1, column2, label2, column3, label...)
expr would be some way to identify how to compare the values (Max, Min, not sure what else)

Or maybe you would just want some ui to select multiple columns and we build the expression for you.

Thoughts?

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-08-2021 00:13
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Stefan

Great response and certainly agree that the key fields are easy to generate if the data you start with is non-spatial and requiring preparation before being imported into MapInfo Pro.  These can then be joined to the geographies as per the examples above.

What I should have made more clear is that in the majority of examples above, the original variables data was acquired and already in MapInfo Pro as spatial data.  This meant that to produce the predominance required exporting the tabular data, conducting the analysis and then importing the key fields back into MapInfo Pro to do the spatial join before being able to produce the outputs.

Indeed, there are a number of datasets from Precisely and other suppliers/sources (Demographics, Consumer Spend, any Census data, etc) that come spatially enabled with a plethora of attributes that could benefit from predominance as an analysis output.  Not having to go through the export/prepare/import/join process would add another form of visualization into MapInfo Pro off the shelf.

Granted that data preparation is a significant element of any project, there are a number of users that I work with in the commercial sector where MapInfo Pro is only a part of their job description, that could struggle with the creation of the key variables in an external product.  I'm sure many users would not be aware of the ability to produce predominance in MapInfo Pro (which could be a good opportunity for Precisely to deliver a YouTube video demonstrating the capability or a MapInfo Monday article) regardless of whether the data to begin with is spatial or non-spatial.

If the data is acquired in spatial format it would be much more efficient and powerful to have the capability available without needing extra work to prepare.  Alternatively, if non-spatial data is imported without the key predominance already derived but a variety of attributes available, it would be good if MapInfo Pro could do the heavy lifting after joining the data with the geography.

As demonstrated in my original post, MapInfo Pro can certainly deliver the predominance outputs, but only if the data is appropriately prepared externally to allow it to be joined to an existing spatial object.  I just wanted to test the appetite of the Community for something like Predominance to be built in to a future MapInfo Pro release as an enhancement.  Benefit would be both in efficiency/useability (through not having to prepare data externally) and added analysis capability (always good for users paying their subscription fees to get new features).


Cheers





------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-07-2021 03:36
From: Stefan Hausmann
Subject: Predominance for MapInfo Pro

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

To generate a selection on top of this data structure, that calculates the maximum value of profession for each state is fairly easy, and this table can then be used to map those joined to the state geometries, - a functionality that MiPro already has.

So if you would use for all the cases you explained the raw data instead of the already aggregated form, - MiPro wouldn't need a new functionality.

..but as i said: only my two cents.

best regards
Stefan

Hey Peter

Thanks for your support and assistance with this.  Will download and take a look over the holiday period and come back to you with any questions or comments.

Have a great Xmas and New Year and same to everyone in the Mapinfo Pro Community

------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia
------------------------------

Original Message:
Sent: 12-21-2021 04:31
From: Peter Horsbøll Møller
Subject: Predominance for MapInfo Pro

Hi Tony

Just in time for Christmas, I share a small tool with you.

It's still early days so it doesn't come with any user interface yet but you can use the features from the SQL Window and the MapBasic window.

The basic idea is to give you a couple of functions to use to get the name of the predominant column and the value from the predominant column too.

It works like this:
In the SQL Window enter these statements:
Dim sTab As String
Dim sCols As String

sTab = "MBI_CA_Census_Division_SocioDe"
sCols = "AGE_T0014,AGE_T1529,AGE_T3044,AGE_T4559,AGE_T60PL"

Update sTab
Set AGET_PRED_NAME = GetPredominanceColumnCSL(sTab, sCols),
AGET_PRED_MAX = GetPredominanceValueCSL(sTab, sCols)

You can see how it looks below:

Now let me explain the statements:
The first two lines create two variables to hold the table to update and the columns to compare.

The next two lines assign values to these two variables. First, you specify the name you want to update, and secondly, you specify a comma-separated list of columns whose values to compare.

The last statement uses an Update statement to get the name of the column with the maximum value and the maximum value. These values get inserted into two existing columns (AGET_PRED_NAME and AGET_PRED_MAX).

In my example, I'm comparing some socio-demographic data to find the predominant age group for each of the areas. In the browser window in the image above, you can see that the values have been assigned to the two columns.

I can now create a new thematic map that shows which age group is predominant in each region/census area.

I have attached a zip with the compiled application. Please give it a try and let me know if you find any issues. I'll try to build an interface around this but I'm not sure if I get to that this year.

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------


Original Message:
Sent: 12-16-2021 01:43
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Bob & Peter

Thanks for taking the time to look into this a bit deeper.
Apologies for not responding sooner but have been away from the office for 3 days without access to my computer.
Will take a look into your responses and come back to you ASAP now that I'm back online.

Cheers

------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-14-2021 01:35
From: Peter Horsbøll Møller
Subject: Predominance for MapInfo Pro

I like it, Bob

I had spent some time thinking about how to do this with pure MapBasic and had come to a similar process with two functions that could provide the maximum value and the column that holds that maximum value.

Doing it through MapBasic would be more tricky as you can declare a function that takes a varying number of parameters so I probably would have ended up using an array.

The only thing that would make your solution even better is if the user also could specify an "alias" in lack of a better word) for the column names returned. Instead of using "POP_0_14" use "Population 0-14 years old" as an example.

This could be done using a Cond() expression that replaces the column names with the friendly name. But it would be smoother if it was part of the Predominant Tool. Just a thought.

PS: You stole my typo. It not IFF() but IIf() :-)

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data


Original Message:
Sent: 12-13-2021 16:22
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

I spent a few hours on friday trying this out.
I made two new mapbasic functions that take a variable number of parameters.
The first one - PredominantVal(expr1, expr1 [, exprn...]) returns the maximum of the values passed in.
The return value has the type of the first expression, so it works for ints, float, string, etc.

The second one tPredominantCol takes the table and the names of the columns as strings.
It returns the name of the column with the predominant value (for the current row)

fetch first from world
print world.Country
print PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)

print PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")

Then i created a tool using python to prompt for a table and then the columns you want.
It currently adds two temporary columns to the table but it could create a temp table instead as you suggested:
It takes the col type from the first col specified.
I could also have used Nested IFF() functions to get the same result without creating new functions.

add column world("MAX_Value" float) from world set to PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)
add column world("CAT_Dominant" char(31)) from world set to PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")



Finally, my other thought was to just extent Max() to take multiple values, then create something like WhichMax() to return the index of the max parameter. A little more work for the tool writer but maybe more generic. Could then do same for Min() and maybe some other functions as well.

Thoughts?

-Bob

ps. I am happy to share the python code if any one wants it. Like this thread if you would like to see it in the product...

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-09-2021 23:24
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Bob

Thanks for taking the time to give this some consideration and respond.
Sounds like the IIF() expresions could deliver a partial result (dominant field) but might be a little painful if you've have multiple columns (which would more typically be the case).  What this process does not seem to deliver is the maximum value for the dominant field which can be used for the bivariate thematics.
Just for general information, the expressions I use in Excel to prepare predominance data for use are "=MAX(#2:#2)" to generate the dominant value and "=INDEX($#$1:$#$1,MATCH(MAX(#2:#2),#2:#2,0)) to generate the dominant variable heading.  These are based on when the first row of the data contained Field Headings and the variables commenced in Column 2.
As mentioned in my earlier post, a lot of the MapInfo Pro users that I work with in the commercial sector are only using it as a small part of their role and would generally not have the capability to build the expressions you suggest themselves.  This is why I think that an in-built tool would be beneficial as the potential results can certainly add to the business decision support outputs inherent within MapInfo Pro and grow/strengthen it's role as a mission critical tool.
If it was to be developed as an enhancement, my initial suggestion would be to build a tool with a simple UI that allowed the user to identify the fields to be considered and perhaps output a temporary table that included the map object, it's UID, and the dominant maximum value and dominant field heading as attributes.  The user could then use this result to do any thematic mapping or other analysis/visualization and/or could save the result as a permanent table if they felt it was going to be needed at some later stage.
As a long time MapInfo user/supporter, I felt that this could be a positive and relatively simple enhancement given most of the capability is there somewhere anyway, and the benefit would be both in efficiency/useability (through not having to prepare data externally or build complex expression statements in MapInfo Pro) and added analysis capability (always good for users paying their subscription fees to get new features).  Would also demonstrate nicely in a new release session and could highlight some of the Precisely Data Catalogue in doing so!

Cheers



------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-08-2021 11:59
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

Have you given some thought as to what you would need in Pro to make this easier?
I think it can be done already by using IIF() or Cond() functions.

You should be able to do the same thing you did in excel inside of Pro with a careful update statement to set the value of a new column.

You can also create an individual value theme without creating a new column:

For World Table shading a county by pop_male vs pop_female (similar to democrat vs republican)
theme on str$(iif(world.pop_male>world.pop_fem, "male", "female"))
we have to use str$ since iif can return any type.


For multiple columns you could use nested IIF() expressions

Perhaps we could add a new functions for example Predominance(expr, column1, label1, column2, label2, column3, label...)
expr would be some way to identify how to compare the values (Max, Min, not sure what else)

Or maybe you would just want some ui to select multiple columns and we build the expression for you.

Thoughts?

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-08-2021 00:13
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Stefan

Great response and certainly agree that the key fields are easy to generate if the data you start with is non-spatial and requiring preparation before being imported into MapInfo Pro.  These can then be joined to the geographies as per the examples above.

What I should have made more clear is that in the majority of examples above, the original variables data was acquired and already in MapInfo Pro as spatial data.  This meant that to produce the predominance required exporting the tabular data, conducting the analysis and then importing the key fields back into MapInfo Pro to do the spatial join before being able to produce the outputs.

Indeed, there are a number of datasets from Precisely and other suppliers/sources (Demographics, Consumer Spend, any Census data, etc) that come spatially enabled with a plethora of attributes that could benefit from predominance as an analysis output.  Not having to go through the export/prepare/import/join process would add another form of visualization into MapInfo Pro off the shelf.

Granted that data preparation is a significant element of any project, there are a number of users that I work with in the commercial sector where MapInfo Pro is only a part of their job description, that could struggle with the creation of the key variables in an external product.  I'm sure many users would not be aware of the ability to produce predominance in MapInfo Pro (which could be a good opportunity for Precisely to deliver a YouTube video demonstrating the capability or a MapInfo Monday article) regardless of whether the data to begin with is spatial or non-spatial.

If the data is acquired in spatial format it would be much more efficient and powerful to have the capability available without needing extra work to prepare.  Alternatively, if non-spatial data is imported without the key predominance already derived but a variety of attributes available, it would be good if MapInfo Pro could do the heavy lifting after joining the data with the geography.

As demonstrated in my original post, MapInfo Pro can certainly deliver the predominance outputs, but only if the data is appropriately prepared externally to allow it to be joined to an existing spatial object.  I just wanted to test the appetite of the Community for something like Predominance to be built in to a future MapInfo Pro release as an enhancement.  Benefit would be both in efficiency/useability (through not having to prepare data externally) and added analysis capability (always good for users paying their subscription fees to get new features).


Cheers





------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-07-2021 03:36
From: Stefan Hausmann
Subject: Predominance for MapInfo Pro

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

To generate a selection on top of this data structure, that calculates the maximum value of profession for each state is fairly easy, and this table can then be used to map those joined to the state geometries, - a functionality that MiPro already has.

So if you would use for all the cases you explained the raw data instead of the already aggregated form, - MiPro wouldn't need a new functionality.

..but as i said: only my two cents.

best regards
Stefan

Just a note that this tool, PredominanceHelper, can be found in the MapInfo Marketplace from within MapInfo Pro.

With one click and you have it installed, registered in the Tools window, and loaded.

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------

Original Message:
Sent: 12-21-2021 18:44
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hey Peter

Thanks for your support and assistance with this.  Will download and take a look over the holiday period and come back to you with any questions or comments.

Have a great Xmas and New Year and same to everyone in the Mapinfo Pro Community

------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia
------------------------------


Original Message:
Sent: 12-21-2021 04:31
From: Peter Horsbøll Møller
Subject: Predominance for MapInfo Pro

Hi Tony

Just in time for Christmas, I share a small tool with you.

It's still early days so it doesn't come with any user interface yet but you can use the features from the SQL Window and the MapBasic window.

The basic idea is to give you a couple of functions to use to get the name of the predominant column and the value from the predominant column too.

It works like this:
In the SQL Window enter these statements:
Dim sTab As String
Dim sCols As String

sTab = "MBI_CA_Census_Division_SocioDe"
sCols = "AGE_T0014,AGE_T1529,AGE_T3044,AGE_T4559,AGE_T60PL"

Update sTab
Set AGET_PRED_NAME = GetPredominanceColumnCSL(sTab, sCols),
AGET_PRED_MAX = GetPredominanceValueCSL(sTab, sCols)

You can see how it looks below:

Now let me explain the statements:
The first two lines create two variables to hold the table to update and the columns to compare.

The next two lines assign values to these two variables. First, you specify the name you want to update, and secondly, you specify a comma-separated list of columns whose values to compare.

The last statement uses an Update statement to get the name of the column with the maximum value and the maximum value. These values get inserted into two existing columns (AGET_PRED_NAME and AGET_PRED_MAX).

In my example, I'm comparing some socio-demographic data to find the predominant age group for each of the areas. In the browser window in the image above, you can see that the values have been assigned to the two columns.

I can now create a new thematic map that shows which age group is predominant in each region/census area.

I have attached a zip with the compiled application. Please give it a try and let me know if you find any issues. I'll try to build an interface around this but I'm not sure if I get to that this year.

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data


Original Message:
Sent: 12-16-2021 01:43
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Bob & Peter

Thanks for taking the time to look into this a bit deeper.
Apologies for not responding sooner but have been away from the office for 3 days without access to my computer.
Will take a look into your responses and come back to you ASAP now that I'm back online.

Cheers

------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-14-2021 01:35
From: Peter Horsbøll Møller
Subject: Predominance for MapInfo Pro

I like it, Bob

I had spent some time thinking about how to do this with pure MapBasic and had come to a similar process with two functions that could provide the maximum value and the column that holds that maximum value.

Doing it through MapBasic would be more tricky as you can declare a function that takes a varying number of parameters so I probably would have ended up using an array.

The only thing that would make your solution even better is if the user also could specify an "alias" in lack of a better word) for the column names returned. Instead of using "POP_0_14" use "Population 0-14 years old" as an example.

This could be done using a Cond() expression that replaces the column names with the friendly name. But it would be smoother if it was part of the Predominant Tool. Just a thought.

PS: You stole my typo. It not IFF() but IIf() :-)

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data


Original Message:
Sent: 12-13-2021 16:22
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

I spent a few hours on friday trying this out.
I made two new mapbasic functions that take a variable number of parameters.
The first one - PredominantVal(expr1, expr1 [, exprn...]) returns the maximum of the values passed in.
The return value has the type of the first expression, so it works for ints, float, string, etc.

The second one tPredominantCol takes the table and the names of the columns as strings.
It returns the name of the column with the predominant value (for the current row)

fetch first from world
print world.Country
print PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)

print PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")

Then i created a tool using python to prompt for a table and then the columns you want.
It currently adds two temporary columns to the table but it could create a temp table instead as you suggested:
It takes the col type from the first col specified.
I could also have used Nested IFF() functions to get the same result without creating new functions.

add column world("MAX_Value" float) from world set to PredominantVal(world.Pop_0_14, world.Pop_15_64, world.Pop_65Plus)
add column world("CAT_Dominant" char(31)) from world set to PredominantCol(world, "Pop_0_14", "Pop_15_64", "Pop_65Plus")



Finally, my other thought was to just extent Max() to take multiple values, then create something like WhichMax() to return the index of the max parameter. A little more work for the tool writer but maybe more generic. Could then do same for Min() and maybe some other functions as well.

Thoughts?

-Bob

ps. I am happy to share the python code if any one wants it. Like this thread if you would like to see it in the product...

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-09-2021 23:24
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Bob

Thanks for taking the time to give this some consideration and respond.
Sounds like the IIF() expresions could deliver a partial result (dominant field) but might be a little painful if you've have multiple columns (which would more typically be the case).  What this process does not seem to deliver is the maximum value for the dominant field which can be used for the bivariate thematics.
Just for general information, the expressions I use in Excel to prepare predominance data for use are "=MAX(#2:#2)" to generate the dominant value and "=INDEX($#$1:$#$1,MATCH(MAX(#2:#2),#2:#2,0)) to generate the dominant variable heading.  These are based on when the first row of the data contained Field Headings and the variables commenced in Column 2.
As mentioned in my earlier post, a lot of the MapInfo Pro users that I work with in the commercial sector are only using it as a small part of their role and would generally not have the capability to build the expressions you suggest themselves.  This is why I think that an in-built tool would be beneficial as the potential results can certainly add to the business decision support outputs inherent within MapInfo Pro and grow/strengthen it's role as a mission critical tool.
If it was to be developed as an enhancement, my initial suggestion would be to build a tool with a simple UI that allowed the user to identify the fields to be considered and perhaps output a temporary table that included the map object, it's UID, and the dominant maximum value and dominant field heading as attributes.  The user could then use this result to do any thematic mapping or other analysis/visualization and/or could save the result as a permanent table if they felt it was going to be needed at some later stage.
As a long time MapInfo user/supporter, I felt that this could be a positive and relatively simple enhancement given most of the capability is there somewhere anyway, and the benefit would be both in efficiency/useability (through not having to prepare data externally or build complex expression statements in MapInfo Pro) and added analysis capability (always good for users paying their subscription fees to get new features).  Would also demonstrate nicely in a new release session and could highlight some of the Precisely Data Catalogue in doing so!

Cheers



------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-08-2021 11:59
From: Bob Fortin
Subject: Predominance for MapInfo Pro

Hi Tony,

Have you given some thought as to what you would need in Pro to make this easier?
I think it can be done already by using IIF() or Cond() functions.

You should be able to do the same thing you did in excel inside of Pro with a careful update statement to set the value of a new column.

You can also create an individual value theme without creating a new column:

For World Table shading a county by pop_male vs pop_female (similar to democrat vs republican)
theme on str$(iif(world.pop_male>world.pop_fem, "male", "female"))
we have to use str$ since iif can return any type.


For multiple columns you could use nested IIF() expressions

Perhaps we could add a new functions for example Predominance(expr, column1, label1, column2, label2, column3, label...)
expr would be some way to identify how to compare the values (Max, Min, not sure what else)

Or maybe you would just want some ui to select multiple columns and we build the expression for you.

Thoughts?

------------------------------
Bob Fortin
Software Architect and Distinguished Engineer
MapInfo Pro Development Team


Original Message:
Sent: 12-08-2021 00:13
From: Tony Maber
Subject: Predominance for MapInfo Pro

Hi Stefan

Great response and certainly agree that the key fields are easy to generate if the data you start with is non-spatial and requiring preparation before being imported into MapInfo Pro.  These can then be joined to the geographies as per the examples above.

What I should have made more clear is that in the majority of examples above, the original variables data was acquired and already in MapInfo Pro as spatial data.  This meant that to produce the predominance required exporting the tabular data, conducting the analysis and then importing the key fields back into MapInfo Pro to do the spatial join before being able to produce the outputs.

Indeed, there are a number of datasets from Precisely and other suppliers/sources (Demographics, Consumer Spend, any Census data, etc) that come spatially enabled with a plethora of attributes that could benefit from predominance as an analysis output.  Not having to go through the export/prepare/import/join process would add another form of visualization into MapInfo Pro off the shelf.

Granted that data preparation is a significant element of any project, there are a number of users that I work with in the commercial sector where MapInfo Pro is only a part of their job description, that could struggle with the creation of the key variables in an external product.  I'm sure many users would not be aware of the ability to produce predominance in MapInfo Pro (which could be a good opportunity for Precisely to deliver a YouTube video demonstrating the capability or a MapInfo Monday article) regardless of whether the data to begin with is spatial or non-spatial.

If the data is acquired in spatial format it would be much more efficient and powerful to have the capability available without needing extra work to prepare.  Alternatively, if non-spatial data is imported without the key predominance already derived but a variety of attributes available, it would be good if MapInfo Pro could do the heavy lifting after joining the data with the geography.

As demonstrated in my original post, MapInfo Pro can certainly deliver the predominance outputs, but only if the data is appropriately prepared externally to allow it to be joined to an existing spatial object.  I just wanted to test the appetite of the Community for something like Predominance to be built in to a future MapInfo Pro release as an enhancement.  Benefit would be both in efficiency/useability (through not having to prepare data externally) and added analysis capability (always good for users paying their subscription fees to get new features).


Cheers





------------------------------
Tony Maber
Principal Consultant - Location Analytics & Data
Spatial Decisions
Sydney - Australia


Original Message:
Sent: 12-07-2021 03:36
From: Stefan Hausmann
Subject: Predominance for MapInfo Pro

Hi Tony,

from my limited point of view this "Predominance"-View is, as you already wrote, a problem of data preparation only.
The tables you're using as your mapping sources are already the result of an aggregation, pivot-style.
Take your example of professions per state. The normalized form of the not aggregated base data should be something like this:

To generate a selection on top of this data structure, that calculates the maximum value of profession for each state is fairly easy, and this table can then be used to map those joined to the state geometries, - a functionality that MiPro already has.

So if you would use for all the cases you explained the raw data instead of the already aggregated form, - MiPro wouldn't need a new functionality.

..but as i said: only my two cents.

best regards
Stefan