如何获得一个位置周围的郊区列表，然后重复使用MySql的其他位置？ [英] How to get a list of suburbs surrounding a location then repeat for other locations using MySql?
问题描述
我使用查询A 获取距离单个位置指定距离内的郊区列表。我试图调整查询A获取位置1周围的郊区列表，然后获取位置周围的郊区列表等等（我将称此为查询B ）。基本上查询B与查询A相同，但是为每个单独的位置重复它。 我的问题 - 我怎样才能使用MySQL来做到这一点。如何做到这一点的建议非常感谢。
以下是我工作的数据示例用。 SqlFiddle 此处 $ b
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'）
）
COLLATE ='utf8_unicode_ci'
ENGINE = InnoDB
;
插入geoname（geonameid，asciiname，国家，纬度，经度，fcode，人口，区域）值
（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'， （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， （4774999，'Mountain Top Estates'，'US'，37.1376300，-80.3247700，'PPPL'，0,0），
（4764119，PPLX，0,0），
'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';
选择
作为郊区名称，'Tamworth'作为point_of_interest，country，
（
（
ACOS（SIN（@lat * PI（）/ 180）* SIN（纬度* PI（）/ 180）+ COS（@lat * PI（）/ 180）* COS（纬度* PI（）/ 180）* COS（（b $ b @ lng - longitude
）* PI（）/ 180））* 180 / PI（）
）* 60 * 1.851999999962112
）距离
FROM geoname
WHERE fcode ='PPLX'OR fcode ='PPPL'
具有距离< ='60'
ORDER BY距离ASC;
结果
<上面的查询返回感兴趣点的一个位置。
+ ------------------------- -------- +
| @lat | @lng |
+ --------------------------------- +
| 52.6339900 | -1.6958700 |
+ --------------------------------- +
以及Tamworth周围的郊区清单。
| point_of_interest |郊区|国家|距离|
| ------------------- | ---------------------- | - ------- | -------------------- |
|塔姆沃思|东塔姆沃思| AU | 0.9548077598752538 |
|塔姆沃思|北塔姆沃思| AU | 1.4707125875055387 |
|塔姆沃思|西塔姆沃思| AU | 1.915025922482298 |
我试着创建查询B 使用MySQL用户变量 GROUP_CONCAT（）和 FIND_IN_SET（）。这个想法是，我可以像使用数组一样遍历数值。如果您愿意，我可以发布我的最后一次尝试，但我甚至没有接近解决方案（不是因为缺乏尝试）。
更新：这是我最后一次尝试之一。
SELECT @lat：= GROUP_CONCAT（纬度），@lng：= GROUP_CONCAT（经度），@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郊区，国家，
（
（
ACOS（SIN（FIND_IN_SET（纬度（纬度*）（180）* SIN（纬度* PI（）/ 180）+ COS（FIND_IN_SET（纬度，@lat）* PI（）/ 180）* COS（纬度* PI（）/ 180） * COS（（
FIND_IN_SET（经度，@ lng） - 经度
）* PI（）/ 180））* 180 / PI（）
）* 60 * 1.851999999962112
） AS距离
FROM geoname
HAVING距离< = FIND_IN_SET（距离，@area）
ORDER BY距离ASC;
查询结果B. 对于3点兴趣 - 塔姆沃思，伯明翰和罗阿诺克 - 这是我期望看到的。
| point_of_interest |郊区|国家|距离|
| ------------------- | ---------------------- | - ------- | -------------------- |
|塔姆沃思|东塔姆沃思| AU | 0.9548077598752538 |
|塔姆沃思|北塔姆沃思| AU | 1.4707125875055387 |
|塔姆沃思|西塔姆沃思| AU | 1.915025922482298 |
|伯明翰|阿斯顿| GB | 2.347111909955497 |
|伯明翰| Hockley | GB | 2.3581405942861164 |
|伯明翰| Edgbaston | GB | 2.568384753388139 |
|罗阿诺克|坎伯兰森林|美国| 36.66226789588173 |
|罗阿诺克|山顶庄园|美国| 37.02185777044897 |
|罗阿诺克|高地公园|美国| 40.174566427830094 |
有关如何使用MySQL执行此操作的建议非常感谢。
您只需执行自我连接。 加入表格是SQL的一个非常重要的部分
SELECT poi.asciiname，
suburb.asciiname，
suburb.country，
DEGREES（
ACOS（
SIN（RADIANS（poi.latitude））
* SIN（RADIANS（ （RADIANS（poi.latitude））
* COS（RADIANS（suburb.latitude））
* COS（RADIANS（poi.longitude - suburb.longitude） ）
）
）* 60 * 1.852 AS距离
FROM geoname AS poi
JOIN geoname AS郊区
WHERE poi.asciiname IN（'Tamworth'，'Birmingham' ，'罗阿诺克'）
和poi.population> 0
AND poi.fcode ='PPL'
AND suburb.fcode IN（'PPLX'，'PPPL'）
具有距离< = 60
ORDER BY poi.asciiname ，距离
在 sqlfiddle 。
您已经注意到我使用了MySQL的 IN（） 运算符为 value = A = value = B OR ... 。
您也会注意到我已经使用了MySQL的 DEGREES（） 和 RADIANS（ ） 功能，而不是试图明确地执行这样的转换。
1.851999999962112 ，这很奇怪：它非常接近 1.852 ，这是精确的公里数一个海里（历史上定义为纬度的一分钟），但却略微有点不同 - 我假设你打算使用它。 最后，你有您将结果集中的距离作为字符串进行过滤，即 '60'，但显然这是一个数字值，应该不加引号。
I get a list of suburbs within a specified distance from a single location using Queries A.
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.
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);
What I tried
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;
RESULTS
The query above returns one location for the point of interest.
+---------------------------------+
| @lat | @lng |
+---------------------------------+
| 52.6339900 | -1.6958700 |
+---------------------------------+
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 |
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).
UPDATE: Here is one of my last attempts.
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;
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 |
Suggestions on how to do this using MySQL are greatly appreciated.
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
See it on sqlfiddle.
You'll have noticed that I've used MySQL's IN() operator as a shorthand for value = A OR value = B OR ....
You'll also have noticed that I've used MySQL's DEGREES() and RADIANS() functions rather than trying to perform such conversions explicitly.
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.
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.
这篇关于如何获得一个位置周围的郊区列表，然后重复使用MySql的其他位置？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
