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:
In the upper right box choose the joined image in the map from which fields will be joined.
In the second row of boxes, choose the geometry and tile 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.
Check the Save update query box to create a query in the project that captures what the Join dialog will do.
Press Join Component.
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.
See the video version of this example in the Join Dialog Part 3 - Raster to Vector Joins video.
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.
The Parcels drawing has no attributes. We will add a new field called Average Height that gives the average height of the terrain under each parcel's area object.
With the focus on the Parcels
layer in the opened map window, we choose Edit
- Join from the main menu.
We choose Terrain as the joined image. The only option we have for a method is contains. For each area in the Parcels drawing, the join finds all pixels that area contains. In the case of area objects an area might contain many pixels.
Press
the Add button to add a new channel, and choose Channel
0, the only choice. There is no Fields
choice because there is only one "field," Channel
0, in the raster.
If the image had more than one channel, all of the channels would appear in the pull down menu, as well as the Fields choice.
We double-click into the Channel 0 name to change it.
We change the name to Average Height, and then we double-click into the method cell to change the method to average.
We press Join Component.
A new Average Height field is added to the Parcels Table. The new field is filled with the average value of heights in all pixels underneath each parcel area in the terrain.
We will add two more new fields, a Max Height field and a Min Height field.
With the focus on the Parcels layer in the opened map window, we again choose Edit - Join from the main menu.
Following the same procedure, we add two new fields, a Max Height field that uses max as the method, and a Min Height field that uses min as the method.
Press Join.
Two more new fields, a Max Height field and a Min Height field appear in the Parcels Table, providing the maximum and minimum heights found in the terrain within each area object.
If we do not like many decimal places in the result, we can easily save the query generated by the Join dialog, and then modify that query to use the Round( ) function.
Going back to when we set up the Join dialog to add an Average Height field, instead of pressing the Join button we press the Setup Join and Edit Query button.
That prepares the table for the Join operation by adding an Average Height field, and it opens the query that will populate that field in a Command Window.
We do not show the Command Window as an illustration to save space, but the query text in that Command Window is:
-- $manifold$
--
-- Auto-generated
-- Join
--
VALUE @target NVARCHAR = ComponentCoordSystem([Terrain]);
VALUE @source NVARCHAR = ComponentCoordSystem([Parcels]);
VALUE @conv TABLE = CALL CoordConverterMake(@target, @source);
UPDATE (
SELECT
tkey0,
t0,
TileGeomAvg([Terrain], 0, tgeom) AS s0
FROM (
SELECT
[mfd_id] AS tkey0,
[Average Height] AS t0,
CoordConvert(@conv, [Geom]) AS tgeom
FROM [Parcels]
) THREADS SystemCpuCount()
) SET
t0 = s0;
That is an UPDATE query in that it uses the UPDATE statement to modify the target table. We modify the query by adding a Round function to:
-- $manifold$
--
-- Auto-generated
-- Join
--
VALUE @target NVARCHAR = ComponentCoordSystem([Terrain]);
VALUE @source NVARCHAR = ComponentCoordSystem([Parcels]);
VALUE @conv TABLE = CALL CoordConverterMake(@target, @source);
UPDATE (
SELECT
tkey0,
t0,
Round(TileGeomAvg([Terrain], 0, tgeom)) AS s0
FROM (
SELECT
[mfd_id] AS tkey0,
[Average Height] AS t0,
CoordConvert(@conv, [Geom]) AS tgeom
FROM [Parcels]
) THREADS SystemCpuCount()
) SET
t0 = s0;
Only one line has been changed, to wrap the use of TileGeomAvg([Terrain], 0, tgeom) within the Round( ) function.
We press the ! Run button in the main toolbar to run the query that is in the Command window.
Instantly, the table updates to use rounded values.
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 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.
See the Join Videos list of videos showing how to use the Join dialog.