# How to find most frequent values?

Entry layers: polygons - no attributes (columns/values), points - needed attributes (columns/values)

How to get this result?: polygon layer with attribute (column/value) of most often (most frequent - MODUS) of values from points values within polygons

Probably best for me with SQL select in Mapinfo but I can convert layers and do it in Qgis or ArcGIS if you know the simple way in these SW.

Additionally: How to get count of points within polygons and add it as column (attribute) to polygon layer in Mapinfo? - solved - this not help to main question

Example of points within polygons - polygons represented by outlines (a lot of overlapping - it is import from "nonGIS" software through dxf - nobody cared about topology etc.):

Here is example how to get maximum/minimum/mean... there is a simple solution in ArcMap - right click on layer - Joins and Relates / Join... / Join data from another layer based on spatial location (as described here: In ArcGIS how can I append the number of points that are located inside each individual block groups of the entire US?) - but I need MODUS.

• The question does not clearly distinguish between a maximum count of points in polygons and the mode of the counts, which would be a count that appears most frequently among the polygons. Which one do you really want? (I edited the title to reflect the former, which has an easy solution; if it's the latter, I would suggest breaking this into two questions: one concerning counting points within polygons and a second concerning obtaining the mode of a discrete attribute in a data table.) Jun 8, 2012 at 13:21
• BTW, What is the relevance of the illustration to the question? There are no polygons apparent in the figure. Jun 8, 2012 at 13:22
• @whuber No, probably it is my bad English so to make it clear: Except of additionall question which I hoped could help me bit to solve the main problem, I am not much interested in number of points in polygons. What I need is to assign values (atributes) from points layer to polygons layer. NOT number of points. So lets say in my case there are usually around 6 points in corners of polygons which holds the needed attributes. From these points values I want to get MODUS (mode, most often value) and assign it to polygon which contain these points -
– Miro
Jun 12, 2012 at 0:18
• respectively assign the modus values to all polygons - nothing if there are no points over polygons (polygons are represented with outlines in the figure). So the change of the title does not reflect my question. Anyway thank you for your help.
– Miro
Jun 12, 2012 at 0:19
• In other words and precisely what I am looking for: I have two layers: points and polygons. Points layer has attribute (column) YEAR. I need to get this attribute (column) YEAR and add it to polygons. Where there are no points within polygon, there will be nothing. Where is one point within polygon, there will be that Year of point. Where are more points within polygon - I need to get MODUS (mode, most often value). The post I have linked shows how to get maximum/minimum/avarage etc. but well, unfortunately I need Modus and thats what is this whole question about.
– Miro
Jun 12, 2012 at 2:53

## 1 Answer

Update a column with the number of point

In MI Pro go to: Table > Update Column... and fill the dialog out like this:

Table to update: POLYGONS Column to Update: (or pick an existing column) Get Value From Table: POINTS Calculate: Count

Click on Join and specify "where objects from table POINTS objects from table POLYGON" Push OK in the Specify Join dialog

And Push OK in the Update Column dialog to run this update statement

Update a column with the most frequent value

This is a bit more tricky as we need to find the most frequent value and MI Pro doesn't have a single function to do so. You can however find this values thru a number of queries: - Count how many times each value occurs - Find the maximum number of occurencies - Use the maximum number to identify the most frequent used values

For this to work your polygons need a unique ID (can be a column updated with the ROWID or a unique name). This column is called ID in the statements below.

With MapInfo Pro's SQL that would look like this (run these from the MapBasic window which can be opened from Options > Show MapBasic window):

``````Select POLYGONS.ID, POINTS.YEAR, Count(*) "NUM"
From POLYGONS, POINTS
Where POLYGONS.Obj Contains POINTS.Obj
Group by POLYGONS.ID, POINTS.YEAR
Into YEAR__GROUPED

Select ID, Max(NUM) "MAX_NUM"
From YEAR__GROUPED
Group By ID Into YEAR__GROUPED__MAX

Select ID, YEAR, NUM From YEAR__GROUPED
Where ID + "#" + NUM In (Select ID + "#" + MAX_NUM From YEAR__GROUPED__MAX)
Into YEAR__GROUPED__MODUS
``````

To run the statements from the MapBasic window, make sure you highlight the entire statement before hitting the Enter key on your keyboard.

Now follow the description above to update or add a new column to your POLYGONS table. In this case you need to use the final query table (YEAR_GROUPED_MODUS) and join the two tables on the ID column of the POLYGON table.

You can also run this statement (to add a new temporary column) thru the MapBasic window:

``````Add Column POLYGONS (YEAR Float)
From YEAR__GROUPED__MODUS
Set To YEAR Where ID = ID
``````

Or this to update an existing column called YEAR:

``````Add Column POLYGONS (YEAR Float)
From YEAR__GROUPED__MODUS
Set To YEAR Where ID = ID
``````

Also note that you will get a "random" result if some of the points have more than one most frequent value.

• Thank you for answering additional question. What about the main one - Modus (mode, most often value)? :-)
– Miro
Jun 8, 2012 at 7:50
• Wow, thank you very much for explanation. Just one detail - in line: `Where ID + "#" + NUM In (Select NAVN + "#" + MAX_NUM From YEAR__GROUPED__MAX)` shoul be ID instead of NAVN right? With NAVN it stops with NAVN not definied.
– Miro
Jun 18, 2012 at 4:47
• Yes, you are right - good catch. I have edited the article to show the correct names Jun 26, 2012 at 15:09