Example: Transfer DEM Terrain Heights to Areas in a Drawing

Given a map with an image layer that shows terrain heights taken from a DEM, and a drawing layer that contains areas, using a small SQL query we transfer the average terrain height within each area to that area as a Height attribute for the area. Easy!.

 

ico_nb_arrow_blue.png  To use the technique illustrated, the terrain elevation image from which heights are computed and the drawing into which those heights are stored must use the same projection.   In this example both use WGS 84 / Pseudo-Mercator (EPSG:3857) coordinate system.  If they use different projections, it takes but a moment to re-project either the drawing or the image into the coordinate system used by the other.  See the Projections topic.  In addition, the image must have only a single channel, which is typical for raster images that convey data such as terrain elevations or other data.

Our Data

eg_transfer_dem_terrain_heights01_01.png

 

We start with the map seen above.  The 1475 PM layer is a raster image surface showing terrain elevation in the vicinity of Livermore, California.  It is the same the data set imported and styled in the Example: Import DDF SDTS DEM Raster File  topic and also used in the Style: Contouring using Colors topic.  The Drawing has four areas in it.  The Drawing layer is shown with 50% opacity using the Layers pane so the terrain below the areas can partially show through.   The map also has a Height Labels layer, a labels layer which currently does not show anything since there are, as yet, no height values in the areas.

 

The terrain elevation image has been formatted using the following color and height intervals:

 

eg_transfer_dem_terrain_heights01_02.png

 

The drawing contains four areas, each with a Height field that is an INT64 type, an integer.

 

eg_transfer_dem_terrain_heights01_03.png

 

The Height for each area is currently empty.  

Our Task

Our task for each area is to find the height for each pixel of the terrain image that falls within the area, compute the average height for all those pixels, and to put the average height into the Height field for that area.

 

We can do that easily using the TileGeomAvg SQL function.

 

eg_transfer_dem_terrain_heights01_04.png

We launch View - New Command Window - SQL and enter the above query, copying and pasting it from the text below, if we like.

 

-- $manifold$

-- prepare to convert coordinates from drawing to image

VALUE @conv TABLE = CALL CoordConverterMake(

  ComponentCoordSystem([1475 PM]),

  ComponentCoordSystem([Drawing])

);

-- set 'height' field in drawing to average height taken from image

UPDATE (

  SELECT [mfd_id], [Height],

    TileGeomAvg([1475 PM], CoordConvert(@conv, [geom])) AS [computed]

  FROM [Drawing]

  THREADS SystemCpuCount()

) SET [Height] = [computed];

 

We press the ! Run button in the main toolbar to run the query.  Instantly, it computes the average height under each area and places the result into the Height field for each area:

 

eg_transfer_dem_terrain_heights01_05.png

 

The computation results in a floating point number.  Since we want simple labels, we have created the Height field in the table as an integer data type.  When floating point values are stored into that attribute they are automatically rounded into an integer value.

 

Now that there are Height values in the drawing's table, the Height Labels layer swings into action and shows those heights in the map.

 

eg_transfer_dem_terrain_heights01_06.png

 

Comparing the numbers we see for average height in each area to the height intervals used for coloring the terrain elevation, we can see the heights have been calculated accurately.

 

eg_transfer_dem_terrain_heights01_07.png

 

The blue regions in the surface start at 144 feet and go up to 275 feet, so an average Height of 193 for the leftmost area makes sense. The green regions go from 275 to 405, so an average height of 294 for the area in the center also makes sense, since it is right on the edge of the green region and partially overlaps canyons in blue.   The pale yellow regions of the surface go from a height of 405 to 536, so the average heights for the other two areas also seem reasonable.

 

Notes

Adapting the query - We can use the above query in our own work by simply substituting the name of our image for 1475 PM, the name of our drawing for Drawing, and the name of the height field in our drawing for Height.  Ctrl-h for search and replace, a standard Windows shortcut, works within the Command Window.

 

If we prefer to compute a different statistic instead of the average height under each area, we can replace the TileGeomAvg function with a different TileGeom series SQL function, such as TileGeomMax for the maximum height under each area, TileGeomMin for the minimum height under each area, TileGeomSum for the sum of all pixel values within the area, or other functions.

 

When might we use TileGeomSum?  Suppose the value for each pixel in a raster image is not height, but indicates the population at that pixel.  If we wanted to know the total population enclosed within an area in a drawing we would use TileGeomSum to get the sum.

See Also

Maps

 

Drawings

 

Tables

 

Images

 

Labels

 

Data Types

 

Queries

 

Style: Drawings

 

Style: Images

 

Style: Labels

 

Style: Images

 

Style: Contouring using Colors

 

Contents - Layers

 

Command Window

 

Example: Import DDF SDTS DLG Vector File 

 

SQL Example: Process Images with 3x3 Filters -  Shows a step-by-step example of developing an SQL query that takes a query written by the Edit Query button and then modifies that query into a general purpose query that can apply any 3x3 filter.   This makes it easy to use matrix filters we find on the web for custom image processing.   We extend the query by using parameters and adding a function, and then show how it can be adapted to use a 5x5 filter.