当前位置: 首页 > 面试题库 >

如何获取一个位置周围的郊区列表,然后使用MySql对其他位置重复?

蔡楚
2023-03-14
问题内容

我使用 查询A 获得了从单个位置在指定距离内的郊区列表。

我正在尝试修改查询A,以获取location1周围的郊区列表,然后获取location2周围的郊区列表,依此类推(我将其称为 查询B
)。本质上,查询B的操作与查询A的操作相同,但是在每个单独的位置重复此操作。 我的问题-如何仅使用MySQL做到这一点。
非常感谢有关如何执行此操作的建议。

这是我正在使用的数据的样本。SqlFiddle 在这里

CREATE TABLE `geoname` (
    `geonameid` INT(11) NOT NULL,
    `asciiname` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `country` VARCHAR(2) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `latitude` DECIMAL(10,7) NULL DEFAULT NULL,
    `longitude` DECIMAL(10,7) NULL DEFAULT NULL,
    `fcode` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `population` INT(11) NULL DEFAULT NULL,
    `area` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`geonameid`),
    INDEX `asciiname` (`asciiname`),
    INDEX `country` (`country`),
    INDEX `latitude` (`latitude`),
    INDEX `longitude` (`longitude`),
    INDEX `fcode` (`fcode`),
    INDEX `population` (`population`),
    INDEX `area` (`area`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

INSERT INTO geoname(geonameid, asciiname, country, latitude, longitude, fcode, population, area) VALUES
(2147497, 'Tamworth', 'AU', -31.0904800, 150.9290500, 'PPL', 47597, 72),
(8597559, 'Tamworth', 'AU', -21.0457400, 143.6685200, 'PPL', 0, 0),
(8805708, 'Tamworth', 'AU', -21.0471300, 143.6692000, 'HMSD', 0, 0),
(2655603, 'Birmingham', 'GB', 52.4814200, -1.8998300, 'PPL', 984333, 599),
(4782167, 'Roanoke', 'US', 37.2709700, -79.9414300, 'PPL', 97032, 321),
(10114336, 'East Tamworth', 'AU', -31.0854800, 150.9372100, 'PPLX', 2621, 0),
(10114337, 'North Tamworth', 'AU', -31.0786200, 150.9221900, 'PPPL', 0, 0),
(2143940, 'West Tamworth', 'AU', -31.1023600, 150.9144700, 'PPLX', 0, 0),
(2656867, 'Aston', 'GB', 52.5000000, -1.8833300, 'PPLX', 0, 0),
(2646814, 'Hockley', 'GB', 52.5000000, -1.9166700, 'PPLX', 13919, 0),
(2650236, 'Edgbaston', 'GB', 52.4623000, -1.9211500, 'PPLX', 0, 0),
(4754994, 'Cumberland Forest', 'US', 37.1401300, -80.3217100, 'PPLX', 0, 0),
(4774999, 'Mountain Top Estates', 'US', 37.1376300, -80.3247700, 'PPPL', 0, 0),
(4764119, 'Highland Park', 'US', 37.2237400, -80.3917200, 'PPLX', 0, 0);

我尝试了什么

查询A- 获取围绕单个兴趣点的郊区

SELECT @lat := latitude, @lng :=longitude FROM geoname WHERE asciiname = 'Tamworth' and country='AU' and population>0 and fcode='PPL';

SELECT
    name as suburb, 'Tamworth' as point_of_interest, country,
    (
    (
    ACOS(SIN(@lat * PI() / 180) * SIN(latitude * PI() / 180) + COS(@lat * PI() / 180) * COS(latitude * PI() / 180) * COS((
    @lng - longitude
    ) * PI() / 180)) * 180 / PI()
    ) * 60 * 1.851999999962112
    ) AS distance
  FROM geoname
    WHERE fcode='PPLX' OR fcode='PPPL'
  HAVING distance <= '60'
  ORDER BY distance ASC;

结果

上面的查询返回一个兴趣点位置。

+---------------------------------+
|     @lat       |      @lng      |
+---------------------------------+
| 52.6339900     |   -1.6958700   |
+---------------------------------+

以及塔姆沃思(Tamworth)周围的郊区清单。

    | point_of_interest |      suburb          | country |           distance |
    |-------------------|----------------------|---------|--------------------|
    |          Tamworth |  East Tamworth       |      AU | 0.9548077598752538 |
    |          Tamworth |  North Tamworth      |      AU | 1.4707125875055387 |
    |          Tamworth |  West Tamworth       |      AU |  1.915025922482298 |

我试图创建 查询乙
使用MySQL的用户变量,GROUP_CONCAT()FIND_IN_SET()。我的想法是我可以像使用数组一样循环遍历这些值。如果您愿意,我可以发布我的最后一次尝试,但是我甚至还没有找到解决方案(并非因为缺乏尝试)。

更新: 这是我最后的尝试之一。

SELECT @lat := GROUP_CONCAT(latitude), @lng :=GROUP_CONCAT(longitude), @city :=GROUP_CONCAT(asciiname), @area :=GROUP_CONCAT(area) FROM geoname WHERE (asciiname = 'Tamworth' or asciiname = 'Birmingham' or asciiname = 'Roanoke') and population>0 and fcode='PPL';

SELECT
    FIND_IN_SET(asciiname, @city) as point_of_interest, asciiname as suburb, country,
    (
    (
    ACOS(SIN(FIND_IN_SET(latitude, @lat) * PI() / 180) * SIN(latitude * PI() / 180) + COS(FIND_IN_SET(latitude, @lat) * PI() / 180) * COS(latitude * PI() / 180) * COS((
    FIND_IN_SET(longitude, @lng) - longitude
    ) * PI() / 180)) * 180 / PI()
    ) * 60 * 1.851999999962112
    ) AS distance
  FROM geoname   
  HAVING distance <= FIND_IN_SET(distance, @area)
  ORDER BY distance ASC;

查询B的期望结果。 对于3个兴趣点(塔姆沃思,伯明翰和罗阿诺克),这是我希望看到的。

| point_of_interest |      suburb          | country |           distance |
|-------------------|----------------------|---------|--------------------|
|          Tamworth |  East Tamworth       |      AU | 0.9548077598752538 |
|          Tamworth | North Tamworth       |      AU | 1.4707125875055387 |
|          Tamworth |  West Tamworth       |      AU |  1.915025922482298 |
|        Birmingham |        Aston         |      GB |  2.347111909955497 |
|        Birmingham |       Hockley        |      GB | 2.3581405942861164 |
|        Birmingham |      Edgbaston       |      GB |  2.568384753388139 |
|           Roanoke |    Cumberland Forest |      US |  36.66226789588173 |
|           Roanoke | Mountain Top Estates |      US |  37.02185777044897 |
|           Roanoke |        Highland Park |      US | 40.174566427830094 |

非常感谢有关使用MySQL执行此操作的建议。


问题答案:

您只需要执行自联接。 连接表是SQL的一个 非常
基本的部分- 在尝试进一步理解该答案之前,您 确实 应该阅读它。

SELECT   poi.asciiname,
         suburb.asciiname,
         suburb.country,
         DEGREES(
           ACOS(
             SIN(RADIANS(   poi.latitude))
           * SIN(RADIANS(suburb.latitude))
           + COS(RADIANS(   poi.latitude))
           * COS(RADIANS(suburb.latitude))
           * COS(RADIANS(poi.longitude - suburb.longitude))
           )
         ) * 60 * 1.852 AS distance
FROM     geoname AS poi
    JOIN geoname AS suburb
WHERE    poi.asciiname IN ('Tamworth', 'Birmingham', 'Roanoke')
     AND poi.population > 0
     AND poi.fcode = 'PPL'
     AND suburb.fcode IN ('PPLX', 'PPPL')
HAVING   distance <= 60
ORDER BY poi.asciiname, distance

在sqlfiddle上看到它。

您会注意到,我已经使用MySQL的IN()运算符作为的简写value = A OR value = B OR ...

您还会注意到,我使用了MySQL DEGREES()RADIANS()函数,而不是尝试显式执行此类转换。

然后,您将纬度分钟数乘以1.851999999962112,这是很奇怪的:它非常接近1.852,这是海里的精确公里数(历史上定义为纬度分钟),但奇怪的是,我已经假设您打算使用它。

最后,您拥有一个字面值,通过该字面值可以将结果集中的距离过滤为字符串,即'60',而显然这是一个数字值,应不加引号。



 类似资料:
  • 问题内容: 我正在尝试扩展本机地理位置功能 这样我就可以使用访问者的国家/地区名称(也许返回一个信息丰富的数组)。 到目前为止,我所能找到的只是显示google maps界面的函数,但实际上没有提供我想要的东西,除了该库但由于某种原因在我的计算机上不起作用。我不知道为什么在那里出错了。 无论如何,您知道我怎样才能简单地从纬度和经度值中返回一个包含国家,城市等信息的数组? 问题答案: 如果只需要用户

  • 我想创造一种方法,让用户与前面或后面的手机进行通信。虽然这不能用全球定位系统来实现,但我希望使用蓝牙来实现这一效果。我知道有算法可以根据蓝牙信号强度看到一个设备离另一个设备有多远,但我没有看到任何关于给出位置方向的东西。我已经研究了使用uwb信号跟踪用户的其他方法,但还没有能够得到一些可以给出位置方向的东西。

  • 无法计算出如何在给定位置获得特定半径的所有道路。我目前的查询是 我试图添加这样的

  • 问题内容: 这个问题已经在这里有了答案 : 8年前关闭。 可能重复: MySQL中的ROW_NUMBER() 这可能吗?我想按特定的顺序获取一堆结果,然后通过其ID找出行的位置,例如: 我想按字母顺序获得1的位置,它将返回2 问题答案: 从这个答案>> ROW_NUMBER()在MySQL中修改

  • 本文向大家介绍如何在R数据帧中将列从其他位置移动到第一位置?,包括了如何在R数据帧中将列从其他位置移动到第一位置?的使用技巧和注意事项,需要的朋友参考一下 在处理大型数据集时,R数据框中列的位置特别重要。由于第一列首先出现,因此有必要按顺序排列数据框的列,以帮助我们轻松查看重要的列。为此,我们可能要更改列的位置。要将列的位置更改为第一个位置,我们可以使用单个方括号。 示例 请看以下数据帧- 假设我

  • 我意识到这个问题以前被问过很多次,但我现在问这个问题是因为答案很旧(与新的API相比)。 我以前使用过Location Manager,但我发现它非常不可靠。例如,在我的应用程序中,使用getLastKnownLocation和/或current location,它会立即将相机加载到用户的当前位置。但是,如果我在启动应用程序之前打开设备上的位置,它就不会把摄像头放在用户的当前位置,而是放在Nig