Spatial Join Example: Copy Terrain Heights to Parcel Areas

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.

 

Pending updates: The former Save update query check box in the Join dialog has been replaced with a new Setup Join and Edit Query button, which provides slightly different, but more convenient, operation.  The Join Component button has been renamed Join.  Illustrations and commentary in this topic will be updated soon.

 

Add fields using a spatial join:

 

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

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

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

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

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

  7. Press Join Component.

Example

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 check the Save update query box to create a query we can use later.   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 Component.  

 

 

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.

Modifying the Update Query for Rounding

If we do not like many decimal places in the result, we can easily modify the update query that we saved to use the Round( ) function.

 

In the Project pane, we double-click the Parcels Update query saved earlier, when we added the Average Height field.  That opens it in a Command Window.

 

The query text is:

 

VALUE @target NVARCHAR = ComponentCoordSystem([Terrain]);

VALUE @source NVARCHAR = ComponentCoordSystem([Parcels]);

VALUE @conv TABLE = CALL CoordConverterMake(CoordSystemForceXY(@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;

 

We modify that to:

 

VALUE @target NVARCHAR = ComponentCoordSystem([Terrain]);

VALUE @source NVARCHAR = ComponentCoordSystem([Parcels]);

VALUE @conv TABLE = CALL CoordConverterMake(CoordSystemForceXY(@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 to run the query.

 

 

Instantly, the table updates to use rounded values.

Notes

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

 

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

 

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

 

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

 

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

 

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

 

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

 

Videos

See the Join Videos list of videos showing how to use the Join dialog.

See Also

Maps

 

Tables

 

Queries

 

Drawings

 

Images

 

Editing and Combining Data

 

Join

 

Join Videos

 

Join Examples

 

Command Window

 

JOIN Statements

 

Editable Results Tables