How to find most frequent values?

Question

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.

Answer 1

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.