geof_02topojson.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b83b7236-11ec-4cd2-ad6e-fbd581d7e91c",
   "metadata": {},
   "source": [
    "##　位置情報とパブリックデータ#02: <br>topojsonの作成SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca15eba6-68c5-4112-9f6c-00353bbee59f",
   "metadata": {},
   "source": [
    "#### データベース接続"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b4c4a7f5-8d21-4cbe-951e-1e2b31bd89e5",
   "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": "5e437180-ab9c-4170-8297-f4d8b7f74da5",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### 事前のお掃除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "74aa2184-ba30-464e-a262-3ed6556c0580",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "ename": "ERROR",
     "evalue": "Unable to run SQL: Unable to run SQL query: Database reported error:3807:Object 'jumbo.geotopoj' does not exist.",
     "output_type": "error",
     "traceback": [
      "Unable to run SQL: Unable to run SQL query: Database reported error:3807:Object 'jumbo.geotopoj' does not exist."
     ]
    }
   ],
   "source": [
    "drop table jumbo.geotopoj; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26650415-9366-40cc-8369-98234d2b4d38",
   "metadata": {},
   "source": [
    "#### 空テーブル作成"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "28db67ba-028e-4b16-b66b-f0de98dc6ed3",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Success: 0 rows affected"
      ]
     },
     "execution_count": 4,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "create table jumbo.geotopoj ( \n",
    "partitioncolumn integer, \n",
    "partitionnamecolumn varchar(100) character set unicode, \n",
    "ordercolumn integer, \n",
    "x_longitudecolumn float, \n",
    "y_latitudecolumn float \n",
    ") primary index (partitioncolumn, ordercolumn)\n",
    "; "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5bba6b7-d157-4666-8a7e-408c2bb3958a",
   "metadata": {},
   "source": [
    "#### データの挿入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9a2fb388-6ba1-44de-87ea-8a517877608e",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Success: 1 rows affected"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "application/vnd.teradata.resultset": ""
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "insert into jumbo.geotopoj values ('0','ポリゴン1四角形','1','140','40'); \n",
    "insert into jumbo.geotopoj values ('0','ポリゴン1四角形','2','140','38'); \n",
    "insert into jumbo.geotopoj values ('0','ポリゴン1四角形','3','138','38'); \n",
    "insert into jumbo.geotopoj values ('0','ポリゴン1四角形','4','138','40'); \n",
    "insert into jumbo.geotopoj values ('0','ポリゴン1四角形','5','140','40'); \n",
    "insert into jumbo.geotopoj values ('1','ポリゴン2三角形','1','141','39'); \n",
    "insert into jumbo.geotopoj values ('1','ポリゴン2三角形','2','139','37'); \n",
    "insert into jumbo.geotopoj values ('1','ポリゴン2三角形','3','139','39'); \n",
    "insert into jumbo.geotopoj values ('1','ポリゴン2三角形','4','141','39'); "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89505c24-91c0-495a-b626-dcf01cd1a0f9",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### topojsonデータの作成SQL\n",
    "###### lit列をコピーして、メモ帳などに張り付け、.topojson形式で保存する"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bfcadde5-2998-4672-8bfd-0a16198f6a36",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/vnd.teradata.resultset": "\"blockid\",\"orde\",\"pttid\",\"pttname\",\"lit\"\n\"0\",\"1\",\"-1\",\"\",\"{\"\"type\"\":\"\"Topology\"\",\"\"transform\"\":{\"\n\"0\",\"2\",\"-1\",\"\",\"\"\"scale\"\": [0.00003105401469401469,0.00002513449824449824],\"\n\"0\",\"3\",\"-1\",\"\",\"\"\"translate\"\": [122.93267356,20.42276189]\"\n\"0\",\"4\",\"-1\",\"\",\"},\"\"objects\"\":{\"\"po\"\":{\"\"type\"\": \"\"GeometryCollection\"\",\"\"geometries\"\":[\"\n\"1\",\"0\",\"0\",\"ポリゴン1四角形\",\"{\"\"type\"\": \"\"Polygon\"\", \"\"arcs\"\":[[0]],\"\"properties\"\": {\"\"name\"\": \"\"ポリゴン1四角形\"\" }}\"\n\"1\",\"1\",\"1\",\"ポリゴン2三角形\",\",{\"\"type\"\": \"\"Polygon\"\", \"\"arcs\"\":[[1]],\"\"properties\"\": {\"\"name\"\": \"\"ポリゴン2三角形\"\" }}\"\n\"2\",\"5\",\"-1\",\"\",\"]}},\"\"arcs\"\": [\"\n\"3\",\"1\",\"0\",\"ポリゴン1四角形\",\"[[549601.28692441,778899.101925988],\"\n\"3\",\"2\",\"0\",\"ポリゴン1四角形\",\"[0,-79571.9087186387],\"\n\"3\",\"3\",\"0\",\"ポリゴン1四角形\",\"[-64403.9110468212,0],\"\n\"3\",\"4\",\"0\",\"ポリゴン1四角形\",\"[0,79571.9087186387],\"\n\"3\",\"5\",\"0\",\"ポリゴン1四角形\",\"[64403.9110468212,0]],\"\n\"4\",\"1\",\"1\",\"ポリゴン2三角形\",\"[[581803.24244782,739113.147566669],\"\n\"4\",\"2\",\"1\",\"ポリゴン2三角形\",\"[-64403.9110468212,-79571.9087186388],\"\n\"4\",\"3\",\"1\",\"ポリゴン2三角形\",\"[0,79571.9087186388],\"\n\"4\",\"4\",\"1\",\"ポリゴン2三角形\",\"[64403.9110468212,0]]]}\"",
      "text/html": [
       "<div class = \"td-resultset-table-div\" style = \"max-height: 100%; overflow-y: auto\">\n",
       "      <table class=\"tdhistory40228\"><style>\n",
       "      table.tdhistory40228 { display: block !important; min-height: 420px !important; overflow: auto !important; height: 420px !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.tdhistory40228 { 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.tdhistory40228:last-child { text-align: left !important; }\n",
       "      tbody.tdhistory40228 tr:nth-child(even) { background: rgba(243, 243, 243, 0.75) !important; }\n",
       "      tbody.tdhistory40228 tr:nth-child(odd) { background: var(--jp-ui-inverse-font-color1) !important; }\n",
       "      td.tdhistory40228 { 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=\"tdhistory40228\"></th><th class=\"tdhistory40228\">blockid</th><th class=\"tdhistory40228\">orde</th><th class=\"tdhistory40228\">pttid</th><th class=\"tdhistory40228\">pttname</th><th class=\"tdhistory40228\">lit</th></tr></thead><tbody class=\"tdhistory40228 resultset-body\"><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">1</th><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">-1</td><td class=\"tdhistory40228\"></td><td class=\"tdhistory40228\">{\"type\":\"Topology\",\"transform\":{</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">2</th><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">2</td><td class=\"tdhistory40228\">-1</td><td class=\"tdhistory40228\"></td><td class=\"tdhistory40228\">\"scale\": [0.00003105401469401469,0.00002513449824449824],</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">3</th><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">3</td><td class=\"tdhistory40228\">-1</td><td class=\"tdhistory40228\"></td><td class=\"tdhistory40228\">\"translate\": [122.93267356,20.42276189]</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">4</th><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">4</td><td class=\"tdhistory40228\">-1</td><td class=\"tdhistory40228\"></td><td class=\"tdhistory40228\">},\"objects\":{\"po\":{\"type\": \"GeometryCollection\",\"geometries\":[</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">5</th><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">ポリゴン1四角形</td><td class=\"tdhistory40228\">{\"type\": \"Polygon\", \"arcs\":[[0]],\"properties\": {\"name\": \"ポリゴン1四角形\" }}</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">6</th><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">ポリゴン2三角形</td><td class=\"tdhistory40228\">,{\"type\": \"Polygon\", \"arcs\":[[1]],\"properties\": {\"name\": \"ポリゴン2三角形\" }}</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">7</th><td class=\"tdhistory40228\">2</td><td class=\"tdhistory40228\">5</td><td class=\"tdhistory40228\">-1</td><td class=\"tdhistory40228\"></td><td class=\"tdhistory40228\">]}},\"arcs\": [</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">8</th><td class=\"tdhistory40228\">3</td><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">ポリゴン1四角形</td><td class=\"tdhistory40228\">[[549601.28692441,778899.101925988],</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">9</th><td class=\"tdhistory40228\">3</td><td class=\"tdhistory40228\">2</td><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">ポリゴン1四角形</td><td class=\"tdhistory40228\">[0,-79571.9087186387],</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">10</th><td class=\"tdhistory40228\">3</td><td class=\"tdhistory40228\">3</td><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">ポリゴン1四角形</td><td class=\"tdhistory40228\">[-64403.9110468212,0],</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">11</th><td class=\"tdhistory40228\">3</td><td class=\"tdhistory40228\">4</td><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">ポリゴン1四角形</td><td class=\"tdhistory40228\">[0,79571.9087186387],</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">12</th><td class=\"tdhistory40228\">3</td><td class=\"tdhistory40228\">5</td><td class=\"tdhistory40228\">0</td><td class=\"tdhistory40228\">ポリゴン1四角形</td><td class=\"tdhistory40228\">[64403.9110468212,0]],</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">13</th><td class=\"tdhistory40228\">4</td><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">ポリゴン2三角形</td><td class=\"tdhistory40228\">[[581803.24244782,739113.147566669],</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">14</th><td class=\"tdhistory40228\">4</td><td class=\"tdhistory40228\">2</td><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">ポリゴン2三角形</td><td class=\"tdhistory40228\">[-64403.9110468212,-79571.9087186388],</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">15</th><td class=\"tdhistory40228\">4</td><td class=\"tdhistory40228\">3</td><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">ポリゴン2三角形</td><td class=\"tdhistory40228\">[0,79571.9087186388],</td></tr><tr><th class=\"tdhistory40228\" style= \"background: rgba(243, 243, 243, 0.75)\">16</th><td class=\"tdhistory40228\">4</td><td class=\"tdhistory40228\">4</td><td class=\"tdhistory40228\">1</td><td class=\"tdhistory40228\">ポリゴン2三角形</td><td class=\"tdhistory40228\">[64403.9110468212,0]]]}</td></tr></tbody></table></div>"
      ]
     },
     "execution_count": 7,
     "metadata": {
      "application/vnd.teradata.resultset": "{\"resultSetID\":\"C:\\\\\\\\Users\\\\\\\\youruserdirectory\\\\\\\\Teradata\\\\\\\\Resultsets\\\\\\\\2023.10.19_16.13.48.687_JST\",\"historyID\":39,\"chunkID\":\"00001\",\"sessionid\":\"\",\"portnum\":\"\",\"dockermode\":\"standalone\",\"totalRowCount\":16,\"chunkCount\":0,\"rowLimit\":0,\"columnMetadata\":[{\"columnName\":\"blockid\",\"columnTypeName\":\"INTEGER\"},{\"columnNumber\":1,\"columnName\":\"orde\",\"columnTypeName\":\"INTEGER\"},{\"columnNumber\":2,\"columnName\":\"pttid\",\"columnTypeName\":\"INTEGER\"},{\"columnNumber\":3,\"columnName\":\"pttname\",\"columnTypeName\":\"VARCHAR\",\"length\":100,\"scale\":100},{\"columnNumber\":4,\"columnName\":\"lit\",\"columnTypeName\":\"VARCHAR\",\"length\":172,\"scale\":172}]}"
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with paras as ( \n",
    "\t /*描画するしたレイヤーのパラメーターをセット*/ \n",
    "\tselect \n",
    "\tcast('0.00003105401469401469' as varchar(100)) as scalex_vc, \n",
    "\tcast('0.00002513449824449824' as varchar(100)) as scaley_vc, \n",
    "\tcast('122.93267356' as varchar(100)) as transx_vc, \n",
    "\tcast('20.42276189' as varchar(100)) as transy_vc, \n",
    "\tcast(scalex_vc as float) as scalex, \n",
    "\tcast(scaley_vc as float) as scaley, \n",
    "\tcast(transx_vc as float) as transx, \n",
    "\tcast(transy_vc as float) as transy \n",
    "), src as ( \n",
    "\t /*取得してきたポリゴンの数値データをセット。複数あってよい*/ \n",
    "\t /*列名やテーブル名は作成対象のものをソースとしてセット、以降の処理は別名にて進む*/ \n",
    "\tselect \n",
    "\tpartitioncolumn as ptt, --複数ポリゴンがあるときの識別列\n",
    "\tpartitionnamecolumn as pttname, --複数ポリゴンそれぞれの名前\n",
    "\tordercolumn as ord, --ポリゴン内の順番\n",
    "\tx_longitudecolumn as 経度, --各ポリゴン頂点の経度列, \t\n",
    "\ty_latitudecolumn as 緯度 --各ポリゴン頂点の経度列 \n",
    "\tfrom jumbo.geotopoj \n",
    "), ordcross as ( \n",
    "\t /*順番を1からの値に振りなおす。すでにそうなっている場合が多いと思うが一応*/ \n",
    "\t /*パラメーターの値を利用して拡大縮小と位置移動をしておく*/ \n",
    "\tselect \n",
    "\tptt, \n",
    "\tdense_rank() over(order by ptt) - 1 as pttid, --arcsの番号に入れる値を0始まりにする\n",
    "\tpttname, \n",
    "\trow_number() over(partition by ptt order by ord) as orde, \n",
    "\trow_number() over(partition by ptt order by ord desc) as reverseorde, \n",
    "\t経度, \n",
    "\t緯度, \n",
    "\tscalex, \n",
    "\tscaley, \n",
    "\ttransx, \n",
    "\ttransy, \n",
    "\t(経度 - transx)/scalex as trax, \n",
    "\t(緯度 - transy)/scaley as tray \n",
    "\tfrom src a1 cross join paras a2 \n",
    "), lagger as ( \n",
    "\tselect \n",
    "\tmax(pttid) over() as maxpttid, \n",
    "\tpttid, \n",
    "\tpttname, \n",
    "\torde, \n",
    "\treverseorde, \n",
    "\ttrax, \n",
    "\ttray, \n",
    "\t /*一行ずつ値をずらし、その値を用いて前の行の値を引き算する*/ \n",
    "\ttrax - lag(trax, 1, null) over(partition by ptt order by orde) as lagx, \n",
    "\ttray - lag(tray, 1, null) over(partition by ptt order by orde) as lagy, \n",
    "\t /*最初の行はtrax,trayを、2行目以降はlagx,lagyを採用する*/ \n",
    "\tcase when orde=1 then trax else lagx end as valx, \n",
    "\tcase when orde=1 then tray else lagy end as valy, \n",
    "\t /*json用の整形、最後の行のみ,をセットしないようにする*/ \n",
    "\t(case when orde=1 then '[[' else '[' end)||to_char(valx)||','||to_char(valy)||\n",
    "\t(case when reverseorde=1 and pttid=maxpttid then ']]]}' when reverseorde=1 and pttid<>maxpttid then ']],' else '],' end) as lit \n",
    " \tfrom ordcross \n",
    ") \n",
    "\tselect * from ( \n",
    "\t( \n",
    "\t\tselect \n",
    "\t\tcast(1 as integer) as blockid, \n",
    "\t\tpttid as orde, \n",
    "\t\tpttid, \n",
    "\t\tpttname, \n",
    "\t\t(case when pttid=0 then '{\"type\": \"Polygon\", \"arcs\":[[' else ',{\"type\": \"Polygon\", \"arcs\":[[' end)||to_char(pttid)||\n",
    "\t\t']],\"properties\": {\"name\": \"'||pttname||'\" }}' as lit\n",
    "\t\tfrom lagger \n",
    "\t\tgroup by 1,2,3,4  \n",
    "\t) union ( \n",
    "\t\t /*必要列のみに絞り込み*/ \n",
    "\t\t /*pttidとpttnameは1つづつjsonファイルobjects内のデータとしてセットする*/ \n",
    "\t\t /*finvalueはpttidごとにjsonファイルarcs内のデータとしてセットする*/ \n",
    "\t\tselect \n",
    "\t\tpttid+3 as blockid, \n",
    "\t\torde, \n",
    "\t\tpttid, \n",
    "\t\tpttname, \n",
    "\t\tlit \n",
    "\t\tfrom lagger \n",
    "\t) union (  \n",
    "\t\t /*scale部分*/ \n",
    "\t\tselect \n",
    "\t\tcast(0 as integer) as blockid, \n",
    "\t\tcast(2 as integer) as orde, \n",
    "\t\tcast(-1 as integer) as pttid, \n",
    "\t\tcast(null as varchar(10)) as pttiname, \n",
    "\t\t '\"scale\": ['||scalex_vc||','||scaley_vc||'],' as lit \n",
    "\t\tfrom paras \n",
    "\t) union ( \n",
    "\t\t /*translate部分*/ \n",
    "\t\tselect \n",
    "\t\tcast(0 as integer) as blockid, \n",
    "\t\tcast(3 as integer) as orde, \n",
    "\t\tcast(-1 as integer) as pttid, \n",
    "\t\tcast(null as varchar(10)) as pttiname, \n",
    "\t\t '\"translate\": ['||transx_vc||','||transy_vc||']' as lit \n",
    "\t\tfrom paras \n",
    "\t) union ( \n",
    "\t\t /*文字列部分の行を作成*/ \n",
    "\t\tselect \n",
    "\t\tcase when day_of_calendar=3 then cast(2 as integer) else cast(0 as integer) end as blockid, \n",
    "\t\tcase \n",
    "\t\twhen day_of_calendar=2 then 4 \n",
    "\t\twhen day_of_calendar=3 then 5 \n",
    "\t\telse day_of_calendar end as orde, \n",
    "\t\tcast(-1 as integer) as pttid, \n",
    "\t\tcast(null as varchar(10)) as pttiname, \n",
    "\t\tcase \n",
    "\t\twhen day_of_calendar=1 then '{\"type\":\"Topology\",\"transform\":{' \n",
    "\t\twhen day_of_calendar=2 then '},\"objects\":{\"po\":{\"type\": \"GeometryCollection\",\"geometries\":[' \n",
    "\t\twhen day_of_calendar=3 then ']}},\"arcs\": [' \n",
    "\t\telse null end as lit \n",
    "\t\tfrom sys_calendar.calendar\n",
    "\t\twhere day_of_calendar between 1 and 3 \n",
    "\t) \n",
    "\t) a1 \n",
    "\torder by 1,2  \n",
    "\t; "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e16896fd-bbfb-4789-a8b1-22b68231736e",
   "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
}