Join Examples

Join Example: Add Publisher Name to a Table of Book Titles

A table to table join:  We use the Join dialog to add the name of a book's publisher to a table that has a list of book titles.

Join Example: Join Data from Many Records into One Record

A table to table join:  We take data from many records and add that into a single record, as a list, as an average and as a maximum value from the many records.

Join Example: Joins between Data Sources

The Join dialog works with tables and queries within the same data source.   This topic shows how to use tables and queries from other data sources by aliasing them into the current data source.

Join Example: Joins Entirely within an External Data Source

The Join dialog also works when both the original table and the joined table are in an external data source, assuming that data source is not read-only and it allows modification of tables.   This topic provides an example where both the original table and the joined table are in an external data source.

Join Example: Update an Existing field with Join

In other Join examples we add new fields to the original table, which are then populated with data from the joined table.   We can also populate an existing field in the original table with data from the joined table.  This topic shows how.

Join Example: Converting between Different Data Types

When using the Add button in the Join dialog to add new fields to an original table, there is no need for conversion since the data type of the newly-added field will be the same as the data type as the field from the joined table that was chosen to populate the new field.    However, when using the Join dialog to load data from the joined table into existing fields in the original table, the data types may be different, but close enough that automatic conversion can occur.  This example topic explores such automatic conversion, and shows how to control rounding.

Join Example: Join into a Query

A table to query spatial join:  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. We create a query and then copy data from the publishers table into the query using the Join dialog.

Join Example: Join into a Query into a Selection

A table to query spatial join:  A table to query spatial join:  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. We create a query and then copy data from the publishers table into the query using the Join dialog.  We write the query so it steers the joined data only into selected records.

Spatial Join Example: Add Names of Provinces to Cities

A drawing to drawing spatial join:  Given a drawing of cities and a drawing of provinces, we copy the name of the province in which it is located to each city.

Spatial Join Example: Add State Names to Cities

A drawing to drawing spatial join:  Given a drawing of cities and a drawing of states in the US, we copy the name of the state in which it is located to each city.

Spatial Join Example: Add the Sum of City Populations to States

A drawing to drawing spatial join:  Given a drawing of cities in the US, with a population for each city, and a drawing of states, we sum the population of each city located in a state to get the population for that state.

Spatial Join Example: Join Areas to Areas

A drawing to drawing spatial join:  Given a drawing showing circular buffer zones, and a drawing of provinces in Mexico, we explore the results of different spatial joins between the provinces and buffer zones, showing the effect of touches and contains join criteria, and the effect of different transfer options, such as union areas, copy and more.

Spatial Join Example: A Spatial Self Join

A drawing to drawing spatial join where the same drawing is used both as the source, joined drawing and the target, original drawing.   A self join is when a drawing or table is joined to itself.    This is one of those nuanced concepts in data science and SQL that the Join dialog makes easy.  We consider a classic example, doing a self join using a spatial relationship.   We then apply that example to find small gaps in line sequences, a typical job when cleaning up sequences of lines that are to be used as road or other networks.

Spatial Join Example: Copy Terrain Heights to Points

An image to drawing spatial join:  Given a raster terrain image that shows terrain elevations, and a drawing of points, we transfer the heights in the terrain elevation raster at the location of each point into a new Height attribute for each point in the drawing.  We will use a map similar to that used in the Example: Transfer DEM Terrain Heights to Areas in a Drawing  topic.  This example is directly analogous to that example, but instead of using ESRI-style overlays we do the same thing more easily using the Join dialog.

Spatial Join Example: Copy Terrain Heights to Parcel Areas

An image to drawing spatial join:  Given a raster terrain image that shows terrain elevations, and a drawing of areas, we transfer average height within the terrain elevation raster within each area into a new Height attribute for each area in the drawing.  We will use a map similar to that used in the Example: Transfer DEM Terrain Heights to Areas in a Drawing  topic.  This example is directly analogous to that example,  but instead of using ESRI-style overlays we do the same thing more easily using the Join dialog.

Spatial Join Example: Transfer Drawing Attributes into Image Pixels

A drawing to image spatial join:  Given a raster terrain image that shows terrain elevations, and a drawing of areas with Z value for each area, we transfer the Z value for each area into the pixels in the terrain that fall within that area.   We show how the Join dialog works when areas do not overlap, and what happens when multiple areas overlap so a given pixel in the terrain might fall within more than one area.   

Spatial Join Example: Create RGB Image with a Raster Join

An image to image join:  Given three images, each of which is a single channel image containing only red channel, blue channel or green channel data, we use the Join dialog to join the three images into a new RGB image.

Spatial Join Example:  Convert Vector to Raster

A drawing to image spatial join:   Given a drawing of areas where each area has some attribute of interest, we create an equivalent image with a single channel, where the value in that channel for each pixel is the attribute of the area within which that pixel is located.    Starting with a vector drawing of areas where each area has a classification attribute, a numeric code for the land cover within that area.  We create a raster image where each pixel has a numeric value that is the same as the classification code for the area containing that pixel.

Other Examples using Join

Example: Use Edit - Join to Map a Pandemic

Creating a data source for a CSV web server, we acquire current data on cases and deaths worldwide for the Covid-19 pandemic.  We then use the point and click Edit - Join dialog to rapidly join that data, automatically aggregated as we desire, into a world map for visualization.

Example: Find Percentages of Open Space in ZIP Code Areas

Given a drawing showing ZIP codes as areas (polygons) and another drawing showing open spaces like parks and nature preserves, we add a field to each ZIP code area that gives the percentage of open space in that ZIP code area.  The workflow we show handles situations where some open space regions overlap multiple ZIP code areas, correctly reckoning only that part of the open space within each ZIP code area.

Example: Create a Map Showing OSM Use by Country

A start-to-finish real life example of map creation that combines various Manifold capabilities, including use of Edit - Join.  Copying a table of numbers from a web site, we create a map that is thematically colored to show usage of OpenStreetMap by country in proportion to the population of that country.

Example: Shuffle Channels with a Raster Self Join

We use the Join dialog to rearrange channels within an image,   Starting with a four channel image that has RGB plus infrared channels, we rearrange the order of channels so that infrared values are in the first channel powering the red output, red values are in the second channel powering the green output, and green values are in the third channel the blue output.   This is the classic Color Infrared (CIR) channel arrangement.  Unlike a virtual rearrangement using Style shown in the  Example: Display an NAIP Four Band Image as Color Infrared (CIR) topic, rearranging channels in this way changes the structure of the data so that any exported image will retain the new arrangement.

Example: Join Districts to Building Footprints

Given a map of a city with a drawing layer containing the footprints of buildings as polygonal areas, and a second drawing layer containing districts in the city as polygonal areas, we use the Join dialog to add a new attribute field to each building giving the district in which it is located.   We consider first the simple case where district boundaries always fall between buildings, so buildings are always only in one district.  Next, we deal with the case where buildings can straddle district boundaries, so parts of the same building can be in different districts.  In that case we use the Transform pane to quickly build centroids for building footprints, and then we use the centroids to guide the spatial join.

Example: Import E00 and Rearrange

An intricate example showing how to use Manifold tools to adapt legacy data imported from E00 format into useful, modern form.  We also use the Join dialog to add a column.

Example: Trace Vector Areas from Raster Pixels

This example follows the Trace Vector Areas from Raster Pixels video on the Gallery page.   We use the Trace Areas template in the Transform pane for images to create a drawing with vector areas covering regions of similarly-colored pixels.  Next, we use the Join dialog to add classification codes.   To show how to do the same thing using SQL, we use a simple query to add classification codes from a USGS table of classes to the resulting drawing, using a simple INNER JOIN SQL statement.