Join

The Edit - Join command applies the power of JOINs in a point and click dialog with no need to write SQL.   

 

The dialog provides extensive capabilities to combine data from tables to tables, drawings to drawings, images to drawings, drawings to images, and images to images, providing a faster and easier way to do table to table relations, vector to vector overlays, raster to vector, vector to raster, and raster to raster overlays than by using traditional spatial overlays.  A single, easy to use interface provides a wealth of capabilities by applying power of a unified join concept that is applicable to all.

 

The Join command provides classic joins between tables, and it also provides spatial joins between drawings and other drawings in a map, and between drawings and images in a map.  It also provides spatial joins between images (rasters), and between images and drawings.    Spatial joins done by the Join dialog are an easier way to accomplish classic GIS "overlay" operations.  Spatial joins between drawings and images, or between images and images, are an easier, more straightforward way of doing what various dialogs do in classic GIS packages, such as the transfer heights feature in Release 8.

 

The Join command appears in the Edit menu when the focus is on a writable table window,excluding system tables such as mfd_meta or mfd_root or mfd__srid and similar, or on a writable drawing or image layer in a map.

 

The Join dialog alters a table, query, drawing, or image, called the original table, drawing or image, by adding new fields or channels populated with data taken from another table, query, drawing or image, called the joined table, drawing or image.  Existing fields or channels in the original table, drawing, or image can also be filled with data copied from the joined component.

 

Behind the scenes, the dialog creates an update query using SQL joins to populate new fields or channels and to copy data into existing fields or channels in the original table. Checking the Save update query box saves that update query into the project for future use.  We can run the saved update query at any time to update the original table with any changes in the joined or original component.   We can also edit the update query to make custom changes before running it.

 

Added fields and channels are static, that is, if data in the joined component changes the fields or channels in the original component will not automatically be updated.  We can easily update the result any time we want by running a saved update query.  

 

To follow along with table examples in this topic, download the books.mxb project that contains the publishers and titles tables used in examples.

 

The easiest way to rapidly learn how to apply the Join dialog is to review the step by step examples listed in the Join Examples  topic.

Joins from a Table to a Table

To introduce the Join dialog, we begin with joins between tables.   Ultimately, all joins are about data that is joined between tables, whether the data in the table is attribute data for drawings or tile data containing pixels and channels for images.

 

 

Add fields to the original table using the Join dialog:

 

  1. With the focus on the original table window, choose Edit - Join.
  2. In the upper right box choose the joined table or query from which fields will be joined.

  3. In the second row of boxes, choose the key fields from each table that have common values.

  4. Click the Add button to choose a field in the joined table that will provide data for a new field in the original table.

  5. Double-click a cell to edit the field name (new fields) or to choose a different source field (joined table) or to choose a different transfer method.

  6. Check the Save update query box to create a query in the project that captures what the Join dialog will do.

  7. Press Join Component.

 

The new field will be added to the original table, and populated with values from the joined table where there is a match in the two fields specified to have common values.  In the example above, a titles table for book titles will have added to it a publisher's name field taken from the publishers table, where the pub_id (publisher ID) is the same in both tables.

 

Load data into an original field from the joined table:

 

  1. With the focus on the original table window, choose Edit - Join.

  2. In the upper right box choose the joined table or query from which fields will be joined.

  3. In the second row of boxes, choose the key fields from each table that have common values.

  4. In the row for the field that is to be loaded, double-click into the rightmost column cell and choose the field from the joined table that will provide data.

  5. In that same row, double-click into the center column cell and choose the transfer method, for example, copy.

  6. Double-click a cell to choose a different source field (joined table) or to choose a different transfer method.

  7. Check the Save update query box to create a query in the project that captures what the Join dialog will do.

  8. Press Join Component.

 

Update the table using an update query:

 

  1. Right-click the saved update query and choose Run.

 

If either table changes, running the update query will update the titles table so it incorporates all changes in both tables.

 

 For faster performance, the key fields used in the Join method must have an index on them.   We can use whatever btree index type works with that field.  Allowing duplicates or NULLs in the index is OK.  

Controls

With the Join dialog open, press F1 to open a quick reference guide in a browser window. 

 

 

Two additional options appear when the original component is an image.

 

 

Original table, drawing or image

The table, drawing, or image to which data will be added.  Automatically picked from the window and layer that had the focus when Edit - Join was launched.

Joined table, drawing or image

The table, drawing, or image from which data is taken.  When the target is a table or query, choose a table or query in the same data source, for example, saved within the .map project.   When the target is a drawing or image that is a layer in the map, choose a drawing or image that is a layer in the same map.  

Key fields

A field in the original component and a field in the joined component that are compared, or matched, to guide how data from the joined table will be added to fields in the original table.   The dialog will try to guess and load by default which field we might want as a key field, using logic described in the Notes section below.  Joins using drawings and images are always spatial join, automatically using geom and tile fields.

 

Important: fields used as key field should have an index on them.  Drawings automatically have an index on a Geom field, and Images also have an index on the Tile field, so spatial joins using Geom or Tile fields will be able to use an index.   In other cases, take a moment to pop open the Schema dialog on any tables used in a join to verify that an index exists on the field that will be used as a key field.

Join method

The join method used to compare key fields.  For tables, equal to is the only method available, matching records when the key field in the original table has the same value as the key field in the joined table.

Add

Add a new field to the original table or drawing, or a new channel to the original image, by choosing a field or channel from the joined component.   A new field added to the original table or drawing will have the same name and data type as the field in the joined table that provides data, with the join method set to copy.   We can double-click into the name of the new field to change the name.  New fields will be shown with blue preview color background.

 

Images are limited to a maximum of four channels, but tables or drawings can have an effectively unlimited number of fields.

 

When there is more than one field or more than one channel in the joined component, the pull-down menu in the Add button includes a Fields... option at the end, to choose multiple fields or multiple channels to add at the same time using the Add Fields dialog.

 

The Add button will be enabled if the original table, drawing or image allows adding new fields or channels.  Some data sources might allow updating existing fields, but not adding new fields.  We can still use the Join dialog with such tables by populating existing fields in the original table with data from the joined table.

 Delete

Enabled when a newly-added field or channel is selected.  Ctrl-click a new field to select it, and then press the Delete button to delete it.  This allows us to remove newly added fields if we decide we do not want to add them.

Filter Box

Enter text to be matched, case not significant.   The list of fields or channels will be reduced to show only those which include the text.  Matches either fields from the original table or fields from the joined table.  For example, entering pub will reduce the field list in the illustration to pub_id, pubdate, and pub_name.

(Fields list)

Each row in the list is a field in the original table or drawing, or a channel in the original imge, including new fields or channels to be added.

 

  • Read-only fields or channels are shown with gray background color.  Data from the joined table cannot loaded by the join into read-only fields.
  • Writable fields or channels that were in the original table when the Join dialog was launched are shown with white background color. Data from the joined table can be loaded by the join into writable fields.
  • Fields or channels added to the original table with the Add button are shown with blue preview background color. Data from the joined table can be loaded by the join into added fields.

 

The list has three columns, from left to right:

 

  • Fields or Channels in original component - These include fields in the original table when the Join dialog was launched and also new fields added with the Add button.   Double-click into an added field cell to rename the new field.  (Use the Schema dialog to changes the names of previously existing fields in an original table.)
  • Transfer methods - The method to be used to transfer data from the joined component to the original component for this field or channel.  Double-click into a transfer method cell to choose a transfer method.
  • Fields or channels from joined component - The field or channel  in the joined component that will be the source field for data transferred into the original table.  Double-click into a joined field cell to choose a transfer method.

 

To provide a cleaner display, the dialog does not show horizontal and vertical grid lines between rows and columns.

Cell cursor

Click a cell to move the cell cursor onto that cell.  Press Enter to choose from a menu for that cell or to start editing that cell.  Alternatively, simply double-click a cell to choose from a menu or to edit the cell.

Clear image pixels

When checked, clear all pixels (setting values to NULL) in  the target, original image before transferring data.  When not checked (the default) leave data in the original image as is except for those pixels altered with joined data.

Resize image

When checked, resize the target, original image before transferring data.  During the resize, the coordinate system, including the pixel size, stay the same, but the image rect (the size of the image in X and Y dimensions) changes. If the Clear image pixels option is off, the image can only be resized larger. If the Clear image pixels option is on, the image can be resized either larger or smaller.  

Join Component

Launch the join operation, adding any new fields or channels to the original component and loading it with data from the joined component, as specified by choice of transfer method and joined component field or channel for various original fields or channels.

 

A join operation copies data from the joined component into the destination component.  It is not a dynamic link where joined data stays in the joined component.  

Save update query

Check to create a new query in the project that contains the SQL update query which accomplishes the join operation specified in the dialog.  The saved query can be run at any time to repeat the join operation, for example, to update an original table with a fresh join operation that incorporates changes made to the original table's data or the joined table's data.

Close

Close the dialog without launching the join operation.

 

When adding a new field or channel, the Join dialog will automatically copy coordinate systems for geom and Tile fields from the specified source fields in the joined component into the new geom and Tile fields created in the original component.   The Join dialog will also automatically copy tile reduce,  tile size, and tile type properties from the source field into newly created tile fields.

Add Fields Dialog

 

The Add button pull down menu of available fields includes a Fields... choice at the end, to launch the Add Fields dialog.  

 

 

The Add Fields dialog allows us to choose more than one field from the joined table or drawing to add at the same time, or more than one channel from a joined image to add at the same time.  Check the boxes for each field or channel to be added.

 

Images are limited to a total of four channels, while tables and drawings can have effectively an unlimited number of fields.

 

Filter Box

Enter text to be matched, case not significant.   The list of fields or channels will be reduced to show only those which match the text.    For example, entering pu will reduce the list to fields such as pub_id, and pubdate,  which have the two letter sequence pu in their names, but will not include layers like pages or sales.   A great way to reduce very long lists of fields to only those of interest.

Click  the on/off box to toggle the field or channel on and off for use.   Any changes to a selected field's box will apply to all selected fields or channels.  Filled box = field or channel will be added.  Empty box = field or channel will not be added (default).  

spacebar

Toggles the context field on/off.   Same as clicking the on/off box.

Click

Click a field or channel to make it the context item in the list, putting the grid cursor on that field or channel.

Ctrl-click

Ctrl-click a field or channel to select or to de-select that field or channel.

 

Shift-ctrl-click will select that field and the swath of fields to the previously selected field.

OK

Close the dialog and add the checked fields as new fields in the Join dialog.

Cancel

Close the dialog without applying any changes.

 

The Add Fields dialog uses the same selection features as other Manifold dialogs and panes using grid style layouts.

 

 

For example, we can Ctrl-click some fields to select them, and then click on the box for one of them.

 

 

The click to pick will be applied to all selected fields.   See the Layers pane topic for examples of more selection moves using grid layouts that also work in the Add Fields dialog.

 

See the Join Dialog Part 2 - Joins between Tables video and also examples in the Join Example: Add Publisher Name to a Table of Book Titles  and  Join Example: Join Data from Many Records into One Record  topics.    See the Spatial Join Example: Create RGB Image with a Raster Join topic for an example using the Fields dialog to add more than one channel to an image.

Join Dialog and Queries

The source, joined table used with the Join dialog can be a query, but the Join dialog also can also use a query as the target, original table, to be used to copy data into the query, if the result table is writable.

 

This allows joining data to, for example, a table alias, such as TABLE [datasource]::[table]  or to a selection in an existing component, such as  TABLE CALL Selection([component], true) .

 

This works with queries that have been saved as a query component in the project pane, and not to queries that exist only within a Command Window, which have no persistent name and, as such, cannot be addressed from an update query.

 

See the Join Dialog Part 4 - Joins into Queries video and also examples in the Join Example: Join into a Query  and Join Example: Join into a Query into a Selection  topics.

Spatial Joins from a Drawing to a Drawing (Vector to Vector overlay)

Spatial joins between drawings use spatial relationships between the geometry of objects in the source, joined drawing and the geometry of objects in the target, original drawing to join data into the original drawing.   The Edit - Join dialog provides spatial joins between drawings that are layers in maps.    This provides functionality like classic GIS "overlay" functions, but packaged within an easy to use dialog.  Spatial joins work between drawing layers in the same map.  Layers can be in different data sources.

 

Overlays in ESRI nomenclature are called spatial joins in the data science / IT world, two different terms to describe exactly the same thing.  The Edit - Join dialog is a more modern way to do the same jobs with easier workflow.  Edit - Join, for example, updates the target table or drawing in place.   Even skilled ESRI people often prefer Edit - Join.

 

Add fields using a spatial join:

 

  1. With the focus on a drawing layer in an open map window, choose Edit - Join.
  2. In the upper right box choose the joined drawing in the map from which fields will be joined.

  3. In the second row of boxes, choose the geometry fields to use and the spatial method, such as contains or contained in.

  4. Click the Add button to choose a field in the joined drawing's table that will provide data for a new field in the original table.

  5. Double-click a cell to edit the field name (new fields) or to choose a different source field (joined table) or to choose a different transfer method.

  6. Check the Save update query box to create a query in the project that captures what the Join dialog will do.

  7. Press Join Component.

 

See the Join Dialog Part 1 -  Spatial Joins video and also examples in the  Spatial Join Example: Add Names of Provinces to Cities,  Spatial Join Example: Add State Names to Cities,  Spatial Join Example: Add the Sum of City Populations to States, and Spatial Join Example: A Spatial Self Join topics.

Spatial Methods available in Spatial Joins

When combining spatial data using a spatial Join, we have five different spatial methods to use:

 

adjacent to

An original object is adjacent to a joined object if both objects have at least one boundary location in common but have no interior locations in common.  Any locations in common between an original object and a joined object are only on the boundaries of the objects, and nowhere else.

 

An object that is adjacent to another object also touches that other object, but it does not intersect the other object.  Objects that are contained by or which contain another object are not adjacent.

 

contained in

An original object is contained in a joined object only if all locations in the original object are entirely within the joined object.

 

An object that is contained in another object also touches that other object but it is not adjacent to and it does not intersect that other object.

 

contains

An original object contains a joined object if all locations in the joined object are entirely within the original object.

 

An object that contains another object also touches that other object but it is not adjacent to and it does not intersect that other object.

 

intersects

An original object  intersects a joined object if both objects have at least one interior location in common and also at least one exterior location not in common.

 

An object that intersects another object also touches that other object, but it is not adjacent to, is not contained in, and does not contain the other object.

 

touches

An original object touches a joined object if there is any location in common between the two.

 

One object that touches another object may or may not be intersecting, and it may or may not be adjacent to the other object.  Objects that are contained by or which contain another object are also touching.

 

 

Spatial Joins from an Image to a Drawing (Raster to Vector overlay)

Spatial joins between  images and drawings use spatial relationships between the locations of pixels in the source, joined image and the geometry of objects in the target, original drawing to join data into the original drawing.    The Edit - Join dialog provides spatial joins between images and drawings that are layers in maps.    This allows transferring values from images, such as terrain elevation rasters, to objects in drawing layers, packaged within an easy to use dialog.  Spatial joins between images and drawings work between layers in the same map.  Layers can be in different data sources.

 

Add fields using a spatial join:

 

  1. With the focus on a drawing layer in an open map window, choose Edit - Join.
  2. In the upper right box choose the joined drawing in the map from which fields will be joined.

  3. In the second row of boxes, choose the geometry fields to use and the spatial method, such as contains or contained in.

  4. Click the Add button to choose a field in the joined drawing's table that will provide data for a new field in the original table.

  5. Double-click a cell to edit the field name (new fields) or to choose a different source field (joined table) or to choose a different transfer method.

  6. Check the Save update query box to create a query in the project that captures what the Join dialog will do.

  7. Press Join Component.

 

See the Join Dialog Part 3 - Raster to Vector Joins  video and also examples in the Spatial Join Example: Copy Terrain Heights to Points and Spatial Join Example: Copy Terrain Heights to Parcel Areas topics.

 

Typical Numeric Transfer Methods

Transfer methods are typically aggregates, as occur within SQL and other settings.  Transfer methods for raster to vector operations for single channels are numeric aggregates, as follows:

 

average

Calculate the average of pixel values within the object.

count

Count the total number of pixels  within the object.

diversity

Count the total number of different pixel values within the object.  

diversity index

Calculate the diversity index of pixel values within the object.  A diversity index provides a measure of diversity, computed as 1 - sum(individualcount^2) / (totalcount^2).  0 means that all values are equal.

major

Find the most frequently occurring pixel value within the object.

max

Find the largest pixel value within the object.

median

Calculate the median of pixel values within the object.

min

Find the smallest pixel value within the object.

sample

Pick a pixel value that occurs within the object.

stdev

Calculate the standard deviation of pixel values within the object.

stdevpop

Calculate the square root of the population variance of pixel values within the object.

sum

Calculate the sum of pixel values within the object.

var

Calculate the sample variance of pixel values within the object.

varpop

Calculate the population variance of pixel values within the object.

 

Spatial Joins from a Drawing to an Image (Vector to Raster overlay)

Spatial joins between drawings and images use spatial relationships between the geometry of objects in the source, joined drawing and the locations of pixels in the target, original image to join data into the original image.    The Edit - Join dialog provides spatial joins between drawings and images that are layers in maps.    This allows transferring values from objects in drawings, such as a value in an area object, into pixels in a target image layer, packaged within an easy to use dialog.  

 

 

Add channels using a spatial join:

 

  1. With the focus on an image layer in an open map window, choose Edit - Join.
  2. In the upper right box choose the joined drawing in the map from which channels will be joined.  

  3. Click the Add button to choose a field in the joined drawing that will provide data for a new channel in the original image.  

  4. We can add channels up to a total of four channels in the image.

  5. Double-click the transfer method cell to change the transfer method, with options being count, min, max, and sum.  The transfer method says how to aggregate what is transferred when more than one object in the source drawing contains the target pixel. For example, if areas overlap where the pixel is located, the transfer method specifies how to handle the two, possibly different, area values for that pixel.  If only one area contains the pixel, then min, max, or sum will all result in transferring that area's value into the pixel.

  6. Check the Save update query box to create a query in the project that captures what the Join dialog will do.

  7. Press Join Component.

 

Transfer into an existing channel using a spatial join:

 

  1. With the focus on an image layer in an open map window, choose Edit - Join.

  2. In the upper right box choose the joined drawing in the map from which channels will be joined.  

  3. In the row for the channel in the original image that will receive data, double-click on the rightmost cell to choose a field in the joined drawuing that will provide data for that existing channel in the original image.  

  4. Double-click the transfer method cell to change the transfer method, with options being count, min, max, and sum.  The transfer method says how to aggregate what is transferred when more than one object in the source drawing contains the target pixel. For example, if areas overlap where the pixel is located, the transfer method specifies how to handle the two, possibly different, area values for that pixel.  If only one area contains the pixel, then min, max, or sum will all result in transferring that area's value into the pixel.

  5. Check the Save update query box to create a query in the project that captures what the Join dialog will do.

  6. Press Join Component.

 

See the Join Dialog Part 5 - Vector to Raster Joins video and also examples in the Spatial Join Example: Transfer Drawing Attributes into Image Pixels topic.

 

Spatial Joins from an Image to an Image (Raster to Raster overlay)

Spatial joins between  images and images use spatial relationships between the locations of pixels in the source, joined image and the locations of pixels in the target, original image to join data into the original image.    The Edit - Join dialog provides spatial joins between images that are layers in maps.    This allows transferring values from pixels in an image, such as a terrain elevation raster, to pixels in a target image layer, packaged within an easy to use dialog.  

 

 

Add channels using a spatial join:

 

  1. With the focus on an image layer in an open map window, choose Edit - Join.
  2. In the upper right box choose the joined image in the map from which channels will be joined.  This can be the same image as the original image, for a self-join.

  3. Click the Add button to choose a channel in the joined image that will provide data for a new channel in the original image.  

  4. We can add channels up to a total of four channels in the image.

  5. Check the Save update query box to create a query in the project that captures what the Join dialog will do.

  6. Press Join Component.

 

Transfer into an existing channel using a spatial join:

 

  1. With the focus on an image layer in an open map window, choose Edit - Join.
  2. In the upper right box choose the joined image in the map from which channels will be joined.   This can be the same image as the original image, for a self-join.

  3. In the row for the channel in the original image that will receive data, double-click on the rightmost cell to choose a channel in the joined image that will provide data for that existing channel in the original image.  

  4. Check the Save update query box to create a query in the project that captures what the Join dialog will do.

  5. Press Join Component.

 

See the Join Dialog Part 6 - Raster to Raster Joins video and also examples in the Spatial Join Example: Create RGB Image with a Raster Join topic.

 

Videos

See the Join Videos topic for a list of many videos using the Join dialog.

 

Notes

Terminology - The original table also may be called the target table, and the table providing data also may be called the source table.

 

Quick reference - With the Join dialog open, press F1 to open a quick reference guide in a browser window.

 

Guessing the key field - When we launch the dialog with an original field and when we choose a joined field, the dialog will try to guess what fields we might want to use as key fields and will load those first by default.  If it guesses wrong, we can choose the field we want.   For the original table, the dialog tries to use a field (other than mfd_id) that has a BTREE / BTREENULL index, with a data type preference for numbers, then text, then everything else, and a name preference for field names with id in them, such as ... id or ..._id, then ...id and then everything else. For the joined table, the dialog uses similar logic, but first tries to use a field with the same name as in the original table.

 

Added fields are static - Added fields are static, that is, if data in the joined table changes the fields in the original table that received data in the Join operation from the joined table will not automatically be updated.  We can easily update the table any time we want using a saved update query.  

 

Spatial joins are automatically parallel - Joining data between drawings uses parallel GeomOverlay function variants.  Joins from images to drawings are parallelized through a nested SELECT using THREADS.

 

Why only in the same data source - The Join dialog works only with tables and queries within the same data source to keep the dialog simple and fast.   An important part of keeping the dialog simple is to show only those tables or queries in the pull down list for the joined table that can be used in a join.   Scanning all tables and queries in the same data source is reasonable, but if there are many data sources or many levels of nesting (data sources within data sources) there could be hundreds of thousands, if not millions, of tables and queries within the hierarchy to scan.  For people working in map projects without such elaborate data sources, most of the time what they do will be within the .map project data source so everything works by default.  People who link in data sources can quickly add a simple aliasing query, as illustrated in this topic, to use tables and queries from other data sources.   This method also makes it possible for the same Join dialog to work within other data sources, when aliasing queries refer back either to the .map project data source or to other data sources.

 

Two meanings of "intersect" - There are two notions of what "intersect" should mean, both of which are used by Manifold.   Topology overlays, as discussed in the Topology Overlays topic, use the classic set-theoretic meaning of "intersect," in which objects that are entirely contained by other objects are said to intersect as well.   A different meaning is used in Select pane templates and spatial joins in Join, where an object that is entirely contained within another object does not "intersect" that object but is contained by that object.  In the Join dialog and in Select pane templates, as discussed in the Select Templates topic, an object only intersects another object if some part of the object is outside the other object and some part is within the other object.   This allows the use of contained and containing to provide different selection criteria instead of simply duplicating what intersect does in a selection.

 

Buy and read a Fehily book on SQL - Manifold recommends Chris Fehily's excellent books on SQL.  Chris makes learning SQL really easy.  To encourage Manifold readers to get a Fehily book and to read it, Manifold examples are often integrated with examples in the Fehily SQL books, such as the tables used in this topic.    

 

Example data - Download the books.mxb project that contains the publishers and titles tables used in Join Example topics.

 

Spatial overlays - Spatial overlays that once were done using Transform templates are now done using the Select pane or the Join dialog.   See the Spatial Overlays using Select or Join topic.

 

Videos

Join Videos

 

Find Percentages of Open Space in ZIP Code Area - Find the percentage of open space in each ZIP code area given a layer of polygons representing ZIP codes and a layer of polygons showing open spaces like parks and green spaces. This video shows how to do that start to finish in a few simple steps, from initial importing of shape files to final results, in just five minutes, with an additional few minutes of explanation what each step does. Works in Manifold Release 9 or using the free Manifold Viewer.

 

See Also

Maps

 

Tables

 

Queries

 

Drawings

 

Images

 

Editing and Combining Data

 

Join Videos

 

Join Examples

 

Command Window

 

JOIN Statements

 

Editable Results Tables

 

SQL Example: Extract Airport Runways from an OpenStreetMap PBF - We write a simple SQL query using INNER JOIN to extract runway lines from an OpenStreetMap PBF of Cyprus, and to save those lines to a new drawing and table.