Joining Tables Geographically (Using Geographic Operators) - MapInfo_Pro - 2023

When two tables have graphic objects, MapInfo Pro can join the tables based on the spatial relationship between those objects. Thus, even if your tables do not share a common column, you may be able to join the tables.

Geographic operators allow you to select objects on the basis of their spatial relationship to some other object. MapInfo Pro has a special column name you use with geographical operators: "obj" or "object". This column name refers to the graphic objects that are attached to your table.

The geographic operators go between the objects being specified. Select the geographic operators from the Operators drop-down list.

The following table lists the geographic operators:

The difference between Contains and Within on the one hand, and Contains Entire and Entirely Within on the other, hinges on how the geographic comparison is made. For Contains and Within, the comparison is based on object centroids. For Contains Entirely and Entirely Within, the comparisons are based on the whole object.

The following graphic illustrates this point:

In each case, object A contains object B because the centroid of object B is inside the boundary of object A. However, in the cases at the left and in the middle, part of object B is outside the boundary of object A. Only in the case to the right is all of object B inside object A. Only in this case could we assert "object A Contains Entire Object B" or "Object B Entirely Within Object A." Further, if A contains entire B, then A contains B, and If A is entirely within B then A is within B.

MapInfo Pro can perform a simple Contains or Within comparison more rapidly than a Contains Entire or Entirely Within. Therefore, unless you are absolutely sure that objects are completely inside other objects, you should use Contains and Within rather than Contains Entire or Entirely Within.

Geographic operators provide a way of joining tables. When there are no columns in the tables on which you can base your join, you can use a geographical operator to specify the join (in the Where Condition field). If you want to perform a query that involves both a Cities table and a States table, you can join the tables using either of the following expressions:

1. Cities.obj within States.obj
2. States.obj contains Cities.obj

In either case, MapInfo Pro finds the cities within each state and then associates a row for a city with the row for the state that contains it. In the same SQL Select query, you could also use aggregate functions to count the number of cities per state or to summarize city-based data on a statewide basis.

When you have a table of counties and one of customers, where counties are polygons and customers are points, you could specify a geographic join using either of the following geographic expressions:

1. Customer.obj within County.obj
2. County.obj contains Customer.obj

Geographic operators are particularly useful in conjunction with subselects.

Performing Subselects

MapInfo Pro allows subselects in SQL Select. A subselect is a select statement that is placed inside the Where Condition field of the SQL Select dialog box. MapInfo Pro first evaluates the subselect and then uses the results of the subselect to evaluate the main SQL Select.

For example, suppose you want to select all states where the population is greater than the national per-state average for 1990. In other words, if the average state population is five million, you want to select all states having population greater than five million. In effect, you want to use the following filter criterion in the Where Condition field:

Pop_1990 > The Average State Population 

However, you do not know what that average is. But you know that MapInfo Pro can calculate that average using the following aggregate expression:

Avg(Pop_1990)

To calculate the average state population, enter a subselect in the Where Condition field. The Where Condition field can then compare the results of the subselect against the Pop_1990 column. To perform such a query, fill in the SQL Select dialog box as follows:

The subselect is in the Where Condition field, following the greater-than (>) operator. The subselect must be enclosed in parentheses.

The most useful subselects contain a select clause, a from clause, and a where clause, as follows:

select some columns from some tables where some condition

Consider the following SQL Select, which selects all cities in states with more than 4,000,000 people:

The subselect returns the graphic objects for all states with a 1990 population greater than 4,000,000. The main Select statement then gets all the cities that are in any of the states chosen in the subselect. Notice that the main Select statement uses a geographical operator (within) to do this.

Although the preceding query uses both the States table and the Cities table, the Cities table is the only table listed in the From Tables field. That is because we are not joining the two tables. We are only using States in the subselect. If a table is only used within a subselect, the table name does not need to appear in the From Tables field.

In the next example, we select all states that intersect Tennessee, in other words, all of the neighboring states.

First, MapInfo Pro performs the subselect:

select obj from states where state = "TN"

This subselect finds the graphic object for Tennessee. Next, the main Where condition finds all objects in the States table that intersect the graphic object for Tennessee. You could use a similar query to select all street segments that cross a given street.

Now consider this example:

This query finds all counties containing a dealer. The main Where condition has the form: a county object contains a dealer object. The set of dealer objects is produced by the subselect: select obj from dealers. MapInfo Pro selects the rows for every county object that contains a dealer object.

Finally, here are a few notes on subselects:

• You can use tables in the subselect that are not listed in the From Tables field. However, you must list these tables in the From clause of your subselect (as shown in the preceding examples).
• When the subselect is used with the "any" or "all" keywords, the subselect must return one and only one column. The following syntax example is not valid, because it tries to return two columns (state_name and Pop_1990):
• Any(select state_name, pop_1990 from states)
• When the subselect is not used with "any", "all", or "in", the subselect must return exactly one row value. The following example would not be valid, because the subselect returns a set of rows:
• obj within (select obj from states where POP_1990 > 2000000)
• When the subselect is not used with "any", "all", or "in", you cannot use a Group by Columns clause in the subselect.
• You cannot have nested subselects, that is to say you can only have one subselect per Select statement.