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.

 

 

eg_process_images_3x3_stpeters_grayscale.png

 

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

 

ico_nb_arrow_blue.png  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.

 

eg_process_images_3x3_01.png

 

With the focus on the st_peters_grayscale image we launch the Transform panel of the Contents pane.  We choose Tile as the target field and the Blur template, since we know from reading the Transform Templates - Drawings documentation that template uses a filter.   Choosing a Radius of 1 specifies a 3x3 matrix.    We press the Edit Query button to see what SQL Manifold uses to implement that template.

The query written by Edit Query

The Command Window launches with the following SQL query loaded.  We can run the query by pressing the ! Run button and the result will be just as if we pressed the Add Component 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:

 

eg_process_images_3x3_02.png

 

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

-- Transform - Blur - Add Component

--

CREATE TABLE [st_peters_grayscale Tiles Blur] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

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

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

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

  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 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [st_peters_grayscale Tiles Blur] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(TileFilter(

    TileCutBorder([st_peters_grayscale], VectorMakeX2([X], [Y]), 1), 1, TileFilterDefSquare(1, 1)

    ), 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([st_peters_grayscale Tiles Blur Image]);

 

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([st_peters_grayscale], VectorMakeX2([X], [Y]), 1), 1, TileFilterDefSquare(1, 1)

    ), 1)) AS FLOAT64)

 

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([st_peters_grayscale], VectorMakeX2([X], [Y]), 1), 1, TileFilterDefSquare(1, 1)

    ), 1)) AS FLOAT64)

 

...into a simpler form:

 

  CASTV (TileFilter([Tile], 1, TileFilterDefSquare(1, 1)) AS FLOAT64)

 

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

 

-- $manifold$

--

-- Auto-generated

-- Transform - Blur - Add Component

--

CREATE TABLE [st_peters_grayscale Tiles Blur] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

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

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

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

  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 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [st_peters_grayscale Tiles Blur] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

    CASTV (TileFilter([Tile], 1, TileFilterDefSquare(1, 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([st_peters_grayscale Tiles Blur Image]);

 

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

 

eg_process_images_3x3_03.png

 

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.

 

ico_nb_arrow_blue.png  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.

 

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, TileFilterDefSquare(1, 1))

 

The query uses:

 

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

 

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, TileFilterDefSquare(1, 1)) , 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.   We will begin by rewriting the query slightly to use a parameter to replace the above in the query text.  We will use a parameter called @filter that we will define with a VALUE statement.   The full query now becomes:

 

-- $manifold$

--

-- Auto-generated

-- Transform - Blur - Add Component

--

 

-- Use built in Blur matrix

 

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

 

CREATE TABLE [st_peters_grayscale Tiles Blur] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

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

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

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

  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 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [st_peters_grayscale Tiles Blur] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(TileFilter(

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

    ), 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([st_peters_grayscale Tiles Blur Image]);

 

We can copy and paste the above into a Command Window, press ! to run it and see what happens.

 

eg_process_images_3x3_04.png

 

The result is exactly what we would expect from a 3x3 matrix Blur, a slight fuzzing of the image.  We have inset into the illustration a portion of the original image at the same scale, showing how the details of the dome of the basilica are sharper in the original.

 

There are no grid lines of missing pixels on the edges of tiles, since the query uses "border" functions to first grab a slightly bigger tile before processing, and then after processing trimming the tile.  All we have done is simply moved the reference to the TileFilterDefSquare function into a VALUE statement at the beginning.  That allows us to conveniently change the filter definition as we like without having to count so many parentheses in a complex, in-line expression.

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 @filter TILE = TileFilterDefSquare(1, 1);

 

With

 

VALUE @filter 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:

 

-- $manifold$

--

 

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

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

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

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

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

  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 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(TileFilter(

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

    ), 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([st_peters_grayscale Tiles Custom Image]);

 

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

 

eg_process_images_3x3_05.png

 

As expected, the result is the same as if we had run the Sharpen transform, 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 panel 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 panel 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 @filter TILE = TileFilterDefSquare(1, 1);

 

We could write:

 

VALUE @filter 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 function) and inserting the new VALUE line.  The query now looks like:

 

-- $manifold$

--

 

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

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

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

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

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

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

  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 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(TileFilter(

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

    ), 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([st_peters_grayscale Tiles Custom Image]);

 

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

 

eg_process_images_3x3_06.png

 

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 @filter 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:

 

-- $manifold$

--

 

-- Use built-in Sharpen filter matrix

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

 

-- Manually define a Blur filter

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

 

-- Emboss

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

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

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

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

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

  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 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(TileFilter(

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

    ), 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([st_peters_grayscale Tiles Custom Image]);

 

ico_nb_arrow_blue.png  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.

 

eg_process_images_3x3_07.png

 

The result is a classic emboss effect.    We can now cruise the Internet finding 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 @filter 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:

 

-- $manifold$

--

 

-- Use built-in Sharpen filter matrix

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

 

-- Manually define a Blur filter

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

 

-- Emboss

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

 

-- Laplacian Edge Detect

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

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

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

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

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

  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 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(TileFilter(

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

    ), 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([st_peters_grayscale Tiles Custom Image]);

 

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.

 

eg_process_images_3x3_08.png

 

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.

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 Soble

VALUE @filter 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:

 

-- $manifold$

--

 

-- Use built-in Sharpen filter matrix

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

 

-- Manually define a Blur filter

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

 

-- Emboss

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

 

-- Laplacian Edge Detect

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

 

-- Vertical Edge Detect Single Soble

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

 

CREATE TABLE [st_peters_grayscale Tiles Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

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

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

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

  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 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [st_peters_grayscale Tiles Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(TileFilter(

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

    ), 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([st_peters_grayscale Tiles Custom Image]);

 

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.

 

eg_process_images_3x3_09.png

 

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.

Cleaning Up the Query

We have come a long way by editing a query originally created by the Edit Query button.    We will make three 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:

 

-- $manifold$

--

 

-- Use built-in Sharpen filter matrix

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

 

-- Manually define a Blur filter

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

 

-- Emboss

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

 

-- Laplacian Edge Detect

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

 

-- Vertical Edge Detect Single Soble

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

 

CREATE TABLE [Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

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

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

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

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [Custom Image] (

  PROPERTY 'Table' '[Custom]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(TileFilter(

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

    ), 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

TABLE CALL TileUpdatePyramids([Custom Image]);

 

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, magenta color highlights the only two places where the name of the input image is specified, and the Rect values for the size of the image.   We can now easily change the query to use a different image by entering the name of the image in two places and changing the Rect numbers to the size of that image.

Simplify setting the projection

The projection is set by specifying the FieldCoordSystem.Tile property of the table that is created.  The original query does that by verbosely citing the entire projection.   We will edit the query to remove the specification of the FieldCoordSystem.Tile property from the CREATE TABLE statement.   

 

At the end of the query, we will add an ALTER TABLE statement to add that property by referring to the coordinate system of the original image.  We do that near the end of the query so that the name of the input image appears nearby to the other two places where it is used.  This will make it easier to change the name of the input image.   The query now reads:

 

-- $manifold$

--

 

-- Use built-in Sharpen filter matrix

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

 

-- Manually define a Blur filter

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

 

-- Emboss

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

 

-- Laplacian Edge Detect

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

 

-- Vertical Edge Detect Single Soble

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

 

CREATE TABLE [Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [Custom Image] (

  PROPERTY 'Table' '[Custom]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(TileFilter(

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

    ), 1)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

ALTER TABLE [Custom] (

  ADD PROPERTY 'FieldCoordSystem.Tile' ComponentCoordSystem([st_peters_grayscale])

);

TABLE CALL TileUpdatePyramids([Custom Image]);

 

The query becomes shorter and more understandable.   The three places where we must change the name of the input image are close together for easier editing.

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:

 

-- $manifold$

--

 

-- Use built-in Sharpen filter matrix

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

 

-- Manually define a Blur filter

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

 

-- Emboss

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

 

-- Laplacian Edge Detect

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

 

-- Vertical Edge Detect Single Soble

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

 

VALUE @radius UINT8 = 1;

 

CREATE TABLE [Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [Custom Image] (

  PROPERTY 'Table' '[Custom]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

 

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

  TileFilter(@t, @r, @f)

) END;

 

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(processtile(

    TileCutBorder([st_peters_grayscale], VectorMakeX2([X], [Y]), @radius), @radius, @filter

    ), @radius)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

ALTER TABLE [Custom] (

  ADD PROPERTY 'FieldCoordSystem.Tile' ComponentCoordSystem([st_peters_grayscale])

);

TABLE CALL TileUpdatePyramids([Custom Image]);

 

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 @filter 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 @filter 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.

 

-- $manifold$

--

 

-- Vertical Edge Detect Single Soble

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

 

-- Unsharp 5x5

VALUE @filter 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;

 

CREATE TABLE [Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [Custom Image] (

  PROPERTY 'Table' '[Custom]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 1214, 862 ]'

);

 

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

  TileFilter(@t, @r, @f)

) END;

 

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(processtile(

    TileCutBorder([st_peters_grayscale], VectorMakeX2([X], [Y]), @radius), @radius, @filter

    ), @radius)) AS FLOAT64)

FROM [st_peters_grayscale]

THREADS SystemCpuCount();

ALTER TABLE [Custom] (

  ADD PROPERTY 'FieldCoordSystem.Tile' ComponentCoordSystem([st_peters_grayscale])

);

TABLE CALL TileUpdatePyramids([Custom Image]);

 

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:

 

eg_process_images_3x3_10.png

Original, above.

 

eg_process_images_3x3_11.png

5x5 Unsharp, above.

 

eg_process_images_3x3_12.png

Default Sharpen template, 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, and change the Rect values to those of the new image.   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.

 

eg_process_images_3x3_ginevra_grayscale.png

 

Adapting our query is a simple matter of using Ctrl-H to search and replace st_peters_grayscale with ginevra grayscale, and also changing the Rect values.  We find the Rect values by right-clicking onto the ginevra grayscale image in the Project pane and choosing Properties.

 

eg_process_images_3x3_13.png

 

We can simply right-click onto the values cell for the Rect property to Copy the value, [ 0, 0, 2593, 2695 ], and then Paste that into our query, taking care that we accidentally do not paste over anything necessary, like the single quote ' characters that surround the bracketed [ 0, 0, 2593, 2695 ] text.   The modified query now becomes:

 

-- $manifold$

--

 

-- Vertical Edge Detect Single Soble

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

 

-- Unsharp 5x5

VALUE @filter 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;

 

CREATE TABLE [Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [Custom Image] (

  PROPERTY 'Table' '[Custom]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 2593, 2695 ]'

);

 

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

  TileFilter(@t, @r, @f)

) END;

 

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(processtile(

    TileCutBorder([ginevra grayscale], VectorMakeX2([X], [Y]), @radius), @radius, @filter

    ), @radius)) AS FLOAT64)

FROM [ginevra grayscale]

THREADS SystemCpuCount();

ALTER TABLE [Custom] (

  ADD PROPERTY 'FieldCoordSystem.Tile' ComponentCoordSystem([ginevra grayscale])

);

TABLE CALL TileUpdatePyramids([Custom Image]);

 

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.

 

eg_process_images_3x3_14.pngeg_process_images_3x3_15.png

 

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.

One More Improvement

Even easier than manually changing the name of the image in three places is to use a parameter at the beginning of the query so the name of the source image need be changed in only one location.  The image is of type TABLE, but the reference must be to the name of the image component so TileCutBorder can do its work.    We use a VALUE statement:

 

VALUE @source_image TABLE = [ginevra grayscale];

 

And then throughout we use @source_image instead of the literal name of the image, [ginevra grayscale].   We can also do the same with Rect, using a VALUE statement at the beginning of the query.   

 

VALUE @source_image_rect  NVARCHAR = '[ 0, 0, 2593, 2695 ]';

 

The Rect values need only be changed in one location, but to have all the values that need to be changed at the beginning of the query reduces the chance we will forget to change something that is buried deeper within the query.   The new, super-duper, improved Unsharp query now reads:

 

-- $manifold$

--

 

VALUE @source_image TABLE = [ginevra grayscale];

VALUE @source_image_rect  NVARCHAR = '[ 0, 0, 2593, 2695 ]';

 

-- Unsharp 5x5

VALUE @filter 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;

 

CREATE TABLE [Custom] (

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

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

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

  PROPERTY 'FieldTileType.Tile' 'float64'

);

CREATE IMAGE [Custom Image] (

  PROPERTY 'Table' '[Custom]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' @source_image_rect

);

 

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

  TileFilter(@t, @r, @f)

) END;

 

PRAGMA ('progress.percentnext' = '100');

INSERT INTO [Custom] (

  [X], [Y],

  [Tile]

) SELECT

  [X], [Y],

  CASTV ((TileRemoveBorder(processtile(

    TileCutBorder(@source_image, VectorMakeX2([X], [Y]), @radius), @radius, @filter

    ), @radius)) AS FLOAT64)

FROM @source_image

THREADS SystemCpuCount();

ALTER TABLE [Custom] (

  ADD PROPERTY 'FieldCoordSystem.Tile' ComponentCoordSystem(@source_image)

);

TABLE CALL TileUpdatePyramids([Custom Image]);

 

Thanks to SQL expert and Manifold user Tim Baigent for suggesting the above!

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]);,

 

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

 

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.

 

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 panel to rearrange the order of channels within the data.