Snowflakeで特定エリア内の地理空間オブジェクトをカウントしてみた

			
			
$ snowsql
❄ SnowSQL ❄ v1.2.14
Type SQL statements or !help
foo_bar#(no warehouse)@(no database).(no schema)>PUT file://./japan.geojson @~/spatial/;
japan.geojson_c.gz(1.18MB): [##########] 100.00% Done (0.175s, 6.78MB/s).       
╒═══════════════╤══════════════════╤═════════════╤═════════════╤════════════════════╤════════════════════╤══════════╤═════════╕
│ source        │ target           │ source_size │ target_size │ source_compression │ target_compression │ status   │ message │
╞═══════════════╪══════════════════╪═════════════╪═════════════╪════════════════════╪════════════════════╪══════════╪═════════╡
│ japan.geojson │ japan.geojson.gz │    13050092 │     1240891 │ NONE               │ GZIP               │ UPLOADED │         │
╘═══════════════╧══════════════════╧═════════════╧═════════════╧════════════════════╧════════════════════╧══════════╧═════════╛
1 Row(s) produced. Time Elapsed: 1.215s

		

			
			
-- 都道府県のエリアテーブル（COPY用）を作成
CREATE OR REPLACE TABLE japan_geojson_raw (
  src VARIANT
);

-- 都道府県のエリアテーブル（実データ用）を作成
CREATE OR REPLACE TABLE japan_geojson (
  code NUMBER,
  name VARCHAR,
  area GEOGRAPHY
);

-- データをユーザーステージからCOPY
COPY INTO
  japan_geojson_raw
FROM
  @~/spatial/
FILE_FORMAT = (TYPE = 'JSON')
;

-- ネストを解除しつつ、実データテーブルにINSERT
INSERT INTO japan_geojson
SELECT
  value:properties.id::number AS code,
  value:properties.nam_ja::varchar AS name,
  TO_GEOGRAPHY(value:geometry) AS area
FROM
    japan_geojson_raw
  , LATERAL FLATTEN(INPUT => src:features)
;

		

			
			
-- ステージを作成
CREATE OR REPLACE STAGE ookla url = 's3://ookla-open-data/';

-- 登録先テーブルを作成
CREATE OR REPLACE TABLE oolka_geo (
  avg_d_kbps INTEGER,
  avg_u_kpbs INTEGER,
  avg_lat_ms INTEGER,
  tests      INTEGER,
  devices    INTEGER,
  quadkey    VARCHAR,
  tile       GEOGRAPHY
);

-- parquetデータをCOPY
COPY INTO oolka_geo FROM (
  SELECT $1:avg_d_kbps::INTEGER,
         $1:avg_u_kpbs::INTEGER,
         $1:avg_lat_ms::INTEGER,
         $1:tests::INTEGER,
         $1:devices::INTEGER,
         $1:quadkey::VARCHAR,
         ST_GEOGRAPHYFROMWKT($1:tile::VARCHAR)
    FROM @ookla/parquet/performance/type=mobile/year=2020/quarter=4/2020-10-01_performance_mobile_tiles.parquet
)
FILE_FORMAT = (TYPE=PARQUET COMPRESSION=SNAPPY);

		

			
			
-- 都道府県は47件
SELECT COUNT(area) AS rec_count FROM japan_geojson;
-- 47

-- Ooklaの地理空間オブジェクト数は約420万件
SELECT COUNT(tile) AS rec_count FROM ookla_geo;
-- 4,197,854

		

			
			
-- エリア内のタイルをカウント
SELECT
  japan_geojson.code,
  japan_geojson.name,
  COUNT(ookla_geo.tile) AS contains_counts
FROM
  japan_geojson, ookla_geo
WHERE
  ST_CONTAINS(japan_geojson.area, ookla_geo.tile)
GROUP BY
  japan_geojson.code,
  japan_geojson.name
;

		

			
			
-- 結果登録テーブルを作成
CREATE OR REPLACE TABLE tile_count_result (
  code NUMBER,
  name VARCHAR,
  count NUMBER
);

-- カウント用プロシージャを作成
CREATE OR REPLACE PROCEDURE update_tile_count()
  RETURNS FLOAT NOT NULL
  LANGUAGE javascript
  AS
  $$
    // クエリ
    const select_pref_query = 'SELECT code, name, ST_ASWKT(area) AS area FROM japan_geojson;';
    const insert_result_query = 'INSERT INTO tile_count_result (code, name, count) VALUES (?, ?, ?);'
    const select_count_query = 'SELECT COUNT(ookla_geo.tile) AS count FROM ookla_geo WHERE ST_CONTAINS(TO_GEOGRAPHY(?), ookla_geo.tile);'

    // 県でループ
    const select_pref_statement = snowflake.createStatement( {sqlText: select_pref_query} );
    const select_pref_result_set = select_pref_statement.execute();
    while (select_pref_result_set.next()) {
      const pref_code = select_pref_result_set.getColumnValue('CODE');
      const pref_name = select_pref_result_set.getColumnValue('NAME');
      const pref_area = select_pref_result_set.getColumnValue('AREA');

      // 県域内のタイルをカウント
      const select_count_statement = snowflake.createStatement(
                                        {
                                          sqlText: select_count_query,
                                          binds:[pref_area]
                                        }
                                      );
      const select_count_result_set = select_count_statement.execute();
      while (select_count_result_set.next()) {
        const contains_count = select_count_result_set.getColumnValue('COUNT');

        // 結果テーブルにインサート
        const insert_result_statement = snowflake.createStatement(
                                          {
                                            sqlText: insert_result_query,
                                            binds:[pref_code, pref_name, contains_count]
                                          }
                                        );
        insert_result_statement.execute();
      }
    }

    return 0;
  $$
;

-- プロシージャを実行
CALL update_tile_count();

		

			
			
-- カウント結果を確認
SELECT
  code,
  name,
  count
FROM
  tile_count_result
ORDER BY
  code
;