Adding to a Table - MapInfo_Pro - 2023

One important operation in maintaining tables is the ability to update the data contained in the table. MapInfo Pro's Update Column feature allows you to:

• Add a temporary column or update an existing column with data from another table
• Update a table
• Place graphic information into visible columns

Update Column command is located on the TABLE tab in the Edit group.

Updating Information in a Subset of a Table

Use Update Column to update information in a subset of a table. For example, you could marquee the southeast of the United States, then use Update Column to set the "sales rep" field to the name of your sales representative for the Selection table.

You can update all the rows in a table or a selection of rows. To update a selection of rows you need to select the rows using SQL Select, the Select command, the Boundary Selection command, or the Radius Selection command, and then update the selection.

Adding a Temporary Column using Update Column

If you need to bring data from one table into another, you can use the Update Column command in MapInfo Pro. In fact to bring this data into the table, you can either add a temporary column or you can update an existing column. In both cases, go to the TABLE tab and click Update Column to bring over the information.

You have tremendous flexibility with how information is imported and what information is transferred. You can perform aggregate functions to transfer averages, minima, maxima, sums, weighted averages, and proportional sums. You can also create expressions to derive additional information from the data.

To add a temporary column:

1. Open at least two tables.
2. On the TABLE tab, in the Edit group, click Update Column to open the Update Column dialog box.
3. Choose the table to which the temporary column will be added.
4. Choose the table from which MapInfo Pro will retrieve the update information.
5. Accept to Add a temporary column from the Column to Update option.

   If necessary, specify the information MapInfo Pro will need to associate the data (join) in the two tables by using their common tabular or graphic data.

6. Choose how to produce the column data by specifying how to calculate the information based on columns and expressions at the Calculate and of boxes.
7. Select the Browse Results check box to display the table with its temporary column in browser format.
8. Click OK. The temporary column is added based on the specified information.

   When you make changes to the data table, the temporary columns update automatically.

   To save the table with its new temporary columns, on the TABLE tab, in the Content group, click Save, and Save Table. If the temporary columns are added to a table in a workspace, the changes are automatically saved when you save the workspace.

To illustrate the Update Column command, we have created the following example. In this example we have a company which maintains two tables, a table of states (STATES) and a table of customers' order amounts (US_CUSTG). You can use these files and the Update Column command to calculate the sum of your customers' order amounts and report that information by state. Update Column creates a temporary column in the STATES2 table to store the information from the Order_amt table. During the operation MapInfo Pro calculates each customer's order amount with the order amounts of other customers from the same state. A browser of the STATES2 table shows the sum of order amount by state.

You can follow along with similar tables of your own.

Placing Graphic Information into Visible Columns

One use of Update Columns is to place graphic information into visible columns. Tables containing graphic objects (that is, maps) store graphic information that is not visible in Tables. You can get some of that information into columns so that information can be viewed in a Browser window.

To put graphic information into a browsable format:

1. On the TABLE tab, in the Edit group, click Update Column to open the Update Column dialog box.

   Use this dialog box to place the graphic information, such as area, perimeter, or length, into the temporary columns.

2. On the TABLE tab, in the Edit group, click Save, and Save Copy As to open the Save Copy As dialog box.

   Use this dialog box to save the temporary columns in a new table. The new table will contain all of the original columns, in addition to each of the temporary columns.

If you edit your objects, the information in the visible columns must be updated to reflect the changes you have made.

Calculating Sums using Update Column

To calculate the sum of orders and report the amount using Update Column:

1. On the TABLE tab, click Update Column and fill in the Update Column dialog box:
   
   

   

   
   
   • Table to Update - Select this table to update from this list.
   • Column to Update - Select this column to update from this list or select Add new temporary column.
   • Get Value From Table - Select the table that contains the update values in this list.

     Notice the Join button is activated when you select Add new temporary column. In this example the Join operation will match columns in each table based on equivalent columns that hold common information such as where State from table STATES2 table matches State from the US_CUSTG table. In other situations you can match information based on common geography.

     Note: You can use this dialog box to create a temporary column in the original data source file. To do this, select the same table in both the Table to Update drop-down list and the Get Value From Table drop-down list.
2. On the TABLE tab, click Update Column and fill in the Update Column dialog box:
   
   

   

   
   
   • Table to Update - Select this table to update from this list.
   • Column to Update - Select this column to update from this list or select Add new temporary column.
   • Get Value From Table - Select the table that contains the update values in this list.

     Notice the Join button is activated when you select Add new temporary column. In this example the Join operation will match columns in each table based on equivalent columns that hold common information such as where State from table STATES2 table matches State from the US_CUSTG table. In other situations you can match information based on common geography.

     Note: You can use this dialog box to create a temporary column in the original data source file. To do this, select the same table in both the Table to Update drop-down list and the Get Value From Table drop-down list.
3. At the Calculate list box, choose Sum for purposes of this example. You can also select: Value, Avg, Count, Min, Max, Sum, WtAvg, Proportion Sum, Proportion Avg, or Proportion WtAvg from this list. (For a discussion of these functions, see Aggregating Data.)

   At the Of list box, MapInfo Pro automatically defaults to the first numeric field in the table when you choose any function other than Value. You can select another column, as appropriate.

4. Click OK to begin the update. MapInfo Pro updates the STATES2 table and reports the order amount by state in a Browser or in the table you selected.

   When the order amount of a customer changes, you can simply make the change in the Order_amt table. MapInfo Pro automatically updates the sum of the order amount for that state in the STATES2 table.

5. To save the temporary column, on the TABLE tab, click Save Copy As.

If saved in a workspace, MapInfo Pro recomputes the column whenever you open the workspace. Also, the Update Column dialog box defaults to the last column that was updated and the last expression that was used for updating the column, whenever you run the command. For additional details about saving a table, see Saving a Table or a Copy of a Table.

Geographic and Descriptive Joins

A join allows you to join two tables. To do this, MapInfo Pro must know how to associate records in the two tables.

For example, assume you are shading STATES by the values in SALES. Obviously, you want all Pennsylvania sales to be associated with Pennsylvania, all Maryland sales to be associated with Maryland, etc. MapInfo Pro has two ways of doing this:

1. From a common descriptive field.
2. Geographically/graphically.

In this case, the most likely descriptive field contains the state name or an abbreviation for the state name. Thus the STATES table might have a field called Abbr, containing the abbreviation of the state name, and SALES might have a field called State, that contains the state abbreviation. MapInfo Pro matches the entries in these two fields so that information in the SALES State field is matched to the record having the same entry in the Abbr field of STATE, thus matching NY (sales) to NY (state), PA (sales) to PA (state), etc.

However, if both tables contain graphic information, MapInfo Pro can also make a graphic match.

For example, if you are a conservation official and you want to shade a map of COUNTIES according to the number of FIRES that occurred in the last year. Your FIRES database has been geocoded. In this case MapInfo Pro could examine the coordinates of each fire location and determine the county where the fire had occurred. That would allow MapInfo Pro to associate each fire record with the appropriate county.

Specifying a Join Based on Descriptive Criteria

There are two versions of the Join dialog box. When both tables can be mapped, the dialog box allows you to choose whether to make the join on a geographic basis or on a non-geographical attributes basis.

When only one table can be mapped, the dialog box only allows you to specify the join in terms of non-geographical attributes.

To specify a join based on descriptive criteria:

1. Open at least two tables.
2. On the TABLE tab, in the Edit group, click Update Column to open the Update Column dialog box.

   If both tables are mappable, go to step 4.

   If only one table is mappable, go to step 3.

3. Choose different tables for the Table to Update and Get Value From Table drop-down lists.
4. Click the Join button. The Specify Join dialog box displays.
5. Click the radio button next to the Where drop-down list in the Specify Join dialog box.
6. Use the Where drop-down list to choose the match field in the source table.
7. Use the Matches drop-down list to choose the match field in the target table.
8. Click OK.

Specifying a Join Based on Geographic Criteria

To specify a join based on geographic criteria:

1. Open at least two mappable tables.
2. On the TABLE tab, in the Edit group, click Update Column to open the Update Column dialog box.
3. Choose different tables for the Table to Update and Get Value From Table drop-down lists.
4. Click the Join button in the Update Column dialog box. The Specify Join dialog box displays.
5. Click the radio button next to Where object from table.
6. In the Where object table from drop-down list, choose either contains or is within, depending on the geographic relationship between objects in the two tables.

   Polygons "contain" points, lines, or other polygons. Points, lines, and small polygons are "within" other (larger) polygons.

7. Click OK.