A quick tour of SQL functions used with ST_Raster

You can use the database's Structured Query Language (SQL) to work with ST_Raster data stored in a geodatabase using specific functions installed with the ST_Raster type.

SQL is a standardized language used to select and manipulate data stored in a database management system (DBMS). When you install the ST_Raster type in a geodatabase in Oracle, PostgreSQL, or Microsoft SQL Server, specific SQL functions and types are created in the geodatabase administrator's schema. You can use SQL and these functions and types to query and edit data.

The names of the functions vary depending on the DBMS implementation. In Oracle and SQL Server, the functions are member methods of the ST_Raster or ST_PixelData object. In PostgreSQL, they are SQL functions implemented at the PL/SQL package level. Be aware that you must use the case shown when using the functions with SQL Server. When used with Oracle or PostgreSQL, whether you use all lowercase, all uppercase, or mixed case does not matter.

When using ST_Raster functions in all databases, you must qualify the function with the geodatabase administrator's schema. In most cases, this is sde. However, in SQL Server databases, it may be dbo.

Equivalent functions are shown in the table at the end of this topic. Links in the table take you to specific ST_Raster SQL function reference topics, which are structured as follows:

• Definition

  A description of the function

• Syntax

  The proper syntax to use the function

• Return type

  The type of data that is returned when the function is issued

• Parameters

  A variable value or list of values that can be specified as part of the function argument

• Example

  Samples that use the specific function

ST_Raster functions

Oracle and SQL Server functions	PostgreSQL functions
ST_PixelData	st_pixeldata
ST_PixelData.getValue	getvalue
ST_PixelData.getValueByLoc (SQL Server only)
ST_PixelData.setExtent	setextent
ST_PixelData.setValue	setvalue
ST_Raster	st_raster
ST_Raster.buildPyramid	buildPyramid
ST_Raster.buildStats	buildStats
ST_Raster.crop	crop
ST_Raster.deleteColormap	deletecolormap
ST_Raster.deleteStats	deletestats
ST_Raster.describe	describe
ST_Raster.export	export
ST_Raster.getCompressionType	getcompressiontype
ST_Raster.getInterleaveType	getinterleavetype
ST_Raster.getInterpolationType	getinterpolationtype
ST_Raster.getPixelData	getpixeldata
ST_Raster.getPixelType	getpixeltype
ST_Raster.getPyramidLevel	getpyramidlevel
ST_Raster.getValue	getvalue
ST_Raster.getValueByLoc (SQL Server only)
ST_Raster.getVersion	getversion
ST_Raster.hasColormap	hascolormap
ST_Raster.hasStats	hasstats
ST_Raster.mosaic	mosaic
ST_Raster.setColormap	setcolormap
ST_Raster.subset	subset
ST_Raster_Util.checkLibraryVersion (Oracle only)	st_raster_check_compatibility
ST_Raster_Util.describe	st_raster_util_describe
ST_Raster_Util.getLibraryVersion (Oracle only)	st_raster_getversionlib
ST_Raster_Util.getVersion	st_raster_util_getversion
ST_Raster_Util.initialize	st_raster_util_initialize