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 very 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 Mt. Hood in Oregon.


We launch the Transform pane for images and choose the Contour Areas transform, choosing the Tile field.




We use the same values for the heights and Step as in the Example: Contour Areas and Contour Lines topic.   Those do not matter since we will edit the created query.   We leave the default Decompose to shapes option checked, which creates independent area objects in all regions for similar contour values.   Unchecking this box creates a single, multibranched area object for each contour interval.




Manifold thinks for a moment and shows a preview.   We do not care about the preview since we will create our own query.




To do that, we 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 whatever table and file names would be set by the options in the Options button.  The illustration above shows the Command Window, with a few of the lines modified by the addition of extra end-of-line characters to allow a more compact display.


The text we care about is highlighted.   It is a single CALL of the ValueSequence function:


CALL ValueSequence(0, 3300, 300)


That generates a sequence 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))


Important:  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:




The full text of the query now is:


-- $manifold$


-- Auto-generated

-- Transform - Contour Areas - Add Component


CREATE TABLE [ASTGTM2_N45W122_dem PM Tiles Contour Areas 2] (

  [Geom] GEOM,

  [ValueMin] FLOAT64,

  [ValueMax] FLOAT64,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [Geom_x] RTREE ([Geom]),

  PROPERTY 'FieldCoordSystem.Geom' '{ "Axes": "XY", "Base": "WGS 84 (EPSG:4326)",

    "CenterLat": 0, "CenterLon": 0, "Eccentricity": 0.08181919084262149, "LocalScaleX": 36.9,

    "LocalScaleY": 36.9, "MajorAxis": 6378137, "Name": "WGS 84 / Pseudo-Mercator (EPSG:3857)",

    "System": "Pseudo Mercator", "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }'


CREATE DRAWING [ASTGTM2_N45W122_dem PM Tiles Contour Areas Drawing 2] (

  PROPERTY 'Table' '[ASTGTM2_N45W122_dem PM Tiles Contour Areas 2]',

  PROPERTY 'FieldGeom' 'Geom'


PRAGMA ('progress.percentnext' = '100', 'progress.percentinsertsource' = '80');

INSERT INTO [ASTGTM2_N45W122_dem PM Tiles Contour Areas 2] (

  [Geom], [ValueMin], [ValueMax]


  [Geom], [ValueMin], [ValueMax]

FROM CALL TileContourAreasPar([ASTGTM2_N45W122_dem PM],

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

  true, ThreadConfig(SystemCpuCount()));


When we press the ! Run button the query is executed and a new drawing with contour areas is created.




We can drop the new drawing into the map and thematically format it with area fill 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 like, we can do the same procedure with the Contour Lines transform, to create contour lines at the 500, 600, 700, 1500 and 3000 values as seen above.



Edit Query is an easy shortcut, not a requirement - If we happen to be maximum SQL experts we can, of course, write an SQL query freehand using the various TileContour SQL functions, as listed in the Transform Templates - Images topic.    Many people , especially beginners but also average SQL experts, like use 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 and so on.   



Trace Vector Areas from Raster Pixels


Five Minutes for Contours


Contour a 300MB DEM in Five Seconds


See Also



Transform Pane


Transform Templates - Images


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 Areas 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 Lines transform template to create a drawing with vector lines showing height contours at the desired intervals.


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.