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.
Best 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):
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:
Or this to update an existing column called YEAR:
Also note that you will get a "random" result if some of the points have more than one most frequent value.