使用 SingleStore 作为地理空间数据库

2022-04-08 00:00:00 数据 文件 复制 所示 伦敦

摘要
SingleStore 是一个多模型数据库系统。除了关系数据,它还支持键值、JSON、全文搜索、地理空间和时间序列。

此前的一篇文章展示了 SingleStore 管理时间序列数据的能力,而在本文中,我们将探索地理空间数据。我们使用伦敦行政区和伦敦地铁的数据,用它们的数据集执行一系列地理空间查询,以测试 SingleStore 处理地理空间数据的能力。此外,我们还将讨论一个伦敦地铁数据的实际用例,即查找网络中两点之间的短路径。后,使用 Folium 和 Streamlit 创建伦敦地铁的可视化。

本文中使用的 SQL 脚本、Python 代码和笔记本文件可在GitHub 上获得,支持DBC、HTML 和 iPython 格式。

介绍
在此前的文章中,我们指出了使用 Polyglot Persistence 来管理各种数据和处理需求的问题,此外还讨论了 SingleStore 如何通过业务和技术优势成为时间序列数据的出色解决方案。本文将重点介绍地理空间数据,以及 SingleStore 如何提供统一的方法来存储和查询字母数字及地理空间数据。

首先,我们需要在 SingleStore 网站上创建一个免费的托管服务帐户,并在 Databricks 网站上创建一个免费的社区版(CE)帐户。在撰写本文时,SingleStore 的托管服务帐户附带 500 美元的积分,这对于本文中描述的案例研究来说绰绰有余。对于 Databricks CE,我们需要注册免费帐户而不是试用版。我们使用 Spark 是因为,如前一篇文章所述,Spark 非常适合使用 SingleStore 进行 ETL。

伦敦行政区的数据可以从London Datastore下载。我们使用的文件是statistics-gis-boundaries-london.zip,该文件大小为 27.34 MB。此外,需要对提供的数据进行一些转换,以便与 SingleStore 一起使用,接下来会对此简要说明。

伦敦地铁的数据可以从Wikimedia获得。它以CSV格式提供车站、路线和线路定义。该数据集虽被广泛使用,却落后于伦敦地铁的新发展。但是,它足以满足我们的需求,并在未来很容易更新。

也可以在GitHub上找到伦敦地铁数据集的一个版本,其在路线中添加了额外的 time 列。这有助于查找短路径,我们稍后讨论。

可以从本文的GitHub页面下载一组更新的伦敦地铁 CSV 文件。

总结一下:

1. 从London Datastore下载zip 文件。

2. 从本文的GitHub页面下载三个伦敦地铁 CSV 文件。

配置 Databricks CE
此前的文章给出了有关如何配置 Databricks CE 以和 SingleStore 一起使用的详细说明,在这个用例中我们可以借助它们。如图 1 所示,除了 SingleStore Spark Connector 和 MariaDB Java Client jar 文件外,还需要使用 PyPI 添加GeoPandas和Folium。


图 1. 库

上传 CSV 文件

要使用三个伦敦地铁 CSV 文件,需要将它们上传到 Databricks CE 环境。上一篇文章提供了如何上传 CSV 文件的详细说明。我们可以在这个用例中使用这些确切的说明。

伦敦行政区数据
转换伦敦行政区数据

解压下载的zip文件。其中有两个文件夹:ESRI和MapInfo。在 ESRI 文件夹中,我们只关心以London_Borough_Excluding_MHW开头的文件。有不同的文件扩展名,如图 2 所示。

图 2. ESRI 文件夹

我们需要为 SingleStore把这些文件中的数据转换为已知文本 (WKT)格式。为此,我们可以按照 SingleStore 网站上加载地理空间数据到 SingleStore文章的建议。

步是使用MyGeodata Converter工具。可以拖放文件或浏览文件进行转换,如图3所示。

图 3. 添加文件

添加图 2 中高亮的全部九个文件,如图 4 所示。接下来,单击Continue 按钮。

图 4. 添加文件并继续

在下一页中,需要核实输出格式是WKT,坐标系是WGS 84, 然后点击 Convert now! 按钮,如图 5 所示。

图 5. 转换选项

可以下载转换结果,如图6所示。

图 6. 下载转换结果

这会下载一个 zip 文件,其中含有一个名为London_Borough_Excluding_MHW.csv的 CSV 文件。该文件包含一个标题行和 33 行数据。名为 WKT的列,有 30 行POLYGON数据,有 3 行MULTIPOLYGON数据。我们需要将MULTIPOLYGON数据转成POLYGON数据。使用 GeoPandas 可以很快实现。

接下来,我们也要将此 CSV 文件上传到 Databricks CE。

创建伦敦行政区数据库表

在我们的 SingleStore 托管服务帐户中,使用 SQL 编辑器创建一个新数据库,名为geo_db,如下:
SQL
CREATE DATABASE IF NOT EXISTS geo_db;
.
还要创建一个表,如下:


SQL
USE geo_db;

CREATE ROWSTORE TABLE IF NOT EXISTS london_boroughs (
name VARCHAR(32),
hectares FLOAT,
geometry GEOGRAPHY,
centroid GEOGRAPHYPOINT,
INDEX(geometry)
);
SingleStore 可以存储三种主要的地理空间类型:多边形、路径和点。在上表中,GEOGRAPHY可以保存多边形和路径数据。GEOGRAPHYPOINT可以保存点数据。在我们的示例中,geometry列保存每个伦敦行政区的形状,centroid列保存每个行政区的大致中心点。如上所示,可以将此地理空间数据与其他数据类型(例如VARCHAR和FLOAT)一起存储。


伦敦行政区数据加载器

现在新建一个 Databricks CE Python 笔记本,命名为Data Loader for London Boroughs。把新笔记本附加到 Spark 集群上。

在一个新代码单元中,添加以下代码以导入几个库:

Python
import pandas as pd
import geopandas as gpd

from pyspark.sql.* import *
from shapely import wkt

接下来,定义模式:

Python
geo_schema = StructType([
StructField("geometry", StringType(), True),
StructField("name", StringType(), True),
StructField("gss_code", StringType(), True),
StructField("hectares", DoubleType(), True),
StructField("nonld_area", DoubleType(), True),
StructField("ons_inner", StringType(), True),
StructField("sub_2009", StringType(), True),
StructField("sub_2006", StringType(), True)
])

现在使用定义的模式读取 CSV:

Python
boroughs_df = spark.read.csv("/FileStore/London_Borough_Excluding_MHW.csv",
header = True,
schema = geo_schema)
.
删除一些列:


Python
boroughs_df = boroughs_df.drop("gss_code", "nonld_area", "ons_inner", "sub_2009", "sub_2006")
现在我们浏览一下数据结构和内容:

Python
boroughs_df.show(33)
输出应如下所示:


Plain Text
+--------------------+--------------------+---------+
| geometry| name| hectares|
+--------------------+--------------------+---------+
|POLYGON ((-0.3306...|Kingston upon Thames| 3726.117|
|POLYGON ((-0.0640...| Croydon| 8649.441|
|POLYGON ((0.01213...| Bromley|15013.487|
|POLYGON ((-0.2445...| Hounslow| 5658.541|
|POLYGON ((-0.4118...| Ealing| 5554.428|
|POLYGON ((0.15869...| Havering|11445.735|
|POLYGON ((-0.4040...| Hillingdon|11570.063|
|POLYGON ((-0.4040...| Harrow| 5046.33|
|POLYGON ((-0.1965...| Brent| 4323.27|
|POLYGON ((-0.1998...| Barnet| 8674.837|
|POLYGON ((-0.1284...| Lambeth| 2724.94|
|POLYGON ((-0.1089...| Southwark| 2991.34|
|POLYGON ((-0.0324...| Lewisham| 3531.706|
|MULTIPOLYGON (((-...| Greenwich| 5044.19|
|POLYGON ((0.12021...| Bexley| 6428.649|
|POLYGON ((-0.1058...| Enfield| 8220.025|
|POLYGON ((0.01924...| Waltham Forest| 3880.793|
|POLYGON ((0.06936...| Redbridge| 5644.225|
|POLYGON ((-0.1565...| Sutton| 4384.698|
|POLYGON ((-0.3217...|Richmond upon Thames| 5876.111|
|POLYGON ((-0.1343...| Merton| 3762.466|
|POLYGON ((-0.2234...| Wandsworth| 3522.022|
|POLYGON ((-0.2445...|Hammersmith and F...| 1715.409|
|POLYGON ((-0.1838...|Kensington and Ch...| 1238.379|
|POLYGON ((-0.1500...| Westminster| 2203.005|
|POLYGON ((-0.1424...| Camden| 2178.932|
|POLYGON ((-0.0793...| Tower Hamlets| 2157.501|
|POLYGON ((-0.1383...| Islington| 1485.664|
|POLYGON ((-0.0976...| Hackney| 1904.902|
|POLYGON ((-0.0976...| Haringey| 2959.837|
|MULTIPOLYGON (((0...| Newham| 3857.806|
|MULTIPOLYGON (((0...|Barking and Dagenham| 3779.934|
|POLYGON ((-0.1115...| City of London| 314.942|
+--------------------+--------------------+---------+
需要将MULTIPOLYGON行转换为POLYGON,因此,先建一个 Pandas DataFrame:

Python
boroughs_pandas_df = boroughs_df.toPandas()
然后使用 wkt.loads将geometry列从字符串转为多边形:

Python
boroughs_pandas_df["geometry"] = boroughs_pandas_df["geometry"].apply(wkt.loads)
现在转换为GeoDataFrame:

Python
boroughs_geo_df = gpd.GeoDataFrame(boroughs_pandas_df, geometry = "geometry")
这样就可以使用explode()将MULTIPOLYGON变更为POLYGON:

Python
boroughs_geo_df = boroughs_geo_df.explode(column = "geometry", index_parts = False)
如果查看 DataFrame 的结构:

Python
boroughs_geo_df
现在应该看不到任何MULTIPOLYGON行。

可以绘制伦敦行政区的地图,如下所示:

Python
map = boroughs_geo_df.plot(column = "hectares", cmap = "OrRd", legend = True)
map.set_axis_off()
应该会呈现图 7 中所示的图像。

图 7. 伦敦行政区

此时,由于正在渲染地图,因此需要添加以下内容:

“Contains National Statistics data © Crown copyright and database right [2015]” and “Contains Ordnance Survey data © Crown copyright and database right [2015]”

也可以添加一个新列,存储每个行政区的中心:

复制

Python
boroughs_geo_df = boroughs_geo_df.assign(centroid = boroughs_geo_df["geometry"].centroid)
获取GeoDataFrame信息:

复制

Python
boroughs_geo_df.info()
然后产生以下输出:

复制

Plain Text
<class 'geopandas.geodataframe.GeoDataFrame'>
Int64Index: 36 entries, 0 to 32
Data columns (total 4 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 name 36 non-null object
1 hectares 36 non-null float64
2 geometry 36 non-null geometry
3 centroid 36 non-null geometry
d*: float64(1), geometry(2), object(1)
memory usage: 1.4+ KB
从输出中,我们可以看到包含地理空间数据的两列 (geometry和centroid)。这两列需要使用wkt.dumps转回字符串以便 Spark 可以将数据正确写入 SingleStore:

复制

Python
boroughs_geo_df["geometry"] = boroughs_geo_df["geometry"].apply(wkt.dumps)
boroughs_geo_df["centroid"] = boroughs_geo_df["centroid"].apply(wkt.dumps)
首先,需要转换回到 Spark DataFrame:

复制

Python
boroughs_df = spark.createDataFrame(boroughs_geo_df)
现在,建立到 SingleStore 的连接:

复制

Python
%run ./Setup
在Setup 笔记本中,需要确保已为 SingleStore 托管服务集群添加了服务器地址和密码。

在下一个代码单元中,为 SingleStore Spark 连接器设置一些参数,如下所示:

复制

Python
spark.conf.set("spark.datasource.singlestore.ddlEndpoint", cluster)
spark.conf.set("spark.datasource.singlestore.user", "admin")
spark.conf.set("spark.datasource.singlestore.password", password)
spark.conf.set("spark.datasource.singlestore.disablePushdown", "false")
.
后,准备使用 Spark 连接器将 DataFrame 写入 SingleStore:

复制

Python
(boroughs_df.write
.format("singlestore")
.option("loadDataCompression", "LZ4")
.mode("ignore")
.save("geo_db.london_boroughs"))
这会将 DataFrame 写入geo_db数据库中的london_boroughs表中。可以从 SingleStore检查该表是否已成功填充。

伦敦地铁数据
创建伦敦地铁数据库表

现在需要关注伦敦地铁数据了。在 SingleStore 托管服务帐户中,使用 SQL 编辑器创建几个数据库表,如下所示:

复制

SQL
USE geo_db;

CREATE ROWSTORE TABLE IF NOT EXISTS london_connections (
station1 INT,
station2 INT,
line INT,
time INT,
PRIMARY KEY(station1, station2, line)
);

CREATE ROWSTORE TABLE IF NOT EXISTS london_lines (
line INT PRIMARY KEY,
name VARCHAR(32),
colour VARCHAR(8),
stripe VARCHAR(8)
);

CREATE ROWSTORE TABLE IF NOT EXISTS london_stations (
id INT PRIMARY KEY,
latitude DOUBLE,
longitude DOUBLE,
name VARCHAR(32),
zone FLOAT,
total_lines INT,
rail INT,
geometry AS GEOGRAPHY_POINT(longitude, latitude) PERSISTED GEOGRAPHYPOINT,
INDEX(geometry)
);
有三张表。london_connections表包含由特定线路连接的站点对。稍后,使用time列来确定短路径。

london_lines表中每一行有一个标识符,以及线路名称和颜色等信息。

london_stations表包含每个站点的信息,例如其经纬度。当我们将数据上传到该表中时,SingleStore 会为我们创建并填充geometry列。这是一个由经度和纬度组成的地理空间点。当我们想开始进行地理空间查询时,这将非常有用。稍后我们会使用此功能。

伦敦地铁数据加载器

由于我们已经有了三张表的正确格式的 CSV 文件,因此将数据加载到 SingleStore 很容易。现在新建一个 Databricks CE Python 笔记本,命名为Data Loader for London Underground。把新笔记本附加到 Spark 集群上。

在一个新代码单元中,添加以下代码:

复制

Python
connections_df = spark.read.csv("/FileStore/london_connections.csv",
header = True,
inferSchema = True)
这会加载connections数据。对线路重复此操作:

复制

Python
lines_df = spark.read.csv("/FileStore/london_lines.csv",
header = True,
inferSchema = True)
和站点:

复制

Python
stations_df = spark.read.csv("/FileStore/london_stations.csv",
header = True,
inferSchema = True)
由于我们不需要display_name列,因此将它删除:

复制

Python
stations_df = stations_df.drop("display_name")
现在,建立到 SingleStore 的连接:

复制

Python
%run ./Setup
1.
在下一个代码单元中,为 SingleStore Spark 连接器设置一些参数,如下所示:

复制

Python
spark.conf.set("spark.datasource.singlestore.ddlEndpoint", cluster)
spark.conf.set("spark.datasource.singlestore.user", "admin")
spark.conf.set("spark.datasource.singlestore.password", password)
spark.conf.set("spark.datasource.singlestore.disablePushdown", "false")
1.
后,准备使用 Spark 连接器将 DataFrame 写入 SingleStore:

复制

Python
(connections_df.write
.format("singlestore")
.option("loadDataCompression", "LZ4")
.mode("ignore")
.save("geo_db.london_connections"))
这会将 DataFrame 写入geo_db数据库的london_connections表中。对线路重复此操作:

复制

Python
(lines_df.write
.format("singlestore")
.option("loadDataCompression", "LZ4")
.mode("ignore")
.save("geo_db.london_lines"))
还有站点:

复制

Python
(stations_df.write
.format("singlestore")
.option("loadDataCompression", "LZ4")
.mode("ignore")
.save("geo_db.london_stations"))
可以从 SingleStore检查这些表是否已成功填充。

示例查询
现在我们已经构建了系统,可以运行一些查询了。SingleStore 支持一系列非常有用的功能来处理地理空间数据。图 8 展示了这些函数,我们通过示例运行每个函数。

图 8. 地理空间函数

面积 (GEOGRAPHY_AREA)

这部分测量多边形的平方米面积。

我们以平方米为单位查找一个伦敦行政区的面积。在这个例子中使用 Merton:

复制

SQL
SELECT ROUND(GEOGRAPHY_AREA(geometry), 0) AS sqm
FROM london_boroughs
WHERE name = "Merton";
输出应该是:

复制

Plain Text
+---------------+
| sqm |
+---------------+
| 3.745656182E7 |
+---------------+
由于我们已经为每个行政区存储了公顷数,因此可以将结果与公顷数进行比较,同时这些数字是很接近的。数字没有完美匹配,是因为行政区多边形数据存储的点数量有限,因此计算的面积会有所不同。如果我们存储更多的数据点,准确性就会提高。

距离 (GEOGRAPHY_DISTANCE)

这部分以米为单位,测量两个地理空间对象之间的短距离。该函数使用球体上距离的标准度量。

我们可以查询每个伦敦行政区与特定行政区间的距离。在这个例子中使用 Merton:

复制

SQL
SELECT b.name AS neighbour, ROUND(GEOGRAPHY_DISTANCE(a.geometry, b.geometry), 0) AS distance_from_border
FROM london_boroughs a, london_boroughs b
WHERE a.name = "Merton"
ORDER BY distance_from_border
LIMIT 10;
输出应该是:

复制

Plain Text
+------------------------+----------------------+
| neighbour | distance_from_border |
+------------------------+----------------------+
| Lambeth | 0.0 |
| Kingston upon Thames | 0.0 |
| Merton | 0.0 |
| Wandsworth | 0.0 |
| Sutton | 0.0 |
| Croydon | 0.0 |
| Richmond upon Thames | 552.0 |
| Hammersmith and Fulham | 2609.0 |
| Bromley | 3263.0 |
| Southwark | 3276.0 |
+------------------------+----------------------+
长度 (GEOGRAPHY_LENGTH)

这部分测量路径的长度。路径也可以是多边形的总周长,该测量以米为单位。

这里我们计算伦敦各行政区的周长,并将结果按长优先排序。

复制

SQL
SELECT name, ROUND(GEOGRAPHY_LENGTH(geometry), 0) AS perimeter
FROM london_boroughs
ORDER BY perimeter DESC
LIMIT 5;
输出应该是:

复制

Plain Text
+----------------------+-----------+
| name | perimeter |
+----------------------+-----------+
| Bromley | 76001.0 |
| Richmond upon Thames | 65102.0 |
| Hillingdon | 63756.0 |
| Havering | 63412.0 |
| Hounslow | 58861.0 |
+----------------------+-----------+

包含 (GEOGRAPHY_CONTAINS)

这部分确定一个对象是否完全在另一个对象内。

在这个例子中,我们试着找出Merton内的所有伦敦地铁站:

复制

SQL
SELECT b.name
FROM london_boroughs a, london_stations b
WHERE GEOGRAPHY_CONTAINS(a.geometry, b.geometry) AND a.name = "Merton"
ORDER BY name;
输出应该是:

复制

Textile
+-----------------+
| name |
+-----------------+
| Colliers Wood |
| Morden |
| South Wimbledon |
| Wimbledon |
| Wimbledon Park |
+-----------------+
相交 (GEOGRAPHY_INTERSECTS )

这部分确定两个地理空间对象之间是否有任何相交。

在此示例中,我们试着确定伦敦的哪个行政区与Morden 站相交:

复制

SQL
SELECT a.name
FROM london_boroughs a, london_stations b
WHERE GEOGRAPHY_INTERSECTS(b.geometry, a.geometry) AND b.name = "Morden";
1
输出应该是:

复制

Plain Text
+--------+
| name |
+--------+
| Merton |
+--------+
近似相交 (APPROX_GEOGRAPHY_INTERSECTS)

这部分是前一个函数的快速近似。

复制

SQL
SELECT a.name
FROM london_boroughs a, london_stations b
WHERE APPROX_GEOGRAPHY_INTERSECTS(b.geometry, a.geometry) AND b.name = "Morden";
输出应该是:

复制

Plain Text
+--------+
| name |
+--------+
| Merton |
+--------+
距离内 (GEOGRAPHY_WITHIN_DISTANCE)

这部分确定两个地理空间对象是否在一定距离内,测量以米为单位。

在下面的示例中,我们尝试查找距中心 100 米范围内的任何伦敦地铁站。

复制

SQL
SELECT a.name
FROM london_stations a, london_boroughs b
WHERE GEOGRAPHY_WITHIN_DISTANCE(a.geometry, b.centroid, 100)
ORDER BY name;
输出应该是:

复制

Plain Text
+------------------------+
| name |
+------------------------+
| High Street Kensington |
+------------------------+
可视化
伦敦地铁地图

我们的 SingleStore 数据库中存储了地理空间数据,我们可以使用这些数据创建可视化。首先,创建一个伦敦地铁网络的图表。

从新建一个 Databricks CE Python 笔记本开始,名为Shortest Path。把新笔记本附加到 Spark 集群上。

在新的代码单元中,添加以下代码导入几个库:

复制

Python
import pandas as pd
import networkx as nx
import matplotlib.pyplot as plt
import folium

from folium import plugins
现在,建立到 SingleStore 的连接:

复制

Python
%run ./Setup
在下一代码单元中,为 SingleStore Spark 连接器设置一些参数,如下所示:

复制

Python
spark.conf.set("spark.datasource.singlestore.ddlEndpoint", cluster)
spark.conf.set("spark.datasource.singlestore.user", "admin")
spark.conf.set("spark.datasource.singlestore.password", password)
spark.conf.set("spark.datasource.singlestore.disablePushdown", "false")
把数据从三张伦敦地铁表读到 Spark DataFrames 中,然后将其转成 Pandas:

复制

Python
df1 = (spark.read
.format("singlestore")
.load("geo_db.london_connections"))

connections_df = df1.toPandas()

df2 = (spark.read
.format("singlestore")
.load("geo_db.london_lines"))

lines_df = df2.toPandas()

df3 = (spark.read
.format("singlestore")
.load("geo_db.london_stations"))

stations_df = df3.toPandas()
接下来,使用NetworkX构建一张图。以下代码的灵感来自GitHub 上的一个示例。该代码创建节点和边来表示站点及它们间的连接:

复制

Python
graph = nx.Graph()

for station_id, station in stations_df.iterrows():
graph.add_node(station["name"],
lon = station["longitude"],
lat = station["latitude"],
s_id = station["id"])

for connection_id, connection in connections_df.iterrows():
station1_name = stations_df.loc[stations_df["id"] == connection["station1"], "name"].item()
station2_name = stations_df.loc[stations_df["id"] == connection["station2"], "name"].item()
graph.add_edge(station1_name,
station2_name,
time = connection["time"],
line = connection["line"])
可以检查节点和边的数量,如下:

复制

Python
len(graph.nodes()), len(graph.edges())
输出应该是:

复制

Plain Text
(302, 349)
接下来,获取节点位置。以下代码的灵感来自DataCamp上的一个示例。

复制

Python
node_positions = {node[0]: (node[1]["lon"], node[1]["lat"]) for node in graph.nodes(data = True)}
可以检查这些值:

复制

Python
dict(list(node_positions.items())[0:5])
.
输出应类似于:

复制

Plain Text
{'Aldgate': (-0.0755, 51.5143),
'All Saints': (-0.013, 51.5107),
'Alperton': (-0.2997, 51.5407),
'Angel': (-0.1058, 51.5322),
'Archway': (-0.1353, 51.5653)}
现在获取连接站点的线路:

复制

Python
edge_lines = [edge[2]["line"] for edge in graph.edges(data = True)]
可以查看这些值:

复制

Python
edge_lines[0:5]
输出应类似于:

复制

Plain Text
[8, 3, 13, 13, 10]
从这些信息中,可以查找线条颜色:

复制

Python
edge_colours = [lines_df.loc[lines_df["line"] == line, "colour"].iloc[0] for line in edge_lines]
可以查看这些值:

复制

Python
edge_colours[0:5]
输出应类似于:

复制

Plain Text
['#9B0056', '#FFD300', '#00A4A7', '#00A4A7', '#003688']
现在可以进行绘制,如下所示:

复制

Python
plt.figure(figsize = (12, 12))
nx.draw(graph,
pos = node_positions,
edge_color = edge_colours,
node_size = 20,
node_color = "black",
width = 3)
plt.title("Map of the London Underground", size = 20)
plt.show()
.
这会创建图 9 中所示的图像。

图 9. 伦敦地铁地图

也可以将图表示为 DataFrame。以下代码的灵感来自GitHub 上的一个示例。

复制

Python
network_df = pd.DataFrame()

lons, lats = map(nx.get_node_attributes, [graph, graph], ["lon", "lat"])
lines, times = map(nx.get_edge_attributes, [graph, graph], ["line", "time"])

for edge in list(graph.edges()):
network_df = network_df.append(
{"station_from" : edge[0],
"lon_from" : lons.get(edge[0]),
"lat_from" : lats.get(edge[0]),
"station_to" : edge[1],
"lon_to" : lons.get(edge[1]),
"lat_to" : lats.get(edge[1]),
"line" : lines.get(edge),
"time" : times.get(edge)
}, ignore_index = True)
.
如果现在将此 DataFrame 与伦敦地铁线路合并,就能为我们提供站点、坐标和站点之间线路的完整图片。

复制

Python
network_df = pd.merge(network_df, lines_df, how = "left", on = "line")
.
如果愿意,现在可以将其存回 SingleStore 以供将来使用。也可以使用 Folium 将其可视化,如下所示:

复制

Python
London = [51.509865, -0.118092]

m = folium.Map(location = London, tiles = "Stamen Terrain", zoom_start = 12)

for i in range(0, len(stations_df)):
folium.Marker(
location = [stations_df.iloc[i]["latitude"], stations_df.iloc[i]["longitude"]],
popup = stations_df.iloc[i]["name"],
).add_to(m)

for i in range(0, len(network_df)):
folium.PolyLine(
locations = [(network_df.iloc[i]["lat_from"], network_df.iloc[i]["lon_from"]),
(network_df.iloc[i]["lat_to"], network_df.iloc[i]["lon_to"])],
color = network_df.iloc[i]["colour"],
weight = 3,
opacity = 1).add_to(m)
plugins.Fullscreen(
position = "topright",
title = "Fullscreen",
title_cancel = "Exit",
force_separate_button = True).add_to(m)
m
这将生成一张地图,如图 10 所示。可以滚动和缩放地图。单击时,一个标记将显示车站名称,并根据伦敦地铁方案对线路进行着色。

图 10. 使用Folium的地图

短路径

还可以将图表用于更实际的用途。例如,查找两个站点之间的短路径。

可以使用 NetworkX 内置的shortest_path功能,我们这里期望从Oxford Circus到Canary Wharf的旅行:

复制

Python
shortest_path = nx.shortest_path(graph, "Oxford Circus", "Canary Wharf", weight = "time")
可以查看路线:

复制

Python
shortest_path
输出应该是:

复制

Plain Text
['Oxford Circus',
'Tottenham Court Road',
'Holborn',
'Chancery Lane',
"St. Paul's",
'Bank',
'Shadwell',
'Wapping',
'Rotherhithe',
'Canada Water',
'Canary Wharf']
为了可视化路线,可以将其转换成 DataFrame:

复制

Python
shortest_path_df = pd.DataFrame({"name" : shortest_path})
.
然后将它与站点的数据合并,这样就可以得到地理空间数据:

复制

Python
merged_df = pd.merge(shortest_path_df, stations_df, how = "left", on = "name")
现在可以使用 Folium 创建地图,如下所示:

复制

Python
m = folium.Map(tiles = "Stamen Terrain")

sw = merged_df[["latitude", "longitude"]].min().values.tolist()
ne = merged_df[["latitude", "longitude"]].max().values.tolist()

m.fit_bounds([sw, ne])

for i in range(0, len(merged_df)):
folium.Marker(
location = [merged_df.iloc[i]["latitude"], merged_df.iloc[i]["longitude"]],
popup = merged_df.iloc[i]["name"],
).add_to(m)

points = tuple(zip(merged_df.latitude, merged_df.longitude))

folium.PolyLine(points, color = "red", weight = 3, opacity = 1).add_to(m)

plugins.Fullscreen(
position = "topright",
title = "Fullscreen",
title_cancel = "Exit",
force_separate_button = True).add_to(m)
m
这将生成一张地图,如图 11 所示。可以滚动和缩放地图。单击时,一个标记将显示车站名。

图 11. 使用Folium的短路径

加分:Streamlit可视化
可以使用 Streamlit 创建一个小应用程序,允许我们选择伦敦地铁旅程的起点和终点站,该应用程序能找出短路径。

安装所需软件

需要安装以下软件包:

复制

Python
streamlit
streamlit-folium
pandas
networkx
folium
pymysql
这些可以在GitHub 上的requirements.txt文件中找到。运行文件如下:

复制

Shell
pip install -r requirements.txt
示例应用程序

以下是streamlit_app.py的完整代码清单:

复制

Python
# streamlit_app.py

import streamlit as st
import pandas as pd
import networkx as nx
import folium
import pymysql

from streamlit_folium import folium_static

# Initialize connection.

def init_connection():
return pymysql.connect(**st.secrets["singlestore"])

conn = init_connection()

# Perform query.

connections_df = pd.read_sql("""
SELECT *
FROM london_connections;
""", conn)

stations_df = pd.read_sql("""
SELECT *
FROM london_stations
ORDER BY name;
""", conn)

stations_df.set_index("id", inplace = True)

st.subheader("Shortest Path")

from_name = st.sidebar.selectbox("From", stations_df["name"])
to_name = st.sidebar.selectbox("To", stations_df["name"])

graph = nx.Graph()

for connection_id, connection in connections_df.iterrows():
station1_name = stations_df.loc[connection["station1"]]["name"]
station2_name = stations_df.loc[connection["station2"]]["name"]
graph.add_edge(station1_name, station2_name, time = connection["time"])

shortest_path = nx.shortest_path(graph, from_name, to_name, weight = "time")

shortest_path_df = pd.DataFrame({"name" : shortest_path})

merged_df = pd.merge(shortest_path_df, stations_df, how = "left", on = "name")

m = folium.Map(tiles = "Stamen Terrain")

sw = merged_df[["latitude", "longitude"]].min().values.tolist()
ne = merged_df[["latitude", "longitude"]].max().values.tolist()

m.fit_bounds([sw, ne])

for i in range(0, len(merged_df)):
folium.Marker(
location = [merged_df.iloc[i]["latitude"], merged_df.iloc[i]["longitude"]],
popup = merged_df.iloc[i]["name"],
).add_to(m)

points = tuple(zip(merged_df.latitude, merged_df.longitude))

folium.PolyLine(points, color = "red", weight = 3, opacity = 1).add_to(m)

folium_static(m)

st.sidebar.write("Your Journey", shortest_path_df)
本地 Streamlit 应用程序会从应用程序的根目录读取机密文件 .streamlit/secrets.toml。需要按如下方式创建这个文件:

复制

Plain Text
# .streamlit/secrets.toml

[singlestore]
host = "<TO DO>"
port = 3306
database = "geo_db"
user = "admin"
password = "<TO DO>"
主机和密码的应替换为在创建集群时从SingleStore托管服务获取的相应值。

运行代码

可以按如下方式运行 Streamlit 应用程序:

复制

Shell
streamlit run streamlit_app.py
输出应该是如图 12 所示的 Web 浏览器。

图 12. 短路径

随意尝试代码以满足您的需求。

总结
通过本文,我们看了 SingleStore 支持的一系列非常强大的地理空间函数。从示例中,我们已经看到这些函数在地理空间数据中发挥作用,此外我们还看到了如何通过各种库创建图形结构并进行查询。这些库与 SingleStore相结合,可以轻松地对图形结构进行建模和查询。

几个可完善之处:

伦敦地铁的数据需要更新,近有新的车站和延线站点开通。
还可以添加其他交通方式,例如伦敦有轨电车网络。
还可以添加有关交通网络的其他连接信息。例如,一些站点可能没有直接相连,但距离很近,步行可达。
各种地铁线路的可视化也可以改进,因为有多条线路服务的任一路线目前只显示其中一条线路。
短路径是根据静态数据计算的。如果扩展代码并引入允许延迟的交通网络实时更新将很有益处。

致谢

如果没有其他作者和开发人员提供的示例,这篇文章不可能完成。

引用艾萨克·牛顿爵士的一句名言:

如果我看得更远,那是因为站在巨人的肩膀上。

相关文章