SQLで3次メッシュコードをWKT形式に変換する

次のような3次メッシュコードをWKTに変換したいとき

mesh3

mcode3
55370046
54390560

以下で変換できる。クエリエンジンはPrestoを想定

convert_wkt

WITH base as (
  SELECT
    ,cast(cast(substr(mcode3,3,2) as double) +100 + cast(substr(mcode3,6,1) as double) * 1/8   + cast(substr(mcode3,8,1) as double) * 1/8/10 as varchar) as west
    ,cast(cast(substr(mcode3,3,2) as double) +100 + cast(substr(mcode3,6,1) as double) * 1/8   + (cast(substr(mcode3,8,1) as double)+1) * 1/8/10 as varchar) as east
    ,cast(cast(substr(mcode3,1,2) as double) /1.5 + cast(substr(mcode3,5,1) as double) * 2/3/8 + cast(substr(mcode3,7,1) as double) * 2/3/8/10 as varchar) as south
    ,cast(cast(substr(mcode3,1,2) as double) /1.5 + cast(substr(mcode3,5,1) as double) * 2/3/8 + (cast(substr(mcode3,7,1) as double)+1) * 2/3/8/10 as varchar) as north
  FROM mesh3
)
SELECT
  'POLYGON((' || west || ' ' || south || ',' ||  west || ' ' || north || ',' ||  east || ' ' || north  || ',' ||  east || ' ' || south || ',' || west || ' ' || south || '))'
FROM base

参考：GISソフトウエアでメッシュコードに対応した値を表示する方法