SQL Example: Re-tile an Image using a Different Tile Size

Starting with an image that uses a tile size of 128 x 128 pixels this SQL example creates a copy of the image using 500 x 500 pixel tiles.

 

Important: For simplicity, the following examples do not include a THREADS SystemCpuCount() command in the query.  When writing queries manually using the Command Window we should make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in our system.  See the THREADS command for more info.

 

eg_retile_image01_01.png

 

Our example project contains a table with tiles and an image that shows those tiles.  In addition it contains a query.

 

eg_retile_image01_02.png

 

The example image that shows Puerto Rico, a territory of the United States.

 

eg_retile_image01_03.png

 

The Info pane shows the image consists of tiles that are 128 x 128 pixels in size, with each pixel containing data type of uint8x3, a typical data type for an image originally imported from some RGB format.

 

eg_retile_image01_04.png

 

Opening the table we can also see the tile size and data type in the Tile field.

 

eg_retile_image01_05.png

 

The Info pane shows us there are 527 tiles in the table.

 

eg_retile_image01_06.png

 

We double-click the query to open it in a Command window. The SQL text of the query follows:

 

-- create table, use new tile size

--

CREATE TABLE [puerto_rico Retiled] (

  [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 (500, 500) TILETYPE UINT8X3),

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

  PROPERTY 'FieldTileType.Tile' 'uint8x3'

);

-- create image, copy rect from original image, no mention of tile size

--

CREATE IMAGE [puerto_rico Retiled Image] (

  PROPERTY 'Table' '[puerto_rico Retiled]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 3918, 2175 ]'

);

-- add tiles, compute X / Y ranges manually from rect and new tile size

--

INSERT INTO [puerto_rico Retiled] (

  [X], [Y],

  [Tile]

) SELECT

  [XS].[Value], [YS].[Value],

  TileCutRect([puerto_rico],

    VectorMakeX4([XS].[Value] * 500, [YS].[Value] * 500, [XS].[Value] * 500 + 500, [YS].[Value] * 500 + 500))

FROM CALL ValueSequence(0, 7, 1) AS [XS], CALL ValueSequence(0, 4, 1) AS [YS]

THREADS SystemCpuCount();

-- build intermediate levels

--

EXECUTE CALL TileUpdatePyramids([puerto_rico Retiled Image]);

 

The comments make it clear what the different parts of the query do.   There are two places where we insert numbers specific to this image:

 

 

 

No other parts of the query depend upon specific numbers, other than, of course, our specification of a desired tile size for the new image of 500 x 500.   

 

eg_retile_image01_07.png

To run the query we press the ! button.   The Result of 17 is the number of records in the last operation in the query, when intermediate levels are built using the TileUpdatePyramids function.  

 

eg_retile_image01_08.png

In the Project pane we see that a new table and image have been created by the query.

 

eg_retile_image01_09.png

 

If we double-click open the new puerto_rico Retiled Image we see it is exactly the same as the original image.

 

eg_retile_image01_10.png

 

However, the Info Pane reports that the image is now constructed of tiles that are 500 x 500 pixels in size.

 

eg_retile_image01_11.png

 

Likewise, the image's table also shows the use of 500 x 500 pixel tiles.

 

eg_retile_image01_12.png

The Info pane shows there are now only 48 records in the table, since each record uses larger tiles.

 

See Also

Images

 

Tables

 

Command Window

 

Info Pane

 

Data Types

 

Example: How Images use Tiles from Tables - An example showing how an image is made up from data stored in a table in tiles.

 

Example: Create Two Images From One Table - More than one image can show data from the same table, including from the same tile field.

 

Example: An Image using Computed Fields in a Table - How an image can be created from tiles where the data for the tiles is taken from a field that is computed on the fly.