注:本記事は(2021年7月28日)に公開された(Unleash Advanced Geospatial Analytics in Snowflake)を翻訳して公開したものです。

企業と官公庁・公的機関は両者とも、世界規模のパンデミックに対応するため、新型コロナの症例を視覚化し、抑制策を伝え、ワクチン接種の進捗状況を追跡するためのインタラクティブなユーザーエクスペリエンスと空間アプリケーションの開発を余儀なくされています。 

加えて、消費財、小売、ロジスティクス、電気通信、および不動産といったビジネスはすべて、地理空間アナリティクスを用いることで、根本的なネットワーク最適化の意思決定を促進し、急速に変化する消費者行動に適応する必要がありました。

現在では、ほぼすべての組織において、処理され、分析されているデータのほとんどに、ある種の空間要素が含まれています。開発者やデータサイエンティストにとっての主な課題は、この大量の情報の中にあるインサイトをどのようにシームレスに解き放つかということです。

2020年にSnowflakeがローンチした一連の地理空間機能に触発され、当社はその優れた機能をもとに、CATRO Spatial Extension for Snowflakeを開発しました。

この開発により、データサイエンティストとアナリストはSnowflakeプラットフォームから直接、60以上のオープンソース地理空間機能を実行できるようになりました。それだけでなく当社は、データエンリッチメントワークフローをさらに合理化するため、Snowflakeデータマーケットプレイスで主要な地理空間データセットを提供しています。

CATRO Spatial Extension for Snowflakeとは

Spatial Extensionは、Snowflakeの既存の地理空間機能を拡張する、厳選された一連のJavaScript UDF(ユーザー定義関数)です。関数には最も一般的な空間分析ルチンが含まれており、複雑なETLプロセスの必要なしに、またデータをプラットフォームから移動させることなく、高度なロケーションベースのアナリティクスを可能にします。さらに、これらすべてを便利なSnowflakeデータシェアリングで利用可能にすることもできます。

Spatial Extensionを使用したアナリティクスの例

まず、Snowflakeデータマーケットプレイスで提供されているSafeGraphのコアプレースデータセットから抽出した米国内のすべてのスターバックスの場所を可視化しましょう。

SELECT ST_MAKEPOINT(LONGITUDE, LATITUDE) as geom FROM CDA26920_STARBUCKS_CORE_PLACES_SAMPLE.PUBLIC.CORE_POI

このデータをすばやく探索するためにSpatial Extensionを使用すると、解像度15のクワッドキーでこれらのロケーションの集合を簡単に演算し、結果をヒートマップとして可視化できます。下図はマンハッタンエリアをクローズアップしたものであり、どのエリアにスターバックスの店舗が集中しているかを簡単に特定できます。

with qks as 
(SELECT sfcarto.quadkey.LONGLAT_ASQUADINT(LONGITUDE, LATITUDE, 15) as qk
FROM CDA26920_STARBUCKS_CORE_PLACES_SAMPLE.PUBLIC.CORE_POI)
SELECT count(*) as num_stores, sfcarto.quadkey.ST_BOUNDARY(qk) as geom from qks GROUP BY qk

それではさらに分析を進め、Spatial Extensionのジオメトリ構築モジュールを使用して、各店舗の周囲3 kmバッファ内にある店舗を集計しましょう。

SELECT sfcarto.constructors.ST_MAKEELLIPSE(ST_POINT(LONGITUDE,LATITUDE),3,3,0,'kilometers',12) as geom
FROM CDA26920_STARBUCKS_CORE_PLACES_SAMPLE.PUBLIC.CORE_POI

搭載されているポリフィル機能を使用して、各バッファと交差するクワッドキーを解像度15でコンピュートすることで、分析をさらに深めることができます。

WITH qks AS(
SELECT sfcarto.quadkey.ST_ASQUADINT_POLYFILL(
    sfcarto.constructors.ST_MAKEELLIPSE(ST_POINT(LONGITUDE,LATITUDE),3,3,0,'kilometers',12), 15) AS qk
FROM CDA26920_STARBUCKS_CORE_PLACES_SAMPLE.PUBLIC.CORE_POI
)

SELECT sfcarto.quadkey.ST_BOUNDARY(VALUE) AS geom
FROM qks, lateral FLATTEN(input => qk)

さらに、Snowflakeデータマーケットプレイスで提供されているCARTOのSpatial Featuresデータセットを使用して、先ほどの結果として得られたクワッドキーを、総人口と性別ごとの人口でエンリッチ化します。このデータセットはすでに解像度15のクアッドキーで提供されていますので、エンリッチメントは単純なJOINで実行できます。

WITH qks AS(
SELECT sfcarto.quadkey.ST_ASQUADINT_POLYFILL(
    sfcarto.constructors.ST_MAKEELLIPSE(ST_POINT(LONGITUDE,LATITUDE),3,3,0,'kilometers',12), 15) AS qk
FROM CDA26920_STARBUCKS_CORE_PLACES_SAMPLE.PUBLIC.CORE_POI
),
geom_data AS(
SELECT sfcarto.quadkey.QUADKEY_FROMQUADINT(VALUE::BIGINT) AS geoid, sfcarto.quadkey.ST_BOUNDARY(VALUE) AS geom
FROM qks, lateral FLATTEN(input => qk)
GROUP BY VALUE
)
SELECT t1.geom AS geom, population, t2.female, t2.male
FROM geom_data t1
INNER JOIN DATA_MARKETPLACE.CARTO.VIEW_DERIVED_SPATIALFEATURES_USA_QUADGRID15_V1_YEARLY_2020 t2
ON t1.geoid = t2.geoid;

特定のエリアにズームインすることで、総人口の分布を表示できます(下の図の場合、暗い色になるほど人口が多い)。

データの準備が完了すると、各店舗の3 km圏内の総人口を計算できます。このキャッチメント分析は、最初から最後まで以下のクエリで実行できます。

WITH qks AS(
SELECT sfcarto.quadkey.ST_ASQUADINT_POLYFILL(
    sfcarto.constructors.ST_MAKEELLIPSE(ST_POINT(LONGITUDE,LATITUDE),3,3,0,'kilometers',12), 15) AS qk, SAFEGRAPH_PLACE_ID as store_id
FROM CDA26920_STARBUCKS_CORE_PLACES_SAMPLE.PUBLIC.CORE_POI
),

flat_qks AS(
SELECT t1.store_id, sfcarto.quadkey.QUADKEY_FROMQUADINT(VALUE::BIGINT) AS geoid
FROM qks t1, lateral FLATTEN(input => qk)
),

enriched_qks AS(
SELECT t1.store_id, t1.geoid, t2.population, t2.female, t2.male
FROM flat_qks t1
INNER JOIN DATA_MARKETPLACE.CARTO.VIEW_DERIVED_SPATIALFEATURES_USA_QUADGRID15_V1_YEARLY_2020 t2
ON t1.geoid = t2.geoid
),

kpis_per_store AS(
SELECT store_id, SUM(population) as tot_pop, SUM(female) as tot_female, sum(male) as tot_male
FROM enriched_qks
GROUP BY store_id
)

SELECT t1.store_id, t1.tot_pop, t1.tot_female, t1.tot_male, ST_POINT(LONGITUDE,LATITUDE) as geom
FROM kpis_per_store t1
INNER JOIN CDA26920_STARBUCKS_CORE_PLACES_SAMPLE.PUBLIC.CORE_POI t2
ON t1.store_id = t2.SAFEGRAPH_PLACE_ID

結果として、以下のような素晴らしいマップとして視覚化できました。スターバックスの各店舗は点で表され、それぞれのキャッチメントは3 km圏内の総人口に比例しています。

これは、CATRO Spatial Extension for Snowflakeの一部として利用可能な60以上の地理空間機能のうちの一例ですが、これを使用することにより、サードパーティのソースを利用してユーザーデータをシームレスに強化し、貴重なロケーションベースのインサイトを、Snowflakeデータクラウドから直接引き出すことができます。

Snowflakeのデータストレージや処理機能がほぼ無限であることを考えると、ユーザーは分析ワークフローをシンプル化して、クラウドにおける高度な地理空間分析のパフォーマンス要件も満たすことができます。

CATRO Spatial Extension for Snowflakeには、Snowflakeデータマーケットプレイスからアクセスできます。

詳細については、ドキュメンテーションセンターにアクセスしてください。CATRO Spatial Extension for Snowflakeをお試しになりたい場合は、こちらで登録してください。