»ç¿ëÀÚ ¾È³» ¹× ÂüÁ¶¼­


»ùÇà ÇÁ·Î±×·¥ Á¶°¢

»öÀÎÀÌ ÀÛ¼ºµÇ´Â ¹æ¹ý°ú SQL¿¡ ¾î¶»°Ô »ç¿ëµÇ´ÂÁö¿¡ ´ëÇÑ ´ÙÀ½ÀÇ ¿¹¸¦ °í·ÁÇØ º¾½Ã´Ù. SQL ÂüÁ¶¼­¿¡¼­ CREATE INDEX ¹× CREATE INDEX EXTENSION ¸í·É¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. »öÀÎÀÌ ÀÛ¼ºµÈ ÈÄ¿¡´Â °ø°£ ÇÔ¼ö¿Í °ü·Ã ¼ú¾î¸¦ »ç¿ëÇϴ ǥÁØ DDL ¹× DML ¸í·É¹®À» ¹ßÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

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 db2gse.spatial_index(10e0,
 100e0, 1000e0)
create index storesx1 on stores(loc) extend using db2gse.spatial_index(10e0, 100e0,
 1000e0)
create index storesx2 on stores(zone) extend using db2gse.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)
 


[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]