dayjournal memo

Total 1006 articles!!

wherobots

Wherobots #003 - Overture Mapsのデータを空間検索

Yasunori Kirimoto's avatar
Yasunori Kirimoto 2024/12/10

img



Overture Mapsのデータを空間検索するメモ。



WherobotsDBを利用

WherobotsDBを利用するために「SedonaContext」オブジェクトを作成します。

from sedona.spark import *

config = SedonaContext.builder().getOrCreate()
sedona = SedonaContext.create(config)

Wherobots Spatial CatalogのOpen Data Catalogを確認

Open Data Catalogでは、Overture MapsやFoursquareのデータをプリセットで利用できます。

Open Data Catalogの一覧を表示します。

sedona.sql("SHOW SCHEMAS IN wherobots_open_data").show()
+--------------------+
|           namespace|
+--------------------+
|            overture|
| overture_2024_02_15|
| overture_2024_05_16|
| overture_2024_07_22|
|overture_2024_01_...|
|overture_2024_05_...|
|overture_2023_07_...|
|overture_2024_06_...|
|overture_2024_09_...|
|overture_2024_08_...|
|overture_2024_10_...|
|overture_2024_03_...|
|overture_2024_04_...|
|overture_2023_10_...|
|overture_2023_11_...|
|overture_2024_06_...|
|overture_2024_07_...|
|overture_2024_02_...|
|overture_2023_12_...|
|foursquare_2024_1...|
+--------------------+
only showing top 20 rows

Overture Mapsデータベースのテーブルを表示します。

sedona.sql("SHOW tables IN wherobots_open_data.overture").show(truncate=False)
+---------+-----------------------------+-----------+
|namespace|tableName                    |isTemporary|
+---------+-----------------------------+-----------+
|overture |admins_administrativeBoundary|false      |
|overture |admins_locality              |false      |
|overture |buildings_building           |false      |
|overture |places_place                 |false      |
|overture |transportation_connector     |false      |
|overture |transportation_segment       |false      |
+---------+-----------------------------+-----------+

Overture Mapsの「places_place」テーブルのスキーマを表示します。

sedona.table("wherobots_open_data.overture.places_place").printSchema()
root
 |-- id: string (nullable = true)
 |-- updatetime: string (nullable = true)
 |-- version: integer (nullable = true)
 |-- names: map (nullable = true)
 |    |-- key: string
 |    |-- value: array (valueContainsNull = true)
 |    |    |-- element: map (containsNull = true)
 |    |    |    |-- key: string
 |    |    |    |-- value: string (valueContainsNull = true)
 |-- categories: struct (nullable = true)
 |    |-- main: string (nullable = true)
 |    |-- alternate: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |-- confidence: double (nullable = true)
 |-- websites: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- socials: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- emails: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- phones: array (nullable = true)
 |    |-- element: string (containsNull = true)
 |-- brand: struct (nullable = true)
 |    |-- names: map (nullable = true)
 |    |    |-- key: string
 |    |    |-- value: array (valueContainsNull = true)
 |    |    |    |-- element: map (containsNull = true)
 |    |    |    |    |-- key: string
 |    |    |    |    |-- value: string (valueContainsNull = true)
 |    |-- wikidata: string (nullable = true)
 |-- addresses: array (nullable = true)
 |    |-- element: map (containsNull = true)
 |    |    |-- key: string
 |    |    |-- value: string (valueContainsNull = true)
 |-- sources: array (nullable = true)
 |    |-- element: map (containsNull = true)
 |    |    |-- key: string
 |    |    |-- value: string (valueContainsNull = true)
 |-- bbox: struct (nullable = true)
 |    |-- minx: double (nullable = true)
 |    |-- maxx: double (nullable = true)
 |    |-- miny: double (nullable = true)
 |    |-- maxy: double (nullable = true)
 |-- geometry: geometry (nullable = true)
 |-- geohash: string (nullable = true)

Spatial SQLで空間検索

Spatial SQLを利用することでさまざまな空間検索が実行できます。

Overture Mapsの「places_place」テーブルから「places」ビューを作成します。

sedona.table("wherobots_open_data.overture.places_place").createOrReplaceTempView("places")

名前・カテゴリー・ポイント座標の属性とポイントデータを取得します。

sedona.sql("SELECT categories.main AS category, names.common[0].value AS name, geometry FROM places LIMIT 20").show(truncate=False)
+---------------------+----------------------------------+------------------------------+
|category             |name                              |geometry                      |
+---------------------+----------------------------------+------------------------------+
|taiwanese_restaurant |台湾料理四季紅                    |POINT (136.885079 35.343057)  |
|japanese_restaurant  |いろ川                            |POINT (139.848961 35.739373)  |
|farm                 |株式会社阿部農園                  |POINT (139.0049956 37.7329319)|
|train_station        |岩波駅                            |POINT (138.919082 35.215899)  |
|japanese_restaurant  |甲南そば                          |POINT (135.27482 34.728534)   |
|restaurant           |Cuatro                            |POINT (138.8529754 35.1067167)|
|smoothie_juice_bar   |ゴクゴク　横浜ワールドポーターズ店|POINT (139.6385154 35.4541338)|
|hotel                |Curation Hotel                    |POINT (139.0785116 35.1066403)|
|asian_restaurant     |集来軒                            |POINT (135.470227 34.717611)  |
|professional_services|株式会社CDF                       |POINT (135.1936619 34.6962007)|
|public_plaza         |深見歴史の森スポーツ広場          |POINT (139.464748 35.491567)  |
|pet_store            |うさぎ専門店ちゅらうさぎ          |POINT (138.2966425 34.8537383)|
|japanese_restaurant  |無添くら寿司横浜長津田店          |POINT (139.497489 35.521179)  |
|health_and_medical   |大滝漢方堂                        |POINT (136.5 36.05)           |
|japanese_restaurant  |焼き鳥酒場　ちょりちょり          |POINT (139.5350146 36.0305335)|
|eat_and_drink        |味のじゅん天                      |POINT (140.386382 37.399886)  |
|japanese_restaurant  |まご茶亭                          |POINT (139.071774 35.09486)   |
|gym                  |スマートフィット100大塚店         |POINT (140.408632 36.383624)  |
|NULL                 |法隆寺駅                          |POINT (135.739107 34.601617)  |
|bar                  |布施酒場かい                      |POINT (135.564394 34.663048)  |
+---------------------+----------------------------------+------------------------------+

指定ポイントから半径10km以内にある「hiking_trail」を取得します。

sedona.sql("""
SELECT names.common[0].value AS name, categories.main AS category, geometry FROM places WHERE ST_DistanceSphere(ST_GeomFromWKT('POINT (139.7645 35.6811)'), geometry) < 10000 AND categories.main = 'hiking_trail' LIMIT 20
""").show(truncate=False)
+-----------------------------+------------+------------------------------+
|name                         |category    |geometry                      |
+-----------------------------+------------+------------------------------+
|玉川上水旧水路幡ヶ谷緑道     |hiking_trail|POINT (139.678356 35.676208)  |
|向日葵の小径                 |hiking_trail|POINT (139.756216 35.674825)  |
|荒木坂                       |hiking_trail|POINT (139.7393195 35.7115361)|
|目黒川緑道池尻大橋駅側入口   |hiking_trail|POINT (139.6854 35.651325)    |
|芝浦アイランド 遊歩道        |hiking_trail|POINT (139.750809 35.63663)   |
|大塚バラロード               |hiking_trail|POINT (139.72557 35.729693)   |
|解剖坂                       |hiking_trail|POINT (139.759613 35.721601)  |
|足立の平成五色桜             |hiking_trail|POINT (139.770207 35.759781)  |
|芸術の散歩道                 |hiking_trail|POINT (139.77332 35.7163)     |
|玉川上水旧水路世田谷緑道     |hiking_trail|POINT (139.662694 35.670916)  |
|三段坂                       |hiking_trail|POINT (139.769306 35.718932)  |
|飛鳥大坂                     |hiking_trail|POINT (139.737485 35.750156)  |
|目黒川東海禅寺裏遊歩道       |hiking_trail|POINT (139.739045 35.615981)  |
|山王男坂                     |hiking_trail|POINT (139.740707 35.674686)  |
|レインボープロムナード 台場口|hiking_trail|POINT (139.775662 35.635127)  |
|大横川の桜並木               |hiking_trail|POINT (139.794909 35.671448)  |
|白鷺坂                       |hiking_trail|POINT (139.736717 35.723212)  |
|三平坂                       |hiking_trail|POINT (139.731583 35.758)     |
|玉川上水旧水路初台緑道       |hiking_trail|POINT (139.687152 35.680788)  |
|レインボープロムナード 芝浦口|hiking_trail|POINT (139.759193 35.637868)  |
+-----------------------------+------------+------------------------------+

空間検索結果のDataFrameを作成します。

trails_df = sedona.sql("""
SELECT names.common[0].value AS name, categories.main AS category, geometry FROM places WHERE ST_DistanceSphere(ST_GeomFromWKT('POINT (139.7645 35.6811)'), geometry) < 10000 AND categories.main = 'hiking_trail'
""")

空間検索結果を可視化

SedonaKeplerやSedonaPyDeckを利用し空間検索結果を可視化できます。

SedonaKeplerで空間検索結果を可視化します。

SedonaKepler.create_map(trails_df, "Hiking Trails")

img




book

Q&A