Use IBMDB2SpatialExtender to manage spatial data: Get spatial data and develop

This series of tutorials describes common tasks for using IBMDB2SpatialExtender to manage spatial data, including importing and creating spatial data, constructing and executing spatial queries, and using IBM, third-party, and open-source Spatial tools to tune performance, consider the spatial environment in the data warehouse environment.

This series of tutorials describes common tasks of using IBM DB2 Spatial Extender to manage Spatial data, including importing and creating Spatial data, constructing and executing Spatial queries, and using IBM, third-party, and open-source Spatial tools, adjust performance and consider the spatial environment in the data warehouse environment.

In the first article in this series, we will learn how to get space data and build applications. Learn how to use shapefile, spatial data table, and spatial index.

Understand what knowledge can be learned from the book and how to fully master the knowledge.

About this series

For more than 10 years, DB2 Spatial Extender has provided the ability to manage and analyze the Spatial data stored in DB2 Relational Tables. You can define table columns with spatial types for points, lines, and polygon. They can represent the following objects:

Point Location

Customers, retail stores, transformers, and cellular towers

Line

Expressway, coastline, distribution route, power transmission line

Polygon

Sales/service area, flood/fire risk, national/provincial/County

SQL query can combine spatial functions to analyze spatial relationships, such as finding customers in a flood area. Listing 1 shows the sample code for merging spatial functions.

Listing 1. Merging spatial functions

SELECT cust_name, cust_addrFROM customers, floodzonesWHERE ST_Within(cust_loc, flood_loc) = 1

The results of this query generate the names and addresses of customers whose houses are at risk of flood. This information is useful when calculating the insurance rate or issuing the flood insurance purchase.

This series of tutorials will introduce common tasks for processing Spatial data in DB2 Spatial Extender. This includes importing and creating space data, constructing and executing space queries, using IBM, third-party and open-source space tools, tuning performance, and considering the space environment in the data warehouse environment.

Although this series of tutorials focuses on Linux®, UNIX®And Windows®DB2 Spatial Extender on the operating system, but many concepts also apply to other IBM products with Spatial functions, including:

• Spatial Support for DB2 for z/OS®
• Informix®Spatial Datablade
• Netezza®Spatial

About this tutorial

This tutorial introduces the technology provided by DB2 Spatial Extender. It also provides a method to populate spatial data with DB2 tables: a key step before spatial queries are implemented. This tutorial also introduces the execution space query environment, including the DB2 command line processor, IBM Data Studio, and the Environment embedded into the application using JDBC, CLI, and other interfaces.

Target

After completing this tutorial, you will:

• Understand important spatial concepts related to spatial types and coordinate systems
• Master how to import and create space data
• Understanding spatial indexes
• Familiar with the execution space query environment

DB2 Spatial Extender Overview

Install and set DB2 Spatial Extender to provide the following functions and components:

Spatial Data Type

A set of data types that can be used to define columns that contain spatial data. This includes ST_Point, ST_Linestring, and ST_Polygon for atomic space values. This also includes ST_MultiPoint, ST_MultiLinestring, and ST_MultiPolygon for Space Value Sets of the same type.

Spatial functions and predicates

A large number of SQL udfs are used to create spatial values, return information about spatial values, identify spatial relationships, and perform operations on spatial values. These spatial udfs can be merged into SQL queries, so that all functions of the SQL language can be used.

Spatial Index

Provides a spatial index mechanism to support the two-dimensional nature of spatial data. Some tools can help specify spatial indexes.

Space Command Line processor (CLP)-db2se

Db2se CLP provides a convenient command line interface for the stored procedure to perform operations, such as enabling the space function for the database and importing or exporting space data.

Space reference system and coordinate system

Space Data is usually represented by coordinate value tuples (the most common form is x and y), but it also supports z and m coordinates. This tutorial considers data with x and y coordinate values. It will also consider using only the longitude and latitude (decimal value in degrees) data. Note that when longitude and latitude are used, longitude corresponds to x and latitude corresponds to y.

To perform spatial operations correctly, each spatial value must have an associated coordinate system to describe the relationship between the coordinate value and the location on the Earth's surface. Although Spatial Extender provides more than 3,000 different pre-defined coordinate systems, this tutorial uses only two of the most common ones: Coordinates NAD83 for North America and WGS84 for Global coordinates. Note that most GPS devices use WGS84 to report coordinates.

To effectively store and process Spatial data, Spatial Extender internally expresses coordinates as 64-bit integers. Spatial Extender uses a Spatial reference system (SRS) to manage this data, which specifies the offset and proportional coefficient used for conversion between the user and internal representation of the double-precision value. Each SRS also has an associated coordinate system. In fact, SRS is associated with each space value. SRS can refer to its 128-character Value Name (srsName or SRS_NAME) or its 32-bit integer identifier (srid or SRS_ID ). Srid is used in SQL statements, and srsName is used in db2se CLP, which is most often used when importing spatial data.

Spatial Extender provides srid 1 for the SRS named NAD83_SRS_1, indicating that data from the NAD83 coordinate system is used. Spatial Extender provides srid 1003 for SRS named WGS84_SRS_1003, which indicates data in the WGS84 coordinate system.