geof_05_1bufferfuncs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b7b01cea-c1bc-438e-80c5-9a30f8aff569",
   "metadata": {},
   "source": [
    "## 位置情報とパブリックデータ#05: <br>(地理空間関数)バッファ範囲の特定\n",
    "###### 利用データはgeof_03geometry.ipynbと同じものを利用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "575f3231-daa6-419b-afd5-8d889dc62c21",
   "metadata": {},
   "source": [
    "#### データベース接続"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1ea03938-90d3-41a7-ae87-cb47a3872fcd",
   "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": "201098db-c696-4ff3-8c5f-915c38801446",
   "metadata": {},
   "source": [
    "#### バッファ計算して計算結果を書き戻す\n",
    "###### 10kmバッファが欲しい: \n",
    "###### 10000/(25*3600)=0.111111111\n",
    "###### 10000/(28*3600)=0.099206349\n",
    "###### 10000/(36*3600)=0.089605735"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "838683bc-cc50-4adf-80f7-052eb7f5f70e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "insert into jumbo.geoshape \n",
    "select \n",
    "cast(9 as integer) as 番号, \n",
    "cast('バッファ折れ線' as varchar(10)) as 名前, \n",
    "lonlat.st_buffer(0.099206349) as lonlat \n",
    "from jumbo.geoshape \n",
    "where 番号=4 \n",
    "; \n",
    "\n",
    "insert into jumbo.geoshape \n",
    "select \n",
    "cast(825 as integer) as 番号, \n",
    "cast('バッファ825' as varchar(10)) as 名前, \n",
    "lonlat.st_buffer(0.111111111) as lonlat \n",
    "from jumbo.geoshape \n",
    "where 番号=1 \n",
    ";\n",
    "\n",
    "insert into jumbo.geoshape \n",
    "select \n",
    "cast(828 as integer) as 番号, \n",
    "cast('バッファ828' as varchar(10)) as 名前, \n",
    "lonlat.st_buffer(0.099206349) as lonlat \n",
    "from jumbo.geoshape \n",
    "where 番号=1 \n",
    ";\n",
    "\n",
    "insert into jumbo.geoshape \n",
    "select \n",
    "cast(831 as integer) as 番号, \n",
    "cast('バッファ831' as varchar(10)) as 名前, \n",
    "lonlat.st_buffer(0.089605735) as lonlat \n",
    "from jumbo.geoshape \n",
    "where 番号=1 \n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "221fa475-9fd5-45d3-9d0f-f0707d7fa1aa",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### データの確認"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2fdf20ef-a2da-4fd8-8cfd-bbea94aaf30f",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"番号\",\"名前\",\"lonlat\"\n\"831\",\"バッファ831\",\"POLYGON ((139.875862735 35.671\"\n\"825\",\"バッファ825\",\"POLYGON ((139.89736811100002 3\"\n\"828\",\"バッファ828\",\"POLYGON ((139.88546334900002 3\"\n\"9\",\"バッファ折れ線\",\"POLYGON ((139.363937349000025 \"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory451301\"><style>\n",
       "      table.tdhistory451301 { display: block !important; min-height: 168px !important; overflow: auto !important; height: 168px !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.tdhistory451301 { 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.tdhistory451301:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory451301 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory451301 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory451301 { 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=\"tdhistory451301\"></th><th class=\"tdhistory451301\">番号</th><th class=\"tdhistory451301\">名前</th><th class=\"tdhistory451301\">lonlat</th></tr></thead><tbody class=\"tdhistory451301 resultset-body\"><tr><th class=\"tdhistory451301\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory451301\">831</td><td class=\"tdhistory451301\">バッファ831</td><td class=\"tdhistory451301\">POLYGON ((139.875862735 35.671</td></tr><tr><th class=\"tdhistory451301\" style= \"background: rgba(243, 243, 243, 0.75)\">2</th><td class=\"tdhistory451301\">825</td><td class=\"tdhistory451301\">バッファ825</td><td class=\"tdhistory451301\">POLYGON ((139.89736811100002 3</td></tr><tr><th class=\"tdhistory451301\" style= \"background: rgba(243, 243, 243, 0.75)\">3</th><td class=\"tdhistory451301\">828</td><td class=\"tdhistory451301\">バッファ828</td><td class=\"tdhistory451301\">POLYGON ((139.88546334900002 3</td></tr><tr><th class=\"tdhistory451301\" style= \"background: rgba(243, 243, 243, 0.75)\">4</th><td class=\"tdhistory451301\">9</td><td class=\"tdhistory451301\">バッファ折れ線</td><td class=\"tdhistory451301\">POLYGON ((139.363937349000025 </td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.00.35.705_JST\",\"historyID\":63,\"chunkID\":\"00001\",\"sessionid\":\"\",\"portnum\":\"\",\"dockermode\":\"standalone\",\"totalRowCount\":4,\"chunkCount\":0,\"rowLimit\":0,\"columnMetadata\":[{\"columnName\":\"番号\",\"columnTypeName\":\"INTEGER\"},{\"columnNumber\":1,\"columnName\":\"名前\",\"columnTypeName\":\"VARCHAR\",\"length\":10,\"scale\":10},{\"columnNumber\":2,\"columnName\":\"lonlat\",\"columnTypeName\":\"VARCHAR\",\"length\":30,\"scale\":30}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select \n",
    "番号, \n",
    "名前, \n",
    "cast(lonlat as varchar(30)) as lonlat --途中でぶった切る \n",
    "from jumbo.geoshape \n",
    "where 番号 in (9,825,828,831) \n",
    "; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f917b3aa-8d88-49cb-9e6d-466f944a1f65",
   "metadata": {},
   "source": [
    "#### ライン距離の計算\n",
    "###### こちらも度数表現なのでメートル変換が必要\n",
    "###### 前述のメートルを度数に変換するのと逆になるため、出力結果に対して以下の値を用いる\n",
    "###### 経度: 出力結果*(25*3600)\n",
    "###### 緯度: 出力結果*(31*3600)\n",
    "###### 中庸: 出力結果*(28*3600)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "efef6ba7-f21b-4464-9063-5f5dc7304404",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"番号\",\"名前\",\"ライン距離\"\n\"4\",\"折れ線\",\"796080.0960000004\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory982345\"><style>\n",
       "      table.tdhistory982345 { 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.tdhistory982345 { 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.tdhistory982345:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory982345 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory982345 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory982345 { 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=\"tdhistory982345\"></th><th class=\"tdhistory982345\">番号</th><th class=\"tdhistory982345\">名前</th><th class=\"tdhistory982345\">ライン距離</th></tr></thead><tbody class=\"tdhistory982345 resultset-body\"><tr><th class=\"tdhistory982345\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory982345\">4</td><td class=\"tdhistory982345\">折れ線</td><td class=\"tdhistory982345\">796080.0960000004</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 6,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.02.23.313_JST\",\"historyID\":64,\"chunkID\":\"00001\",\"sessionid\":\"\",\"portnum\":\"\",\"dockermode\":\"standalone\",\"totalRowCount\":1,\"chunkCount\":0,\"rowLimit\":0,\"columnMetadata\":[{\"columnName\":\"番号\",\"columnTypeName\":\"INTEGER\"},{\"columnNumber\":1,\"columnName\":\"名前\",\"columnTypeName\":\"VARCHAR\",\"length\":10,\"scale\":10},{\"columnNumber\":2,\"columnName\":\"ライン距離\",\"columnTypeName\":\"FLOAT\"}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select \n",
    "番号, \n",
    "名前, \n",
    "(lonlat.st_length())*(28*3600) as ライン距離 \n",
    "from jumbo.geoshape \n",
    "where 番号 = 4 \n",
    "; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4034631-a9f9-4365-a749-eb7193508d8a",
   "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
}