地理情報データを データベースに格納しよう ～ GPUを活用した爆速データベース PG-Stromの紹介 ～ 2024/11/10 日本仮想化技術株式会社 坂井 恵 FOSS4G 2024 Japan

地理情報をデータベースに格納しよう ～GPUを活用した爆速データベースPG-Stromの紹介 • 坂井 恵 @sakaik • 有限会社アートライ 代表取締役 • 日本仮想化技術株式会社「爆速DB Powered by PG-Strom」チームに参画中 • MySQL 8.0(2018) でGIS機能が拡充されたのをきっかけに地理情報に関心 • 測量士補 発表者紹介 日本仮想化技術株式会社 • サーバ仮想化 や DevOps の技術を提供 • PG-Stromを使用したデータ分析基盤「爆速DB」を提供 • OSGeo.JP 団体会員 講演 タイトル

みなさん データベース(RDBMS)に 地理情報などのデータを 格納していますか！？

タイプ別 今日の話の聞き方 • DBなんて使っていない／ファイルで十分！ →データベースで出来ることをぜひ聞いていってください • DBは使ってるけど地理情報には活用していない →こういう活用方法もあるよ、という情報として • DBで地理情報扱っているけどパフォーマンスが... →もしかしたらGPUパワーを活用したPG-Stromが解決するかも • その他 →こういう世界もあるんだー、とお気軽にお聞きください

データベースで地理データを管理しよう 私の印象ですが地理情報の人って結構、 • データを、あんまりデータベースに入れない • ファイルで十分だと思っているフシがある データベース(DBMS)を使わなくても良い？ • データが少なかったらDBMSなくてもいいかも? • 変化がない固定のデータなら一度ファイルを作って終わりで良い? • ファイルベースでの情報配信の仕組みが確立している分野 データベースの使いどころ • データ量が多く構造的であれば使うとメリットがあるかも • 配信用地図データのマスタデータの保存場所として • 条件を指定しての抽出操作、集計・集約操作が得意 • 様々な切り口でのデータ提供が可能

データベースで扱える主なデータ型 • 文字列 • 数値 • 整数 • 小数 • 日付・時刻 • 空間情報 • 点 • 線（ライン） • 面（ポリゴン） • その他 • JSONとか 「表(テーブル)」の形式で格納 データの保全 データの高速な取扱い （集計、検索(抽出)）

PG-Stromとは 今日、これだけは知って帰ってください！ • GPUパワーを活用した高速データベース • 大抵のデータベースはCPUのみを使用している • 大量データの処理が得意 • 例： テラバイトクラス～、数十億件～ • PostgreSQL の extensionとして動作 • 通常の PostgreSQLを使う感覚で使用できる • Apache Arrow形式のファイルをテーブルとして使用可能

爆速DB powered by PG-Strom • PG-Stromをベースにデータ分析基盤としてアプライアンス提供 • 位置情報、IoT、ログ分析などに（GISにも対応） • 大量データの処理や演算に向いている • 爆速DB powered by PG-Strom の技術 • GPU ： 数千コアによる超並列処理 • NVMe SSD ： 高速バス経由でGPUにダイレクト転送 • Apache Arrow ： 列指向フォーマットをテーブルとして使用可能

爆速DBは こんなに速い ■ テーマ こんなクエリがこんなに速く！ PG-Stromと位置情報 PG-Stromと列指向データ PG-Stromと超大量データ

PG-Stromと位置情報1 • 複雑なポリゴンとの交叉または包含関係 • 1000万ポイント • 新宿区のポリゴン（ポイント数の多い複雑なポリゴン） SELECT p.gid, p.x, p.y FROM tdfk j, geopoint p WHERE ST_Contains(j.geom,ST_SetSRID(ST_MakePoint(x,y), 4326)) AND j.n03_004 like '新宿区' CPU(PostgreSQL) ： 5500 msec (5.5秒) GPU(PG-Strom) ： 630 msec (0.6秒) in 新宿区 Result: Query: 公共施設/コンビニ/チェーン店/ 公衆トイレ/看板/人流 etc

PG-Stromと位置情報2 • 複雑なポリゴンとの交叉または包含関係 • 1000万ポイント • 静岡県内の市区町村ごとのポリゴン(複雑かつ多量) • 市区町村ごとに集計 SELECT n03_001 || n03_004, COUNT(*) cnt FROM tdfk j, geopoint p WHERE ST_Contains(j.geom, ST_SetSRID(ST_MakePoint(x,y), 4326)) AND n03_001 LIKE '静岡県' GROUP BY n03_001, n03_004 ORDER BY COUNT(*) DESC CPU(PostgreSQL) ： 26800 msec (26秒) GPU(PG-Strom) ： 900 msec ( 0.9秒) Result: Query: in 静岡県 市区町村毎に集計

PG-Stromと位置情報 まとめ • 交叉／包含判定関数をはじめとした様々な関数がGPU対応 • 注）現時点で PostGISの全関数に対応しているわけではない • 案件や検証で必要であれば追加を提案できるかも 対応関数の例 st_makepoint (float8,float8) st_point (float8,float8) st_makepoint (float8,float8,float8) st_makepoint (float8,float8,float8,float8) st_setsrid (geometry,int4) st_distance (geometry,geometry) st_dwithin (geometry,geometry,float8) st_contains (geometry,geometry) st_crosses (geometry,geometry) st_linecrossingdirection (geometry,geometry) https://heterodb.github.io/pg-strom/ja/ref_devfuncs/#postgis

PG-Stromと列指向データ 概要 • RDBMSは一般に行単位でデータを格納している • カラム数の多いテーブルから、ひとつひとつのクエリではその中のごく少 数のカラムだけを参照することは多い • 列単位でデータがまとまっている「列指向」のフォーマットがある • PG-Stromは列指向データフォーマットである ApacheArrow形式のファイ ルをテーブルとして参照できる 行指向 の格納 列指向 の格納

PG-Stromと列指向 • lineorder_flat テーブル • 列数： 54列 • 件数：約24億件 • データサイズ • PostgreSQL(heap)テーブル • 1526GB • Arrowファイル • heapとほぼ同サイズ(1598GB) lo_orderkey lo_linenumber lo_custkey lo_partkey lo_suppkey lo_orderdate lo_orderpriority lo_shippriority lo_quantity lo_extendedprice lo_ordertotalprice lo_discount lo_revenue lo_supplycost lo_tax lo_commit_date lo_shipmode c_name c_address c_city c_nation c_region c_phone c_mktsegment s_name s_address s_city s_nation s_region s_phone p_name p_mfgr p_category p_brand1 p_color p_type p_size p_container d_date d_dayofweek d_month d_year d_yearmonthnum d_yearmonth d_daynuminweek d_daynuminmonth d_daynuminyear d_monthnuminyear d_weeknuminyear d_sellingseason d_lastdayinweekfl d_lastdayinmonthfl d_holidayfl d_weekdayfl 列名一覧:

PG-Stromと列指向 1 CPU(PostgreSQL) ： 5分14秒 GPU(PG-Strom)＋Arrow ： 5秒165 Result: Query: SELECT lo_orderpriority, COUNT(*) FROM %TABLE% GROUP BY lo_orderpriority ORDER BY lo_orderpriority; lo_orderpriority | count ------------------+----------- 1-URGENT | 479992014 2-HIGH | 480037805 3-MEDIUM | 479957940 4-NOT SPECI | 479967889 5-LOW | 480056415 (5 rows) 単純なグループカウント(全件対象)

PG-Stromと列指向 2 CPU(PostgreSQL) ： 6分07秒 GPU(PG-Strom)＋Arrow ： 3秒958 Result: Query: SELECT lo_orderpriority, COUNT(*) FROM %TABLE% WHERE d_year=1995 GROUP BY lo_orderpriority ORDER BY lo_orderpriority; lo_orderpriority | count ------------------+---------- 1-URGENT | 72803951 2-HIGH | 72816667 3-MEDIUM | 72799284 4-NOT SPECI | 72802326 5-LOW | 72831556 (5 rows) 抽出付グループカウント(d_yearで絞り込み)

PG-Stromと列指向 3 CPU(PostgreSQL) ： 5分58秒 GPU(PG-Strom)＋Arrow ： 11秒253 Result: Query: SELECT d_year, lo_orderpriority, sum(lo_extendedprice) FROM %TABLE% WHERE s_region='ASIA' GROUP BY d_year,lo_orderpriority ORDER BY d_year, lo_orderpriority; d_year | lo_orderpriority | sum --------+------------------+---------------- 1992 | 1-URGENT | 55754054433397 1992 | 2-HIGH | 55720071150222 1992 | 3-MEDIUM | 55737456621929 1992 | 4-NOT SPECI | 55745409996470 1992 | 5-LOW | 55747692515383 1993 | 1-URGENT | 55582507698342 1993 | 2-HIGH | 55617539949435 : 抽出付き 2列指定でのグループ合計

PG-Stromと列指向 まとめ PostgreSQL標準のテーブル格納方式とは別に Apache Arrow フォーマット での データ格納を利用することで 高速に結果を得ることができます。

PG-Stromと超大量データ • 登記所備付地図データを例に • 点、線、ポリゴンへと積み上げのデータ構造 • surface - curves - points と積み上げるデータ構造 • 次ページに詳細情報 • basics に名称等の情報 num | tbl | cnt -----+-----------------+--------------- 1 | moj_basics | 316,300 2 | moj_curves_data | 6,455,800,708 3 | moj_fude_info | 243,443,243 4 | moj_line_info | 3,206,759,982 5 | moj_points | 1,556,071,844 6 | moj_surface | 3,194,536,648 8 | moj_zukaku_info | 7,541,896 9 | moj_zukaku_ref | 286,190,006 参考：全データ件数:

PG-Stromと超大量データ points curves_data surface ver | filename | point_no | xml_pid | x | y | refpoint_type | buri_kbn | point_tag --------+---------------------+----------+------------+------------+-----------+---------------+----------+----------- 202404 | 12222-0424-1265.zip | 2529047 | P000000541 | -15402.457 | 24941.103 | | | 筆界点 202404 | 12222-0424-1265.zip | 2529048 | P000000542 | -15407.726 | 24937.207 | | | 筆界点 202404 | 12222-0424-1265.zip | 2529049 | P000000543 | -15412.905 | 24933.192 | | | 筆界点 202404 | 12222-0424-1265.zip | 2529050 | P000000544 | -15417.984 | 24929.054 | | | 筆界点 202404 | 12222-0424-1265.zip | 2529051 | P000000545 | -15422.967 | 24924.789 | | | 筆界点 ver | filename | curve_id | x | y | xml_pid | num --------+---------------------+------------+---+---+------------+----- 202404 | 12222-0424-1265.zip | C000004229 | | | P000000110 | 1 202404 | 12222-0424-1265.zip | C000004229 | | | P000002610 | 2 202404 | 12222-0424-1265.zip | C000004230 | | | P000001373 | 1 202404 | 12222-0424-1265.zip | C000004230 | | | P000001408 | 2 ver | filename | surface_id | curve_id | num --------+---------------------+------------+------------+----- 202404 | 12222-0424-1265.zip | F000000062 | C000000469 | 1 202404 | 12222-0424-1265.zip | F000000062 | C000000470 | 2 202404 | 12222-0424-1265.zip | F000000062 | C000000471 | 3 202404 | 12222-0424-1265.zip | F000000062 | C000000472 | 4 202404 | 12222-0424-1265.zip | F000000063 | C000000473 | 1 登記所備付地図データ 点、線、面データの例

PG-Stromと超大量データ 登記所備付地図データ 点-線-面データの例(注目列を抜粋) points curves_data surface xml_pid | x | y | ------------+------------+-----------+ P000000541 | -15402.457 | 24941.103 | P000000542 | -15407.726 | 24937.207 | P000000543 | -15412.905 | 24933.192 | P000000544 | -15417.984 | 24929.054 | P000000545 | -15422.967 | 24924.789 | curve_id | xml_pid | num ------------+------------+----- C000004229 | P000000110 | 1 C000004229 | P000002610 | 2 C000004230 | P000001373 | 1 C000004230 | P000001408 | 2 surface_id | curve_id | num ------------+------------+----- F000000062 | C000000469 | 1 F000000062 | C000000470 | 2 F000000062 | C000000471 | 3 F000000062 | C000000472 | 4 F000000063 | C000000473 | 1 F000000062 | POLYGON((140.10768708299807 35.8623759553336,140.10771663745726 35.86237950222092,140.10778125873233 （略）,140.10768708299807 35.8623759553336)) 得たい結果： 点ｰ線-面の積み上げにより面のポリゴン座標を得たい データ構造

PG-Stromと超大量データ • クエリ概要 千葉県我孫子市(12222)を対象 「公共座標」のみを抽出 点-線-面に基本情報(basics)を加えた4テーブルを使用 前ページの構造に従いIDを使って結合 平面直角座標系を地理座標系に変換 今回は9系への変換 最終的にポリゴンが構成される num | tbl | cnt -----+-----------------+--------------- 5 | moj_points | 1,556,071,844 2 | moj_curves_data | 6,455,800,708 6 | moj_surface | 3,194,536,648 1 | moj_basics | 316,300 参考：データ件数

PG-Stromと超大量データ WITH t1 AS ( SELECT b.ver, b.filename, MAX(city_name) city_name , MAX(coord) coord, MAX(s.num) surface_num, MAX(s.surface_id) surface_id, c.curve_id, MAX(CASE WHEN c.num=1 THEN c.xml_pid ELSE '' END) pid1, MAX(CASE WHEN c.num=2 THEN c.xml_pid ELSE '' END) pid2, MAX(CASE WHEN c.num=1 THEN p.x ELSE null END)::float p1x, MAX(CASE WHEN c.num=1 THEN p.y ELSE null END)::float p1y, MAX(CASE WHEN c.num=2 THEN p.x ELSE null END)::float p2x, MAX(CASE WHEN c.num=2 THEN p.y ELSE null END)::float p2y FROM moj_surface s LEFT OUTER JOIN moj_basics b ON (b.ver=s.ver AND b.filename=s.filename) LEFT OUTER JOIN moj_curves_data c ON (s.ver=c.ver AND s.filename=c.filename AND s.curve_id=c.curve_id ) LEFT OUTER JOIN moj_points p ON (b.ver=c.ver AND b.filename=p.filename AND p.xml_pid=c.xml_pid) WHERE b.filename LIKE '12222-%.zip' AND coord LIKE '公共座標%' GROUP BY b.ver, b.filename, c.curve_id ORDER BY ver, filename, surface_id, surface_num ), t2 AS( SELECT filename, surface_id, ST_MakeLine(ST_MakePoint(p1y,p1x)) g FROM t1 GROUP BY filename, surface_id ) SELECT filename, surface_id, ST_Transform(ST_SetSRID(ST_MakePolygon(ST_AddPoint(g,ST_StartPoint(g))), 6668+9), 6668) FROM t2; Query:

PG-Stromと超大量データ • 得られた結果件数 約4400 Polygons • PG-Stromを使わない素のPostgreSQLでは、3時間40分で結果を得られなかったので中断 した filename | surface_id | st_astext ---------------------+------------+------------------------------------------------------------------------------------------------ 12222-0424-1265.zip | F000000001 | POLYGON((140.10768708299807 35.8623759553336,140.10771663745726 35.86237950222092,140.10778125873233 35.86202309043169,140.1074876615119 35.86200930321591,140.107469067712 12222-0424-1265.zip | F000000002 | POLYGON((140.10881934551975 35.862082186144555,140.10875883806983 35.862086885655714,140.10869821611357 35.86209049477578,140.10863747831317 35.86209262593087,140.10857670 12222-0424-1265.zip | F000000003 | POLYGON((140.10730388459191 35.86209545801973,140.10746906771254 35.862114649358936,140.1074876615119 35.86200930321591,140.10741172337947 35.86200573590365,140.1073605579 12222-0424-1265.zip | F000000004 | POLYGON((140.10782966239697 35.86183496904813,140.1078239837028 35.86181937980182,140.10781692226777 35.86180418129098,140.1078088432409 35.86178930057391,140.107799658476 12222-0424-1265.zip | F000000005 | POLYGON((140.10786214492612 35.86133905787694,140.10782198356955 35.86131633668292,140.10778081864078 35.861294780498305,140.10777241140715 35.8612906715582,140.1076900701 12222-0424-1265.zip | F000000006 | POLYGON((140.1077476547699 35.861609226133645,140.10776428149174 35.86162077940842,140.10779716037655 35.86164443662493,140.10780678528442 35.86165165240926,140.1078091949 : CPU(PostgreSQL) ： ∞ GPU(PG-Strom) ： 58秒 Result:

PG-Stromと超大量データ まとめ このように 大量のデータ 複雑なクエリ でも PG-Stromは高速です！

まとめ： PG-Strom • GPUを活用した爆速DB • 大量のデータを分析したい場合 • 位置情報の抽出を行いたい場合 • Arrow: カラム数が多いデータに悩んでいる場合 • 複雑な処理を速くしたい場合 マッチする場合としない場合がありますので 一緒に検証していければと思います。 お声がけください。

広報：データベースで位置情報を扱おう https://bakusokudb.connpass.com 「爆速DB勉強会」やっています。 次回は12月16日(月) ＠渋谷 「Apache Arrow」がテーマです！ ぜひご参加ください。 データベースで地理情報を扱う アドベントカレンダー、 RDBMS-GIS、今年もやっています。 ぜひご参加ください。 https://qiita.com/advent-calendar/2024/rdbms_gis

爆速DB Powered by PG-Strom 本日、懇親会まで参加しております。 興味を持った方はぜひお声がけください。 GPUパワーをフルパワー