VERIFIED SOLUTION i
X

How to join a table containing region objects (polygons) to a non-mappable data table

UPDATED: March 28, 2017


See the following instructions to join a table containing region objects (polygons) to a non-mappable data table:
  • Open a mappable table containing Region objects such as postal codes, county boundaries, US Zip Codes, etc. For this example US Zip Codes will be referenced.
  • Open a data table that has a list of Zip Codes in a column (or other matching postal codes, or county codes for example if using something other than Zip Codes).  The Zip Code field must be formatted as Character (5) in order to join properly.
  • It is important to match the data type for the column containing the postal code, county code, etc. since they are usually character types that may appear as numbers.
  • Use Table>Maintenance>Table>Modify Table Structure to verify the data types for the fields containing the codes that will be used to join the two tables.
  • The data table would typically be a list of Zip Codes that would come from an Excel file, .txt, or .csv format.  Once opened in MapInfo Pro, the table will only display as a browser having no region or polygon objects yet associated with the file.
  • When opening an Excel file, .txt, or .csv from the Home>Content>Table>Open Table dialog, make sure the box is checked at the bottom of the dialog to “create copy in MapInfo format for read/write”.  This will allow an editable copy to be created as a native MapInfo table which can be modified directly within MapInfo Pro.
  • During the process of opening the Excel file, make sure the Zip Code field is formatted as Character (5) for this example so it will join to the Zip Code table containing the Region objects.
  • Go to Table>Selection>Sql Select and use the following syntax example to join on the common zip code field information.
  • In this example, there is a “zip” column in each table that contains the actual Zip Code numbers:
           Select Columns: *
           From Tables: ZipCodePolygonTable, NoMapDataTable
           Where Condition:  ZipCodePolygonTable.zip = NoMapDataTable.zip
  • Click OK to run the query and join the two tables.
  • It is important to select the mappable table first in the query dialog “from Tables” section in order for the resulting query to inherit the map objects, in this case, the Zip Code polygons.
  • Once the query is finished, make a permanent table (queries are temporary) out of the results by going to:
  • Home>File>Save>Save Copy as… and select the Query# that matches the resulting browser and click Save.
  • Give the query a new name such as DataWithZipObjects and save to a folder.
  • Then go to Home>File>Open Table and open the new, permanent table.  The new DataWithZipObjects should open as a map.  Use Table>Content>New Browser to see the data for the new table.

Downloads

  • No Downloads