Join

The Edit - Join dialog applies the power of JOINs in a point and click dialog with no need to write SQL. The dialog is powered internally by full, spatial SQL, but it presents a point and click user interface that is easier to use. The Join command appears in the Edit menu when the focus is on a writable table window or on a writable drawing or image layer in a map.

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 dialog 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 or special case dialogs.

The Join dialog alters a table, 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 the new fields. Checking the Setup Join and Edit Query box will add any new fields required by the join and will open the update query in a Command Window. We can adjust the update query as desired, or run it at any time to update the original table with any changes in the joined table.

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.

The Join dialog requires joined tables or queries to be within the same data source. To use Join between tables and queries from different data sources, use a simple aliasing query, as illustrated in the Join Example: Joins between Data Sources topic, the Join Example: Join into a Query topic, and the Join Example: Join into a Query into a Selection topic. Almost all queries can be used as the source, joined table. The Join dialog also can also use a query as the target, original table, to be used to copy data into the table through the query, if the result table is writable and the Join does not attempt to create new columns in the table.

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:

With the focus on the original table window, choose Edit - Join.
In the upper right box choose the joined table or query from which fields will be joined.
In the second row of boxes, choose the key fields from each table that have common values.
Click the Add button to choose a field in the joined table that will provide data for a new field in the original table.
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.
Press Join.

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:

With the focus on the original table window, choose Edit - Join.
In the upper right box choose the joined table or query from which fields will be joined.
In the second row of boxes, choose the key fields from each table that have common values.
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.
In that same row, double-click into the center column cell and choose the transfer method, for example, copy.
Double-click a cell to choose a different source field (joined table) or to choose a different transfer method.
Press Join.

Saving an update query:

Arrange the Join dialog as desired for the join.
Press Setup Join and Edit Query.
That will add any new fields required to the target table, and will open the update query in a Command Window.
With the focus on the Command Window, choose Edit - Save as Query to save the update query.

Update the table using an update query:

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.

Indexes

For faster performance, the key fields used in the Join method 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. When doing a join using fields that do not have indexes, the internal Manifold query optimizer will build temporary indexes and will use those automatically. That usually is fast enough that it is not worth the time to manually create indexes.

However, if we are working with large data, or if we are doing repeated joins with that data, it may be worth it to take a few minutes to add indexes so that Manifold does not have to create temporary indexes with every join that we might do. It is easy to add indexes with a few clicks in the Schema dialog. When adding an index on a field for use with a join, 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.
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	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.
Setup Join and Edit Query	A command that does two things, based on how we have arranged the Join dialog: First, alter the original table by adding any new fields to that table that are required b the join operation specified in the dialog. The table's schema is modified to add the new fields, but no data is added to those fields and no fields are deleted. Second, open the Command Window loaded with an update query using SQL that populates the new fields, accomplishing the join operation specified in the dialog. We can save the update query for future use by using Edit - Save as Query from the Command window. Pressing the button gets the original table ready by adding any new required fields, and then it creates the query that would be used to do the join, but it does not actually run that query. It also close the Join dialog. The next time we run the Join dialog against the original table, we will see the new fields added by the button. The Setup Join and Edit Query button is used when we expect to repeat the join into the same table in the future. For example, we may have a map that must be updated every week with fresh data from a report that is published every week. The first time we do the join, we use the Setup Join and Edit Query button to add any required fields to the target table and to write the update query we will use thereafter. We can then run the update query every week without having to set up the Join dialog every week. We can also use the Setup Join and Edit Query button to learn how to do joins in SQL. Since the original table is modified only by adding empty fields when we press the button, if we do not need those fields we can simply use Edit - Schema to delete them.
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 menu includes a list of available fields from the joined table, drawing or image. It also includes a Fields... choice at the bottom of the menu.

Choose Fields 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.

The Add Fields dialog can add many fields at once to the proposed join. We can then adjust each of the added fields as we like, for example, changing the transfer method or the name of the destination field.

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.

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:

With the focus on a drawing layer in an open map window, choose Edit - Join.
In the upper right box choose the joined drawing in the map from which fields will be joined.
In the second row of boxes, choose the geometry fields to use and the spatial method, such as contains or contained in.
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.
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.
Press Join.

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:

With the focus on a drawing layer in an open map window, choose Edit - Join.
In the upper right box choose the joined drawing in the map from which fields will be joined.
In the second row of boxes, choose the geometry fields to use and the spatial method, such as contains or contained in.
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.
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.
Press Join.

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.

Transfer Methods

Transfer methods available depend upon the data type of the field being joined, and the context. For example, there is a longer list of transfer methods that make sense when joining numeric fields in one table to fields in a different table than when joining pixel values between rasters or from rasters to a vector. When doing a many to one join, such as joining the values of many pixels that fall within a particular area object in a raster to vector join, the transfer methods are typically many to one joins and are thus aggregates, as occur within SQL and other settings.

Following are transfer methods for frequently-used data types.

Pixel Values, Raster to Vector

Transfer methods for raster to vector operations for single channels are numeric aggregates, because they are many to one joins. When calculating using pixels in raster to vector operations, typically the calculation is done on the number of pixel values within the containing object, for example, the average of pixel values within an area into which pixel values are joined.

average	Calculate the average of pixel values.
count	Count the total number of pixels.
diversity	Count the total number of different pixel values.
diversity index	Calculate the diversity index of pixel values. 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.
maximum	Find the largest pixel value.
median	Calculate the median of pixel values.
minimum	Find the smallest pixel value.
sample	Pick a pixel value that occurs within the object. This can be any such pixel value or number, randomly chosen.
std deviation	Calculate the standard deviation of pixel values.
std deviation pop	Calculate the square root of the population variance of pixel values.
sum	Calculate the sum of pixel values.
variance	Calculate the sample variance of pixel values.
variance pop	Calculate the population variance of pixel values.

Numeric to Pixel Values, Vector to Raster

Vector to raster spatial join operations are typically one to many joins, taking numeric values from an area and transferring those into values for the specified pixel channel in the image tiles that are contained in that object. More than one value can be involved in a vector to raster join when more than one object overlaps at the same pixel, resulting in a many to one join for that pixel.

count	Count the total number of numbers in the containing area.
maximum	Find the largest number in the containing area.
minimum	Find the smallest number in the containing area.
sum	Calculate the sum of numbers in the containing area.

Conversion between Data Types

The convert method lets us join data from the joined drawing or table into an existing field within the destination table that is a different data type. This make sense only when data already is in a form that can be unambiguously converted.

convert

Automatically change the data type, doing a CAST, to convert data into the data type of the destination field. Equivalent to doing a copy or sample with a change of data type.

When used as a transfer method between like data types, is equivalent to a copy or sample.

For example, a field with text strings like 4.3, 3.14, 8368 can sensibly be converted into a floating point or integer (with truncation of decimal values) numeric field. But it does not make sense to use convert to send text strings like Durango, New York, or London into a numeric field.

Numeric Fields

Transfer methods for numeric fields are numeric aggregates for many to one joins, but can also be simple joins of single values such as vector to vector joins or table to table joins.

average	Calculate the average of values.
bit and	Combine numeric values using the BitAnd operator.
bit or	Combine numeric values using the BitOr operator.
bit xor	Combine numeric values using the BitXor operator.
convert	Convert the numeric data type of the joined data into the data type of an existing destination field, for example, converting a numeric 3.14 value into the text equivalent if the destination is a text field. Equivalent to copy using the same data type when joining data into a new field.
copy	Pick a value that occurs within the joined set in the case of more than one value. This can be any such value, randomly chosen. In the case of a one-to-one join, the single value picked is the only one there is, which is what we want in a copy.
count	Count the total number of values.
diversity	Count the total number of different values.
diversity index	Calculate the diversity index of values. 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 value.
maximum	Find the largest value.
median	Calculate the median of values.
minimum	Find the smallest value.
std deviation	Calculate the standard deviation of values.
std deviation pop	Calculate the square root of the population variance of values.
sum	Calculate the sum of values.
variance	Calculate the sample variance of values.
variance pop	Calculate the population variance of values.

Text Fields

Transfer methods for text fields are text aggregates for many to one joins, which basically boils down to a join tokens concatenation in addition simple lexicographic aggregates, but can also be simple joins of single values such as vector to vector joins or table to table joins.

convert	Convert the numeric data type of the joined data into the data type of an existing destination field, for example, converting a text string 3.14 value into the numeric equivalent if the destination is a numeric field. Some conversions do not make sense to do, like converting New York into a floating point number. Equivalent to copy using the same data type when joining data into a new field.
copy	Pick a value that occurs within the joined set in the case of more than one value. This can be any such value, randomly chosen. In the case of a one-to-one join, the single value picked is the only one there is, which is what we want in a copy.
join tokens	Concatenate all joined text values into a list, each separated by a comma , character.
maximum	Find the lexicographically maximum text value.
median	Find the lexicographically median text value.
minimum	Find the lexicographically minimum text value.

Datetime Fields

Aggregates for datetime fields in many to one joins are relatively simple, with most joins involving datetime fields being simple joins of single values such as vector to vector joins or table to table joins.

convert	Convert the datetime data type of the joined data into the data type of an existing destination field, for example, converting a date into the text equivalent if the destination is a text field. Equivalent to copy using the same data type when joining data into a new field.
copy	Pick a value that occurs within the joined set in the case of more than one value. This can be any such value, randomly chosen. In the case of a one-to-one join, the single value picked is the only one there is, which is what we want in a copy.
maximum	Find the maximum (latest) date.
median	Find the median date.
minimum	Find the minimum (latest) date.

Geometry

Joins can also transfer geometry data, either into existing fields or into new geometry fields.

center	Compute the center of the joined geometry.
convex hull	Compute the convex hull of all objects in the joined set.
copy	Pick a value that occurs within the joined set in the case of more than one value. This can be any such value, randomly chosen.
merge lines	Combine joined lines into a single, multibranched line.
merge points	Combine joined points into a single, multibranched point (a multipoint).
union areas	Combine areas into a single, unioned area, discarding lines and points. Any touching or overlapping areas are unioned into a single branch of the area while areas that do not touch or overlap will be separate branches.
union rects	Combine the bounding boxes (rectangles) of joined objects into the common bounding box of all the rectangles.

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.

Spatial joins between drawings and images work between layers in the same map.
Layers can be in different data sources.
Drawing and image can be in different coordinate systems: The join operation dynamically converts the drawing into a raster in the coordinate system of the image and transfers data between the images.
The only join condition available is contained in, that is, pixels in the target, original image into which joined data is transferred are within objects in the source, joined drawing.
Any channel in the target, original image can receive data from a field in the source, joined drawing.

Add channels using a spatial join:

With the focus on an image layer in an open map window, choose Edit - Join.
In the upper right box choose the joined drawing in the map from which channels will be joined.
Click the Add button to choose a field in the joined drawing that will provide data for a new channel in the original image.
We can add channels up to a total of four channels in the image.
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.
Press Join.

Transfer into an existing channel using a spatial join:

With the focus on an image layer in an open map window, choose Edit - Join.
In the upper right box choose the joined drawing in the map from which channels will be joined.
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.
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.
Press Join.

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.

Spatial joins between images and images work between layers in the same map.
Layers can be in different data sources.
The join operation dynamically re-projects the source, joined image to the coordinate system of the target, original image and transfers data between matching pixels.
The only spatial method is matches, that is, the pixels from which joined data is transferred are at the same location.
Any channel in the target, original image can receive data from any channel in the source, joined image.
If the pixel data type of the source, joined image is the same as the pixel data type of the target, original image, the transfer method will be set to copy. If the pixel data types are different, the transfer method will be set to convert.
When generating an update query, the query will automatically include SQL to clear or not clear image pixels and to resize or not resize the image, depending on whether those boxes are checked.
An image can be joined to itself, a self-join. This is a great way to shuffle channel values, as shown in the Shuffle Channels with a Raster Self Join video.

Add channels using a spatial join:

With the focus on an image layer in an open map window, choose Edit - Join.
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.
Click the Add button to choose a channel in the joined image that will provide data for a new channel in the original image.
We can add channels up to a total of four channels in the image.
Press Join.

Transfer into an existing channel using a spatial join:

With the focus on an image layer in an open map window, choose Edit - Join.
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.
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.
Press Join.

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.

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.

Example: Simple Join of data from a table

The following example is short. For more detailed, step-by-step examples that show every small step, see the list of many examples in the Join Examples topic.

We take a drawing of provinces in Mexico, and add a population field to each, taking the populations in a join with a table that lists populations for Mexican provinces.

We open a map with one layer, Provinces, a drawing that shows provinces in Mexico as areas.

Opening up the Provinces Table associated with the drawing, we see that it has three fields: an mfd_id field used as an ID field and index. a Name field, and a geometry Geom field. We would like to add a population field for each province.

We will get the population for each province from a table called Populations by State. This is simply a table, with no drawing, that we downloaded in a CSV file from a web site giving demographic information about Mexico.

We click the open Provinces Table window, so the focus is on that window, and then in the main menu we choose Edit - Join.

Launching the Join dialog with the focus on the Provinces Table window loads Provinces Table as the destination of the join. We choose Populations by State as the joined table. The Name fields in both tables will by default be chosen as the key fields for both tables, and equal to will be chosen as the joining method. If we were working with larger tables with many more fields each, we could choose the key fields we wanted.

Using the Add button, we add the Population field from the Populations by State table for a new row, using the same name, Population, for the destination field in Provinces Table. We use the default transfer method, copy, that appeared when we added the row.

Press Join.

The result is that the drawing's table, Provinces Table, now has a new Population field that gives the population for each province. We can now use that field for analytics, thematic formatting, and so on with the drawing.

Example: A spatial join from data in another drawing

We start with a map that has two layers: a drawing of provinces in Mexico, and a drawing of big cities in Mexico. We add a population field to the drawing of provinces that gives the sum of the populations of the big cities that are located within each province.

We open a map with two layers: Provinces, a drawing that shows provinces in Mexico as areas, and Cities, a drawing that shows big cities in Mexico as points.

Opening up the Provinces Table associated with the Provinces drawing, we see that it has three fields: an mfd_id field used as an ID field and index. a Name field, and a geometry Geom field. We would like to add an urban population field for each province that gives the sum of populations of any big cities that are located in the province.

Opening up the Cities Table associated with the Cities drawing, we see that it has four fields: an mfd_id field used as an ID field and index. a Name field, a population field giving the population of the city, and a geometry Geom field.

We click the open Mexico map window, and we click the Provinces tab in the map, so the focus is on the Provinces layer in the map window. In the main menu we choose Edit - Join.

Launching the Join dialog with the focus on the Provinces tab in the map window loads the Provinces drawing as the destination of the join. We choose Cities as the joined drawing. The Geom fields in both tables will by default be chosen as the key fields for both tables. If we were working with larger tables with many more fields each, we could choose the key fields we wanted.

We choose contains as the join method, since we want the join condition to pair up each province with all the cities it contains.

Using the Add button, we add the population field from the Cities drawing for a new row, using the name, Urban Population, for the destination field in the Provinces drawing.

We change the transfer method to sum, so that the sum of the populations from the cities it contains will be placed into the Urban Population field for each province.

Press Join.

The result is that the drawing's table, Provinces Table, now has a new Urban Population field that gives the sum of the populations of all the large cities that are located within that province.

Example: Add a list of names of big cities to each province

Using the same layers as in the prior example, we will add a field to the Provinces layer that contains a list of the names of big cities that province contains.

We open a map with two layers: Provinces, a drawing that shows provinces in Mexico as areas, and Cities, a drawing that shows big cities in Mexico as points.

Opening up the Provinces Table associated with the Provinces drawing, we see that it has four fields: an mfd_id field used as an ID field and index, a Name field, and a geometry Geom field. We would like to add a text field for each province that gives a list of the names of big cities located in that province.

We click the open Mexico map window, and we click the Provinces tab in the map, so the focus is on the Provinces layer in the map window. In the main menu we choose Edit - Join.

Choose contains as the join method, since we want the join condition to pair up each province with all the cities it contains.

Press the Add button, and choose the name field from the Cities drawing. That adds a new row.

Double-click the name of the destination field (by default, name 2) to the name Big Cities, for the destination field in the Provinces drawing.

Double-click into the copy transfer method and change the transfer method to join tokens. The list of available transfer methods is different from the prior example, because the name field being joined is a text field, which has different transfer methods available compared to the numeric field that was joined in the previous example.

The join tokens method takes the various names of any cities contained by each province and concatenates them into a list, with the city names separated by comma , characters.

Press Join.

The result is that the drawing's table, Provinces Table, now has a new Big Cities field that contains a concatenated list of the names of big cities located within each province.

Tech Tip: The Join dialog is powered by the full spatial SQL of Manifold, so it is easy to customize joins by altering the SQL the dialog uses. Setup the Join dialog as in the above example, and then before pressing Join, press the Setup Join and Edit Query button to have the Join dialog create a query that accomplishes the join specified by how the dialog has been set up. We can then tinker with that query to customize the join, even if our SQL skills would not allow us to write a complex join query from scratch.

For example, if we wanted a different character separating city names in the list, or if we wanted a space character after each comma, that is easy to do. Setup the Join dialog as it was just before we pressed Join.

Instead of pressing Join, press the Setup Join and Edit Query button. That opens in an SQL Command Window the query that accomplishes the actual join.

If we want a space character after each comma, we edit the line in the query:

StringJoinTokens([o_name], ',') AS s0

StringJoinTokens([o_name], ', ') AS s0

That adds a space character after the comma that is used as the separator in the StringJoinTokens function the query uses. Run the query by pressing the ! Run button in the main toolbar and we get:

We could further edit the line with the StringJoinTokens function to alter the text output however we wanted.

Videos

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

Examples

See the many, step by step examples listed in the Join Examples topic.

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.

System tables excluded - The Join dialog does not operate on system tables such as mfd_meta or mfd_root or mfd__srid and similar.

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 setup, and then update? - Why does the Setup Join and Edit Query button first add the new field desire and then generate an UPDATE query, instead of generating a query that both adds the new field and also does the UPDATE? Two steps are used to enable repetitive use of the same query to adjust what data is joined into the new field. If the generated query also created that new field, to run that query multiple times, each time we ran it we would first have to delete the newly created field from the schema, so that part of the query would not cause an error. By generating only an UPDATE query we can modify it and re-run it however many times we want, to try out different variations of how we UPDATE the table.

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 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. To use tables and queries from other data sources as the joined table in the Join dialog we can quickly add a simple aliasing query, as illustrated in the Join Example: Joins between Data Sources topic. 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. Almost all queries can be used as the source, joined table. The Join dialog also can also use a query as the target, original table, to be used to copy data into the table through the query, as shown in the Join Example: Join into a Query and Join Example: Join into a Query into a Selection topics, if the result table is writable and the Join does not attempt to create new columns in the table.

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 users to buy a Fehily book and to read it, Manifold examples often use examples similar to those in the Fehily SQL books.

Spatial relationships - The Select pane and Join dialog both use the following definitions for spatial relationships:

adjacent	An object is adjacent to another object if both objects have at least one boundary location in common but have no interior locations in common. Any locations in common between the objects 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	An object is contained in another object only if all locations in the object are entirely within the other 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.
containing	An object contains another object if all locations in the other object are entirely within the 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.
intersecting	An object intersects another 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.
touching	An object touches another 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.

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, 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.

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.

Join

Joins from a Table to a Table

Indexes

Controls

Add Fields Dialog

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

Spatial Methods available in Spatial Joins

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

Transfer Methods

Pixel Values, Raster to Vector

Numeric to Pixel Values, Vector to Raster

Conversion between Data Types

Numeric Fields

Text Fields

Datetime Fields

Geometry

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

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

Join Dialog and Queries

Example: Simple Join of data from a table

Example: A spatial join from data in another drawing

Example: Add a list of names of big cities to each province

Videos

Examples

Notes

Videos

See Also