{"id":1440,"date":"2011-06-22T14:09:38","date_gmt":"2011-06-22T14:09:38","guid":{"rendered":"http:\/\/www.gisdeveloper.co.kr\/?p=1440"},"modified":"2017-01-27T20:57:09","modified_gmt":"2017-01-27T11:57:09","slug":"gis-%ec%97%94%ec%a7%84-%ea%b0%9c%eb%b0%9c%ec%8b%9c-%ec%9c%a0%ec%9a%a9%ed%95%9c-postgis%ec%9d%98-sql-%eb%ac%b8","status":"publish","type":"post","link":"http:\/\/www.gisdeveloper.co.kr\/?p=1440","title":{"rendered":"[GIS] \uc720\uc6a9\ud55c PostGIS\uc758 SQL \ubb38"},"content":{"rendered":"<p>geometry \ud544\ub4dc\ub97c \uac00\uc9c4 \ud14c\uc774\ube14\uc774 \uad6c\uc131\ud558\ub294 Row\ub4e4\uc774 \uad6c\uc131\ud558\ub294 \ud558\ub098\uc758 MBR\uc744 \uc5bb\ub294 \ucffc\ub9ac\ubb38\uc740 \uc544\ub798\uc640 \uac19\uc73c\uba70 \uacb0\uacfc\ub294 BOX(MinX MinY, MaxX MaxY) \ud615\ud0dc\uc785\ub2c8\ub2e4.<\/p>\n<pre>select ST_extent(the_geom) from public.\"tst_Table\";<\/pre>\n<p>\ub2e4\uc74c\uc740 \uc9c0\uc815\ub41c \ud14c\uc774\ube14\uc758 \uc2a4\ud0a4\ub9c8\ub97c \uc5bb\ub294 \ucffc\ub9ac\ubb38\uc785\ub2c8\ub2e4. attname\uc740 \ud544\ub4dc\uba85\uc774\uba70 atttypid\ub294 \ud544\ub4dc\ud0c0\uc785\uc5d0 \ub300\ud55c id \ucf54\ub4dc\uc785\ub2c8\ub2e4. \uadf8\ub9ac\uace0 atttypmod\ub294 \ud0c0\uc785\uc774 \uac00\ubcc0 \uae38\uc774 \ubb38\uc790\uc5f4(varchar type)\uc77c\ub54c \ud5c8\uc6a9 \ucd5c\ub300 \uae38\uc774\uc774\uba70 \uc2e4\uc81c\ubcf4\ub2e4 4\ub9cc\ud07c \ub354 \ud06c\uba70 \ud0c0\uc785\uc774 \ubb38\uc790\uc5f4\uc774 \uc544\ub2c8\uba74 -1\uc785\ub2c8\ub2e4.<\/p>\n<pre>SELECT\r\n    attname, atttypid, atttypmod\r\nFROM \r\n    pg_attribute, pg_type\r\nWHERE \r\n    typname = 'tst_Table' AND \r\n    attrelid = typrelid AND \r\n    attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax');<\/pre>\n<p>atttypid\uc5d0 \ub300\ud55c \ucf54\ub4dc\uac12\uc5d0 \ud574\ub2f9\ud558\ub294 \uc758\ubbf8\ub294 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n<ul>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=16 : &#8216;boolean&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=17 : &#8216;bytea&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=18 : &#8216;char&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=19 : &#8216;name&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=20 : &#8216;int8&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=21 : &#8216;int2&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=22 : &#8216;int2vector&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=23 : &#8216;int4&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=24 : &#8216;regproc&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=25 : &#8216;text&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=26 : &#8216;oid&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=27 : &#8216;tid&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=28 : &#8216;xid&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=29 : &#8216;cid&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=30 : &#8216;oidvector&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=210 : &#8216;smgr&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=700 : &#8216;float4&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=701 : &#8216;float8&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=702 : &#8216;abstime&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=705 : &#8216;unknown&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1007 : &#8216;_int4&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1033 : &#8216;aclitem&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1034 : &#8216;_aclitem&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1042 : &#8216;bpchar&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1043 : &#8216;varchar&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1082 : &#8216;date&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1083 : &#8216;time&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1184 : &#8216;timestamp&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1266 : &#8216;timetz&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=1700 : &#8216;numeric&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=2500 : &#8216;int1&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=2569 : &#8216;nucl&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=2570 : &#8216;prot&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=2522 : &#8216;nchar&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=2530 : &#8216;nvarchar&#8217;<\/font><\/li>\n<li><font face=\"'Arial','helvetica','sans-serif'\" size=\"1\">atttypid=278 : &#8216;ntext&#8217;<\/font><\/li>\n<\/ul>\n<p>\ub2e4\uc74c\uc740 geometry\ub97c \uac00\uc9c0\ub294 \ud14c\uc774\ube14\uc758 \uc9c0\uc624\uba54\ud2b8\ub9ac \ud0c0\uc785\uc744 \uc5bb\ub294 \ucffc\ub9ac\ubb38\uc778\ub370 \ud558\ub098\uc758 \ud14c\uc774\ube14\uc5d0 \ub3d9\uc77c\ud55c \uc9c0\uc624\uba54\ud2b8\ub9ac \ud0c0\uc785\uc744 \uac00\uc9c0\ub294 \ub3c4\ud615\ub9cc \uc800\uc7a5\ud558\uace0 \uc788\ub2e4\ub294 \uac00\uc815\uc774 \ud544\uc694\ud569\ub2c8\ub2e4.<\/p>\n<pre>select GeometryType(the_geom) from public.\"tst_Table\" limit 1;<\/pre>\n<p>\uadf8\ub9ac\uace0 \uc5b4\ub5a4 MBR\uc5d0 \uac78\uce58\ub294 \ub3c4\ud615\uc744 \uacf5\uac04\uac80\uc0c9\ud558\ub294 \ucffc\ub9ac\ubb38\uc740 \ub2e4\uc74c\uacfc \uac19\uc2b5\ub2c8\ub2e4.<\/p>\n<pre>SELECT\r\n    the_geom\r\nFROM  \r\n    public.\"tst_Table\"\r\nWHERE \r\n    ST_Intersects \r\n    (\r\n        the_geom, \r\n        ST_MakeEnvelope(456315, 382558, 460432, 386381, -1)\r\n    );<\/pre>\n<p>\uc704\uc758 \uacf5\uac04\uac80\uc0c9 \ucffc\ub9ac\ub294 PostGIS\uc758 JDBC API\ub97c \uc0ac\uc6a9\ud558\uba74 \uc27d\uac8c \uc9c0\uc624\uba54\ud2b8\ub9ac\uc758 \ud0c0\uc785\uacfc \uc88c\ud45c\uac12 \ub4f1\uc744 \uc5bb\uc744 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ubb3c\ub860 \uc790\ubc14 \uc5b8\uc5b4\ubfd0 \uc544\ub2c8\ub77c C\uc5b8\uc5b4\uc5d0 \ub300\ud55c API\ub3c4 \uc81c\uacf5\ud569\ub2c8\ub2e4. <\/p>\n<p>\uc704\uc758 SQL\ubb38\uc740 MBR\uc744 \uae30\uc900\uc73c\ub85c MBR\uacfc \uc911\ucca9\ub418\ub294 \uac83\uc744 \uac80\uc0c9\ud558\ub294 \uc608\uc774\uba70 \uc544\ub798\ub294 \uae30\uc900\uc744 \uc77c\ubc18\uc801\uc778 \uc9c0\uc624\uba54\ud2b8\ub9ac\ub97c WKT \ud615\uc2dd\uc73c\ub85c \ubc1b\uc544 \uc911\ucca9\ub418\ub294 \uac83\uc744 \uac80\uc0c9\ud558\ub294 \uc608\uc785\ub2c8\ub2e4.<\/p>\n<pre>SELECT \r\n    *, ST_BOX2D(the_geom) \r\nFROM \r\n    public.\"tst_Table\"\r\nWHERE \r\n    ST_Intersects\r\n    (\r\n        the_geom, \r\n        'LINESTRING (244049 543725, 244134 543762)'::geometry\r\n    );<\/pre>\n<p>\ub367\ubd99\uc5ec \uac80\uc0c9 \ud544\ub4dc\ub85c\uc368 ST_BOX2D\ub97c \uc0ac\uc6a9\ud574 \uac80\uc0c9\ub41c \ud56d\ubaa9\uc758 MBR\uae4c\uc9c0 \uc5bb\uc5b4\uc624\ub3c4\ub85d \ud558\uc600\uc2b5\ub2c8\ub2e4.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>geometry \ud544\ub4dc\ub97c \uac00\uc9c4 \ud14c\uc774\ube14\uc774 \uad6c\uc131\ud558\ub294 Row\ub4e4\uc774 \uad6c\uc131\ud558\ub294 \ud558\ub098\uc758 MBR\uc744 \uc5bb\ub294 \ucffc\ub9ac\ubb38\uc740 \uc544\ub798\uc640 \uac19\uc73c\uba70 \uacb0\uacfc\ub294 BOX(MinX MinY, MaxX MaxY) \ud615\ud0dc\uc785\ub2c8\ub2e4. select ST_extent(the_geom) from public.&#8221;tst_Table&#8221;; \ub2e4\uc74c\uc740 \uc9c0\uc815\ub41c \ud14c\uc774\ube14\uc758 \uc2a4\ud0a4\ub9c8\ub97c \uc5bb\ub294 \ucffc\ub9ac\ubb38\uc785\ub2c8\ub2e4. attname\uc740 \ud544\ub4dc\uba85\uc774\uba70 atttypid\ub294 \ud544\ub4dc\ud0c0\uc785\uc5d0 \ub300\ud55c id \ucf54\ub4dc\uc785\ub2c8\ub2e4. \uadf8\ub9ac\uace0 atttypmod\ub294 \ud0c0\uc785\uc774 \uac00\ubcc0 \uae38\uc774 \ubb38\uc790\uc5f4(varchar type)\uc77c\ub54c \ud5c8\uc6a9 \ucd5c\ub300 \uae38\uc774\uc774\uba70 \uc2e4\uc81c\ubcf4\ub2e4 4\ub9cc\ud07c \ub354 \ud06c\uba70 \ud0c0\uc785\uc774 \ubb38\uc790\uc5f4\uc774 \uc544\ub2c8\uba74 -1\uc785\ub2c8\ub2e4. SELECT &hellip; <\/p>\n<p class=\"link-more\"><a href=\"http:\/\/www.gisdeveloper.co.kr\/?p=1440\" class=\"more-link\">\ub354 \ubcf4\uae30<span class=\"screen-reader-text\"> &#8220;[GIS] \uc720\uc6a9\ud55c PostGIS\uc758 SQL \ubb38&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[5],"class_list":["post-1440","post","type-post","status-publish","format-standard","hentry","category-gis-working-history","tag-gis"],"_links":{"self":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/1440","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1440"}],"version-history":[{"count":1,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/1440\/revisions"}],"predecessor-version":[{"id":3179,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=\/wp\/v2\/posts\/1440\/revisions\/3179"}],"wp:attachment":[{"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1440"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1440"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gisdeveloper.co.kr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1440"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}