Table Joins

Non-spatial data from a database table, spread sheet or even a text file can be loaded in a Cadcorp SIS Overlay Table Join.

A Table Join allows one-to-many connections and requires a unique schema column on the  SIS spatial overlay.

Because Table Joins are created on an overlay and not a dataset, they can be stored in the SWD.

The dataset read elsewhere will have no information on any join within the SIS Workspace Definition (SWD). Only a single Table Join is permitted for each overlay.

Joined columns retrieve values from a table via the Join expression.

SIS Desktop 9 can read the following non-spatial database and file formats:

  • Oracle
  • Microsoft SQL
  • PostgresSQL
  • HTML Documents (*.html; *htm)
  • Lotus 1-2-3 (*.wk*)
  • Microsoft Access (*.mdb; *.accdb)
  • SQLite (.db/.sqlite)
  • Microsoft Excel (*.xls; *.xlsb; *.xlsm)
  • Microsoft FoxPro (*.dbf; *.dbc)
  • Paradox (*.db)
  • Text Files (*.txt;*.csv;*.tab; *.asc)
  • dBASE III, IV, 5 (*.dbf)

Note: File-based datasets can be read via a direct to file connection (as ActiveX Objects or ODBC Data Source.) Cadcorp SIS (64 bit) will use ADO (via the Microsoft Access Database Engine) to read Microsoft Excel *.xlsx files, Cadcorp SIS (32 bit) uses DAO to read spread sheets. Cadcorp SIS (64 bit) can only read .xlsx files and not .xls spread sheets.

Benefits of linking data to overlays

  1. Linking tables allows you to query data from external non-spatial data sources. Spatial data which is represented as overlays in Cadcorp SIS Desktop can be linked to non-spatial items via a unique identifier.
  2. The Overlay Table Join allows you to create one-to-many connections between the spatial items and non-spatial data.
  3. Linking data allows you to minimize the size of geographic datasets and facilitate the benefits of relational database technologies. The same data source can be referenced by several Cadcorp SIS Desktop overlays.
  4. Linking data helps to reduce the amount of duplicate data and maintain the actuality of data sources.

Note: Due to limitations with the current implementation of sorting, the Table Window no longer allows sorting operations on Overlays with a Joined Table which has many-to-one joins enabled.

Joining a table to an overlay

Note: See also API method: SetOverlayJoinTable

A Table Join can be initiated easily using the Connect command (Overlays > Schema > Join)

The Connect wizard allows you to select and setup the appropriate data connection to access the data table. Select one of the available Database or File Types.

Option Description

Microsoft SQL Server

Connects to a Microsoft SQL Server database

PostgreSQL

Connects to a PostgreSQL database

Oracle 8

Connects to an Oracle 8i, or later database

File-based database

Reads one of the supported database formats directly from a file, e.g. Microsoft Access .mdb/accdb, or SQLite .db/.sqlite

Comma Separated File

Reads delimited data, e.g. comma- or tab-separated, from a file.

ActiveX Data Objects

Connects to a OLE-DB Data Source using Microsoft ActiveX Data Objects.

ODBC Data Source

Connects to an ODBC Data Source
  • Select the location of the database or file and then click Next
  • The next display depends on the option chosen in the above Database Types dialog. Each option will display specific dialogs to enable the required dataset.
  • In the next window, which is the Recordset dialog.

Once a table connection has been established the table column for the Overlay Table Join can be selected.

The Database Tables/Columns pane shows the tables and columns available in the connected database.

The Selected columns pane displays the selected database table columns.

Use the Alias column in this list (move the slider to the right to reveal) to map the database column name onto a SIS Desktop property/attribute name:

If the recordset is being used as part of a database-based dataset, you can enter existing property names (e.g. _shape$) to redefine the created items' properties.

Select the entire table or expand the list to display individual fields.

Click Add to move the required item(s) to the Selected columns pane.

SQL Where: the SQL WHERE clause can be used to join several different tables or restrict which rows are imported. Do not include the WHERE SQL keyword. A Dynamic connection updates recordsets when the source dataset is changed.

The final step of the connection wizard allows you to configure the join.

  • The join expression must be defined from a unique schema expression to a connected table column.
  • A one-to-many connection can be allowed for the table join. It is not necessary to display the column which is used for the join. By definition this column will be identical with an existing column in the Overlay Data Schema.
  • Joined table columns can be added to the dataset schema or replace the existing dataset schema.

Untick any of the boxes for any of the joined columns not to be displayed.

The Allow one-to-many relationships will allow multiple rows in the joined table to be added to single items in the overlay.

Select Import joined columns as attributes if required and enter the Attribute prefix.

Select Append columns to Schema or Replace Schema columns as appropriate.

Click on the Join from expression... fx button to display the Expression Builder dialog:

Click OK.

Select the ... to Table Column from the drop-down list and click Finish.

The connect wizard will close and complete the table join. At the Overlays dialog Join tab click OK.