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

译者 | 杨晓娟

审校 | 梁策 孙淑娟

摘要

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.types 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

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

dtypes: 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

在下一个代码单元中，为 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

(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";

输出应该是：

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. Pauls",

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 = ""

port = 3306

database = "geo_db"

user = "admin"

password = ""

主机和密码的应替换为在创建集群时从 SingleStore 托管服务获取的相应值。

运行代码

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

Shell

streamlit run streamlit_app.py

输出应该是如图 12 所示的 Web 浏览器。

图 12. 最短路径

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

总结

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

几个可完善之处：

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

致谢

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

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

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

译者介绍

杨晓娟，51CTO社区编辑，西安电子科技大学计算机专业硕士研究生，资深研发工程师，信息系统项目管理师，拥有近20年Java开发经验。分别在NEC、甲骨文、英方从事数据存储、Oracle数据库的数据迁移以及同构/异构数据库复制等研发工作，尤其在数据库、数据编码等方面有深入钻研和了解。

原文标题：​​Using SingleStore as a Geospatial Database​​，作者：Akmal Chaudhri