geof_06objvarious.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4cde0ff4-b767-4a4e-aba2-71f0531ed0e8",
   "metadata": {},
   "source": [
    "## 位置情報とパブリックデータ#06: <br>(地理空間関数)オブジェクトの値取得\n",
    "###### 利用データはgeof_03geometry.ipynbと同じものを利用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "375fae7c-6e1f-482a-ae46-5f6f251be166",
   "metadata": {},
   "source": [
    "#### データベース接続"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d25ba011-dc1b-4cff-9550-73e9d86aa964",
   "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": "3f4a4638-2e0a-4a32-891a-f89d9108f628",
   "metadata": {},
   "source": [
    "#### 次元数の確認: xとyの2次元データか、z含めた3次元データかの確認"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c5611c6f-42e4-4632-ad87-25d992a53b45",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"番号\",\"次元数\"\n\"3\",\"2\"\n\"825\",\"2\"\n\"9\",\"2\"\n\"0\",\"2\"\n\"5\",\"2\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory984130\"><style>\n",
       "      table.tdhistory984130 { display: block !important; min-height: 189px !important; overflow: auto !important; height: 189px !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.tdhistory984130 { 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.tdhistory984130:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory984130 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory984130 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory984130 { 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=\"tdhistory984130\"></th><th class=\"tdhistory984130\">番号</th><th class=\"tdhistory984130\">次元数</th></tr></thead><tbody class=\"tdhistory984130 resultset-body\"><tr><th class=\"tdhistory984130\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory984130\">3</td><td class=\"tdhistory984130\">2</td></tr><tr><th class=\"tdhistory984130\" style= \"background: rgba(243, 243, 243, 0.75)\">2</th><td class=\"tdhistory984130\">825</td><td class=\"tdhistory984130\">2</td></tr><tr><th class=\"tdhistory984130\" style= \"background: rgba(243, 243, 243, 0.75)\">3</th><td class=\"tdhistory984130\">9</td><td class=\"tdhistory984130\">2</td></tr><tr><th class=\"tdhistory984130\" style= \"background: rgba(243, 243, 243, 0.75)\">4</th><td class=\"tdhistory984130\">0</td><td class=\"tdhistory984130\">2</td></tr><tr><th class=\"tdhistory984130\" style= \"background: rgba(243, 243, 243, 0.75)\">5</th><td class=\"tdhistory984130\">5</td><td class=\"tdhistory984130\">2</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 2,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.25.06.579_JST\",\"historyID\":68,\"chunkID\":\"00001\",\"sessionid\":\"\",\"portnum\":\"\",\"dockermode\":\"standalone\",\"totalRowCount\":5,\"chunkCount\":0,\"rowLimit\":0,\"columnMetadata\":[{\"columnName\":\"番号\",\"columnTypeName\":\"INTEGER\"},{\"columnNumber\":1,\"columnName\":\"次元数\",\"columnTypeName\":\"SMALLINT\"}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select top 5 \n",
    "番号, \n",
    "lonlat.st_coorddim() as 次元数 \n",
    "from jumbo.geoshape \n",
    "; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a00e37e1-3ca7-487d-98b7-fc926071253c",
   "metadata": {},
   "source": [
    "#### 以下の書き方でも良い"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a18d6348-21f2-4c9a-b462-7fedd37094b4",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"番号\",\"lonlat\"\n\"831\",\"2\"\n\"0\",\"2\"\n\"5\",\"2\"\n\"825\",\"2\"\n\"3\",\"2\"\n\"9\",\"2\"\n\"1\",\"2\"\n\"10\",\"2\"\n\"828\",\"2\"\n\"6\",\"2\"\n\"4\",\"2\"\n\"2\",\"2\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory82858\"><style>\n",
       "      table.tdhistory82858 { display: block !important; min-height: 336px !important; overflow: auto !important; height: 336px !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.tdhistory82858 { 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.tdhistory82858:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory82858 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory82858 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory82858 { 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=\"tdhistory82858\"></th><th class=\"tdhistory82858\">番号</th><th class=\"tdhistory82858\">lonlat</th></tr></thead><tbody class=\"tdhistory82858 resultset-body\"><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory82858\">831</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">2</th><td class=\"tdhistory82858\">0</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">3</th><td class=\"tdhistory82858\">5</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">4</th><td class=\"tdhistory82858\">825</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">5</th><td class=\"tdhistory82858\">3</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">6</th><td class=\"tdhistory82858\">9</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">7</th><td class=\"tdhistory82858\">1</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">8</th><td class=\"tdhistory82858\">10</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">9</th><td class=\"tdhistory82858\">828</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">10</th><td class=\"tdhistory82858\">6</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">11</th><td class=\"tdhistory82858\">4</td><td class=\"tdhistory82858\">2</td></tr><tr><th class=\"tdhistory82858\" style= \"background: rgba(243, 243, 243, 0.75)\">12</th><td class=\"tdhistory82858\">2</td><td class=\"tdhistory82858\">2</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 4,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.26.06.779_JST\",\"historyID\":70,\"chunkID\":\"00001\",\"sessionid\":\"\",\"portnum\":\"\",\"dockermode\":\"standalone\",\"totalRowCount\":12,\"chunkCount\":0,\"rowLimit\":0,\"columnMetadata\":[{\"columnName\":\"番号\",\"columnTypeName\":\"INTEGER\"},{\"columnNumber\":1,\"columnName\":\"lonlat\",\"columnTypeName\":\"SMALLINT\"}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select 番号, jumbo.geoshape.lonlat.st_coorddim(); "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4135275b-9900-44ee-b2e8-beb0b8f3d55c",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 線オブジェクトの位置を取得する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ebf2fdfc-2520-46f0-9119-0d50d16c4c59",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"番号\",\"名前\",\"開始地点\",\"地点25p\",\"地点50p\",\"地点75p\",\"終了地点\"\n\"4\",\"折れ線\",\"POINT (139.264731 39.087856)\",\"POINT (139.264731 37.113451)\",\"POINT (139.264731 35.139046)\",\"POINT (137.371949 35.057423)\",\"POINT (135.397544 35.057423)\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory927062\"><style>\n",
       "      table.tdhistory927062 { 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.tdhistory927062 { 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.tdhistory927062:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory927062 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory927062 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory927062 { 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=\"tdhistory927062\"></th><th class=\"tdhistory927062\">番号</th><th class=\"tdhistory927062\">名前</th><th class=\"tdhistory927062\">開始地点</th><th class=\"tdhistory927062\">地点25p</th><th class=\"tdhistory927062\">地点50p</th><th class=\"tdhistory927062\">地点75p</th><th class=\"tdhistory927062\">終了地点</th></tr></thead><tbody class=\"tdhistory927062 resultset-body\"><tr><th class=\"tdhistory927062\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory927062\">4</td><td class=\"tdhistory927062\">折れ線</td><td class=\"tdhistory927062\">POINT (139.264731 39.087856)</td><td class=\"tdhistory927062\">POINT (139.264731 37.113451)</td><td class=\"tdhistory927062\">POINT (139.264731 35.139046)</td><td class=\"tdhistory927062\">POINT (137.371949 35.057423)</td><td class=\"tdhistory927062\">POINT (135.397544 35.057423)</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 7,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.27.36.348_JST\",\"historyID\":73,\"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\":\"VARCHAR\",\"length\":1000,\"scale\":1000},{\"columnNumber\":3,\"columnName\":\"地点25p\",\"columnTypeName\":\"VARCHAR\",\"length\":1000,\"scale\":1000},{\"columnNumber\":4,\"columnName\":\"地点50p\",\"columnTypeName\":\"VARCHAR\",\"length\":1000,\"scale\":1000},{\"columnNumber\":5,\"columnName\":\"地点75p\",\"columnTypeName\":\"VARCHAR\",\"length\":1000,\"scale\":1000},{\"columnNumber\":6,\"columnName\":\"終了地点\",\"columnTypeName\":\"VARCHAR\",\"length\":1000,\"scale\":1000}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select \n",
    "番号, \n",
    "名前, \n",
    "cast((lonlat.st_startpoint()) as varchar(1000)) as 開始地点, \n",
    "cast((lonlat.st_line_interpolate_point(0.25)) as varchar(1000)) as 地点25p, \n",
    "cast((lonlat.st_line_interpolate_point(0.50)) as varchar(1000)) as 地点50p, \n",
    "cast((lonlat.st_line_interpolate_point(0.75)) as varchar(1000)) as 地点75p, \n",
    "cast((lonlat.st_endpoint()) as varchar(1000)) as 終了地点 \n",
    "from jumbo.geoshape \n",
    "where 番号 = 4 \n",
    "; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18579886-ec5e-4aad-a46e-ee96d277cc59",
   "metadata": {},
   "source": [
    "#### ポリゴンの面積を取得する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "5ad3091a-ce09-4791-8c82-fa12a8d27643",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"番号\",\"名前\",\"lonlat\",\"面積\"\n\"5\",\"ポリゴン1\",\"POLYGON ((132.0 35.5,134.0 35.5,134.0 33.5,132.0 33.5,132.0 35.5))\",\"4\"\n\"6\",\"ポリゴン2\",\"POLYGON ((133.0 34.5,135.0 34.5,135.0 32.5,133.0 32.5,133.0 34.5))\",\"4\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory110956\"><style>\n",
       "      table.tdhistory110956 { display: block !important; min-height: 126px !important; overflow: auto !important; height: 126px !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.tdhistory110956 { 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.tdhistory110956:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory110956 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory110956 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory110956 { 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=\"tdhistory110956\"></th><th class=\"tdhistory110956\">番号</th><th class=\"tdhistory110956\">名前</th><th class=\"tdhistory110956\">lonlat</th><th class=\"tdhistory110956\">面積</th></tr></thead><tbody class=\"tdhistory110956 resultset-body\"><tr><th class=\"tdhistory110956\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory110956\">5</td><td class=\"tdhistory110956\">ポリゴン1</td><td class=\"tdhistory110956\">POLYGON ((132.0 35.5,134.0 35.5,134.0 33.5,132.0 33.5,132.0 35.5))</td><td class=\"tdhistory110956\">4</td></tr><tr><th class=\"tdhistory110956\" style= \"background: rgba(243, 243, 243, 0.75)\">2</th><td class=\"tdhistory110956\">6</td><td class=\"tdhistory110956\">ポリゴン2</td><td class=\"tdhistory110956\">POLYGON ((133.0 34.5,135.0 34.5,135.0 32.5,133.0 32.5,133.0 34.5))</td><td class=\"tdhistory110956\">4</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 8,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.28.15.373_JST\",\"historyID\":74,\"chunkID\":\"00001\",\"sessionid\":\"\",\"portnum\":\"\",\"dockermode\":\"standalone\",\"totalRowCount\":2,\"chunkCount\":0,\"rowLimit\":0,\"columnMetadata\":[{\"columnName\":\"番号\",\"columnTypeName\":\"INTEGER\"},{\"columnNumber\":1,\"columnName\":\"名前\",\"columnTypeName\":\"VARCHAR\",\"length\":10,\"scale\":10},{\"columnNumber\":2,\"columnName\":\"lonlat\",\"columnTypeName\":\"VARCHAR\",\"length\":30000,\"scale\":30000},{\"columnNumber\":3,\"columnName\":\"面積\",\"columnTypeName\":\"FLOAT\"}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select \n",
    "番号, \n",
    "名前, \n",
    "cast(lonlat as varchar(30000)) as lonlat, \n",
    "lonlat.st_area() as 面積 \n",
    "from jumbo.geoshape \n",
    "where 番号 in (5,6)  \n",
    "; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee50918c-2395-4d33-bff2-16ab3dcf5403",
   "metadata": {},
   "source": [
    "#### ポリゴンの中心座標を取得する\n",
    "###### 結果はst_geometry型で返されるため、表示用に文字型へキャスト"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d30c2c80-669f-41d5-a2b6-f84b47096dae",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"番号\",\"名前\",\"lonlat\",\"centroid\"\n\"5\",\"ポリゴン1\",\"POLYGON ((132.0 35.5,134.0 35.5,134.0 33.5,132.0 33.5,132.0 35.5))\",\"POINT (133.0 34.5)\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory758824\"><style>\n",
       "      table.tdhistory758824 { 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.tdhistory758824 { 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.tdhistory758824:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory758824 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory758824 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory758824 { 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=\"tdhistory758824\"></th><th class=\"tdhistory758824\">番号</th><th class=\"tdhistory758824\">名前</th><th class=\"tdhistory758824\">lonlat</th><th class=\"tdhistory758824\">centroid</th></tr></thead><tbody class=\"tdhistory758824 resultset-body\"><tr><th class=\"tdhistory758824\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory758824\">5</td><td class=\"tdhistory758824\">ポリゴン1</td><td class=\"tdhistory758824\">POLYGON ((132.0 35.5,134.0 35.5,134.0 33.5,132.0 33.5,132.0 35.5))</td><td class=\"tdhistory758824\">POINT (133.0 34.5)</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 9,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_17.29.17.374_JST\",\"historyID\":75,\"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\":30000,\"scale\":30000},{\"columnNumber\":3,\"columnName\":\"centroid\",\"columnTypeName\":\"VARCHAR\",\"length\":1000,\"scale\":1000}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "select \n",
    "番号, \n",
    "名前, \n",
    "cast(lonlat as varchar(30000)) as lonlat, \n",
    "cast((lonlat.st_centroid()) as varchar(1000)) as centroid \n",
    "from jumbo.geoshape \n",
    "where 番号 = 5 \n",
    "; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e19bb5e4-333e-4d43-a384-86a2793c85ac",
   "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
}