Return Most Common Value

[Cirrus]

Hello All. In ArcGIS I am trying to correct an erroneously classified area (called "developed") with a more accurate value based on the classification of surrounding polygons. I have a table that lists the classification of polygons in 8 directions of the compass (ALCES_NW, ALCES_N, etc). A column is created for each direction - thus producing 8 columns of data. I need to return the most common value out of the 8 columns of data for each UID and copy the result in an empty field (DEV_RECLASS).
There are some conditions that need to be adhered to:

• I only want agriculture or natural landcover types to be analyzed (footprint types like roads or transmission lines must be ignored)
• if all of the 8 columns contain footprint types then a value of Unknown should be written to DEV_RECLASS
• if there is a tie between landscape types (eg: 3 columns are Ag-Annual Crop and 3 are Grassland and 2 are footprint) then the first landscape type value encountered should be returned

I have attached a screen shot of a small subset of my data. As an example: UID 1146400 would have a value of Wetland - Herb returned to DEV_RECLASS since it occurs 5 out of 8 times in the direction columns. UID 1348632 would return a value of Ag -Perennial Crop since it is the only agriculture/natural landscape type in each of the 8 columns.

Any help would be greatly appreciated - I'm not even sure where to begin.

 

[mdlueck]

Any help would be greatly appreciated - I'm not even sure where to begin.

I would begin by building a SQL SELECT right in the SQL view, SELECT which columns you want returned.

Then start by adding criteria to the WHERE clause to eliminate the records you are not interested in.

So starting with your first bullet, hopefully that is just multiple possible values for one column, so have that be a value list where the value must be in the list of options, then it is cleared to be in the result set. (unless knocked out by further criteria in the WHERE clause)

Second bullet needs a SQL CASE statement, test for all column conditions if true then put what value you want, ELSE put some other value. (I would add this later in you development of the query.)

The last bullet you do by ordering the records so that the top record is always the one you want selected, then do a JOIN "OUTER APPLY SELECT TOP 1" type scenario (assuming Access SQL can handle such) and that returns you only the one row, not rows for every combination of values.

Example of OUTER APPLY / SELECT TOP 1 query
http://www.access-programmers.co.uk/forums/showthread.php?p=1257545

 

[Cirrus]

Thank you for your reply Michael. I have tried including a WHERE clause in my query and it doesn't look like it is going to work in this case since I don't want to eliminate any records I only want to ignore certain values within the 8 columns of data. I must analyze every row of data and the WHERE clause does not allow for this. Perhaps I don't need the WHERE clause based on your comments to my first bullet. Can you provide more information on how to accomplish your suggestion to my first bullet?

 

[mdlueck]

Can you provide more information on how to accomplish your suggestion to my first bullet?

Two samples quickly came up... grep is my friend...

AND ISNULL([p].[stocktypeid], -1) IN (-1, 3, 10)  --Only consider parts which are NULL/B/P Stocking Type
WHERE ISNULL([utoolstatusid], -1) IN (-1, 1, 2)  --Only consider parts which are No Tool / Not Quoted / Out for

So first is obviously not the only criteria in there WHERE clause (AND means there was at least one criteria prior, so logically AND'ed to the prior criteria), second was the first criteria in there WHERE clause.

 

[Cirrus]

Thank you for your comments Micheal but I am new to writing code in Access and I'm confused by the use of ISNULL since none of the data I want to evaluate contains null values. I will also need some more direction on how each of values in the sample code relates to my data. Sorry for the need to be spoon fed here but like I said, I'm a newbie to writing and understanding code.

 

[mdlueck]

I'm confused by the use of ISNULL

That SQL is from Stored Procedures to be executed by a SQL BE DB.

Never mind about ISNULL, the rest of the syntax is still valid examples of how to SELECT based on a pick list of supplied values.