geof_07unioninter.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6ce2445e-7680-4705-8d62-8f32bd84908c",
   "metadata": {},
   "source": [
    "## 位置情報とパブリックデータ#07: <br>(地理空間関数)ポリゴンの併合と交差\n",
    "###### 利用データはgeof_03geometry.ipynbと同じものを利用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12bdb898-e637-4cd4-b716-0f25911aa5ec",
   "metadata": {},
   "source": [
    "#### データベース接続"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c5a0fd60-89e5-41b5-930f-97b3cd700096",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password: ········\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Success: 'jumbo' connection established and activated for user 'jumbo', with default database 'jumbo'\n"
     ]
    }
   ],
   "source": [
    "%connect jumbo"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4093b63b-1e22-4900-935e-933077002933",
   "metadata": {},
   "source": [
    "#### 複数のポリゴンを併合する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "73f571af-e05c-453c-96c9-f32893ad9e99",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"lonlat\",\"p\"\n\"POLYGON ((134.0 34.5,135.0 34.5,135.0 32.5,133.0 32.5,133.0 33.5,132.0 33.5,132.0 35.5,134.0 35.5,134.0 34.5))\",\"1\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory493127\"><style>\n",
       "      table.tdhistory493127 { display: block !important; min-height: 105px !important; overflow: auto !important; height: 105px !important; width: 100% !important; border: 1px solid rgb(207, 207, 207) !important; border-collapse: collapse !important; ; color: var(--jp-ui-font-color1) !important; }\n",
       "      th.tdhistory493127 { border: 1px solid rgb(198,198,198) !important; border-collapse: collapse !important; ; padding: 2px 5px !important; ; font-size: 13px !important; ; text-align: center !important; white-space: normal !important; color: var(--jp-ui-font-color1) !important; }\n",
       "      th.tdhistory493127:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory493127 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory493127 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory493127 { border: 1px solid rgb(207, 207, 207) !important; border-collapse: collapse !important; ; padding: 2px 5px !important; ; font-size: 13px !important; ; text-align: left !important;          white-space: nowrap !important; overflow:hidden !important; text-overflow:ellipsis !important;; }\n",
       "    </style><thead><tr style= \"background: rgba(243, 243, 243, 0.75)\"><th class=\"tdhistory493127\"></th><th class=\"tdhistory493127\">lonlat</th><th class=\"tdhistory493127\">p</th></tr></thead><tbody class=\"tdhistory493127 resultset-body\"><tr><th class=\"tdhistory493127\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory493127\">POLYGON ((134.0 34.5,135.0 34.5,135.0 32.5,133.0 32.5,133.0 33.5,132.0 33.5,132.0 35.5,134.0 35.5,134.0 34.5))</td><td class=\"tdhistory493127\">1</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 2,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.40.14.146_JST\",\"historyID\":76,\"chunkID\":\"00001\",\"sessionid\":\"\",\"portnum\":\"\",\"dockermode\":\"standalone\",\"totalRowCount\":1,\"chunkCount\":0,\"rowLimit\":0,\"columnMetadata\":[{\"columnName\":\"lonlat\",\"columnTypeName\":\"VARCHAR\",\"length\":1000,\"scale\":1000},{\"columnNumber\":1,\"columnName\":\"p\",\"columnTypeName\":\"INTEGER\"}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select \n",
    "cast((lonlat) as varchar(1000)) as lonlat, \n",
    "p \n",
    "from agggeom ( \n",
    "\ton ( \n",
    "\t\t /*入力データ、partition句が必須のため、ダミーをセット*/ \n",
    "\t\t /*何かpartition句がある場合はそれをセットするとpartition句単位で処理をしてくれる*/ \n",
    "\t\t /*最初の列がst_geometryである必要がある*/ \n",
    "\t\tselect \n",
    "\t\tlonlat, \n",
    "\t\tcast(1 as integer) as p \n",
    "\t\tfrom jumbo.geoshape \n",
    "\t\twhere 番号 in (5,6) \n",
    "\t) \n",
    "\tpartition by p \n",
    "\tusing \n",
    "\toperation('Union') \n",
    ") a1 \n",
    "; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c556a99-9ef1-4425-b18d-9b44a627d2b8",
   "metadata": {},
   "source": [
    "#### 複数のポリゴンの交差結合部分をポリゴンとして取得する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ece10cf0-6ec9-482d-b13d-119c008b7e60",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"lonlat\",\"p\"\n\"POLYGON ((133.0 34.5,134.0 34.5,134.0 33.5,133.0 33.5,133.0 34.5))\",\"1\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory438689\"><style>\n",
       "      table.tdhistory438689 { display: block !important; min-height: 105px !important; overflow: auto !important; height: 105px !important; width: 100% !important; border: 1px solid rgb(207, 207, 207) !important; border-collapse: collapse !important; ; color: var(--jp-ui-font-color1) !important; }\n",
       "      th.tdhistory438689 { border: 1px solid rgb(198,198,198) !important; border-collapse: collapse !important; ; padding: 2px 5px !important; ; font-size: 13px !important; ; text-align: center !important; white-space: normal !important; color: var(--jp-ui-font-color1) !important; }\n",
       "      th.tdhistory438689:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory438689 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory438689 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory438689 { border: 1px solid rgb(207, 207, 207) !important; border-collapse: collapse !important; ; padding: 2px 5px !important; ; font-size: 13px !important; ; text-align: left !important;          white-space: nowrap !important; overflow:hidden !important; text-overflow:ellipsis !important;; }\n",
       "    </style><thead><tr style= \"background: rgba(243, 243, 243, 0.75)\"><th class=\"tdhistory438689\"></th><th class=\"tdhistory438689\">lonlat</th><th class=\"tdhistory438689\">p</th></tr></thead><tbody class=\"tdhistory438689 resultset-body\"><tr><th class=\"tdhistory438689\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory438689\">POLYGON ((133.0 34.5,134.0 34.5,134.0 33.5,133.0 33.5,133.0 34.5))</td><td class=\"tdhistory438689\">1</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.41.05.279_JST\",\"historyID\":77,\"chunkID\":\"00001\",\"sessionid\":\"\",\"portnum\":\"\",\"dockermode\":\"standalone\",\"totalRowCount\":1,\"chunkCount\":0,\"rowLimit\":0,\"columnMetadata\":[{\"columnName\":\"lonlat\",\"columnTypeName\":\"VARCHAR\",\"length\":1000,\"scale\":1000},{\"columnNumber\":1,\"columnName\":\"p\",\"columnTypeName\":\"INTEGER\"}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select \n",
    "cast((lonlat) as varchar(1000)) as lonlat, \n",
    "p \n",
    "from agggeom ( \n",
    "\ton ( \n",
    "\t\t /*入力データ、partition句が必須のため、ダミーをセット*/ \n",
    "\t\t /*何かpartition句がある場合はそれをセットするとpartition句単位で処理をしてくれる*/ \n",
    "\t\t /*最初の列がst_geometryである必要がある*/ \n",
    "\t\tselect \n",
    "\t\tlonlat, \n",
    "\t\tcast(1 as integer) as p \n",
    "\t\tfrom jumbo.geoshape \n",
    "\t\twhere 番号 in (5,6) \n",
    "\t) \n",
    "\tpartition by p \n",
    "\tusing \n",
    "\toperation('Intersection') \n",
    ") a1 \n",
    "; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "751e4703-2658-42be-a2d3-9b369ab4e09d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Teradata SQL",
   "language": "Teradata SQL",
   "name": "teradatasql"
  },
  "language_info": {
   "codemirror_mode": "Teradata SQL",
   "file_extension": ".tdrs",
   "mimetype": "application/vnd.teradata.resultset",
   "name": "Teradata SQL",
   "nbconvert_exporter": "",
   "pygments_lexer": "",
   "version": "16.20"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}