SQL Example: Process Images with 3x3 Filters

This example 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.    

 

The discussion in this topic is the first in a series of three topics.  The discussion continues in the SQL Example: Process Images using Dual 3x3 Filters  topic, followed by the SQL Example: Process RGB Images using Matrix Filters topic.

 

 

We will work with the image seen above, an overhead view of the vast Basilica of St Peter in the Vatican, in Rome.  

 

 Important: The queries in this topic are hard-wired to use the above image.  To adapt them to work with other images, we must change the name of the image and the table used and also change the Rect size numbers used in the query to the right numbers for the image.

 

The image is a single-channel image using a data type of float64 for the pixels.   Our objective is to create a query that allows us to use filter matrices that we might find on the web to compute effects.   We will choose one of the Transform templates that we know uses a filter, we will press the Edit Query button to see what SQL is used to accomplish that transform, and then we will edit that SQL to be more general purpose.

 

With the focus on the st_peters_grayscale image we launch the Transform pane.  We choose Tile as the target field and then we double-click the Filter template to launch it.

 

 

In the Filter template we choose the blur option for Filter, and we leave the other settings at defaults.   We know from reading the Transform - Tiles: Filter topic that choosing a Radius of 1 specifies a 3x3 matrix.    

 

For the Result, we choose New Table and enter st_peters_grayscale Tiles Blur Image for the name of the new drawing and st_peters_grayscale Tiles Blur for the name of the table.   We can use whatever names we want (shorter names would probably be wiser...) but for now we will use those names since they help us remember what those components are supposed to be.

 

The default table name created automatically as we enter a name for the New image ends in ...Table.  This topic uses a table name that ends in ...Tiles, which is how some users prefer to name tables for images.  Others prefer to have all of their tables, both for drawings and images, end in ...Table.  We can adjust the name automatically created for the table however we like.

 

We press the Edit Query button to see what SQL Manifold uses to implement that transform operation.

The query written by Edit Query

The Command Window launches with the following SQL query loaded.  

 

To reduce the size of this topic, we will show the SQL, but not also show the Command Window in an illustration.  In addition, we will wrap lines to reduce the width of this topic.  In general, it is not a good idea to wrap text values enclosed by single quote ' characters, but we will do it in this topic for illustration only.

 

-- $manifold$

--

-- Auto-generated

--

-- Filter

--   Layer: st_peters_grayscale

--   Field: Tile

--   Channel: channel 0

--   Filter: blur

--   Shape: square

--   Radius: 1

--   Center: 1

--   Result: (new table)

--   Channel type: float64

--   New image: st_peters_grayscale Tiles Blur Image

--   New table: st_peters_grayscale Tiles Blur

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: FALSE

--

 

-- prepare begin

 

CREATE TABLE [st_peters_grayscale Tiles Blur] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

  PROPERTY 'FieldCoordSystem.Tile'

    ComponentFieldCoordSystem([st_peters_grayscale Tiles], 'Tile'),

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [st_peters_grayscale Tiles Blur Image] (

  PROPERTY 'Table' '[st_peters_grayscale Tiles Blur]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds(

    [st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

VALUE @filterDef TILE = TileFilterDefSquare(1, 1);

DELETE FROM [st_peters_grayscale Tiles Blur];

INSERT INTO [st_peters_grayscale Tiles Blur] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV(TileRemoveBorder(TileFilter(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), 1), 1, @filterDef), 1) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

TABLE CALL TileUpdateFieldPyramids([st_peters_grayscale Tiles Blur], 'Tile');

ALTER IMAGE [st_peters_grayscale Tiles Blur Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([st_peters_grayscale Tiles Blur], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([st_peters_grayscale Tiles Blur Image], 'Rect'))

);

 

Let us discuss the query to see how it works.   The query uses a CREATE TABLE statement and a CREATE IMAGE statement to create a destination table and an image for the results of the Blur filter.   It is handy to have such infrastructure available to cut and paste, but that is not our main interest.  Our main interest is what happens in the INSERT INTO... statement, where the newly created table is filled with the results of the Blur filter.

 

The query does its work in a single line (wrapped to keep this topic more compact):

 

  CASTV(TileRemoveBorder(TileFilter(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), 1), 1, @filterDef), 1) AS FLOAT64)

 

The reference to @filterDef uses the global value

 

VALUE @filterDef TILE = TileFilterDefSquare(1, 1);

 

We can run the query by pressing the ! Run button and the result will be just as if we pressed the Transform button: a new drawing and table will be created, and the pixels in the drawing will be blurred using a square filter matrix.  The view below is slightly zoomed in since a 3x3 Blur matrix does not blur the image very much:

 

 

To get a better feel for the blur effect, we can create an illustration, as seen below, that insets into the illustration a portion of the original image at the same scale.

 

 

The inset shows how the details of the dome of the basilica are sharper in the original.

Why do we need Border functions?

The "border" functions are supporting infrastructure, and the CASTV operator simply converts the output into a float64 data type.  The key work of blurring the image is done by the TileFilter function, which applies a filter to a tile.  In the case of the Blur template using a square filter, the filter is defined by the TileFilterDefSquare function.  The syntax templates for these functions are:

 

TileFilter(<tile>, <radius>, <filter>)

 

TileFilterDefSquare(<radius>, <center>)

 

Using a value of 1 for the radius argument and 1 for the center produces the default definition of a Blur filter, using a 3x3 matrix:

 

TileFilterDefSquare(1, 1)

 

The tile being used is simply [Tile] and for the same radius as the filter, the radius is 1, so it seems we could populate the TileFilter function with the following arguments:

 

TileFilter([Tile], 1, TileFilterDefSquare(1, 1))

 

If we like, we can edit the query to see what happens if we try that, changing the original...

 

  CASTV(TileRemoveBorder(TileFilter(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), 1), 1, @filterDef), 1) AS FLOAT64)

 

...into a simpler form:

 

  CASTV (TileFilter([Tile], 1, @filterDef) AS FLOAT64)

 

The full query, as re-written, would be:

 

-- $manifold$

--

-- Auto-generated

--

-- Filter

--   Layer: st_peters_grayscale

--   Field: Tile

--   Channel: channel 0

--   Filter: blur

--   Shape: square

--   Radius: 1

--   Center: 1

--   Result: (new table)

--   Channel type: float64

--   New image: st_peters_grayscale Tiles Blur Image

--   New table: st_peters_grayscale Tiles Blur

--   Resources: all CPU cores, all GPU cores

--   Transform selection only: FALSE

--

 

-- prepare begin

 

CREATE TABLE [st_peters_grayscale Tiles Blur] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

  PROPERTY 'FieldCoordSystem.Tile'

    ComponentFieldCoordSystem([st_peters_grayscale Tiles], 'Tile'),

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [st_peters_grayscale Tiles Blur Image] (

  PROPERTY 'Table' '[st_peters_grayscale Tiles Blur]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds(

    [st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

VALUE @filterDef TILE = TileFilterDefSquare(1, 1);

 

DELETE FROM [st_peters_grayscale Tiles Blur];

INSERT INTO [st_peters_grayscale Tiles Blur] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV (TileFilter([Tile], 1, @filterDef) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([st_peters_grayscale Tiles Blur], 'Tile');

ALTER IMAGE [st_peters_grayscale Tiles Blur Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([st_peters_grayscale Tiles Blur], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([st_peters_grayscale Tiles Blur Image], 'Rect'))

);

 

We can press ! to run that query.    The resulting image is unexpected:

 

 

The image has a grid of missing pixels.   Those arise when the 3x3 filter, working computational magic as described in the How Matrix Filters Work topic, overhangs the edge of a tile.  To avoid such effects, we must feed TileFilter a tile that has an extra border around it of whatever size radius we are using.  In this case given a radius of 1 we must feed TileFilter a tile that includes a border of one pixel surrounding the tile, the tile and the additional pixel surrounding it being cut out of the image.

 

 When we repeatedly run different versions of the queries in this topic, we should keep in mind that the queries we are using do not delete same-named components that already exist  They will fail if we do not first delete st_peters_grayscale Tiles Blur Image and the st_peters_grayscale Tiles Blur table before running a new query that attempts to create and populate components with those same names.   

 

Alternatively, we can run only the query code that comes after the -- prepare end comment line.   We can do that by highlighting all of the lines after the -- prepare end comment, and pressing Alt-Enter to just run highlighted lines.   

 

Queries created by Transform templates are written in two parts, a prepare part that creates the destination drawing or image and table, and a second part that populates those with the results of the transform.   This makes it easy to change the operational part of the transform and to re-run it using Alt-Enter, without trying to recreate the components.

Using Border functions

Instead of simply referring to the [Tile] we must use an expression that carves the desired slightly larger tile out of the image, using the TileCutBorder function.    The template for the TileCutBorder function is:

 

TileCutBorder(<image>, <valuex2>, <border>)  

 

The image is [st_peters_grayscale] and the border is 1, so that leaves the computation of the <valuex2> argument that tells the function which X,Y tile position to use.   We create a <valuex2> value using the VectorMakeX2 function using the X and Y arguments:

 

VectorMakeX2([X], [Y])

 

Therefore, instead of a simple expression such as

 

TileFilter([Tile], 1, @filterDef)

 

The query uses:

 

TileFilter(TileCutBorder([st_peters_grayscale], VectorMakeX2([X], [Y]), 1), 1, @filterDef)

 

The above produces a blurred tile that is one pixel too large on all sides.  We cut it down using TileRemoveBorder with an argument of 1 for the border:

 

TileRemoveBorder(  TileFilter(TileCutBorder([st_peters_grayscale], VectorMakeX2([X], [Y]), 1), 1, @filterDef) , 1)

 

That is a long digression to discuss the infrastructure of the query, and why the "border" functions are used.   Let us return to discussing how the query can be made more general and adapted to using different matrix filters.

Using a Parameter

The actual work in between calls to border functions is done by the TileFilter function that is operating on a filter definition.  In the case of the Blur template, that filter definition is provided by

 

TileFilterDefSquare(1, 1)

 

If we want to use a different filter, we can keep everything about the query the same and simply change the filter definition.   That is why the query uses a parameter called  @filterDef that is defined with a VALUE statement.  

 

VALUE @filterDef TILE = TileFilterDefSquare(1, 1);

 

Everything that comes after can simply write @filterDef instead of TileFilterDefSquare(1, 1).

 

Change the Filter Used

Now that we have the definition of the filter within a neatly compartmentalized parameter at the beginning of the query, if we like we can use a different function to generate a different matrix filter, or we can use the StringJsonTile function to create our own, custom filter.   For example, instead of using the TileFilterDefSquare function we can use the TileFilterDefSharpen function to accentuate edges.   We can replace:

 

VALUE @filterDef TILE = TileFilterDefSquare(1, 1);

 

With

 

VALUE @filterDef TILE = TileFilterDefSharpen(1, 1);

 

In addition to making the above substitution, we remove some unnecessary comments at the beginning of the query, and we use Ctrl-H to rename the components to be created from using st_peters_grayscale Tiles Blur to using st_peters_grayscale Tiles Custom.   If we will be editing the query to use different filters, it doesn't make sense to keep  using  "Blur" in the names of the resulting components.

 

The full query now is:

 

-- prepare begin

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

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

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [st_peters_grayscale Tiles Custom Image] (

  PROPERTY 'Table' '[st_peters_grayscale Tiles Custom]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

VALUE @filterDef TILE = TileFilterDefSharpen(1, 1);

 

DELETE FROM [st_peters_grayscale Tiles Custom];

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV(TileRemoveBorder(TileFilter(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), 1), 1, @filterDef), 1) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([st_peters_grayscale Tiles Custom], 'Tile');

ALTER IMAGE [st_peters_grayscale Tiles Custom Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([st_peters_grayscale Tiles Custom], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([st_peters_grayscale Tiles Custom Image], 'Rect'))

);

 

We can copy and paste the above into an SQL Command Window and then press the ! button to run it.  

 

 Important:  Copying and pasting from web pages can introduce new lines.   Make sure any lines that are commented out with -- characters remain one line after they are pasted, and do not turn into two lines, one commented out and the other not commented out, or the query will throw an error from parts of the line that are no longer commented out.

 

The result:

 

 

As expected, the result is the same as if we had run the Filter : sharpen transform operation, an emphasizing of edge features in the image.

Specifying New, Custom Filters

Our query now has the definition of the filter neatly compartmentalized at the beginning of the query.  So far, we have been using built-in "TileFilterDef" functions like TileFilterDefSquare and TileFilterDefSharpen, which generate a filter matrix definition for the TileFilter function to use in processing the image.

 

If we like, instead of using a built-in tile definition function we can use the StringJsonTile function to create our own, custom filter from a list of numbers that we want in the filter matrix.  The StringJsonTile function takes a specification for a matrix in human-readable JSON text and it generates a filter, just like the "TileFilterDef" functions create, that can be fed to TileFilter.

 

The syntax of the StringJsonTile function is:

 

StringJsonTile(<json>, <cx>, <cy>, <channels>, <strict>)

 

For a 3x3 matrix the cx and cy arguments are both 3, for a single layer thick matrix the channels argument is 1, and we will use 1 for the strict argument.   That leaves the json argument.   To learn how to write that correctly, we can see what Manifold uses by applying the TileJson function to any of the filter definition functions.   We can then use what we learn, from seeing how Manifold does it, to write our own JSON strings.

 

For example, to see what TileFilterDefSquare(1,1) generates, using arguments that specify a radius of 1 to get a 3x3 matrix, and a center of 1, we can launch the Command Window and enter:

 

? TileJson(TileFilterDefSquare(1, 1))

 

Press the ! Run button and the Log pane of the Command Window reports:

 

> ? TileJson(TileFilterDefSquare(1, 1))

nvarchar: [

 1, 1, 1,

 1, 1, 1,

 1, 1, 1

]

 

From the above we can see the json argument is simply a text string.  In the Log pane Manifold conveniently formats the string for us in three rows so it looks like a 3x3 matrix.   When we create our own filter JSON specification we would put the entire string all on the same line.  

 

If we wanted to manually specify our own filter, the exact equivalent of what TileFilterDefSquare generates, instead of using:

 

VALUE @filterDef TILE = TileFilterDefSquare(1, 1);

 

We could write:

 

VALUE @filterDef TILE = StringJsonTile('[ 1, 1, 1, 1, 1, 1, 1, 1, 1 ]', 3, 3, 1, true);

 

The two versions are exactly equivalent.   We will now try that, commenting out the former VALUE line (which still uses the sharpen filter) and inserting the new VALUE line.  The query now looks like:

 

-- prepare begin

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

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

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [st_peters_grayscale Tiles Custom Image] (

  PROPERTY 'Table' '[st_peters_grayscale Tiles Custom]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

-- VALUE @filterDef TILE = TileFilterDefSharpen(1, 1);

VALUE @filterDef TILE = StringJsonTile('[ 1, 1, 1, 1, 1, 1, 1, 1, 1 ]', 3, 3, 1, true);

 

DELETE FROM [st_peters_grayscale Tiles Custom];

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV(TileRemoveBorder(TileFilter(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), 1), 1, @filterDef), 1) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([st_peters_grayscale Tiles Custom], 'Tile');

ALTER IMAGE [st_peters_grayscale Tiles Custom Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([st_peters_grayscale Tiles Custom], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([st_peters_grayscale Tiles Custom Image], 'Rect'))

);

 

We copy and paste that into a Command Window, and press ! to Run to see what happens.   

 

 If we already have a st_peters_grayscale Tiles Custom Image and a st_peters_grayscale Tiles Custom table in our project, we can just highlight all of the lines after the -- prepare end comment, and press Alt-Enter to just run the highlighted lines.

 

Queries created by Transform templates are written in two parts, a prepare part that creates the destination drawing or image and table, and a second part that populates those with the results of the transform.   This makes it easy to change the operational part of the transform and to re-run it using Alt-Enter, without trying to recreate the components.

 

 

Like magic, the result is exactly what we got earlier from using the square matrix Blur.   That is to be expected, because specifying the filter matrix used for a square matrix Blur by listing the numbers is using exactly the same matrix as produced by the TileFilterDefSquare function.

An Emboss Filter

Now that we have a generic way of processing our image using a list of numbers that make up a 3x3 matrix filter, we can use different filters that we find on Internet.   For example, a popular 3x3 matrix filter used for "emboss" effects is this matrix:

 

-2, -1,  0,

-1,  1,  1,

 0,  1,  2

 

We can write that for use with StringJsonTile as:

 

-- Emboss

VALUE @filterDef TILE = StringJsonTile('[ -2, -1, 0, -1, 1, 1, 0, 1, 2 ]', 3, 3, 1, true);

 

If we comment out the prior Blur filter, add a few comments to remind us what we've done, and then add the filter above, we can use that emboss filter matrix with our query:

 

-- prepare begin

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

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

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [st_peters_grayscale Tiles Custom Image] (

  PROPERTY 'Table' '[st_peters_grayscale Tiles Custom]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

 

-- Use built-in Sharpen filter matrix

-- VALUE @filterDef TILE = TileFilterDefSharpen(1, 1);

 

-- Manually define a Blur filter

-- VALUE @filterDef TILE = StringJsonTile('[ 1, 1, 1, 1, 1, 1, 1, 1, 1 ]', 3, 3, 1, true);

 

-- Emboss

VALUE @filterDef TILE = StringJsonTile('[ -2, -1, 0, -1, 1, 1, 0, 1, 2 ]', 3, 3, 1, true);

 

DELETE FROM [st_peters_grayscale Tiles Custom];

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV(TileRemoveBorder(TileFilter(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), 1), 1, @filterDef), 1) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([st_peters_grayscale Tiles Custom], 'Tile');

ALTER IMAGE [st_peters_grayscale Tiles Custom Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([st_peters_grayscale Tiles Custom], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([st_peters_grayscale Tiles Custom Image], 'Rect'))

);

 

 Important:  Copying and pasting from web pages can introduce new lines.   Make sure any lines that are commented out with -- characters remain one line after they are pasted, and do not turn into two lines, one commented out and the other not commented out, or the query will throw an error from parts of the line that are no longer commented out.

 

Copy and paste the above query into an SQL command window, and press ! to run the query.

 

 

The result is a classic emboss effect.    

 

Now that we know how to create and use custom filters, we can cruise the Internet to find a very wide range of various 3x3 matrix filters for different processing effects.   

A Laplacian Edge Detection Filter

Consider a classic Laplacian edge detection filter often described in web sites on convolution matrix filters:

 

-1, -1, -1,

-1,  8, -1,

-1, -1, -1

 

We can write that for use with StringJsonTile as:

 

-- Laplacian Edge Detect

VALUE @filterDef TILE = StringJsonTile('[ -1, -1, -1, -1, 8, -1, -1, -1, -1 ]', 3, 3, 1, true);

 

Commenting out the emboss filter and adding the above to our query, we get:

 

-- prepare begin

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

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

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [st_peters_grayscale Tiles Custom Image] (

  PROPERTY 'Table' '[st_peters_grayscale Tiles Custom]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

 

-- Use built-in Sharpen filter matrix

-- VALUE @filterDef TILE = TileFilterDefSharpen(1, 1);

 

-- Manually define a Blur filter

-- VALUE @filterDef TILE = StringJsonTile('[ 1, 1, 1, 1, 1, 1, 1, 1, 1 ]', 3, 3, 1, true);

 

-- Emboss

-- VALUE @filterDef TILE = StringJsonTile('[ -2, -1, 0, -1, 1, 1, 0, 1, 2 ]', 3, 3, 1, true);

 

-- Laplacian Edge Detect

VALUE @filterDef TILE = StringJsonTile('[ -1, -1, -1, -1, 8, -1, -1, -1, -1 ]', 3, 3, 1, true);

 

DELETE FROM [st_peters_grayscale Tiles Custom];

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV(TileRemoveBorder(TileFilter(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), 1), 1, @filterDef), 1) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([st_peters_grayscale Tiles Custom], 'Tile');

ALTER IMAGE [st_peters_grayscale Tiles Custom Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([st_peters_grayscale Tiles Custom], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([st_peters_grayscale Tiles Custom Image], 'Rect'))

);

 

Copy and paste the above query into an SQL command window.   Remember to check the pasted text to ensure that any lines that are commented out with -- characters remain one line after they are pasted.  Press ! to run the query.

 

 

The result is a classic output of an edge detection computation, where pixels on the edges of transitions in color values are emphasized, using the process discussed in the How Matrix Filters Work  topic.   

 

The appearance of such edge detection results will vary dramatically depending on settings in the Style pane for contrast and range.

 

 

For example, the display above results after using Full Range and then Autocontrast with a high setting.

A Sobel Vertical Edge Detection Filter

There are many 3x3 filters used for edge detection.   By adjusting the numeric coefficients in the matrix, we can create matrices (or, more likely, copy them from web sites we find) that detect edges running in different directions.  For example, a Sobel filter for emphasizing vertical, but not horizontal, edges using a single matrix filter is:

 

 1,  0, -1,

 2,  0, -2,

 1,  0, -1

 

We can write that for use with StringJsonTile as:

 

-- Vertical Edge Detect Single Sobel

VALUE @filterDef TILE = StringJsonTile('[ 1, 0, -1, 2, 0, -2, 1, 0, -1 ]', 3, 3, 1, true);

 

Adding the above to our query and commenting out the Laplacian filter used, we get:

 

-- prepare begin

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

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

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [st_peters_grayscale Tiles Custom Image] (

  PROPERTY 'Table' '[st_peters_grayscale Tiles Custom]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

 

-- Use built-in Sharpen filter matrix

-- VALUE @filterDef TILE = TileFilterDefSharpen(1, 1);

 

-- Manually define a Blur filter

-- VALUE @filterDef TILE = StringJsonTile('[ 1, 1, 1, 1, 1, 1, 1, 1, 1 ]', 3, 3, 1, true);

 

-- Emboss

-- VALUE @filterDef TILE = StringJsonTile('[ -2, -1, 0, -1, 1, 1, 0, 1, 2 ]', 3, 3, 1, true);

 

-- Laplacian Edge Detect

-- VALUE @filterDef TILE = StringJsonTile('[ -1, -1, -1, -1, 8, -1, -1, -1, -1 ]', 3, 3, 1, true);

 

-- Vertical Edge Detect Single Sobel

VALUE @filterDef TILE = StringJsonTile('[ 1, 0, -1, 2, 0, -2, 1, 0, -1 ]', 3, 3, 1, true);

 

DELETE FROM [st_peters_grayscale Tiles Custom];

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV(TileRemoveBorder(TileFilter(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), 1), 1, @filterDef), 1) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([st_peters_grayscale Tiles Custom], 'Tile');

ALTER IMAGE [st_peters_grayscale Tiles Custom Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([st_peters_grayscale Tiles Custom], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([st_peters_grayscale Tiles Custom Image], 'Rect'))

);

 

Copy and paste the above query into an SQL command window.   Remember to check the pasted text to ensure that any lines that are commented out with -- characters remain one line after they are pasted.  Press ! to run the query.

 

 

Compared to the Laplacian edge detection matrix, the Sobel matrix output de-emphasizes horizontal edges, with no horizontal edge on the nave of the basilica, for example, while emphasizing vertical edges.  Edges at angles other than vertical are proportionally emphasized or de-emphasized.

 

The image above uses Full Range and Autocontrast set to high.

Cleaning Up the Query

We have come a long way by editing a query originally created by the Edit Query button.    We will make two more changes to make the query better suited for adaptation to different filters and to different images:

 

 

Generic names

We use Ctrl-H to search and replace,  replacing st_peters_grayscale Tiles Custom with Custom.   The query now reads:

 

-- prepare begin

 

CREATE TABLE [Custom] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

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

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [Custom Image] (

  PROPERTY 'Table' '[Custom]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

 

-- Use built-in Sharpen filter matrix

-- VALUE @filterDef TILE = TileFilterDefSharpen(1, 1);

 

-- Manually define a Blur filter

-- VALUE @filterDef TILE = StringJsonTile('[ 1, 1, 1, 1, 1, 1, 1, 1, 1 ]', 3, 3, 1, true);

 

-- Emboss

-- VALUE @filterDef TILE = StringJsonTile('[ -2, -1, 0, -1, 1, 1, 0, 1, 2 ]', 3, 3, 1, true);

 

-- Laplacian Edge Detect

-- VALUE @filterDef TILE = StringJsonTile('[ -1, -1, -1, -1, 8, -1, -1, -1, -1 ]', 3, 3, 1, true);

 

-- Vertical Edge Detect Single Sobel

VALUE @filterDef TILE = StringJsonTile('[ 1, 0, -1, 2, 0, -2, 1, 0, -1 ]', 3, 3, 1, true);

 

DELETE FROM [Custom];

INSERT INTO [Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV(TileRemoveBorder(TileFilter(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), 1), 1, @filterDef), 1) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([Custom], 'Tile');

ALTER IMAGE [Custom Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([Custom], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([Custom Image], 'Rect'))

);

 

The query works exactly the same way as before, but now it writes the results into a table called Custom and an image called Custom Image.   

 

In the query text above, blue color highlights three places where the name of the input image's table is specified.   We can easily change the query to use a different source image by entering the name of the image's table in those three places.  

Put tile processing in a function

We will define a function called processtile that takes as arguments the tile to be processed, the radius of the filter matrix,  and the filter matrix to use.  The filter matrix is just a tile, albeit a small tile that is only 3x3 pixels in size.   In the simple processing we are doing, the processtile function passes the arguments to TileFilter and then returns the results.   We have added a @radius parameter so that in the future we can change the radius and use larger matrix filters, such as 5x5 matrix filters.

 

The big line of SQL within the CASTV we rewrite to use processtile instead of TileFilter The query now reads:

 

-- prepare begin

 

CREATE TABLE [Custom] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

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

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [Custom Image] (

  PROPERTY 'Table' '[Custom]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

 

-- Use built-in Sharpen filter matrix

-- VALUE @filterDef TILE = TileFilterDefSharpen(1, 1);

 

-- Manually define a Blur filter

-- VALUE @filterDef TILE = StringJsonTile('[ 1, 1, 1, 1, 1, 1, 1, 1, 1 ]', 3, 3, 1, true);

 

-- Emboss

-- VALUE @filterDef TILE = StringJsonTile('[ -2, -1, 0, -1, 1, 1, 0, 1, 2 ]', 3, 3, 1, true);

 

-- Laplacian Edge Detect

-- VALUE @filterDef TILE = StringJsonTile('[ -1, -1, -1, -1, 8, -1, -1, -1, -1 ]', 3, 3, 1, true);

 

-- Vertical Edge Detect Single Sobel

VALUE @filterDef TILE = StringJsonTile('[ 1, 0, -1, 2, 0, -2, 1, 0, -1 ]', 3, 3, 1, true);

 

VALUE @radius UINT8 = 1;

 

FUNCTION processtile(@t TILE, @r UINT8, @f TILE) TILE AS (

  TileFilter(@t, @r, @f)

) END;

 

DELETE FROM [Custom];

INSERT INTO [Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(processtile(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), @radius), @radius, @filterDef

    ), @radius)) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([Custom], 'Tile');

ALTER IMAGE [Custom Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([Custom], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([Custom Image], 'Rect'))

);

 

The changes made are indicated in color, above.   Using a function is not really necessary if all we ever do is run TileFilter once, but if we want to do more sophisticated things it will be much easier to make changes in only one place, within the processtile function.

 

Running the above query generates exactly the same results as the prior versions.  We have not changed the logic of the query.  We have just repackaged parts of it to make future extensions more convenient.  

Use a 5x5 Filter Matrix: "Unsharp" Sharpening

Now that we have the @filterDef parameter and a @radius parameter, we can easily use a 5x5 filter matrix, such as those used for "unsharp" filters.  Unsharp filters are sharpening filters which work by first doing a slight Gaussian blur (the "unsharp" part) and then applying sharpening to remaining edge effects.  A mathematical consequence of the operations is that both phases can be reduced into a single filter matrix.  

 

In the following query @filterDef points at a 5 x 5 matrix filter, formatted in way that makes it clear it is a 5 x 5 matrix, and @radius has been set to 2.    We need make no other changes.   We have eliminated from the query extra filters that have been commented out, except for the Sobel, which we have left for comparison.

 

-- prepare begin

 

CREATE TABLE [Custom] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

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

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [Custom Image] (

  PROPERTY 'Table' '[Custom]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([st_peters_grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

 

-- Vertical Edge Detect Single Sobel

-- VALUE @filterDef TILE = StringJsonTile('[ 1, 0, -1, 2, 0, -2, 1, 0, -1 ]', 3, 3, 1, true);

 

-- Unsharp 5x5

VALUE @filterDef TILE = StringJsonTile('[

-0.00391, -0.01563, -0.02344, -0.01563, -0.00391,

-0.01563, -0.06250, -0.09375, -0.06250, -0.01563,

-0.02344, -0.09375,  1.85938, -0.09375, -0.02344,

-0.01563, -0.06250, -0.09375, -0.06250, -0.01563,

-0.00391, -0.01563, -0.02344, -0.01563, -0.00391

]', 5, 5, 1, true);

 

VALUE @radius UINT8 = 2;

 

FUNCTION processtile(@t TILE, @r UINT8, @f TILE) TILE AS (

  TileFilter(@t, @r, @f)

) END;

 

DELETE FROM [Custom];

INSERT INTO [Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(processtile(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), @radius), @radius, @filterDef

    ), @radius)) AS FLOAT64)

FROM [st_peters_grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([Custom], 'Tile');

ALTER IMAGE [Custom Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([Custom], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([Custom Image], 'Rect'))

);

 

Use of the 5x5 Unsharp filter results in a dramatically cleaner sharpening effect than the default sharpen.   

 

The following three images show first the original, next the Custom Image result of the above 5x5 Unsharp filter query, and then last the result of running the built-in sharpen.

 

 Style settings can have a very big effect on the appearance of images.  The following three images have had Style used to set the range for R,G, and B channels from 0 to 255, which gives them the same dynamic range and approximately the same contrast and other visual characteristics.

 

 

Original, above.

 

 

5x5 Unsharp, above.

 

 

Default Filter : sharpen transform, using 3x3 matrix.   Using a Radius of 2 for a 5x5 matrix produces unpleasant effects in the default Sharpen template.

Use a Different Image

To use our query with a different image, all we need do is change the name of the image in the three spots where it occurs.  We will use a sample image that is a grayscale, single channel rendering of Leonardo da Vinci's portrait of Ginevra de' Benci.   See the discussion in the Notes to the Example: Change the Contrast of an Image  topic.

 

 

Adapting our query is a simple matter of using Ctrl-H to search and replace st_peters_grayscale with ginevra grayscale.   

 

 

In the Command Window for our query, we press Ctrl-H to launch the Replace dialog.   Enter the Search for and Replace with texts, and press Replace All.   While we are at it, we will replace all instances of Custom with ginevra Custom.

 

The resulting query:

 

-- prepare begin

 

CREATE TABLE [ginevra Custom] (

  [X] INT32,

  [Y] INT32,

  [mfd_id] INT64,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

  PROPERTY 'FieldCoordSystem.Tile' ComponentFieldCoordSystem([ginevra grayscale Tiles], 'Tile'),

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [ginevra Custom Image] (

  PROPERTY 'Table' '[ginevra Custom]',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'Rect' CAST(ComponentFieldBounds([ginevra grayscale Tiles], 'Tile', FALSE) AS NVARCHAR)

);

 

-- prepare end

 

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

 

-- Vertical Edge Detect Single Sobel

-- VALUE @filterDef TILE = StringJsonTile('[ 1, 0, -1, 2, 0, -2, 1, 0, -1 ]', 3, 3, 1, true);

 

-- Unsharp 5x5

VALUE @filterDef TILE = StringJsonTile('[

-0.00391, -0.01563, -0.02344, -0.01563, -0.00391,

-0.01563, -0.06250, -0.09375, -0.06250, -0.01563,

-0.02344, -0.09375,  1.85938, -0.09375, -0.02344,

-0.01563, -0.06250, -0.09375, -0.06250, -0.01563,

-0.00391, -0.01563, -0.02344, -0.01563, -0.00391

]', 5, 5, 1, true);

 

VALUE @radius UINT8 = 2;

 

FUNCTION processtile(@t TILE, @r UINT8, @f TILE) TILE AS (

  TileFilter(@t, @r, @f)

) END;

 

DELETE FROM [ginevra Custom];

INSERT INTO [ginevra Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(processtile(

    TileCutBorder(@image, VectorMakeX2([X], [Y]), @radius), @radius, @filterDef

    ), @radius)) AS FLOAT64)

FROM [ginevra grayscale Tiles] THREADS SystemCpuCount();

 

TABLE CALL TileUpdateFieldPyramids([ginevra Custom], 'Tile');

ALTER IMAGE [ginevra Custom Image] (

  ADD PROPERTY 'Rect' Coalesce(

    CAST(ComponentFieldBounds([ginevra Custom], 'Tile', TRUE) AS NVARCHAR),

    ComponentProperty([ginevra Custom Image], 'Rect'))

);

 

We can run the query by pressing the ! button.   Below, we compare a zoomed in look at the original at left with the Unsharp sharpened image at right.

 

 

The basic structure of our query is easily adapted to using more complex processing, such as multiple filters, and also to processing of more than one channel, to allow filter matrix processing of RGB images.   Other topics will show examples.

 

Yet More Improvements

The discussion in this topic continues in the SQL Example: Process Images using Dual 3x3 Filters  topic.

 

Notes

Why a data type of float64? - The example images use a data type of float64.   That allows using a very wide range of transforms and filters, some of which produce fractional values in their output, without having to worry about the use of integer data types clipping data and eliminating intended effects.

 

Converting from some other data type, such as an integer type, into float64 is easy, using the following query:

 

-- $manifold$

-- Suppose we have an image called MyDEM, based on a table

--   called MyDEM Tiles, which stores data using

--   INT numbers (integers).  This query converts the MyDEM

--   data into using FLOAT64 numbers (floating point).

 

-- drop index on int tiles

ALTER TABLE [MyDEM Tiles] (

  DROP INDEX [X_Y_Tile_x]

);

-- convert tiles from int to float

UPDATE [MyDEM Tiles] SET

  [Tile] = CASTV([TILE] AS FLOAT64);

-- read index on float tiles

ALTER TABLE [MyDEM Tiles] (

  ADD INDEX [X_Y_Tile_x] RTREE ([X], [Y], [Tile] TILESIZE (128, 128) TILETYPE FLOAT64),

  ADD PROPERTY 'FieldTileType.Tile' 'float64'

);

-- ask index to rebuild intermediate levels

TABLE CALL TileUpdatePyramids([MyDEM]);,

 

Float64 downside -    There is some downside in using float64, in that we may want to play with Style settings to get the image appearance we want, for example, setting full range or setting the use of a range of 0 to 255, as is commonly used with integer grayscale or RGB images.

 

 

For example, the result of the Sobel filter shown earlier using Full Range and an Autocontrast setting of high is seen above.    Range numbers for channels using float64 numbers range from about -118.0462 to 119.0929.

 

 

If we use the Style pane to set range number for channels to a range of 0 to 255 we get a different visualization of the results of a Sobel filter, seen above.

 

Videos

Manifold Viewer - How Matrix Filters Work - The easy, simple way to learn how filters work! Watch this action-packed video using Manifold Viewer that illustrates how matrix filters, also known as convolution filters, work. In addition to explaining filters, the video provides a real-life look at simple Manifold techniques for moving objects around in drawings using the Shift transform, and fast and easy use of Selection and tables to quickly put desired values into attributes. Sound technical? Sure, but in a very easy and simple way.

 

Manifold Viewer - Create Custom GPU Accelerated Filters in Seconds - A technical video using the free Viewer showing how to create your own, fully custom, fully GPU-parallel, convolution matrix filters, including Emboss, Sobel, Prewitt, and Kirsch edge detection and many more, for use in Viewer or Release 9. Modify the spatial SQL examples in the downloadable example project to specify a custom matrix and in seconds your custom filter can do image processing at GPU-parallel speeds. Viewer is read-only, but you can copy and paste the query text for custom filters to and from Notepad or any other text editor. Download the Custom_Filter_Examples.mxb sample project from the Examples page on the Manifold website to try out the video in Viewer or Release 9.

 

Manifold Viewer - Speed Demo with 1280 GPU Cores - 2 Minutes vs 5 Days - Watch the free Manifold Viewer run CPU parallel and GPU parallel with 8 CPU cores and 1280 GPU cores to absolutely crush a job, doing in 2 minutes what would take non-GPU-parallel software 5 days. The video shows Viewer instantly pop open a 4 GB project that contains a huge, multi-gigabyte terrain elevation surface for Crater Lake, Oregon. With a point and click - no parallel code required - we compute the mean curvature at each pixel of the surface using a 7x7 matrix in under two minutes. We combine that with the original surface for enhanced hill shaded effects to better see details. Using an 11x11 matrix takes just over two minutes, a huge computation that takes days in non-GPU-parallel GIS packages.

 

See Also

Images

 

Tables

 

Data Types

 

Transform Pane

 

How Matrix Filters Work

 

Command Window

 

SQL Functions

 

SQL Example: Process Images using Dual 3x3 Filters  - A continuation of this topic, extending the example query to utilize two filters for processing, as commonly done with Sobel and Prewitt two filter processing.

 

SQL Example: Process RGB Images using Matrix Filters - A continuation of this topic and the above topic, extending the example query to process three channel, RGB images.

 

SQL Example: Create NDVI Displays - How to create a query that creates an NDVI display from a four-band NAIP image, with tips and tricks on how to copy and paste existing information to get the result we want.

 

SQL Example: Create Topographic Position Index TPI Displays - In this example, we use a few short lines of SQL to create a Topographic Position Index (TPI) display.  TPI characterizes the undulations of a terrain elevation surface.  TPI value above zero show locations that are higher than then average of immediately surrounding terrain, and thus tend to show ridges.   TPI values below zero show locations that are lower than the average of immediately surrounding terrain, and thus tend to show valleys.  TPI values that are zero show areas of constant slope.

 

Example: Enhance Terrain with Curvatures -  We enhance a terrain showing Crater Lake, Oregon, by using mean curvature calculation to bring out details.   The example uses a 4 GB project containing a large terrain elevation surface.  Using a point-and-click dialog with no SQL, we apply automatic CPU parallelism and GPU parallelism to absolutely crush a task in two and a half minutes that would take non-parallel software days.

 

Example: Rearrange Channels using an Expression - We use a simple expression in the Transform pane to rearrange the order of channels within the data.