时间戳

首页 > 解决方案 > 用于地理空间分析的 MySQL 循环

mysql - 用于地理空间分析的 MySQL 循环

问题描述

如果在其他地方得到回答，我深表歉意；我查看并尝试了几件事，但无法解决这个问题。

我想要做什么：在 MySQL 中，我有一个表包含来自我们警察部门的服务数据调用，另一个表包含空置属性。每个表都有纬度、经度字段。我们想知道每次服务电话是否在空置物业的 100 英尺范围内？这是我们编写的查询，使用示例坐标来测试距离分析是否有效：

SELECT
    address, (
        (20903520) 
    * acos (
        cos ( radians(38.67054) )
        * cos( radians( lat ) )
        * cos( radians( long ) - radians(-90.22942) )
        + sin ( radians(38.67054) )
        * sin( radians( lat ) )
    )
) AS distance
FROM vacants
HAVING distance < 100;

我如何编写一个循环来遍历每个呼叫服务位置并针对每个空置位置进行测试，以查看每个呼叫是否在 100 英尺半径内找到空置物业？我尝试过编写 DECLARE 语句、创建 COUNTER 变量并使用 BEGIN/END 语法。他们似乎都没有完成它，尽管也许我只是没有以正确的方式编写它们。SQL 不是我的强项；我更喜欢 Python，但我们认为通过 SQL 查询执行此操作将比遍历 .csv 文件更快。大约有 250 万次呼叫服务记录。最后，我们希望能够说“在 250 万个服务电话中，有 X 个在空置物业的 100 英尺范围内”。我还希望能够输出一个带有空置属性地址的 .csv 文件，

像这样，但添加了两个字段。这来自我提供的测试查询。

测试查询结果

我们还想做反向分析，看看有多少服务电话在每个物业的 100 英尺半径范围内。

这是示例数据 - 调用服务坐标：

38.595767638008056,-90.2316138251402
38.57283495467307,-90.24649031378685
38.67497061776659,-90.28415976525395
38.67650431524285,-90.25623757427952
38.591971519414784,-90.27782710145746
38.61272746420862,-90.23292862245287
38.67312983860098,-90.23591869583113
38.625956494342674,-90.18853950906939
38.69044465638584,-90.24339061920696
38.67745024638241,-90.20657832034047`

空置物业坐标：

38.67054,-90.22942
38.642956,-90.21466
38.671535,-90.27293
38.666367,-90.23749
38.65339,-90.23141
38.645996,-90.20334
38.60214,-90.224815
38.67265,-90.214134
38.665504,-90.274414
38.668354,-90.269966`

谢谢你的帮助。

标签： mysqlsqlloopsgeospatial

解决方案

您的正弦/余弦计算会很慢，不能使用索引。然而 MySQL 对几何和 r-tree 索引有很好的支持，以实现高效访问。

存储您的位置的方法是将它们置于数据类型中，POINT并使用几何函数（如st_distance计算距离）。

基于您的信息的示例：

CREATE TABLE vacants (address varchar(255), geo POINT NOT NULL SRID 4326,  SPATIAL KEY (geo))
insert into vacants VALUES ('123 some str', ST_PointFromText('POINT(8.6949639 50.1139589)', 4326) );
insert into vacants VALUES ('123 some ave', ST_PointFromText('POINT(8.6779835 50.1156941)', 4326) );
SELECT address, st_distance(geo, ST_PointFromText('POINT(8.532687 50.1036198)', 4326)) as `distance in meters` FROM vacants;
+--------------+---------------------+
| address      | distance in meters  |
+--------------+---------------------+
| 123 some str | 17983.567531177974  |
| 123 some ave | 16124.382142034325  |
+--------------+---------------------+
2 rows in set (0,00 sec)

SRID值4326描述了空间关系id，描述了像地球这样的几何系统。对于其他行星或人造几何结构，您需要不同的值。我将几何描述为 WKT 或“众所周知的文本”，它是描述各种几何对象的标准化形式。数据也可以以其他格式提供（即 geojson 或其他格式） 我选择的位置位于德国中部的某个地方。

有了这个距离，您应该能够很容易地创建反向搜索。

有关所有详细信息，请参阅https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html和https://dev.mysql.com/doc/refman/8.0/en的手册页/spatial-analysis-functions.html

推荐阅读