MapInfo Pro

Happy #MapInfoMonday

With MapInfo Pro v2019, we gave the SQL support in MapInfo Pro a review by improving both the syntax and capabilities of the MapInfo SQL. We also introduced a couple of new interfaces to buildings SQL Select statements.

One of the big improvements to the syntax was the addition of the four spatial aggregates.

Before MapInfo Pro v20219, MapInfo Pro only supported numerical aggregates likes Count(*), Sum(), Min(), Max(), Avg() and WtAvg(). All these aggregates can help you get statistics from your numerical attributes.

When we used any numerical aggregates, typically in conjunction with a Group By statement, the spatial data was dropped from the query result. By introducing spatial aggregates, we can maintain the spatial data by also aggregating the objects. This allows us to aggregate numerical data and at the same time use a spatial aggregate to combine the spatial records for which we are aggregating the numerical data.

We added four spatial aggregates:

• AggregateBuffer(): Will create a buffer around the combined objects.
• AggregateCombine():  Will combine the objects into a single object
• AggregateConvexHull(): Will create a "rubberband" polygon around the combined objects
• AggregateMBR(): Will return the MBR (Minimum Bounding Rectangle) of the combined objects

You can use the SQL Select dialog or the new SQL Window introduced in MapInfo Pro v2019 when you want to use the spatial aggregates.

Here's one example where I have created a query in the SQL Select dialog to calculate the average speeds during AM Peak, PM Peak, and Night by combining the streets with the same names. The original table holds many street segments for each road and by grouping these by their name, I can get a combined view for the entire road.

Select STREET, STREET2
   , Sum(LENGTH), Avg(SPEED_AMPEAK), Avg(SPEED_PMPEAK), Avg(SPEED_NIGHT)
   , AggregateCombine(obj) 
From Streets 
Group By STREET, STREET2 
Order By STREET, STREET2 
Into AGGR_Streets

Do you have some good examples of where it would be beneficial to use one of the other tree spatial aggregates?

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