While working my way through some of the older ideas on the Precisely Ideas, I came across this idea about joining two or more tables. The idea refers to finding points that fall within polygons in two separate tables.
This is already possible in MapInfo Pro, and with MapInfo Pro v2019, it has been made easier. The problem is often that you need the order of the tables and join conditions to line up: The first table needs to be joined to the second table, and the second table needs to be joined to the third table.
It could look like this:
table1.obj within table2.obj And table2.obj Contains table3.obj
Let me give you a simple example.
Happy #MapInfoMonday
Finding Points Within Polygons in Two Tables
Imagine this: Several schools are looking for businesses where they can send their pupils to introduce them to the work life of ordinary people. First of all, they need to ensure that the business is located close to the schools. And they also need to ensure the business is located near a bus route.
In my example, this results in three tables to be joined:
-
Business: The businesses in the area
-
Bus Routes 500m: A 500-meter buffer around roads with a bus route. This shows that the pupils can walk 500 meters from a bus route.
-
Schools 2km: A 2000-meter buffer around schools. This shows the neighborhood of the schools. Instead of a buffer, you could have used a drive time polygon.
The task now is to identify businesses that fall within a bus route buffer and a school buffer. SQL to the rescue!
As I stated above, the trick is to order the tables and the join condition so that they align. As we know, it is the business that should be joining to the other two tables; the table holding the business points needs to be the second table. The order of the other two tables isn't important.
This results in this as the From part of the Select statement:
From Bus_Routes_500m As "r"
, Businesses as "b"
, Schools_2km as "s"
As I'm using MapInfo Pro v23.2, I can give my tables an alias. That makes it easier to refer to the tables throughout the Select statement. Table aliases were introduced in MapInfo Pro v2019.
Next up is the Where part of the Select statement that joins the three tables. Do note that the order of the tables must align with the From part.
Where r.Obj Contains b.Obj
And b.Obj Within s.Obj
As you can see, I first join the bus route buffers to the business points, and then I join the business points to the school buffers.
The full Select statement looks like this:
Select s.NAME As "SchoolName", b.NAME As "BusinessName"
, ObjectDistance(Centroid(s.Obj), b.Obj, "m") "Dist_m", b.Obj Object
From Bus_Routes_500m As "r"
, Businesses as "b"
, Schools_2km as "s"
Where r.Obj Contains b.Obj
And b.Obj Within s.Obj
Into Selection
I'm using the Object keyword after b.Obj to ensure that I get the business points returned by the query. This was also introduced in MapInfo Pro v2019.
When I run the query, I get this result
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------