使用 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
importpandasaspd
importgeopandasasgpd

frompyspark.sql.typesimport*
fromshapelyimportwkt

接下来，定义模式：

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
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,
timeINT,
     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.nameAS neighbour, ROUND(GEOGRAPHY_DISTANCE(a.geometry, b.geometry),0)AS distance_from_border
FROM london_boroughs a, london_boroughs b
WHERE a.name="Merton"
ORDERBY distance_from_border
LIMIT10;

输出应该是：

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
ORDERBY perimeter DESC
LIMIT5;

输出应该是：

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"
ORDERBY 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)
ORDERBY name;

输出应该是：

Plain Text
+------------------------+
|          name          |
+------------------------+
| High Street Kensington |
+------------------------+

可视化

伦敦地铁地图

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

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

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

Python
importpandasaspd
importnetworkxasnx
importmatplotlib.pyplotasplt
importfolium

fromfoliumimportplugins

现在，建立到 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()

forstation_id, stationinstations_df.iterrows():
graph.add_node(station["name"],
lon=station["longitude"],
lat=station["latitude"],
s_id=station["id"])

forconnection_id, connectioninconnections_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"]) fornodeingraph.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"] foredgeingraph.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] forlineinedge_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"])

foredgeinlist(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)

foriinrange(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)

foriinrange(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

输出应该是：

PlainText
['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])

foriinrange(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

importstreamlitasst
importpandasaspd
importnetworkxasnx
importfolium
importpymysql

fromstreamlit_foliumimportfolium_static

# Initialize connection.

definit_connection():
returnpymysql.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()

forconnection_id, connectioninconnections_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])

foriinrange(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 相结合，可以轻松地对图形结构进行建模和查询。

几个可完善之处：

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

致谢

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

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

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

译者介绍

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

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

文章来源网络，作者：运维，如若转载，请注明出处：https://shuyeidc.com/wp/248599.html<

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

摘要

介绍

配置 Databricks CE

上传 CSV 文件

伦敦行政区数据

转换伦敦行政区数据

创建伦敦行政区数据库表

伦敦行政区数据加载器

伦敦地铁数据

创建伦敦地铁数据库表

伦敦地铁数据加载器

示例查询

面积 (GEOGRAPHY_AREA)

距离 (GEOGRAPHY_DISTANCE)

长度 (GEOGRAPHY_LENGTH)

包含 (GEOGRAPHY_CONTAINS)

相交 (GEOGRAPHY_INTERSECTS )

近似相交 (APPROX_GEOGRAPHY_INTERSECTS)

距离内 (GEOGRAPHY_WITHIN_DISTANCE)

可视化

伦敦地铁地图

最短路径

加分：Streamlit 可视化

安装所需软件

示例应用程序

创建机密文件

运行代码

总结

致谢

译者介绍

相关推荐

个人主题怎么制作？

社群营销管理关键是什么？

香港公司网站备案需要什么材料？

如何企业上云推广

PS设计搜索框的实用技巧有哪些？

发表回复