250mメッシュ(4分の1地域メッシュ)の境界ポリゴンを持つテーブルです。統計値は持たず、mesh_code で census.population_mesh_250m などの250mメッシュ統計と結合して地図にします。
WITH "quarter" (digit, dx, dy) AS (SELECT * FROM (VALUES (1, 0, 0), (2, 1, 0), (3, 0, 1), (4, 1, 1)) AS valueslist), mesh AS (SELECT b.mesh_code AS mesh_1km_code, b.mesh1_code, (((CAST(substr(b.mesh_code, 1, 2) AS INTEGER) / 1.5) + (CAST(substr(b.mesh_code, 5, 1) AS INTEGER) / 12.0)) + (CAST(substr(b.mesh_code, 7, 1) AS INTEGER) / 120.0)) AS south, (((100 + CAST(substr(b.mesh_code, 3, 2) AS INTEGER)) + (CAST(substr(b.mesh_code, 6, 1) AS INTEGER) / 8.0)) + (CAST(substr(b.mesh_code, 8, 1) AS INTEGER) / 80.0)) AS west, h.digit AS half_digit, q.digit AS quarter_digit, ((h.dx * 2) + q.dx) AS x, ((h.dy * 2) + q.dy) AS y FROM {DUCKLAKE_CATALOG}.boundary.mesh_1km AS b CROSS JOIN "quarter" AS h CROSS JOIN "quarter" AS q)SELECT ((mesh_1km_code || half_digit) || quarter_digit) AS mesh_code, mesh_1km_code, mesh1_code, CAST(half_digit AS VARCHAR) AS mesh4_code, CAST(quarter_digit AS VARCHAR) AS mesh5_code, st_setcrs(st_makeenvelope((west + (x / 320.0)), (south + (y / 480.0)), (west + ((x + 1) / 320.0)), (south + ((y + 1) / 480.0))), 'EPSG:4612') AS geometry FROM mesh