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

ST_Within

ST_WithinÀº µÎ °³ÀÇ ±âÇÏÇÐ ¿ÀºêÁ§Æ®¸¦ ÃëÇØ ù¹øÂ° ¿ÀºêÁ§Æ®°¡ ¿Ïº®ÇÏ°Ô µÎ ¹øÂ° ¿ÀºêÁ§Æ®³»¿¡ ÀÖÀ¸¸é 1(TRUE)À» ¸®ÅÏÇÏ°í ±×·¸Áö ¾ÊÀ¸¸é 0(FALSE)À» ¸®ÅÏÇÕ´Ï´Ù.

±¸¹®

db2gse.ST_Within(g1 db2gse.ST_Geometry, g2 db2gse.ST_Geometry)

¸®ÅÏ À¯Çü

Integer

¿¹

¾Æ·¡ÀÇ ¿¹¿¡¼­´Â µÎ °³ÀÇ Å×À̺íÀÌ ÀÛ¼ºµË´Ï´Ù. ù¹øÂ° Å×À̺íÀÎ BUILDINGFOOTPRINTS¿¡´Â µµ½ÃÀÇ ºôµù À§Ä¡°¡ Æ÷ÇԵ˴ϴÙ. µÎ ¹øÂ° Å×À̺íÀÎ LOTS¿¡´Â µµ½Ã ±¸È¹ÀÌ Æ÷ÇԵ˴ϴÙ. µµ½Ã °øÇÐÀÚ´Â ¸ðµç ºôµù À§Ä¡°¡ ÀÌÀÇ ±¸È¹ ¾È¿¡ ¿Ïº®ÇÏ°Ô ÀÖ´ÂÁö È®ÀÎÇÏ·Á ÇÕ´Ï´Ù.

¾ç Å×ÀÌºí¿¡¼­ ´ÙÁß ´Ù°¢Çü µ¥ÀÌÅÍ À¯ÇüÀº ºôµù À§Ä¡¿Í ±¸È¹ÀÇ ±âÇÏÇÐÀ» ÀúÀåÇÕ´Ï´Ù. µ¥ÀÌÅͺ£À̽º ¼³°èÀÚ´Â °­°ú °°Àº ÀÚ¿¬ÀûÀÎ ±â´É¿¡ ÀÇÇØ ±¸È¹ÀÌ °áÇÕ ÇØÁ¦µÉ ¼ö ÀÖ°í ºôµù À§Ä¡°¡ ÀÚÁÖ ¿©·¯ ºôµùµé·Î ±¸¼ºµÉ ¼ö Àֱ⠶§¹®¿¡ µÎ ±â´É¿¡ ´ëÇØ ´ÙÁß ´Ù°¢ÇüÀ» ¼±ÅÃÇß½À´Ï´Ù.

CREATE TABLE BUILDINGFOOTPRINTS (  building_id integer,
                                   lot_id      integer,
                                   footprint   db2gse.ST_MultiPolygon);
 
 
CREATE TABLE LOTS (  lot_id integer, lot db2gse.ST_MultiPolygon ); 

´ÙÀ½ÀÇ SELECT¹®À» »ç¿ëÇÏ¿© µµ½Ã °øÇÐÀÚ´Â ¸ÕÀú ÇÑ ±¸È¹ ³»¿¡ ¿ÏÀüÈ÷ Æ÷ÇÔµÇÁö ¾ÊÀº ºôµùµéÀ» ¼±ÅÃÇÕ´Ï´Ù.

SELECT building_id
  FROM BUILDINGFOOTPRINTS, LOTS
 WHERE db2gse.ST_Within(footprint,lot) <> 1; 

ù¹øÂ° Á¶È¸°¡ lot ´Ù°¢Çü ¿ÜºÎ¿¡ À§Ä¡¸¦ °¡Áø ¸ðµç BUILDING_ID ¸ñ·ÏÀ» Á¦°øÇصµ ³ª¸ÓÁö¿¡ ´ëÇØ ¿Ã¹Ù¸¥ lot_id°¡ ÁöÁ¤µÇ¾î ÀÖ´ÂÁö ÆÇº°ÇÏÁö ¾Ê½À´Ï´Ù. ÀÌ µÎ ¹øÂ° SELECT¹®¿¡¼­´Â BUILDINGFOOTPRINTS Å×À̺íÀÇ LOT_ID Ä÷³¿¡ ´ëÇØ µ¥ÀÌÅÍ ¹«°á¼º Á¡°ËÀ» ¼öÇàÇÕ´Ï´Ù.

SELECT bf.building_id "building id",
       bf.lot_id "buildings lot_id",
       LOTS.lot_id "LOTS lot_id"
  FROM BUILDINGFOOTPRINTS bf, LOTS
 WHERE db2gse.ST_Within(footprint,lot) = 1 AND
       LOTS.lot_id <> bf.lot_id; 


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