Some applications display imagery, such as pictures of traffic signs, rivers, or buildings, to further illustrate a point, line, or polygon feature. These images may or may not be spatially referenced. You can use SQL to create a table with a nonspatial raster column.
The following workflow describes how to create and populate a raster column and prepare it for access by ArcGIS.
Create and populate a table with a raster column
Connect to the database from a SQL editor.
For example, connect to Oracle from SQL*Plus, to PostgreSQL from pgAdminIII or the psql command prompt, or to Microsoft SQL Server from Management Studio.
- Create a table with an ST_Raster column.
CREATE TABLE real_estate (address nvarchar2(255), sde.st_raster picture);
CREATE TABLE real_estate (address varchar(255), sde.st_raster picture);
CREATE TABLE real_estate (address varchar(255), dbo.st_raster picture);
If your geodatabase in SQL Server is stored in the sde user's schema, preface the function with sde.
- Use the ST_Raster_Util_Initialize method to initialize the raster column.
In these examples, the projectID argument is null because the data is not spatially referenced.
BEGIN SDE.ST_RASTER_UTIL.INITALIZE ('real_estate','picture',NULL,'defaults'); END; /
SELECT st_raster_util_initialize ('real_estate','address',4326,'DEFAULTS');
EXEC dbo.st_raster_util_initialize 'sde','bobby','real_estate','address',4326,NULL,'DEFAULTS'
Once this method executes, the raster column is registered with ArcSDE and available to its functionality.
- Convert a TIFF image using the ST_Raster constructor.
In these examples, a picture of the property at 30551 Independence Ave is entered into the real_estate table by converting the TIFF image file, 30551_independence, with the ST_Raster constructor.
INSERT INTO REAL_ESTATE (address, sde.st_raster) VAULES ('30551 Independence Ave', SDE.ST_RASTER('C:\30551_independence.tif','compression=lz77'));
INSERT INTO real_estate (address, sde.st_raster) VAULES ('30551 Independence Ave', sde.st_raster('C:\30551_independence.tif'));
INSERT INTO real_estate (address, dbo.st_raster) VALUES ('30551 Independence Ave', ST_Raster::construct('C:\30551_independence.tif'));
Registering the table with the geodatabase
Use ArcGIS for Desktop to register the table with the geodatabase.
- Start ArcMap and open the Catalog window or start ArcCatalog.
- Connect to the enterprise geodatabase that contains the table you want to register.
This connection is made under the Database Connections node of the Catalog tree. Be sure you connect as the owner of the table.
- Right-click the real_estate table and click Register with Geodatabase.
An ObjectID field will be added to the real_estate table. The ObjectID field allows ArcGIS to cursor through the rows of the table.