|
The DB2 and Informix Spatial/Geodetic features extend relational database technology consistent with traditional SQL processing. Columns of a spatial type such as point, line or polygon can be created in or added to tables. Data values can be inserted directly using SQL INSERT statements or via data import utilities. Once the spatial data is populated in a table, SQL queries incorporating spatial functions and predicates can be executed to answer business questions. Some simple DB2 examples are shown below:
-- Create a table for customer information
CREATE TABLE customers (name VARCHAR(60), location DB2GSE.ST_POINT);
-- Create a table for branch information
CREATE TABLE branches (name VARCHAR(60), region DB2GSE.ST_POLYGON);
-- Insert a row of customer information with the customer location
INSERT INTO customers VALUES ('John Doe', DB2GSE.ST_POINT(-76.5, 42.5, 1));
-- Import a file of branches with polygons defining each branch's territory
db2se import_shape -tableName branches -fileName branches.shp
-- Get a list of customers within each branch's territory
-- with the customer distance from the center of the territory
SELECT
c.name
,b.name
,DB2GSE.ST_DISTANCE(c.location, b.region.ST_CENTROID())
FROM
customers c
branchess b
WHERE DB2GSE.ST_WITHIN(c.location, b.region) = 1
ORDER BY b.name, c.name
The DB2 and Informix Spatial features implement the Open Geospatial Consortium™ (OGC™) specification "Simple Features for SQL with Types and Functions" and the ISO SQL/MM Part 3 Spatial standard. The implementation of standards facilitates the sharing and exploitation of spatial data between third-party and custom applications. It also ensures stability of data and application integrity over time.
| 
