使用 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 (