topic Re: Symbolise by maximum value field's name in ArcGIS Pro Questions

Symbolise by maximum value field's name

HanliePetoors — Wed, 07 Aug 2024 11:58:37 GMT

Hi,

I'm using ArcGIS Pro 3.3.1.

I have some census data that shows how many people are employed in different industries, say Industry A, Industry B and Industry C. So each polygon has a value for Industry A, Industry B and Industry C.

How can I symbolise the census polygons to show the name of the industry that employs the most people? So if polygon 1 employs most people in Industry C, I want to symbolise it with a specific colour corresponding to Industry C.

Is this possible?

Thanks

Hanlie

Re: Symbolise by maximum value field's name

Laura — Thu, 08 Aug 2024 17:35:53 GMT

Could you create a new field that contains data just for which industry is dominated in that polygon and then symbolize based off of that new field?

Re: Symbolise by maximum value field's name

Ed_ — Thu, 08 Aug 2024 18:11:00 GMT

This might sort of help get in that direction but you might have to tweak it a little to make it work with Arcade under Symbology in Pro.

Compare values from multiple fields and return the highest and lowest values of a pop-up using Arcade expression in ArcGIS Online

Re: Symbolise by maximum value field's name

Bud — Thu, 08 Aug 2024 18:40:38 GMT

Can you show us some data in a screenshot of the attribute table or in an Excel export of the attribute table?
What kind of geodatabase? For example, Oracle 18c enterprise geodatabase.
Are there multiple industry fields in the existing data: INDUSTRY_A, INDUSTRY_B, INDUSTRY_C?
Do you want a dynamic solution (the data changes over time) or is populating a static MAX_INDUSTRY field via the Field Calculator acceptable?
You could post an Idea for this, asking for dynamic, out of the box functionality (no code) in the symbology pane, to see what Esri says.

Re: Symbolise by maximum value field's name

HanliePetoors — Mon, 12 Aug 2024 07:46:12 GMT

@Laura it's easy enough to find the maximum value but to get the field name corresponding to the maximum value is tricky.

Regards

Hanlie

Re: Symbolise by maximum value field's name

HanliePetoors — Mon, 12 Aug 2024 08:46:14 GMT

@Ed_ thanks I managed to do what I wanted with a modified version of this arcade.

I give an example here for source of income, one of the groups of fields that I'm interested in.

//specify the field names to reference to var src=[ $feature.IncmSFrmng, $feature.IncmOwnBsns, $feature.IncmWgSlry, $feature.IncmOldAgPnsn, $feature.IncmCshRmtncs, $feature.IncmRtrmntIncm, $feature.IncmOrphnGrnt, $feature.IncmDsbltyGrnt, $feature.IncmOthr ] var max_src=Max(src) var max_txt = [] //Assigning the highest value for (var v in src){ if (src[v] == max_src){ var kodeer = Decode( v, 0, 'Subsistence Farming', 1, 'Own Business', 2, 'Wage / Salary', 3, 'Old Age Pension', 4, 'Cash Remittances', 5, 'Retirement Income', 6, 'Orphan Grant', 7, 'Disability Grant', 8, 'Other Income', '' ) Push(max_txt, kodeer) } } return Concatenate(max_txt, TextFormatting.NewLine)

Regards

Hanlie

Re: Symbolise by maximum value field's name

HanliePetoors — Mon, 12 Aug 2024 08:23:53 GMT

@Bud I'm working in a SQL Server Express SDE.

Below is an example of the data with the maximum values highlighted. For OBJECTID 1 and 3 I want to return the value 'Industry Agriculture Forestry Fisheries' and for OBJECTID 2 I want to return the value 'Industry Manufacturing'. Either as a label or a value that I can use for symbology.

The data is not all that dynamic, it being census data but I have about five of these groups of fields that I would like to handle like this.

I was thinking of a SQL view or a query layer but I can't figure out how to get the field name from the maximum value. This gives me the maximum value for one of these sets of fields (source of income) but this is where I get stuck and I thought perhaps there were other ways of doing it.

select OBJECTID , (SELECT Max(v) FROM (VALUES ([IncmSFrmng]) , ([IncmOwnBsns]) , ([IncmWgSlry]) , ([IncmOldAgPnsn]) , ([IncmCshRmtncs]) , ([IncmRtrmntIncm]) , ([IncmOrphnGrnt]) , ([IncmDsbltyGrnt]) , ([IncmOthr]) ) AS value(v)) as [MaxIncmSrc] from [ldr].[NAMIBIADEMOGRAPHICSPROVINCIAL2011]

Regards

Hanlie

Re: Symbolise by maximum value field's name

Bud — Mon, 12 Aug 2024 11:06:02 GMT

What do you want to happen if there is a tie between two industries?

Re: Symbolise by maximum value field's name

HanliePetoors — Mon, 12 Aug 2024 11:13:23 GMT

@Bud the likelihood of that is extremely low but I suppose one can just select the first one found.

Regards

Hanlie

Re: Symbolise by maximum value field's name

Bud — Mon, 12 Aug 2024 14:08:57 GMT

I normally work with Oracle, not SQL Server, but I was able to quickly mock up the SQL Server solution from this answer: Getting the column name that holds the maximum value of a row

I did it in db<>fiddle.

with temp as (
   select objectid, industry, count
   from employment_by_industry
   unpivot
   (
     count
     for industry in (agr_forest_fish, mining_quarry, mfg, electric, water_sew)
   ) u
)
select distinct t1.objectid, t2.industry
from temp t1
cross apply (
    select top 1 objectid, industry
    from temp
    where t1.objectid = objectid
order by count desc) t2

I don't know if that's the simplest solution or not.

@AlfredBaldenweck, @JoshuaBixby, and @AJR might find this interesting.

Re: Symbolise by maximum value field's name

Bud — Mon, 12 Aug 2024 14:14:29 GMT

Idea: Symbolize using the field name that has the maximum value

Re: Symbolise by maximum value field's name

HanliePetoors — Tue, 13 Aug 2024 06:40:24 GMT

@Bud thanks this is a very elegant piece of SQL and a great solution.

Regards

Hanlie

Re: Symbolise by maximum value field's name

Bud — Tue, 13 Aug 2024 14:34:38 GMT

Glad it helped.

For anyone interested, here's an Oracle solution:

select objectid, max(col_name) keep (dense_rank first order by col_val desc) max_col_name
from employment_by_industry
unpivot (
  col_val
  for col_name in (
    agr_forest_fish, mining_quarry, mfg, electric, water_sew
  )
)
group by objectid

https://dbfiddle.uk/DeaRoikf

Source: https://forums.oracle.com/ords/apexds/post/idea-function-to-get-the-column-name-that-has-the-maximum-v-5088

Re: Symbolise by maximum value field's name

Bud — Wed, 09 Oct 2024 20:40:19 GMT