MapInfo Pro

 View Only
Back to discussions
Expand all | Collapse all

MapInfo Monday: Calculating Average Point Location

  • 1.  MapInfo Monday: Calculating Average Point Location

    Employee
    Posted 03-18-2024 04:49

    Happy #MapInfoMonday

    Today's question is one I got from our technical support a few weeks back.

    They sought a way to calculate aggregated values from points inside vector grid cells. These were values like the minimum, maximum, and average values.

    This first step they had under control.

    But instead of just aggregating these values up into the vector grid cells, they also wanted to calculate an average position of the points inside each grid cell and use this average location to represent the aggregated values.

    This can be done using an SQL statement where you join the two tables on their spatial object, group the result by a unique name or ID from the vector grids, and then calculate various statistics

    My query ended up looking like this:

    Set CoordSys Table DriveTest_Measurements

Select g.Description
   , Min(p.Value) As "Min_Value"
   , Max(p.Value) As "Max_Value"
   , Avg(p.Value) As "Avg_Value"
   , Avg(CentroidX(p.OBJ)) As "Avg_X"
   , Avg(CentroidY(p.OBJ)) As "Avg_Y"
From DriveTest_Measurements As "p", Grid_1km As "g"
Where p.Obj Within g.Obj
Group By g.Description
Into Selection

    A couple of things to pay attention to:

    1. Note that I set the coordinate system to match the coordinate system of the table. This is to ensure I get my coordinates extracted using the same coordinate system as my points table.
    2. I use the Avg() aggregate to calculate average values for both the X and Y coordinates of my points. I extract these coordinates from the spatial OBJ using the functions CentroidX() and CentroidY().
    3. I group the resulting query by the Description column in my Vector Grid.

    In the SQL Window inside MapInfo Pro, it would look like this:

    This is what the result looks like with the aggregated values.
    I now save the resulting query into a named table and open this table. I then need to create points for the calculated average coordinates.
    From the Spatial tab, I select Create Points from the Create dropdown. In the Create Points dialog, I make a few adjustments:
    1. Set the Projection to match the coordinate system used to extract the coordinates.
    2. Make sure the coordinates are read from the two columns holding the average X and Y values.

    You can also set the Symbol to use and set the table to be added to a map.

    Below you can see how the location of the points is based on the average location of the points inside each grid cell. This is very clear when you look at cells where there is only points in one of the corners of the cell.
    Let me just take this one step further. You can also use a weighted average and let the value in each point add weight to the average location. This means that points with a higher value will count more when calculating the average location.
    The query is very similar. Use WtAvg() instead of Avg(), and include the Value column as a parameter.
    Set CoordSys Table DriveTest_Measurements

Select g.Description
   , Min(p.Value) As "Min_Value"
   , Max(p.Value) As "Max_Value"
   , Avg(p.Value) As "Avg_Value"
   , WtAvg(CentroidX(p.OBJ), Value) As "Avg_X"
   , WtAvg(CentroidY(p.OBJ), Value) As "Avg_Y"
From DriveTest_Measurements As "p", Grid_1km As "g"
Where p.Obj Within g.Obj
Group By g.Description
Into Selection

    The result moves some of the points but not all. The red points have been calculated using WtAvg().

    I hope you have found this useful. We will be back next week with another #MapInfoMonday article. Stay tuned!



    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------