User's Guide and Reference

A sample program fragment

Consider the following example of how an index is created and used in SQL. You can refer to the SQL Reference for more information about the CREATE INDEX and CREATE INDEX EXTENSION commands. Notice that after the index is created, you can then issue standard DDL and DML statements that use the spatial functions and predicates.

create table customers (cid int, addr varchar(40), ..., loc db2gse.ST_Point)
create table stores (sid int, addr varchar(40), ..., loc db2gse.ST_Point,
 zone db2gse.ST_Polygon)
 
create index customersx1 on customers(loc) extend using spatial_index(10e0, 100e0,
 1000e0)
create index storesx1 on stores(loc) extend using spatial_index(10e0, 100e0,
 1000e0)
create index storesx2 on stores(zone) extend using spatial_index(10e0, 100e0,
 1000e0)
 
insert into customers (cid, addr, loc) values (:cid, :addr, sdeFromBinary(:loc))
insert into customers (cid, addr, loc) values (:cid, :addr, geocode(:addr))
insert into stores (sid, addr, loc) values (:sid, :addr, sdeFromBinary(:loc))
 
update stores set zone = db2gse.ST_Buffer (loc, 2)
 
select cid, loc from customers
 where db2gse.ST_Within(loc, :polygon) = 1
 
select cid, loc from customers
 where db2gse.ST_Within(loc, :circle1) = 1 OR
       db2gse.ST_Within(loc, :circle2) = 1
 
select c.cid, loc from customers c, stores s
 where db2gse.ST_Contains(s.zone, c.loc) = 1 selectivity 0.01
 
select avg(c.income) from customers c
 where not exist (select * from stores s
                  where db2gse.ST_Distance(c.loc, s.loc) < 10)