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

2022-01-14 00:00:00 geonames geolocation mysql user-variables

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

I get a list of suburbs within a specified distance from a single location using Queries A.

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

I’m trying to adapt Queries A to get a list of suburbs surrounding location1, then get list of suburbs surrounding location2 and so on (I'll call this Queries B). Essentially Queries B is doing the same as Queries A, but repeating it for each separate location. My question- how can I do this using MySQL only. Suggestions on how to do this are much appreciated.

这是我正在处理的数据示例.SqlFiddle 这里

Here is a sample of the data I am working with. SqlFiddle here

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- 获取单个兴趣点周围的郊区

Queries A- get suburbs surrounding a single point of interest

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;

结果

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

The query above returns one location for the point of interest. 

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

以及 Tamworth 周边的郊区列表.

and a list of suburbs surrounding 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() 创建 查询 B.这个想法是我可以像使用数组一样循环遍历这些值.如果您愿意，我可以发布我的最后一次尝试，但我什至没有接近解决方案(不是因为缺乏尝试).

I tried to create Queries B using MySQL user variables, GROUP_CONCAT() and FIND_IN_SET(). The idea was that I could cycle through the values a bit like using an array. I can post my last attempt if you wish, but I am not even close to a solution (not for lack of trying).

更新:这是我最后一次尝试.

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 个兴趣点 - Tamworth、Birmingham 和 Roanoke - 这是我希望看到的.

Desired Results for Queries B. For 3 points of interest-Tamworth, Birmingham and Roanoke- this is what I would expect to see.

| 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 执行此操作的建议.

Suggestions on how to do this using MySQL are greatly appreciated.

推荐答案

你只需要执行一个自加入.加入表是SQL的一个非常基础部分——你真的在尝试进一步理解这个答案之前应该阅读它.

You simply need to perform a self-join. Joining tables is a very fundamental part of SQL—you really should read up on it before trying to understand this answer further.

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 ... 的简写.

You'll have noticed that I've used MySQL's IN() operator as a shorthand for value = A OR value = B OR ....

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

You'll also have noticed that I've used MySQL's DEGREES() and RADIANS() functions rather than trying to perform such conversions explicitly.

然后您将纬度的分钟数乘以 1.851999999962112 的因子，这很奇怪:它非常接近 1.852，这是一个精确的公里数海里(历史上定义为一分钟的纬度)，但奇怪的是略有不同——我假设你打算用它来代替.

You were then multiplying minutes of latitude by a factor of 1.851999999962112, which was rather strange: it's extremely close to 1.852, which is the precise number of kilometres in a nautical mile (historically defined as a minute of latitude), but yet bizarrely slightly different—I've assumed you meant to use that instead.

最后，您获得了作为字符串过滤结果集中距离的文字值，即 '60'，而显然这是一个数值，应该不加引号.

Finally, you had the literal value by which you were filtering the distances in the resultset as a string, i.e. '60', whereas obviously this is a numeric value and should be unquoted.

相关文章