Try #071 – Amazon RedshiftとDBeaverで空間検索してみた

この記事は、「AWS Advent Calendar 2021」の2日目の記事です。

Amazon RedshiftとDBeaverで空間検索をしてみました！

今回は、AWSが提供するクラウドデータウェアハウスのAmazon RedshiftにDBeaverで接続し、位置情報データのインポートや空間関数を実行してみました。

位置情報データの事前準備

事前準備としてQGISを利用し、ランダムポイント(青色の100万件)と空間検索用ポリゴン(黄色の3件)を、Amazon Redshiftで標準対応しているshapefile形式で準備しました。

• QGIS #065 - 指定範囲にランダムポイント作成

また、Amazon Redshiftにインポートするためにデータ一式をS3にアップロードしておきます。

Amazon Redshiftのクラスター作成

はじめに、Amazon Redshiftのクラスターを作成します。

AWSコンソールでAmazon Redshiftを選択 → クラスター → クラスターを作成をクリック。

クラスター名・無料トライアル・ユーザー名・パスワードを設定 → クラスターを作成をクリック。

しばらくするとクラスターが作成されます。

これでAmazon Redshiftのクラスター作成は完了になります。

ロールとパブリックアクセスの設定

次に、Amazon RedshiftからS3へアクセスするためのロール設定と、DBeaverからAmazon Redshiftへ接続するためのパブリックアクセス設定をします。

S3へアクセスするためのロールを作成します。

Amazon Redshiftのクラスター詳細画面で作成したロールを割り当てます。

Amazon Redshiftのクラスター詳細画面のアクション → パブリックアクセス可能な設定を変更をクリック。

パブリックアクセス可能を「有効化」 → 変更を保存をクリック。

有効化されているかを確認します。

Amazon Redshiftのクラスター詳細画面のセキュリティグループ → インバウンドルールを追加しタイプをRedshiftに設定 → ルールを保存をクリック。

インバウンドルールが設定されているかを確認します。

これでロールとパブリックアクセスの設定は完了になります。

DBeaverで位置情報データをインポート

次に、DBeaverを利用しAmazon Redshiftに接続し位置情報データをインポートします。

DBeaverでAmazon Redshiftに接続します。ホスト名(クラスターエンドポイント)・ポート・データベース名・ユーザー名・パスワード・ロール名を設定します。

ランダムポイント(100万件)をインポートしてみます。事前にテーブルを作成し、インポートしたいshapefileの格納先とロールを指定し実行します。

CREATE TABLE points (
   wkb_geometry GEOMETRY,
   id BIGINT
  );

COPY points FROM 's3://redshift-geo-data/points.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::xxxxxxx:role/redshift-geo-role-2112';

インポートされると、マップ上で可視化しながらデータの確認ができます。

空間検索用ポリゴン(3件)をインポートしてみます。事前にテーブルを作成し、インポートしたいshapefileの格納先とロールを指定し実行します。

CREATE TABLE polygon (
   wkb_geometry GEOMETRY,
   id BIGINT
  );

COPY polygon FROM 's3://redshift-geo-data/polygon.shp'
FORMAT SHAPEFILE
CREDENTIALS 'aws_iam_role=arn:aws:iam::xxxxxxx:role/redshift-geo-role-2112';

インポートされると、マップ上で可視化しながらデータの確認ができます。

これでDBeaverでの位置情報データのインポートは完了になります。

空間関数を実行

最後に、Amazon Redshiftで空間関数を実行できるかを試してみます。今回は代表的な例として、「ST_AsGeoJSON」を利用しデータをGeoJSON形式に変換するのと、「ST_Within」を利用し100万件のポイントからポリゴン内にあるポイントを抽出する空間関数を実行してみます。

まずは、「ST_AsGeoJSON」を利用しデータをGeoJSON形式に変換してみます。

SELECT ST_AsGeoJSON(wkb_geometry) FROM public.polygon;

GeoJSON形式のデータで出力されました！

次に、「ST_Within」を利用しポリゴン内にあるポイントを抽出してみます。

SELECT public.points.id, public.points.wkb_geometry FROM public.points, public.polygon WHERE ST_Within(public.points.wkb_geometry, public.polygon.wkb_geometry);

ポリゴン内のみのデータで出力されました！

次に、「ST_Within」を利用しポリゴン内にあるポイントのカウントを抽出してみます。

SELECT COUNT(*) FROM public.points, public.polygon WHERE ST_Within(public.points.wkb_geometry, public.polygon.wkb_geometry);

ポリゴン内のみのカウント「5167件」が出力されました！

最後に、QGISを利用した場合と空間検索結果が一致するかも確認してみたいと思います。

• QGIS #076 - 空間検索でデータ選択

同じ「5167件」になりました！

Amazon RedshiftとDBeaverで空間検索ができました！

Amazon RedshiftとDBeaverを利用することで、Amazon Redshiftで位置情報データのインポートや空間検索をできることが確認できました。今後位置情報データの分析等にもうまく利用できそうです。

当初はサービス内で利用できる、「Query Editor V2」で手軽に試そうとしましたが、現状で位置情報データのインポートや空間関数に対応していないようでした。今後対応されるともっと手軽に利用できたり、今週プレビュー版が発表されたAmazon Redshift Serverlessでも手軽に利用できるようになるかもしれません。

• 参考文献
  Amazon Redshift
  DBeaver