geof_05_2buffertri.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "53884f39-4381-41bf-8f56-f50731c51bbf",
   "metadata": {},
   "source": [
    "## 位置情報とパブリックデータ#05: <br>(地理空間関数)バッファ範囲の特定(続き)\n",
    "###### 利用データはgeof_03geometry.ipynbと同じものを利用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b427f38-976c-4151-9987-3cf138998ad3",
   "metadata": {},
   "source": [
    "#### データベース接続"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "23a58c12-21eb-442d-a8c2-0f198ad19960",
   "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": "53f9bafc-4055-4a17-9f90-8cacda245454",
   "metadata": {},
   "source": [
    "#### MBR値取得関数の利用サンプル\n",
    "###### 月島突端から半径10kmのバッファを考えた際のMBRを計算取得する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d702a03f-db54-47e4-aa99-bf7890efd88c",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"番号\",\"名前\",\"mbr_xmin_ymin_xmax_ymax\"\n\"1\",\"月島突端\",\"(139.676,35.5813,139.897,35.7616)\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory972962\"><style>\n",
       "      table.tdhistory972962 { 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.tdhistory972962 { 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.tdhistory972962:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory972962 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory972962 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory972962 { 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=\"tdhistory972962\"></th><th class=\"tdhistory972962\">番号</th><th class=\"tdhistory972962\">名前</th><th class=\"tdhistory972962\">mbr_xmin_ymin_xmax_ymax</th></tr></thead><tbody class=\"tdhistory972962 resultset-body\"><tr><th class=\"tdhistory972962\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory972962\">1</td><td class=\"tdhistory972962\">月島突端</td><td class=\"tdhistory972962\">(139.676,35.5813,139.897,35.7616)</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 2,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.14.40.275_JST\",\"historyID\":65,\"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\":\"mbr_xmin_ymin_xmax_ymax\",\"columnTypeName\":\"VARCHAR\",\"length\":1000,\"scale\":1000}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select \n",
    "番号, \n",
    "名前, \n",
    "cast((\n",
    "lonlat.st_spheroidalbuffermbr(10000) \n",
    ") as varchar(1000)) as mbr_xmin_ymin_xmax_ymax \n",
    "from jumbo.geoshape \n",
    "where 番号 = 1 \n",
    "; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3cfeffd6-fe67-447a-ab2a-b0f0a4e934c9",
   "metadata": {},
   "source": [
    "#### MBR値からバッファーポリゴンを作成する、三角関数利用、10km\n",
    "###### 計算結果を書き戻す"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "852be752-7942-4625-9975-36acce82cba5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "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",
    "with src0 as ( \n",
    "\t /*mbrから緯度経度の最大値を取得する*/ \n",
    "\tselect \n",
    "\t番号, \n",
    "\t名前, \n",
    "\tlonlat.st_spheroidalbuffermbr(10000).xmax() as xmax, \n",
    "\tlonlat.st_spheroidalbuffermbr(10000).ymax() as ymax, \n",
    "\tlonlat.st_x() as xcentroid, \n",
    "\tlonlat.st_y() as ycentroid \n",
    "\tfrom jumbo.geoshape \n",
    "\twhere 番号 = 1 \n",
    "), src1 as ( \n",
    "\tselect \n",
    "\t番号, \n",
    "\t名前, \n",
    "\t /*度数ベースでの中心からの距離を計算する*/ \n",
    "\txmax - xcentroid as distx, \n",
    "\tymax - ycentroid as disty, \n",
    "\t /*円周率の定義*/ \n",
    "\tcast(3.14159265359 as float) as pi_value, \n",
    "\t /*経度: 距離差に対してコサイン(ラジアン角度)、緯度: 距離差に対してサイン(ラジアン角度)を積算し、中心点の座標ゼロからの移動分を足す*/ \n",
    "\tto_char((distx*cos(-180*pi_value/180) + xcentroid), '999.9999999999') as xn180, \n",
    "\tto_char((distx*cos(-170*pi_value/180) + xcentroid), '999.9999999999') as xn170, \n",
    "\tto_char((distx*cos(-160*pi_value/180) + xcentroid), '999.9999999999') as xn160,\n",
    "\tto_char((distx*cos(-150*pi_value/180) + xcentroid), '999.9999999999') as xn150,\n",
    "\tto_char((distx*cos(-140*pi_value/180) + xcentroid), '999.9999999999') as xn140,\n",
    "\tto_char((distx*cos(-130*pi_value/180) + xcentroid), '999.9999999999') as xn130,\n",
    "\tto_char((distx*cos(-120*pi_value/180) + xcentroid), '999.9999999999') as xn120,\n",
    "\tto_char((distx*cos(-110*pi_value/180) + xcentroid), '999.9999999999') as xn110,\n",
    "\tto_char((distx*cos(-100*pi_value/180) + xcentroid), '999.9999999999') as xn100,\n",
    "\tto_char((distx*cos(-90*pi_value/180) + xcentroid), '999.9999999999') as xn90,\n",
    "\tto_char((distx*cos(-80*pi_value/180) + xcentroid), '999.9999999999') as xn80,\n",
    "\tto_char((distx*cos(-70*pi_value/180) + xcentroid), '999.9999999999') as xn70,\n",
    "\tto_char((distx*cos(-60*pi_value/180) + xcentroid), '999.9999999999') as xn60,\n",
    "\tto_char((distx*cos(-50*pi_value/180) + xcentroid), '999.9999999999') as xn50,\n",
    "\tto_char((distx*cos(-40*pi_value/180) + xcentroid), '999.9999999999') as xn40,\n",
    "\tto_char((distx*cos(-30*pi_value/180) + xcentroid), '999.9999999999') as xn30,\n",
    "\tto_char((distx*cos(-20*pi_value/180) + xcentroid), '999.9999999999') as xn20,\n",
    "\tto_char((distx*cos(-10*pi_value/180) + xcentroid), '999.9999999999') as xn10,\n",
    "\tto_char((distx*cos(0*pi_value/180) + xcentroid), '999.9999999999') as x0,\n",
    "\tto_char((distx*cos(10*pi_value/180) + xcentroid), '999.9999999999') as xp10,\n",
    "\tto_char((distx*cos(20*pi_value/180) + xcentroid), '999.9999999999') as xp20,\n",
    "\tto_char((distx*cos(30*pi_value/180) + xcentroid), '999.9999999999') as xp30,\n",
    "\tto_char((distx*cos(40*pi_value/180) + xcentroid), '999.9999999999') as xp40,\n",
    "\tto_char((distx*cos(50*pi_value/180) + xcentroid), '999.9999999999') as xp50,\n",
    "\tto_char((distx*cos(60*pi_value/180) + xcentroid), '999.9999999999') as xp60,\n",
    "\tto_char((distx*cos(70*pi_value/180) + xcentroid), '999.9999999999') as xp70,\n",
    "\tto_char((distx*cos(80*pi_value/180) + xcentroid), '999.9999999999') as xp80,\n",
    "\tto_char((distx*cos(90*pi_value/180) + xcentroid), '999.9999999999') as xp90,\n",
    "\tto_char((distx*cos(100*pi_value/180) + xcentroid), '999.9999999999') as xp100,\n",
    "\tto_char((distx*cos(110*pi_value/180) + xcentroid), '999.9999999999') as xp110,\n",
    "\tto_char((distx*cos(120*pi_value/180) + xcentroid), '999.9999999999') as xp120,\n",
    "\tto_char((distx*cos(130*pi_value/180) + xcentroid), '999.9999999999') as xp130,\n",
    "\tto_char((distx*cos(140*pi_value/180) + xcentroid), '999.9999999999') as xp140,\n",
    "\tto_char((distx*cos(150*pi_value/180) + xcentroid), '999.9999999999') as xp150,\n",
    "\tto_char((distx*cos(160*pi_value/180) + xcentroid), '999.9999999999') as xp160,\n",
    "\tto_char((distx*cos(170*pi_value/180) + xcentroid), '999.9999999999') as xp170,\n",
    "\tto_char((distx*cos(180*pi_value/180) + xcentroid), '999.9999999999') as xp180,\n",
    "\tto_char((disty*sin(-180*pi_value/180) + ycentroid), '999.9999999999') as yn180, \n",
    "\tto_char((disty*sin(-170*pi_value/180) + ycentroid), '999.9999999999') as yn170, \n",
    "\tto_char((disty*sin(-160*pi_value/180) + ycentroid), '999.9999999999') as yn160,\n",
    "\tto_char((disty*sin(-150*pi_value/180) + ycentroid), '999.9999999999') as yn150,\n",
    "\tto_char((disty*sin(-140*pi_value/180) + ycentroid), '999.9999999999') as yn140,\n",
    "\tto_char((disty*sin(-130*pi_value/180) + ycentroid), '999.9999999999') as yn130,\n",
    "\tto_char((disty*sin(-120*pi_value/180) + ycentroid), '999.9999999999') as yn120,\n",
    "\tto_char((disty*sin(-110*pi_value/180) + ycentroid), '999.9999999999') as yn110,\n",
    "\tto_char((disty*sin(-100*pi_value/180) + ycentroid), '999.9999999999') as yn100,\n",
    "\tto_char((disty*sin(-90*pi_value/180) + ycentroid), '999.9999999999') as yn90,\n",
    "\tto_char((disty*sin(-80*pi_value/180) + ycentroid), '999.9999999999') as yn80,\n",
    "\tto_char((disty*sin(-70*pi_value/180) + ycentroid), '999.9999999999') as yn70,\n",
    "\tto_char((disty*sin(-60*pi_value/180) + ycentroid), '999.9999999999') as yn60,\n",
    "\tto_char((disty*sin(-50*pi_value/180) + ycentroid), '999.9999999999') as yn50,\n",
    "\tto_char((disty*sin(-40*pi_value/180) + ycentroid), '999.9999999999') as yn40,\n",
    "\tto_char((disty*sin(-30*pi_value/180) + ycentroid), '999.9999999999') as yn30,\n",
    "\tto_char((disty*sin(-20*pi_value/180) + ycentroid), '999.9999999999') as yn20,\n",
    "\tto_char((disty*sin(-10*pi_value/180) + ycentroid), '999.9999999999') as yn10,\n",
    "\tto_char((disty*sin(0*pi_value/180) + ycentroid), '999.9999999999') as y0,\n",
    "\tto_char((disty*sin(10*pi_value/180) + ycentroid), '999.9999999999') as yp10,\n",
    "\tto_char((disty*sin(20*pi_value/180) + ycentroid), '999.9999999999') as yp20,\n",
    "\tto_char((disty*sin(30*pi_value/180) + ycentroid), '999.9999999999') as yp30,\n",
    "\tto_char((disty*sin(40*pi_value/180) + ycentroid), '999.9999999999') as yp40,\n",
    "\tto_char((disty*sin(50*pi_value/180) + ycentroid), '999.9999999999') as yp50,\n",
    "\tto_char((disty*sin(60*pi_value/180) + ycentroid), '999.9999999999') as yp60,\n",
    "\tto_char((disty*sin(70*pi_value/180) + ycentroid), '999.9999999999') as yp70,\n",
    "\tto_char((disty*sin(80*pi_value/180) + ycentroid), '999.9999999999') as yp80,\n",
    "\tto_char((disty*sin(90*pi_value/180) + ycentroid), '999.9999999999') as yp90,\n",
    "\tto_char((disty*sin(100*pi_value/180) + ycentroid), '999.9999999999') as yp100,\n",
    "\tto_char((disty*sin(110*pi_value/180) + ycentroid), '999.9999999999') as yp110,\n",
    "\tto_char((disty*sin(120*pi_value/180) + ycentroid), '999.9999999999') as yp120,\n",
    "\tto_char((disty*sin(130*pi_value/180) + ycentroid), '999.9999999999') as yp130,\n",
    "\tto_char((disty*sin(140*pi_value/180) + ycentroid), '999.9999999999') as yp140,\n",
    "\tto_char((disty*sin(150*pi_value/180) + ycentroid), '999.9999999999') as yp150,\n",
    "\tto_char((disty*sin(160*pi_value/180) + ycentroid), '999.9999999999') as yp160,\n",
    "\tto_char((disty*sin(170*pi_value/180) + ycentroid), '999.9999999999') as yp170,\n",
    "\tto_char((disty*sin(180*pi_value/180) + ycentroid), '999.9999999999') as yp180, \n",
    "\t /*得られた36+1点の位置をポリゴンに変換する*/ \n",
    "\tcast((\n",
    "\t /*st_geometry型に変換*/ \n",
    "\tcast(( \n",
    "\t 'polygon(('||xn180||' '||yn180||','||xn170||' '||yn170||','||xn160||' '||yn160||','||xn150||' '||yn150||','||xn140||' '||yn140||','||xn130||' '||yn130||','||xn120||' '||yn120||','||xn110||' '||yn110||','||xn100||' '||yn100||','||xn90||' '||yn90||','||xn80||' '||yn80||','||xn70||' '||yn70||','||xn60||' '||yn60||','||xn50||' '||yn50||','||xn40||' '||yn40||','||xn30||' '||yn30||','||xn20||' '||yn20||','||xn10||' '||yn10||','||x0||' '||y0||','||xp10||' '||yp10||','||xp20||' '||yp20||','||xp30||' '||yp30||','||xp40||' '||yp40||','||xp50||' '||yp50||','||xp60||' '||yp60||','||xp70||' '||yp70||','||xp80||' '||yp80||','||xp90||' '||yp90||','||xp100||' '||yp100||','||xp110||' '||yp110||','||xp120||' '||yp120||','||xp130||' '||yp130||','||xp140||' '||yp140||','||xp150||' '||yp150||','||xp160||' '||yp160||','||xp170||' '||yp170||','||xp180||' '||yp180||'))' \n",
    "\t) as st_geometry) \n",
    "\t) as varchar(20000)) \n",
    "\t as lonlat \n",
    "\tfrom src0 \n",
    ") \n",
    "\t /*余計な出力を除外*/ \n",
    "\tselect \n",
    "\tcast(10 as integer) as 番号, \n",
    "\tcast('bf2_' as varchar(10))||名前 as 名前, \n",
    "\tlonlat \n",
    "\tfrom src1 \n",
    "\t; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "740cd26e-15ce-4a2c-a3a5-3f612bf036f1",
   "metadata": {},
   "source": [
    "#### 格納結果の確認"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "16d848c3-f518-4a6c-a411-b91eb7af5bd2",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"番号\",\"名前\",\"lonlat\"\n\"10\",\"bf2_月島突端\",\"POLYGON ((139.675803895499996 \"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory827910\"><style>\n",
       "      table.tdhistory827910 { 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.tdhistory827910 { 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.tdhistory827910:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory827910 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory827910 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory827910 { 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=\"tdhistory827910\"></th><th class=\"tdhistory827910\">番号</th><th class=\"tdhistory827910\">名前</th><th class=\"tdhistory827910\">lonlat</th></tr></thead><tbody class=\"tdhistory827910 resultset-body\"><tr><th class=\"tdhistory827910\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory827910\">10</td><td class=\"tdhistory827910\">bf2_月島突端</td><td class=\"tdhistory827910\">POLYGON ((139.675803895499996 </td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 4,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.17.15.181_JST\",\"historyID\":67,\"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\":\"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 番号=10 \n",
    "; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "317b1bec-0d64-4c49-a7f7-acfdbbb93fe3",
   "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
}