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.




Our example project contains a table with tiles and an image that shows those tiles.  The image shows terrain elevation in the Alps in the Northern part of Italy.   A map displays the Alps image along with two web server layers from Google which provide context: a satellite image layer as the background and a transparent labels and streets layer to mark cities and roads, which can be dragged above the terrain elevation layer.




Opening Alps Table we see the tiles which make up the raster data set are 128 pixels by 128 pixels in size, with each pixel containing containing a single channel of  data type int16 data.   The data shows terrain elevations taken from Space Shuttle SRTM data, merged together from several SRTM tiles using the procedure shown in the Example: Merge Images topic.




Right-clicking on the Alps image in the Project pane we can choose Properties to see the properties of the image.   Important information for the SQL we will write is the Rect values, in particular that the image is 14402 x 3602 pixels in size.




With the focus on the Alps image layer of the map, the Component pane shows the Alps image uses a coordinate system of WGS 84 (EPSG:4326).    We will need to assign this coordinate system to a component our query will create, so using the procedure given in the Favorite Coordinate Systems topic we have saved it as a favorite.   That will allow us to assign it with a single click.




We double-click the query to open it in a Command window.  We Shift-click the title tab of the Command Window to undock it, as seen below.




The SQL text of the query follows:


-- create table, use new tile size


CREATE TABLE [alps 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 INT16),

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

  PROPERTY 'FieldTileType.Tile' 'int16'


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


CREATE IMAGE [alps Retiled Image] (

  PROPERTY 'Table' '[alps Retiled]',

  PROPERTY 'FieldTile' 'Tile',

  PROPERTY 'FieldX' 'X',

  PROPERTY 'FieldY' 'Y',

  PROPERTY 'Rect' '[ 0, 0, 14402, 3602 ]'


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


INSERT INTO [alps Retiled] (

  [X], [Y],



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


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

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

THREADS SystemCpuCount();

-- build intermediate levels


EXECUTE CALL TileUpdatePyramids([alps 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.   


To run the query we press the ! button in the main toolbar.   We could also run the query from the Project pane by Right-clicking on the query and choosing ! Run.



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





In the Project pane we see that a new table and image have been created by the query, the alps Retiled table and the new  alps Retiled Image that uses tiles from the table.    In the illustration above, we have turned off the Alps image layer in the map and have dragged and dropped the new alps Retiled Image into the map.


However, the alps Retiled Image does not appear.  




With the focus on the alps Retiled Image layer of the map, the Component pane shows, by showing the coordinate system in red text,  the new image requires us to assign an initial coordinate system.  We press the coordinate picker button to do so.





With a single click we can choose the coordinate system of WGS 84 (EPSG:4326)  that we previously saved as a Favorite.





The alps Retiled Image immediately appears in the correct location.   The Component pane also reports that the image is made up of tiles that are 500 x 500 pixels in size, of data type int16.




We Shift-click the title tab of the map to  undock the map window, so we can resize it to make it larger.  Using the techniques shown in the Example: Merge Images topic we can style the image to show terrain elevations and hill shading.  In the illustration above, we have moved the Google transparent streets layer above the alps Retiled Image layer to provide streets in context above the terrain elevation image.


See Also





Command Window


Data Types


SQL Functions


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.


Example: Merge Images - A step-by-step example using the Merge Images command showing how to merge dozens of images showing SRTM terrain elevation data into one image, with various tricks for faster workflow as an experienced Manifold user would do the job.  After creating the new image we style it with a palette and use hill shading to better show terrain elevation.


SQL Example: Kriging - We use SQL functions to create a raster terrain elevation image from vector contour lines in a drawing, using SQL functions for Kriging interpolation.