SQL Example: Custom Contour Intervals

This topic continues on from the Example: Contour Areas and Contour Lines topic.  Please review that topic first.  

 

Suppose we would like to create contour areas or contour lines on arbitrary intervals, and not just on evenly-stepped intervals as illustrated in the Example: Contour Areas and Contour Lines topic?   That is easy to do using SQL.    

 

The easiest way to write the SQL to create arbitrary intervals is to let Manifold do all the supporting work for us.  We choose the same transform as shown in the Example: Contour Areas and Contour Lines topic, and then we use the Edit Query button to instruct Manifold to write all the SQL required.   We change a single line to specify the intervals we want and the task is done.  Easy!

 

 

We begin with the same raster terrain elevation data used the Example: Contour Areas and Contour Lines topic, showing the region surrounding Mount Hood in Oregon.

 

With the focus on the map window, in the Transform pane we choose the N45W122_dem image and the Tile field, and we double-click the Contour template to launch it.

 

 

In the Contour template, we choose channel 0 for the Channel and line as the Output type.  

 

Based on the numbers we saw in the Style pane, we use a Start of 0 and an End of 3300.   We enter 300 as the Step.  

 

We check the Split into shapes box, so that all the various contour areas will be created as separate area objects in all regions for similar contour values.   

 

For the Result destination, we choose New Table and then enter Contour lines custom as the name of the new drawing, with an analogous name for the new table.  

 

The above are the same values for parameters as used in the Example: Contour Areas and Contour Lines topic.   The values for Start, End and Step do not matter since we will edit the created query.

 

Press the Edit Query button.  

 

 

Immediately, Manifold launches a Command Window filled with the SQL which accomplishes the transform as we have commanded, complete with creating a new table and drawing using the names we have specified.  

 

The query is a long query in three parts:  the first part is a series of comments that reports the parameter options used.   The second part, between the -- prepare begin and -- prepare end comments, creates the new table and new drawing.   The third part is the operative part of the query, which generates contour lines and inserts them into the table.

 

 

We can scroll down to the bottom, to see the operative part of the query.  It begins with a pair of VALUE statements that declare global values.    

 

The second VALUE statement, which we have highlighted, generates the table of heights that will be passed to the function that generates contours.  It is a single CALL of the ValueSequence function:

 

VALUE @heights TABLE = CALL ValueSequence(0, 3300, 300);

 

The ValueSequence function call generates a table of values from 0 to 3300 at intervals of 300.     We will replace that line with a VALUES clause that lists the contour values we want.   Suppose, for example, we would like contours at the 500, 600, and 700 heights, and then again at the 1500 and 3000 heights.  

 

The VALUES clause for that would be

 

(VALUES (500), (600), (700), (1500), (3000))

 

 Do not forget the parentheses around the VALUES clause.   We put parentheses around the VALUES clause to avoid confusing the list of arguments for the big TileContourAreasPar function that actually generates contours.

 

The change as seen in the Command Window, with the changed line highlighted:

 

 

The full text of the query now is:

 

-- $manifold$

--

-- Auto-generated

--

-- Contour

--   Layer: N45W122

--   Field: Tile

--   Channel: channel 0

--   Output type: line

--   Start: 0

--   End: 3300

--   Step: 300

--   Round start and end to step: FALSE

--   Split into shapes: TRUE

--   Result: (new table)

--   Result type: geom

--   New drawing: Contour lines custom

--   New table: Contour lines custom Table

--   Resources: all CPU cores, all GPU cores

--

 

-- prepare begin

 

CREATE TABLE [Contour lines custom Table] (

  [mfd_id] INT64,

  [Value] FLOAT64,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' ComponentFieldCoordSystem([N45W122 Tiles], 'Tile')

);

CREATE DRAWING [Contour lines custom] (

  PROPERTY 'Table' '[Contour lines custom Table]',

  PROPERTY 'FieldGeom' 'Geom'

);

 

-- prepare end

 

VALUE @image TABLE = CALL ComponentFieldImage([N45W122 Tiles], 'Tile');

VALUE @heights TABLE = (VALUES (500), (600), (700), (1500), (3000));

DELETE FROM [Contour lines custom Table];

INSERT INTO [Contour lines custom Table] (

  [Value],

  [Geom]

) SELECT

  [Value],

  [Geom]

FROM CALL TileContourLinesPar(@image, 0, @heights, TRUE, ThreadConfig(SystemCpuCount()));

 

When we press the ! Run button, the query is executed, and a new drawing called Contour lines custom with contour lines and a corresponding table appear in the Project pane.

 

 

We can drop the new drawing into the map and thematically format it with line stroke colors to create a display like the above.   Note there are a few contour areas grouped more closely together at the 500, 600 and 700 elevation values.

 

If we would like to create contour areas at the 500, 600, 700, 1500 and 3000 values, back in the Transform pane we choose area as the Output type and we change the names of the New drawing and New table for the Result destination.   We press Edit Query to generate the analogous query, and then we replace the Value line with exactly the same new line, using VALUES, as before:

 

 

When we press the ! Run button, the query is executed, and a new drawing called Contour areas custom with contour areas and a corresponding table appear in the Project pane.

 

 

We drag and drop the new Contour areas custom drawing into the map as a layer, and then we use Style to thematically color it using the ValueMax value.     The CB Spectral palette used in other examples produces a garish effect in this case, so instead we have used pastel colors.

Notes

Edit Query is an easy shortcut -  We do not have to use Edit Query, but it sure is convenient.  If we happen to be maximum SQL experts we can, of course, write an SQL query freehand using the various TileContour SQL functions.    To save time and effort, many people, especially beginners but also those who know SQL, like using the Edit Query button to write most of the query.    The button automatically writes SQL for various infrastructure tasks, like creating tables and drawings using the desired coordinate system, that are tedious for anybody, including experts. 

 

Full Queries - The full text of the original query generated by the Edit Query button follows below:

 

-- $manifold$

--

-- Auto-generated

--

-- Contour

--   Layer: N45W122

--   Field: Tile

--   Channel: channel 0

--   Output type: line

--   Start: 0

--   End: 3300

--   Step: 300

--   Round start and end to step: FALSE

--   Split into shapes: TRUE

--   Result: (new table)

--   Result type: geom

--   New drawing: Contour lines custom

--   New table: Contour lines custom Table

--   Resources: all CPU cores, all GPU cores

--

 

-- prepare begin

 

CREATE TABLE [Contour lines custom Table] (

  [mfd_id] INT64,

  [Value] FLOAT64,

  [Geom] GEOM,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' ComponentFieldCoordSystem([N45W122 Tiles], 'Tile')

);

CREATE DRAWING [Contour lines custom] (

  PROPERTY 'Table' '[Contour lines custom Table]',

  PROPERTY 'FieldGeom' 'Geom'

);

 

-- prepare end

 

VALUE @image TABLE = CALL ComponentFieldImage([N45W122 Tiles], 'Tile');

VALUE @heights TABLE = CALL ValueSequence(0, 3300, 300);

DELETE FROM [Contour lines custom Table];

INSERT INTO [Contour lines custom Table] (

  [Value],

  [Geom]

) SELECT

  [Value],

  [Geom]

FROM CALL TileContourLinesPar(@image, 0, @heights, TRUE, ThreadConfig(SystemCpuCount()));

 

Videos

Trace Vector Areas from Raster Pixels

 

Five Minutes for Contours

 

Contour a 300MB DEM in Five Seconds

 

See Also

Style

 

Transform Pane

 

Transform - Tiles

 

Example: Tanaka Contours - Also known as illuminated contours, Tanaka contours give the appearance of three dimensionality to contour lines by brightening lines on a slope facing a presumed light source while darkening lines on a slope facing away from the light source.  Lines are also made wider when perpendicular to the light source.  This topic shows how to create the Tanaka effect in contour lines.

 

Example: Contour Areas and Contour Lines - In this example we use the Contour : area transform template in the Transform pane for images to create a drawing with vector areas showing height contours at desired altitude steps.   We color the areas using the attribute fields automatically created by the template.  Next, we apply a similar procedure using the Contour : line transform template to create a drawing with vector lines showing height contours at the desired intervals.

 

Example: Flooded Roads - We consider a hypothetical case of a 10 meter rise in sea level in the San Francisco Bay area, and we find what highways and major roads would be flooded by such a rise.   The example uses both raster and vector data sets, combines a number of techniques and uses the Contour, Buffer, Merge, and Clip transform templates.

 

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 a simple query to add classification codes from a USGS table of classes to the resulting drawing, using a simple INNER JOIN SQL statement.