Using Spatial Data

Following up on my post about on spatial data support in DB2 for z/OS, this week we'll cover how to create a table and index containing spatial data. While you read along, you might want to view this visual, a sample application delivered with ArcGIS by ESRI.

Once IBM Spatial Support for DB2 for z/OS is installed, these objects are defined in DB2:

• Stored procedures: When you request a spatial operation (e.g., importing spatial data), Spatial Support invokes the appropriate stored procedure to perform the operation.
• Spatial data types: A spatial data type must be assigned each table or view column that is to contain spatial data.
• Spatial catalog tables and catalog views.
• A spatial grid index, so that you can define grid indexes on spatial columns.
• Spatial functions: These work with spatial data in numerous ways (e.g., determining relationships between geometries, generating additional spatial data).
• Definitions of coordinate systems.
• Default spatial reference systems.
• Two schemas: DB2GSE and SYSPROC.

As a DBA, I can imagine I'd want to include spatial data in table columns or create indexes to ensure that queries are running efficiently. To learn about these and other uses of spatial data, see the IBM Spatial Support for DB2 Version 9.1 for z/OS User’s Guide and Reference.

(Notes: The DB2 LOAD utility will fail if a spatial grid index is created on the target table. Before running this utility, you must drop the spatial grid index by using the ST_drop_index stored procedure. The spatial grid index can be recreated after running the LOAD utility. Some utility support -- Reorg Tablespace , Reorg Index, Rebuild Index and Check Index -- was introduced with DB2 10. Details are here. Utility support for DB2 9 is enabled through APAR PM35200.)

A sample install verification program (IVP) job can be found in the install sample library SDSNSAMP(DSN5SCMD). This job contains statements to create tables and call sample programs that use the spatial stored procedures to create/drop indexes as well as enable/disable spatial support.

Once Spatial Support is installed, these data types can be used:

 

ST_Point

ST_LineString

ST_Polygon

ST_MultiPoint

ST_MultiLineString

ST_MultiPolygon

ST_Geometry

 

In this example, a ADDRESS table is created that includes a LOCATION column of type DB2GSE.ST_POINT:

           CREATE TABLE ADDRESS ( ..., LOCATION DB2GSE.ST_POINT, ...) ;

 

To help improve performance, a “spatial grid index” can be created on this column using the SYSPROC.ST_create_index stored procedure. As I noted in the previous post, standard DDL CANNOT be used create an index on spatial data.

This sample calls the ST_create_index stored procedure to create the LOC_IX index on the LOCATION column in the ADDRESS_TABLE table:

CALL SYSPROC.ST_CREATE_INDEX( NULL, ADDRESS_TABLE, LOCATION , NULL, LOC_IX,NULL, 10.0, 20.0, 35.0, ?, ?)

The DB2 optimizer considers a spatial index for use when a query contains one of these functions in its WHERE clause:

• ST_Contains
• ST_Crosses
• ST_Distance
• EnvelopesIntersect
• ST_Equals
• ST_Intersects
• ST_Overlaps
• ST_Touches
• ST_Within

 

Sample SQL -- List the address found in the Chicago Loop:

SELECT address_line1, City, State, Zip

FROM address_table AS a

WHERE ST_Within(a.location,:Chicago_Loop_Area) = 1

As I mentioned in the previous post, there's little available information on managing spatial data in DB2. However, this IBM developerWorks piece is very informative. It also includes links to additional resources.