中野智文のブログ

データ・マエショリストのメモ

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

まとめ

とりあえず、できた。エラーチェックなどはないので、偉い人は自分でつくろう。

参考文献

https://www.stat.go.jp/data/mesh/pdf/gaiyo1.pdf