Help

Workflow: Creating a raster catalog with SQL

Complexity:
Advanced
Data Requirement:
Use your own data
Goal:
Learn how to create a raster table, initialize the ST_Raster column in the table, add records to the table, build pyramids and statistics on the ST_Raster, then register the table with the geodatabase to create a raster catalog.

The raster catalog is an ArcObjects component that stores spatially referenced raster datasets. It is possible to create and populate a raster catalog from within SQL using the ST_Raster type by following the workflow provided in this topic.

Each of the following sections contains examples to complete the task of creating a raster catalog in each database management system. You can modify the examples to match your data.

Creating a table with an ST_Raster column

  1. Open an SQL editor, such as SQL*Plus for Oracle, pgsql or pgAdminIII for PostgreSQL, or the Query Editor window in Microsoft Management Studio for SQL Server.
  2. Use a CREATE TABLE statement to create a table with an ST_Raster column and a string column.

    In these examples, a table, urban_area, is created. The table contains two columns: name and raster.

    Oracle

    CREATE TABLE urban_area 
(name varchar2(255), sde.st_raster raster);

    PostgreSQL

    CREATE TABLE urban_area 
(name varchar(255), sde.st_raster raster);

    SQL Server

    CREATE TABLE urban_area 
(name varchar(255), dbo.st_raster raster);

Initializing the ST_Raster column to store raster values

Use the ST_Raster_Util initialize function to initialize an ST_Raster column. This function adds a reference for the raster column to the ArcSDE raster metadata tables and creates the raster blocks table and raster auxiliary table, as well as the raster column's insert, update, and delete triggers. Once this function executes, the raster column is registered with ArcSDE and available to its functionality. It adds a record to the table_registry, column_registry, and raster_columns system tables. However, it cannot be accessed as an ArcGIS raster catalog object until it has been registered with the geodatabase.

  1. Use the ST_Raster initialize utility to initialize the ST_Raster column in the urban_area table.

    Oracle

    BEGIN SDE.ST_RASTER_UTIL.INITIALIZE 
('urban_area','raster',4326,'defaults'); END; /

    PostgreSQL

    SELECT st_raster_util_initialize 
('urban_area','raster',4326,'DEFAULTS');

    SQL Server

    EXEC ST_Raster_Util.Initialize 
'sde','bobby','urban_area','raster',4326,NULL,'DEFAULTS'

Inserting a raster value into the ST_Raster column

An INSERT statement adds a row to the table.

In these examples, the name column is populated with the character string 'MILWAUKEE', and the ST_Raster constructor converts a GeoTIFF file to the ST_Raster type.

  1. Use an SQL INSERT statement to insert an image into an ST_Raster column. Be sure to use your own TIFF file in place of milwaukee.tif.

    Oracle

    INSERT INTO URBAN_AREA 
('MILWAUKEE',SDE.ST_RASTER('C:\milwaukee.tif'));

COMMIT;

    PostgreSQL

    INSERT INTO urban_area 
('milwaukee',sde.st_raster('C:\milwaukee.tif'));

    SQL Server

    INSERT INTO urban_area 
('milwaukee',dbo.ST_Raster::construct('C:\milwaukee.tif'));

    2. More INSERT statements could be applied at this point, or you can apply the ST_Raster mosaic function to the existing raster column values with an UPDATE statement. These statements repeat until all the data is loaded into the ST_Raster column.
  2. Use an SQL UPDATE statement to mosaic to the existing raster column.

    Oracle

    UPDATE URBAN_AREA T
SET RASTER = T.RASTER.MOSAIC('C:\milwaukee2.tif')
WHERE NAME = 'MILWAUKEE';

COMMIT;

    PostgreSQL

    UPDATE urban_area 
SET raster = mosaic(RASTER,'C:\milwaukee2.tif') 
WHERE name = 'milwaukee';

    SQL Server

    UPDATE urban_area 
SET raster = raster.mosaic('C:\milwaukee2.tif') 
WHERE name = 'milwaukee';

Building pyramids

After loading the source data, the pyramids are constructed with the ST_Raster buildPyramid function. Either build the pyramids for all the raster values or use a WHERE clause to qualify the raster values for which you want to build pyramids.

All the pyramids are built with the bilinear pyramid interpolation.

  1. Use the buildPyramid function.

    In these examples, a WHERE clause is specified to qualify which raster values must have pyramids built with nearest neighbor interpolation or bilinear.

    Oracle

    UPDATE URBAN_AREA T
SET RASTER =
T.RASTER.BUILDPYRAMID('nearest,level=-1')
WHERE NAME = 'MILWAUKEE';

 COMMIT;

UPDATE URBAN_AREA T
SET RASTER =
T.RASTER.BUILDPYRAMID('bilinear,level=-1')
WHERE NAME = 'TOKYO';

COMMIT;

    PostgreSQL

    UPDATE urban_area
SET raster = buildpyramid(raster,'nearest,level=-1')
WHERE name = 'milwaukee';

UPDATE urban_area
SET raster = buildpyramid(raster,'bilinear,level=-1')
WHERE name = 'tokyo';

    SQL Server

    UPDATE urban_area
SET raster = raster.buildPyramid('bilinear,level=-1')
WHERE name = 'milwaukee';

UPDATE urban_area
SET raster = raster.buildPyramid('nearest,level=-1')
WHERE name = 'tokyo';

Building statistics on the raster

Some raster data needs to be stretched by the display renderer to make it discernible. Otherwise, it appears as a black, gray, or white color. This type of data needs to have raster statistics built. This is done with the ST_Raster buildStats function in an UPDATE statement.

Use a WHERE clause if it is necessary to qualify which rows of the table need statistics built.

  1. Use the buildStats function in an SQL UPDATE statement.

    Oracle

    UPDATE URBAN_AREA T
SET RASTER = T.RASTER.BUILDSTATS() 
WHERE NAME = 'MILWAUKEE';

COMMIT;

    PostgreSQL

    UPDATE urban_area 
SET raster = buildstats(raster) 
WHERE name = 'milwaukee';

    SQL Server

    UPDATE urban_area 
SET raster = raster.buildStats(NULL) 
WHERE name = 'milwaukee';

By default, statistics are calculated on the base level of the raster value, which provides the most accurate statistical result. However, it is possible to forgo accuracy in the interest of saving time by calculating statistics on a specified pyramid level. The higher the pyramid level used, the faster the statistics will be calculated, but at the expense of a decrease in statistical accuracy. Of course, the specified pyramid level will need to have been built before you calculate statistics on it.

  1. Use the buildStats function and provide a level on which to build statistics.

    In these examples, the statistics are calculated on the sixth pyramid level.

    Oracle

    UPDATE URBAN_AREA T 
SET RASTER = T.RASTER.BUILDSTATS(6) 
WHERE NAME = 'MILWAUKEE';

    PostgreSQL

    UPDATE urban_area 
SET raster = buildstats(raster,6) 
WHERE name = 'milwaukee';

    SQL Server

    UPDATE urban_area 
SET raster = buildStats.raster(6) 
WHERE name = 'milwaukee';

Registering the table with the geodatabase

Any table that contains a single, spatially referenced raster column can be converted to an ArcObjects raster catalog. The ST_Raster_Util initialize function registers the raster column with ArcSDE, and once it has been executed, a table can be converted to a raster catalog by registering it with the geodatabase. Since the raster column in this workflow example has been initialized, it can be registered with the geodatabase.

Use ArcGIS for Desktop to register the table with the geodatabase, thereby creating a raster catalog.

  1. Start ArcMap and open the Catalog window or start ArcCatalog.
  2. Connect to the geodatabase that contains the urban_area table.

    Geodatabase connections are made under the Database Connections node in the Catalog tree. Be sure to log in to the geodatabase as the owner of the urban_area table.

  3. Right-click the urban_area table and click Register with Geodatabase.
  4. Click Yes on the dialog box warning that appears telling you that an ObjectID column will be added to the table.

    The geodatabase requires the presence of this unique, not null, ObjectID column.

The table icon changes to a raster catalog icon, indicating that the table has been converted to a raster catalog.

Updating database statistics

To obtain optimum performance, ArcSDE relies on the DBMS cost-based optimizer to select the appropriate execution plan whenever updates or queries are made. The cost-based optimizer in turn relies on up-to-date DBMS statistics to select the best execution plan.

The easiest way to update DBMS statistics on data in a geodatabase is through ArcGIS. Use the Analyze Datasets geoprocessing tool, opened from the Analyze command in ArcGIS for Desktop to update statistics.

  1. Start ArcCatalog or ArcMap and open the Catalog window.
  2. Connect to the database containing the urban_area raster catalog.

    Be sure to connect as the owner of the raster catalog.

  3. Right-click the urban_area raster catalog.
  4. Point to Manage and click Analyze.
  5. Make sure the urban_area raster catalog is checked in the Datasets to Analyze list, then click outside the list.
  6. Check Analyze Base Tables to update the statistics on the raster base table.
  7. Click OK.

Related topics