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:

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

6/19/2015