|Be the Query Ninja! Calculating the amount of overlap between regions |
| ||I have probably said it before, but I don't mind repeating myself, one of the most powerful features of MapInfo Professional is the built-in SQL language. |
This time we will take a look of one of the frequent asked questions on the MapInfo-L discussion group. "How do I calculate how much of the objects in table A are within or overlap the objects in table B".
Use cases for this could be:
Depending on your datasets a region in the first table can be intersected by one, more or perhaps no objects from the second table. You need to consider this in your query. Do you want to see exactly which records from the first table are intersected by records from the second table? Or do you just want to know the total area of overlap.
- How much of my development zones are within flood areas?
- How much of the parcels are covered by planning zones?
- How big a portion of my sales districts can be covered by 2 hour driving regions?
- How many square meters of each house or property will be affected by a potential flood?
Let's have a look at how to do this through the power of the SQL capabilities in MapInfo Professional. We will use a data set containing cadastre parcels and a data set of urban areas to illustrate this.
We will calculate how many square meters of each parcel are within urban areas.
First we need to define what we call a spatial (or geographic) join. We need to specify when an object in the first table is somehow "connected to" or is "intersecting" an object in the second table. For a primer on geographic joins, read this article which appeared in a previous issue.
In this case we will use the Intersects spatial operator to determine which parcels and urban areas have overlaps. The Intersects operator will return true when even the first object is in any way touching the second object, that is if they have a node in the same position, two line segments cross each other or one of the objects is partly or entire within the other object.
Using the SQL Select dialog box we can define the join like this:
We specify the join condition as the first condition in the "where Condition" field:
Cadastre.Obj Intersects Urban_Areas.Obj
The problem with this join is, as we will see later, is that it is very wide. It will find connections between objects that don't really share any area - for example, objects that just have a single node in common. We will fix this later.
The result of this query will look like this.
Notice that I did limit the number of columns in my output to only three. Also notice that I specified the cadastre table as the first table. This means that MapInfo Professional will select/highlight the cadastre objects.
The next step is to calculate the actual overlap between the objects.
For this we will use two MapBasic functions:
As we want the result of the overlap calculation in our browser, we will add this expression to the Select Columns field:
- Overlap(object1, objects2) calculates that actual overlap between two objects and returns the object representing the intersection of these two objects
- CartesianArea(object, area_units) calculates the size/area of a given object using the specified area units. If you are working with non-projected (Longitude/Latitude) data use the SphericalArea() or Area() function instead.
CartesianArea(Overlap(Cadastre.Obj, Urban_Areas.Obj), "sq m") "Overlap sqm"
This is how the SQL Select dialog appears:
The result appears as follows. The column "Overlap_sqm" contains the amount of overlap. That is how much of the urban area is overlapping into each cadastre parcel.
Next, in the Browser window, I have sorted the column "Overlap sqm" from the lowest values to the highest values. There are some cases where the area the two objects have in common is close to zero.
Note that there are 7 records where the size of the overlapping area is less than one square meter.
Depending on your data sets and their accuracy, you might wish to exclude some of the records from your result. You can do this by filtering the values of the overlapping area to meet a certain condition. In this example we will exclude the cases where the area is less than 2 square meters.
We can do this simply by adding a filter condition to the where Condition field in the SQL Select dialog:
CartesianArea(Overlap(Cadastre.Obj, Urban_Areas.Obj), "sq m") > 2
When you add another condition to the query, you need to use either the AND or OR operator to concatenate the two conditions. In this case we need to use the AND operator.
The result looks similar to the previous query - it just holds less records.
This is just one example of how to exclude certain records from the result. You can add more conditions to your query if desired. For example, you could exclude the parcels that are roads if they might be misleading in terms of the analysis you are doing.
Working with lines
The same technique can be used when working with regions and lines. The intersection between a region and a line (or polyline) is a polyline, so remember to calculate the length of the overlap in stead of the area of it.
To calculate the length of a line use:
This calculates the length of a given linear object using the specified distance units. If you are working with non-projected (Longitude/Latitude) data use the SphericalObjectLen() function or the ObjectLen() function instead.
If you want to learn more about these functions take a look at the MapBasic Reference manual and the MapBasic User Guide. You can find both of these on our Web site here.
Article by Peter Horsbøll Møller, Presales Consultant
When not writing articles for "The MapInfo Professional" journal, Peter helps Pitney Bowes Software customers to get the most from their software and is a prolific contributor to the mapinfo-l Google Group. When not working he is an aficionado of fine whiskey and good cigars.