BQで地域メッシュコード(2分の1)のポリゴン
背景
BQで地域メッシュコード(2分の1)からポリゴンを作ることになった。
クエリ
とはいっても、ほとんど temp function ですが。
create temp function INT(S STRING) AS (CAST(S AS INT64)); create temp function STR(F FLOAT64) AS (CAST(F AS STRING)); create temp function FINT(F FLOAT64) AS (CAST(F-0.5 AS INT64)); create temp function SS1(CODE STRING, POS INT64) AS ( INT(SUBSTR(CODE, POS+1, 1))); create temp function SS2(CODE STRING, POS INT64) AS ( INT(SUBSTR(CODE, POS+1, 2))); create temp function LA4(CODE STRING) AS (FINT((SS2(CODE, 8)-1)/2)); create temp function LAT4(CODE STRING, I INT64) AS ((SS2(CODE, 0)+(SS1(CODE, 4)+(SS1(CODE, 6)+(LA4(CODE)+I)/2)/10)/8)/1.5); create temp function LAT40(CODE STRING) AS (STR(LAT4(CODE, 0))); create temp function LAT41(CODE STRING) AS (STR(LAT4(CODE, 1))); create temp function LO4(CODE STRING) AS (FINT(MOD((SS2(CODE, 8)-1), 2))); create temp function LON4(CODE STRING, I INT64) AS (SS2(CODE, 2)+100+(SS1(CODE, 5)+(SS1(CODE, 7)+(LO4(CODE)+I)/2)/10)/8); create temp function LON40(CODE STRING) AS (STR(LON4(CODE, 0))); create temp function LON41(CODE STRING) AS (STR(LON4(CODE, 1))); create temp function WKT4(CODE STRING) AS ("POLYGON((" || LON40(CODE) || " " || LAT40(CODE) || ", " || LON41(CODE) || " " || LAT40(CODE) || ", " || LON41(CODE) || " " || LAT41(CODE) || ", " || LON40(CODE) || " " || LAT41(CODE) || ", " || LON40(CODE) || " " || LAT40(CODE) || "))" ); select ST_GEOGFROMTEXT(WKT4("533946113")) AS geog
まとめ
とりあえず、できた。エラーチェックなどはないので、偉い人は自分でつくろう。