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.
Add channels using a spatial 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. The only spatial method is contained in, that is, the pixels into which joined data is transferred are contained within the object in the source drawing.
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. The only spatial method is contained in, that is, the pixels into which joined data is transferred are contained within the object in the source drawing.
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.
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. For conceptual simplicity, we show how the Join dialog works when areas do not overlap. The next example shows what happens when multiple areas overlap, and a given pixel in the terrain might fall within more than one area.
Our map includes an image layer called Terrain that shows terrain elevations in a single channel image. It also includes a drawing layer called Parcels that shows the location of various parcels as areas, with that layer shown using 60% opacity. Each parcel has a Z value, an arbitrary value we have assigned by hand to each area, that is higher than the average terrain elevation for that area. A Z Labels layer reports the Z value for each area. Parcels have been colored by their Z value.
The Parcels drawing has only one attribute, besides the geometry of each object, the value of the Z field, which is a float32 data type. We will transfer this value into the value of each pixel in the terrain image that is contained within each parcel area in the drawing.
With the focus on the Terrain
layer in the opened map window, we choose Edit
- Join from the main menu.
We choose Parcels as the joined drawing. The only option we have for a method is contained in. For each area in the Parcels drawing, the join finds all pixels contained in that area. In the case of area objects an area might contain many pixels.
We double-click into the right most cell of the Channel 0 row.
That pops open a list of fields in the Parcels drawing we can use as the joined field. We choose the Z field.
Next, we double-click into the transfer method cell for that row. We choose max as the transfer method. If we know that there are no overlapping areas, we get the same result using max, min, or sum. Using max simply says that if multiple areas do overlap at a given pixel, to choose whichever is the largest value of the Z field from the areas that overlap.
Press Join.
The immediate result, which we can see by turning off the Parcels layer, is that the Terrain raster image is modified so pixels underneath each area are assigned the numeric value of the Z attribute for that area.
The visual effect is as if the terrain suddenly acquired six rhomboid shaped plateaus of the specified height. Because the shading option is turned on, each plateau is rendered with a dark, shadowed edge. The plateaus look slightly darker colored than we might expect from the palette used for the Terrain image, because the effect of hill shading is created by assuming a slanted sun angle, which results in slightly darker tones on flat surfaces.
In the Style pane Options tab for the Terrain layer, we can turn off shading to see without hill shading effects the rhomboid regions where pixels have been assigned numeric values from areas in the Parcels drawing.
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. In the previous example, we showed how the Join dialog works when areas do not overlap. This example shows what happens when multiple areas overlap, and a given pixel in the terrain might fall within more than one area.
There are two ways experienced users might use the Join dialog to accomplish task. The simplest workflow is to use the same procedure as with non-overlapping areas, updating Channel 0 of the terrain image with values taken from the drawing's areas.
A more sophisticated approach is to add a second channel to the terrain raster, which can be used to show only the pixel values joined from the drawing's areas. We show both cases.
We repeat, using a different transfer method, the same procedure used with non-overlapping areas.
Our map includes an image layer called Terrain 2 that shows terrain elevations in a single channel image. It also includes a drawing layer called Overlapping Parcels that shows the location of various parcels as areas, with that layer shown in 60% opacity. Each parcel has a Z value, an arbitrary value we have assigned by hand to each area, that is higher than the average terrain elevation for that area. An Overlapping Z Labels layer reports the Z value for each area. Parcels have been colored by their Z value.
Four of the parcels overlap. Pixels that fall within the region of overlap are contained by two different areas, which in this example have different Z values.
The Overlapping Parcels drawing has only one attribute, besides the geometry of each object, the value of the Z field, which is a float32 data type. We will transfer this value into the value of each pixel in the terrain image that is contained within each parcel area in the drawing.
With the focus on the Terrain
2 layer in the opened map window, we choose Edit
- Join from the main menu.
We choose Overlapping Parcels as the joined drawing. The only option we have for a method is contained in. For each area in the Overlapping Parcels drawing, the join finds all pixels contained in that area. In the case of area objects an area might contain many pixels.
We double-click into the right most cell of the Channel 0 row.
That pops open a list of fields in the Overlapping Parcels drawing we can use as the joined field. We choose the Z field.
Next, we double-click into the transfer method cell for that row. We choose sum as the transfer method. If we know that there are no overlapping areas, we get the same result using max, min, or sum. If there is only one area, the "sum" of the Z value of that area alone is simply that Z value.
We use sum to provide different values in the region of overlap where more than one area overlaps. We could have used max, but that would not show any difference between the overlapping area and the area which overlaps that has the higher Z value. Using sum makes for a more educational example, because the value stored into pixels in the overlapping area will be the sum of the Z values for the overlapping areas.
If one of two overlapping areas has a Z value of 300 and the other overlapping area has a Z value of 350, the number 650 (the sum of the two Z values) will be written into the pixels within the region of overlap. That will provide a nice, visible effect to show what happens in overlaps.
We press Join.
The immediate result, which we can see by turning off the Parcels layer, is that the Terrain raster image is modified so pixels underneath each area are assigned the numeric value of the Z attribute for that area.
The visual effect is as if the terrain suddenly acquired ten rhomboid shaped plateaus of the specified heights, with the pixel values being taken from the eight areas in the Overlapping Parcels drawing plus the two additional regions of overlap where the Z values of the overlapping areas were summed.
Because the shading option is turned on, each plateau is rendered with a dark, shadowed edge. The plateaus look slightly darker colored than we might expect from the palette used for the Terrain image, because the effect of hill shading is created by assuming a slanted sun angle, which results in slightly darker tones on flat surfaces.
In particular, the region of overlap on the right side of the window is not as brightly distinct from the adjacent 550 region, even though the region of overlap is significantly higher with pixels values of 550 + 450 = 1000.
If we pop open the Style pane for the Terrain 2 image, we will see it still uses the thematic format for the original Terrain 2 image, which had no terrain higher than about 667. In the illustration above, we have added one more interval, for a height of 1000, and we have assigned a color of magenta to that interval, which should result in a visually distinctive color for regions that are around 1000 in height.
We press Update Style.
Immediately, we can see how the region where two high valued parcels overlapped in the joined drawing has resulted in a region of much higher pixels, summing the Z values of the overlapping areas.
The workflow above is fine for many purposes. However, it modifies the data in Channel 0 of the terrain elevation image. The image no longer has pixel values containing whatever were the original terrain heights. If we failed to make a copy of the image (and its table) before making modifications, the original data is gone and cannot be recovered.
We could have, of course, made a copy of the terrain image and table, and then we could have done the join on that copy. That would give us both the original as well as the copy. However, we now have the original data and the modified data in two different images, two different data sets. Sometimes we would like both the original and the modified data to exist within the same image, so that we can more easily do tile computations, selections, or other operations on both at the same time.
We can do that using a more sophisticated workflow, where instead of joining Z values from the drawing into Channel 0 of the terrain image, we ask the Join dialog to create a new channel, Channel 1, for each pixel in the image, and we join data into that new channel. The new channel can be used for many purposes, for example, to show only the pixel values joined from the drawing's areas.
Our map includes an image layer called Terrain 2 that shows terrain elevations in a single channel image. This is the same as the Terrain 2 layer in the prior example, before it was modified.
The map also includes a drawing layer called Overlapping Parcels that shows the location of various parcels as areas. Each parcel has a Z value, an arbitrary value we have assigned by hand to each area, that is higher than the average terrain elevation for that area. An Overlapping Z Labels layer reports the Z value for each area. Parcels have been colored by their Z value.
Four of the parcels overlap. Pixels that fall within the region of overlap are contained by two different areas, which in this example have different Z values.
The Overlapping Parcels drawing has only one attribute, besides the geometry of each object, the value of the Z field, which is a float32 data type. We will transfer this value into a new, second channel for each pixel in the terrain image that is contained within each parcel area in the drawing.
With the focus on the Terrain
2 layer in the opened map window, we choose Edit
- Join from the main menu.
We choose Overlapping Parcels as the joined drawing. The only option we have for a method is contained in. For each area in the Overlapping Parcels drawing, the join finds all pixels contained in that area. In the case of area objects an area might contain many pixels.
We
press the Add button to add a
new channel to the original image.
That pops open a list of fields in the Overlapping Parcels drawing we can use as the joined field. We choose the Z field.
A new row appears, to add a new channel, Channel 1 to the image. Note that since channels in images use zero-based counting the first channel is Channel 0 and the second channel is Channel 1.
The new row uses Z as the joined field. We double-click into the transfer method and choose sum as the transfer method.
We have set up a join where there will be no modifications to Channel 0. A new Channel 1 will be added to each pixel in the image, and that channel will be populated with the sum of the Z values of any areas that contain each pixel.
Press Join.
If we turn off the Overlapping Parcels layer, we see no change at all in the Terrain 2 image. That is because the Terrain 2 image is still styled to display whatever is in Channel 0. We have not yet adjusted Style to use the new Channel 1.
Instead of using a different Style to display Terrain 2 image, we will copy the Terrain 2 image and paste it to create a new Terrain 3 image. We do not copy the image's table: we simply copy and paste the image, so now we have a duplicate of the image, also taking its data from the same table, in this case called Terrain Table 2.
We turn off the Terrain 2 layer and we drag and drop the new Terrain 3 image into the map. Since it is a copy of the Terrain 2 image, it looks exactly the same as the Terrain 2 image.
If we pop open the Style pane for the Terrain 3 image, we see it is exactly the same thematic format as the Terrain 2 image. That is as expected, since the Terrain 3 image is a copy of the Terrain 2 image.
A quick way to change the use of channels in the image, without losing the thematic format for style already set, is in the Project pane to right click on the Terrain 3 image and to choose Properties. In the Properties dialog, we right click into the value cell for the StylePixel property and we choose Edit, to pop open the JSON style string in a convenient editing window. We simply change 0 to 1 in the JSON string Channel value.
Press OK to close the Edit window.
Press OK. We have just changed the Style used for pixels in Terrain 3 image to use Channel 1 instead of Channel 0, with all other parameters for the thematic format used in the style left unchanged.
Instantly, the Terrain 3 image layer changes Style to show thematic formatting using Channel 1 instead of Channel 0. To understand the styling used, we look at the Style pane for the Terrain 3 image.
The thematic format is the same as was use for the Terrain 2 image, but now applied to Channel 1. Channel 1 has zero as a value for all pixels where there were no containing areas in the Overlapping Parcels drawing, so all of those pixels use the green color for the lowest thematic interval.
Pixels in regions contained by areas in the drawing have values that are the sum of any areas that contain them.
We can "knock out" pixels with zero values by choosing transparent color for the first interval. That is a bit sloppy, as it would be better to add a new interval with a value of 0 and to make that transparent, but in this case we will be sloppy and simply make the first interval color transparent.
We also add a new interval of 1000 using magenta color, to make the region of maximum sum stand out better.
Press Update Style.
Immediately, the new style is applied. All zero value pixels become transparent, so we see the white background of the map. We also can see how the region where two high valued parcels overlapped in the joined drawing has resulted in a region of much higher pixels, summing the Z values of the overlapping areas.
To see the new, joined channel in the context of the other channel, we turn on the Terrain 2 image layer. That is the same image, just visualized using Channel 0. The map window now shows two layers, Terrain 3 and Terrain 2, which both come from the same image table and the same tile field, but displaying two different channels, with the Terrain 3 layer displaying data from Channel 1 and the Terrain 2 layer displaying data from Channel 0.
What is useful about having the joined data in Channel 1 of the image is that any visualizations, that is images, like Terrain 3, using that channel can be styled independently of the Terrain 2 image that shows Channel 0.
For example, in the Style pane Option tab for the Terrain 3 layer we can turn off shading.
That turns off shading for Terrain 3 without altering the style used for Terrain 2. We can now clearly see the color values assigned by the thematic palette to pixels in Channel 1, without any change in tone caused by oblique sun angle used for shading effects.
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.
Why not have a copy transfer method for drawing to image joins? - In the examples above we chose as the transfer method either max when we expected no area overlaps, or sum when we expected overlaps. Why not have a copy transfer method that in the case of multiple areas containing the same pixel, the system just pulls the first value from one of the areas it encounters? That is not done because when areas overlap they usually overlap in a way that covers more than one pixel. Suppose a few hundred pixels are in the region of overlap. If copy existed, it would simply pull values from whatever one of the overlapping areas it first encountered. But because there is no ordering in such things, different pixels within the region of overlap might have values pulled from different areas, resulting in different values for different pixels within the region of overlap.
If count, minimum, maximum, or sum are not sufficient transfer methods to handle the aggregate effect of multiple areas covering the same pixels, there are two approaches to getting more aggregate options. The first solution is to clean up the overlaps in the drawing. One way to do that is to perform a Merge Areas transform, then to convert the resulting areas to lines, and then finally to create Bounded Areas on those lines. The result will be independent area objects in all regions of intersection of overlapping areas. Transferring attribute values to those regions of intersection will still require some sort of logic to choose which area value to assign, but at least such logic can be done using arbitrarily complex expressions instead of the simple list of options within the Join dialog. Another approach would be to use the Setup Join and Edit Query button to open the SQL query that is used in the Command Window, and to then edit that query to use whatever computations are desired.
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.
See the Join Videos list of videos showing how to use the Join dialog.