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, the query in this SQL example creates a copy of the image using 500 x 500 pixel tiles.

 

 This topic uses SQL to change the tile size of an image, to provide an SQL example.   Most people would change the tile size of an image using the point and click Reproject Component dialog.

 

To fit into this documentation, illustrations show an artificially small Manifold desktop, with only a few panes, docked to the right side.   In real life we use a much larger Manifold desktop, and all panes would be turned on, with some panes docked to the left and others docked to the right.

 

 

 

 

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.

 

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.

 

 

 

 

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.  

 

In the illustration above we have right-clicked the column header for the Tile field and chosen Style, to change the format for the Tile field from the default format to a format that reports the size of the tile as well as the data type of each channel and the number of channels.

 

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 Info pane shows the Alps image uses a coordinate system of WGS 84 (EPSG:4326).    The insane numbers in the Pixel size readout are the sizes of pixels in decimal degrees, the unit of measure used in the WGS 84 (EPSG:4326) coordinate system, which is simply a different name for a Latitude / Longitude coordinate system.    SRTM data is provided in Latitude / Longitude, so a good first step to enable saner units of measure is to do a quick reprojection of SRTM data into some meter-based coordinate system, like Pseudo-Mercator.

 

Be that as it may, this example uses SRTM data in the original WGS 84 (EPSG:4326) so that is what we will use.  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, using the override metrics option.   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],

  [Tile]

) SELECT

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

  TileCutRect([alps],

    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

--

TABLE 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 Info 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 # symbol indicates this particular favorite when applied will override metrics, as is often necessary for images.

 

 

 

 

The alps Retiled Image immediately appears in the correct location.   The Info  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.

Notes

Tile size vs. pixel size - In this example we changed the number of pixels per tiles.  We have the same total number of pixels in the image, but those are now grouped in a smaller number of larger tiles.  The total size of the image in pixels has not changed.  A related notion is to change the size of each pixel, so that instead of each pixel covering, say, 90 meters vertically and horizontally, an individual pixel would cover 180 meters.  That would reduce the resolution of the image, with one pixel covering the region formerly covered by four pixels, and it would reduce the overall size of the image by a factor of four.  

 

There are two ways in Manifold to resize an image by changing the size of pixels, a process also known as resampling an image:

 

 

To resample an image to change the size of pixels, we would normally use the Reproject Component dialog.

 

See Also

Images

 

Tables

 

Queries

 

Command Window

 

Data Types

 

Reproject Component

 

Sub-pixel Reprojection

 

Map Projection

 

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: Change the Pixel Size of a Terrain Elevation Image - Use the Reproject Component dialog to change the pixel size of a terrain elevation image, reducing the total number of pixels used.  This process is also called resampling.

 

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.

 

Example: Resize an Image using Merge - We can change the size of an image while maintaining georegistration by using the Merge Images command.  This example shows how to take an image that is 3,038 x 4,334 pixels in size, using approximately 36 meter pixels, and to create a re-sampled image that is 1,115 x 1,590 pixels in size, using 100 meter pixels.  

 

Example: Rescale a Raster Image - See how to rescale pixel values from one raster image, using a given range of values such as 8 bit unsigned integers, into a new raster image using a different range of values such as 16 bit unsigned integers, so that the relative distribution of pixel values, that is, the histogram, does not change.

 

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.