| || |
Be the Query Ninja - Working with data from multiple tables (using joins). Part Two - Using Geographic Joins
This is the second instalment in our series to help you build your MapInfo query muscles. Last month we focused on joining tables where a matching data field exists. Once again, that is commonly referred to as arelational join because you are relating the data from the two tables together with this matching column. This month we demonstrate how you can match data from different tables in a geographic way. To do this you do not need any matching columns. What you do need are tables that have map objects, for example points in one table and regions in another.
- Given a table of school catchment areas (regions) you can count up how many students (points) attend each school.
- Given a table of patients (points) and a table of hospital catchment areas (regions) you could create a new table containing all of the data from the patient table along with the name of the hospital catchment area they are in.
- Given a table of customers and polygons representing the 15 minute driving regions around store locations, you can count up how many customers are within the 15 minute driving regions.
- Given a table of boundaries representing the areas served by different fire stations, you can count up, total and average the risk scores for all of the locations containing high fire risk materials.
All three of the above examples are working with points and regions. It is possible to perform geographic joins and calculations with all forms of map objects. We will expand this topic in a future article.
Geographic Joins: Fire stations and high risk locations
In the map below the points represent fire risk locations and the regions represent the area served by a particular fire station. A Browser window of the hazard sites also appears to give you an idea about the data we are working with.
We will joint these two sets of data together in two different ways.
Example 1: Aggregating the data. How many high risk locations are served by each catchment area?
The following is the SQL Select dialog box filled out to count how many high risk locations are served by each catchment area. The table also includes the average Hazard severity for the sites served by the station.
Note the Join condition: Firestation_catchments.obj Contains Hazard_sites.obj
The operator "contains" refers to the geographic objects. The wording has the region containing the point because the region table is listed first in the dialog box. If you list the Hazard_sites.obj table first then the join condition would change to Hazard_sites.obj within Firestation_catchments.obj.
Example 2: Add the fire station to the hazard site info
For this next example we will join both of the tables together in such a way as to be able to look up a hazard site and know which fire station serves it. This will be a simple join where we take all of the columns from both tables.
The name of the column in the Firestation_catchment table is Area_name.
Here is how you set up the SQL Select dialog box:
Here is the resulting Browser window. The column at the end called "Area Name" contains the name of the Firestation catchment.
A little additional technical info
When you use an aggregate function in an SQL query the result will not have any map objects. If you do not specify an aggregate function then the first table's map objects will be included in the resulting query. This means you can map the query result.
If you do not use an aggregate function then the table will have map objects. The first table listed (with map objects) will bring its objects to the resulting Query table.
Finally, joining tables can be accomplished with the Table > Update Column menu command. In some cases this might be a preferred technique.
Have questions? Why don't you join our LinkedIn Group?
We welcome all input on this article. Or on the journal. Or if you have questions. A good place to start a discussion is in the LinkedIn Group we have set up as a companion to this journal. Click here to visit this group.
Article by Tom Probert, Editor of "The MapInfo Professional" journal
When not writing articles for "The MapInfo Professional", Tom is a Product Marketing Manager for Pitney Bowes Software. When not working he likes to see movies with car chases, explosions and kung-fu fighting.