Happy #MapInfoMonday,
Sometimes finding objects that relate to another object directly might not answer the questions you ask. Typically, when you use a spatial relation you are looking for objects that are within, contain, or intersect another object. But there are cases where the objects don't really touch. In these cases, you can't use the standard spatial operators in MapInfo Pro such as Within, Contains, and Intersects.
With MapInfo Pro v2019, we have given you some new options. Let's have a quick look at one of these.
Cross Join Support in MapInfo Pro v2019
We need to start by looking at the Cross Join support that was added to MapInfo Pro v2019. This opened up new options for joining tables with SQL Select in MapInfo Pro.
Earlier you had to join two tables on two alphanumerical attributes, columns, where you specified the values in these should be the same for a join to happen. You could also use the spatial operators and so indicate that the objects relate directly to each other.
With MapInfo Pro v2019, we did however upgrade the SQL engine. This among other improvements adds the capability of Cross Join support. In a Cross Join, you basically join each record in your first table with each record in your second table. Then you add conditions that the records of this Cross Join result need to meet. Where you earlier were limited to the Equal (=) operator and the spatial operators, we have also extended this greatly in MapInfo Pro v2019.
You can for example now compare the result of a function call to a column or a fixed value, you can use other operators such as <, >, <=, >=, and more.
Finding Objects Nearby
In this example, we'll show how you can use a function to calculate the distance between the objects of the two tables and return only the records where this distance is under a given threshold. Suddenly you are comparing objects that aren't directly related, objects that don't touch.
We will use the Find by Location tool that was also added to MapInfo Pro v2019.
The use case is that the planning department of a local authority is creating a new planning zone for a specific area of the city. They need to inform not only the people directly affected by this new planning zone about it but also people living close by. Here below, you can see the new planned zone that has been selected on the map.
The table I have selected the object from contains multiple records but I'm only looking for the addresses that are nearby this one record. To find addresses nearby, I activate the Select by Location from the SQL dropdown on the Table, Map, or Spatial tab.
In the dialog Select by Location shown, I specify that I want to select the records in the table Addresses compared to the selected object from the table Planning_Zones_Proposals. The spatial method I want to use is Within a Distance, and the Distance I have set it to 250 meters.
In the output options, I have chosen to add the result to a map window and to zoom to the selection.
When I execute the command, I can see the result in my map as highlighted records and also as a new separate query layer. The map has also been zoomed to the extent of the selected records.
You can of course also browse the result to see the attributes for the selected records. The query result is like any result created from a SQL statement.
If you are using an older version of MapInfo Pro, you can do something similar by combining a sub-select query with a Buffer statement. That is however not as elegant as this new way.
And like any query based on SQL Select in MapInfo Pro v2019, you can find the query in the list of queries in the SQL Window, load it into the script area and modify it if you want to. Below you can see the query that was created by the Select by Location dialog to find addresses nearby the selected planning zone.
Happy querying. Do let me know if you are looking for details about specific features of MapInfo Pro for an upcoming #MapInfoMonday post.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------