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:

- 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.
- 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()`

.
- 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:

- Set the
**Projection** to match the coordinate system used to extract the coordinates.
- 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.

------------------------------

Peter Horsbøll Møller

Principal Presales Consultant | Distinguished Engineer

Precisely | Trust in Data

------------------------------