MapInfo Pro

Over the coming weeks, I'll write a #series of articles about the #SQL improvements we have made in MapInfo Pro v2019. This will, of course, look at the Select statement but I'll also dive into the other improvements.

In this article, I'll dive into some of the many improvements we made to the SQL language. Specifically, we will look at the new spatial aggregates.

Spatial Aggregates

MapInfo Pro has always supported  a number of numerical aggregates like Sum(), Min(), Max(), Avg(), WtAvg() and Count(*). You typically use these to get some statistics from your data and often you will use them in combination with Group By.

That could be to calculate the total length of roads in your dataset grouped by road name:

From the example above I get a list of street names with the matching total length of each in kilometers. Unfortunately, the Group By part of the Select statement makes MapInfo Pro drop the spatial part of the records. That's until MapInfo Pro v2019.

With MapInfo Pro v2019, we introduced four spatial aggregates that make it possible for you to also aggregate the spatial part of the records. The four spatial aggregates are:

• AggregateBuffer
• AggregateCombine
• AggregateConvexHull
• AggregateMBR

You can also find the spatial aggregates in the Aggregate list with the other aggregates.

AggregateBuffer combines the object and adds a buffer around the final object. This also means that this aggregate takes a few more parameters:

• object is an expression that returns a geometry.
• resolution is a SmallInt value representing the number of nodes per circle.
• width is a float value representing the radius of the buffer
• unit_name is the name of the distance unit (e.g., "mi" for miles, "km" for kilometers) used by width.

The other three spatial aggregates take only one parameter, the object which typically would be the Object column.

AggreagteCombine combines the input objects into a single object. It only takes one parameter

AggreagteConvexHull combines the objects and returns a convex hull around the set of points contained within the aggregated set of objects.

AggreagteMBR combines the MBR, Minimum Bounding Rectangle, of the objects within the aggregated set of objects.

Some examples

The first example uses the example I used above to calculate the total length of all the roads by road name. Here I extend this query by adding the combined road polylines to the result:

Select roads.Street1, Sum(ObjectLen(obj, "km")) "Total_Length_km", AggregateCombine(obj)
From GBR_South_Links_Pro As "roads"
Group By roads.Street1
Into Selection

The difference lies in the last expression in the first line: AggregateCombine(obj)

You can also use the aggregates to highlight relationships between a selected record and other records in the same table. We can again use the roads as an example. Imagine you have selected a single road segment and want to know the full extent of that road. We can get the road name from the selection using a subselect and then create a buffer around all the roads with the same name. The query looks like this:

Select roads.Street1, AggregateBuffer(obj, 24, 50, "m")
From GBR_South_Links_Pro As "roads"
Where roads.Street1 In (Select Street1 From Selection Group By Street1)
Group By roads.Street1
Into Selected_Road

On the map, the result could look like this. Notice that I have used the SQL Window to add the query to the map and give it an override color.

And you can use the spatial aggregates to derive a new dataset from an existing by combining polygons with the same value in a given column, similar to the command Combine Using Column. With SQL Select you just create a temporary dataset, not a new base table save to the disk. An example could be create continents from a table of countries where each country has a value specifying the continent.

Here is the SQL Select statement:

Select WORLD.Continent, AggregateCombine(OBJ)
From WORLD
Group By WORLD.Continent
Into Continents

In the image below, you can see the result. I have selected Asia so that you can see how it's connected.

I hope you have found this article useful and that you can see some benefits in these new spatial aggregates.

What will you use these for? Are there other agrregates that you would like to see us add to MapInfo Pro, that can be numerical, spatial and even string based aggregates?

------------------------------
Peter Horsbøll Møller
Distinguished Engineer
Pitney Bowes Software & Data
------------------------------