给我一些纬度和经度

2022年9月27号

地理位置是现代应用的基石。无论你是食品配送公司还是家庭摄影师，了解与你或你的客户最接近的“东西”都是一个很好的特点。

在我们的‘缩放和优化‘ MySQL培训课程中，我们讨论的内容之一是列类型。空间类型只是顺便提一下，因为只有不到0.5%的MySQL用户知道它们的存在（这只是猜测，没有事实依据）。在这篇文章中，我们简要讨论了POINT类型以及如何使用它来计算到最近的公共公园的距离。

导入数据

首先，我们需要一些表格和数据。第一个表将保存邮政编码与其相关纬度/经度之间的映射。GeoNames在Creative Commons v3许可证下拥有此数据，此处提供大多数国家的数据。数据文件是CSV格式，readme.txt文件解释了各种列。我们只对其中几个列感兴趣。

 mysql> CREATE TABLE usazips (
 id int unsigned NOT NULL AUTO_INCREMENT,
 zipCode int unsigned NOT NULL COMMENT 'All USA postal codes are integers',
 state varchar(20) NOT NULL,
 placeName varchar(200) NOT NULL,
 placeDesc varchar(100) DEFAULT NULL,
 latLong point NOT NULL /*!80003 SRID 4326 */,
 PRIMARY KEY (id));

关于这个模式，有一些事情在MySQL中并不常见。首先，latLong列类型是POINT，它可以存储X和Y坐标。默认情况下，这些坐标可以在任何平面上。它们可以代表纬度和经度，但也可以是桌子表面上下多少厘米，或者在当地公园最大的树的左右多少码。你怎么知道？您需要空间参考系统。幸运的是，MySQL预装了大约5100个这样的系统（请参阅INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS）。每个SRS都有一个关联的空间引用ID（SRID）。

上面POINT列上的SQL注释将此列中的数据与特定的SRID 4326相关联。我们可以在前面提到的I_S表中看到，此SRID映射到“世界大地测量系统 1984”（又名WGS84），它与GeoNames数据集中使用的SRS相同。通过将我们的列和数据集对齐，我们以后不需要进行任何地理转换。

模式中要注意的第二件事是在latLong列上使用SPATIAL索引。MySQL中的SPATIAL索引是使用R-Trees 创建的，它们是特定于地理空间的数据结构。

让我们将此数据加载到表中。CSV中有12个字段，但我们只关心其中的6个。LOAD DATA命令允许您将每个CSV字段与表中的列名或用户变量进行位置关联。CSV中的第一个字段是国家代码，我们不关心它，所以我们将该字段分配给@dummy变量。第二个字段是邮政编码，我们希望它直接进入表列，因此我们指定了列名。我们对第三、第五、第六和第九个字段也这样做。

第10和第11个CSV字段是纬度和经度。我们需要将这两个VARCHAR字段转换为一个POINT。我们可以使用SET命令应用一些SQL转换，该命令可以引用前面指定的用户变量。这两个字段被指定给@lat和@lon。其余字段全部进入@dummy，因为它们未被使用。

mysql> LOAD DATA INFILE '/var/lib/mysql-files/US.txt'
INTO TABLE usazips
FIELDS TERMINATED BY '\t' (@dummy, zipCode, placeName, @dummy, state, placeDesc, @dummy, @dummy, @dummy, @lat, @lon, @dummy)
SET id = NULL, latLong = ST_PointFromText(CONCAT('POINT(', @lat, ' ', @lon, ')'), 4326);

不幸的是，MySQL中的POINT()函数总是返回SRID 0。由于我们将列指定为特定的SRID，直接导入将失败。

mysql> LOAD ... SET latLong = POINT(@lat, @lon);
ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'latLong'.
The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID
of the geometry or the SRID property of the column.

相反，我们必须通过 Well Known Text format 创建POINT对象的字符串表示来 “走漫长的路”。 MySQL可以将其解析为具有关联SRID的Point列类型。

mysql> LOAD ... SET latLong = ST_PointFromText(CONCAT('POINT(', @lat, ' ', @lon, ')'), 4326);

是的，这也让我很困惑：

“POINT（123 56）”<-WKT格式（字符串）

POINT（123，56）<-MySQL列类型（返回数据的函数）

下面是对我们的数据的快速验证，另外一列显示了将lat-long二进制数据转换回WKT格式。

mysql> SELECT *, ST_AsText(latLong) FROM usazips WHERE zipCode = 76455;
+-------+---------+-------+-----------+-----------+------------------------------------------------------+-------------------------+
| id    | zipCode | state | placeName | placeDesc | latLong                                              | ST_AsText(latLong)      |
+-------+---------+-------+-----------+-----------+------------------------------------------------------+-------------------------+
| 34292 |   76455 | TX    | Gustine   | Comanche  | 0xE6100000010100000068B3EA73B59958C0B84082E2C7D83F40 | POINT(31.8468 -98.4017) |
+-------+---------+-------+-----------+-----------+------------------------------------------------------+-------------------------+
1 row in set (0.04 sec)

加载更多数据

现在我们有了所有这些邮政编码+纬度和经度数据，接下来需要找到距离的位置。这可能是杂货店或咖啡店的列表。在这个例子中，我们将使用德克萨斯州圣安东尼奥的公共公园列表。幸运的是，圣安东尼奥拥有所有这些公开可用的数据。我下载了GeoJSON格式的“公园边界”数据集，因为CSV不包含任何纬度/经度坐标。

mysql> SELECT *, ST_AsText(latLong) FROM usazips WHERE zipCode = 76455;
+-------+---------+-------+-----------+-----------+------------------------------------------------------+-------------------------+
| id    | zipCode | state | placeName | placeDesc | latLong                                              | ST_AsText(latLong)      |
+-------+---------+-------+-----------+-----------+------------------------------------------------------+-------------------------+
| 34292 |   76455 | TX    | Gustine   | Comanche  | 0xE6100000010100000068B3EA73B59958C0B84082E2C7D83F40 | POINT(31.8468 -98.4017) |
+-------+---------+-------+-----------+-----------+------------------------------------------------------+-------------------------+
1 row in set (0.04 sec)

GeoJSON数据是一个巨大的JSON字符串，所有需要的数据都嵌套在各种JSON对象和数组中。我决定尝试JSON导入特性，而不是编写一些Python脚本来抽取数据并首先将JSON直接导入到临时表中。

$ mysqlsh appUser@127.0.0.1/world --import Park_Boundaries.geojson tempParks
Importing from file "Park_Boundaries.geojson" to collection `world`.`tempParks` in MySQL Server at 127.0.0.1:33060
..1..1
Processed 3.29 MB in 1 document in 0.3478 sec (1.00 document/s)
Total successfully imported documents 1 (1.00 document/s)

MySQL Shell实用程序使用以下模式在“world”数据库中创建了一个名为“tempParks”的新表。插入了一行，这是作为一个JSON对象的整个数据集。

CREATE TABLE `tempParks` (
`doc` json DEFAULT NULL,
`_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
`_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
PRIMARY KEY (`_id`),
CONSTRAINT `$val_strict_75A2A431C77036365C11677C92B55F4B307FB335` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
);

此处，我从JSON中提取了所需的信息，并将其插入到最后一个表中，如上图所示。此SQL使用MySQL 8中的新JSON_TABLE函数从嵌套的JSON数据创建结果集（奇怪的是，数据集中有几个重复的park，这就是为什么您会看到ON duplicate修饰符）。注意：该数据集的经度在索引0处，纬度在索引1处。

INSERT INTO parks
SELECT k.parkID, k.parkName, ST_PointFromText(CONCAT('POINT(', parkLat, ' ', parkLon, ')'), 4326)
FROM tempParks, JSON_TABLE(
tempParks.doc,
"$.features[*]" COLUMNS (
parkID int PATH "$.properties.ParkID",
parkName varchar(150) PATH "$.properties.ParkName",
parkLat varchar(20) PATH "$.geometry.coordinates[0][0][0][1]",
parkLon varchar(20) PATH "$.geometry.coordinates[0][0][0][0]"
)
) AS k ON DUPLICATE KEY UPDATE parkId = k.parkID;

Query OK, 391 rows affected (0.11 sec)
Records: 392  Duplicates: 0  Warnings: 0

最近的公园是什么？

既然所有信息都已加载，那么让我们根据您的邮政编码找到离您最近的公园。首先，获取相关邮政编码的参考位置（作为MySQL POINT数据）：

mysql>SELECT latLong INTO@zipLocation FROM usazips WHERE zipCode=78218|

然后，计算我们的参考邮政编码和所有公园位置之间的距离。显示最近的五个（不包括任何学校公园）：

 mysql> SELECT p.parkName, ST_AsText(p.parkLocation) AS location, ST_Distance_Sphere(@zipLocation, p.parkLocation) AS metersAway
FROM parks p WHERE parkName NOT LIKE '%School%' ORDER BY metersAway LIMIT 5;
+-----------------------+----------------------------------------------+--------------------+
| parkName              | location                                     | metersAway         |
+-----------------------+----------------------------------------------+--------------------+
| Robert L B Tobin Park | POINT(29.501796110000043 -98.42111988)       |   1817.72881969296 |
| Wilshire Terrace Park | POINT(29.486397887000063 -98.41776879999996) | 1830.8541086553364 |
| James Park            | POINT(29.48168424800008 -98.41725954999998)  |  2171.268012559491 |
| Perrin Homestead Park | POINT(29.524477369000067 -98.41258342199997) |  3198.082796589456 |
| Tobin Library Park    | POINT(29.510899863000077 -98.43343147299998) |  3314.044809806559 |
+-----------------------+----------------------------------------------+--------------------+
5 rows in set (0.01 sec)

最近的公园距离1817.7米！该野餐了！

结论

MySQL中的地理位置并不困难。最困难的部分是在一些表中查找数据和数据抽取。MySQL的原生列类型和对OpenGIS标准的支持使得处理数据非常容易。需要注意的几点：

邮政编码的参考位置可能在几百米或1000米之外。因此，你房子后面的公园可能不会显为实际最近的公园，因为另外一个公园更接近参考。
没有向表中添加SPATIAL索引，因为它们没有任何用处，因为我们必须对所有公园（即所有行）执行距离函数，以查找最近的公园，因此“metersAway”查询将始终执行全表扫描
所有美国邮政编码都是整数。您可以通过简单地更改列类型将其扩展到您的国家。

MySQL 地理位置使用案例

给我一些纬度和经度

导入数据

加载更多数据

最近的公园是什么？

结论