Как получить список пригородов, окружающих местоположение, затем повторить для других местоположений, используя MySql?
Я получаю список пригородов в пределах определенного расстояния из одного места, используя запросы А.
Я пытаюсь адаптировать Запросы A, чтобы получить список пригородов, окружающих location1, затем получить список пригородов, окружающих location2 и так далее (я назову это Queries 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 |
+---------------------------------+
и список пригородов, окружающих Тамворт.
| point_of_interest | suburb | country | distance |
|-------------------|----------------------|---------|--------------------|
| Tamworth | East Tamworth | AU | 0.9548077598752538 |
| Tamworth | North Tamworth | AU | 1.4707125875055387 |
| Tamworth | West Tamworth | AU | 1.915025922482298 |
Я пытался создать запросы B с помощью пользовательских переменных 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, с благодарностью.
2 ответа
Вам просто нужно выполнить самостоятельное соединение. Присоединение к таблицам является очень фундаментальной частью 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
Смотрите это на http://sqlfiddle.com/.
Вы заметили, что я использовал MySQL IN()
оператор как сокращение для value = A OR value = B OR ...
,
Вы также заметили, что я использовал MySQL DEGREES()
а также RADIANS()
функции, а не пытаться выполнять такие преобразования явно.
Затем вы умножали минуты широты на коэффициент 1.851999999962112
что было довольно странно: это очень близко к 1.852
, которое является точным числом километров в морской миле (исторически определенное как минута широты), но все же странно немного другое - я предположил, что вы хотели использовать это вместо этого.
Наконец, у вас было буквальное значение, по которому вы фильтровали расстояния в наборе результатов в виде строки, т.е. '60'
тогда как, очевидно, это числовое значение, и его следует заключать в кавычки.
Использование пространственных типов данных.
Ну, во-первых, если у вас много геопространственных данных, вы должны использовать геопространственные расширения mysql, а не такие вычисления. Затем вы можете создать пространственные индексы, которые ускорили бы многие запросы, и вам не нужно писать длинные вытянутые запросы, подобные приведенному выше.
Использование сравнения с ST_Distance или создание геометрии с радиусом интереса вместе с ST_within может дать вам хорошие результаты и может быть намного быстрее, чем текущий. Однако лучший и самый быстрый способ достичь этого, ST_Dwithin еще не реализован в MySQL.
Эти типы данных доступны в MySQL 5.7 и более поздних версиях, но это стоит того, чтобы обновить вашу БД, если вы используете более старую версию.
Новая структура таблицы.
CREATE TABLE `geoname2` (
`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',
`pt` POINT,
`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 `fcode` (`fcode`),
INDEX `population` (`population`),
INDEX `area` (`area`),
SPATIAL INDEX `pt` (`pt`)
)COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;
Обратите внимание, что latitude
а также longitude
поля были заменены pt
и их индексы были заменены одним индексом.
Новый запрос А
SELECT asciiname as suburb, 'Tamworth' as point_of_interest, country,
ST_DISTANCE(`pt`, POINT(@lat,@lng)) as distance
FROM geoname2
WHERE (fcode='PPLX' OR fcode='PPPL') AND ST_DISTANCE(`pt`, POINT(@lat,@lng)) <= 1
ORDER BY distance ASC;
Очевидно, это намного проще. Вероятно, это тоже быстрее, но при тестировании только 14 записей трудно прийти к какому-либо заключению, для таких небольших таблиц индекс не будет использоваться.
Обратите внимание, что результаты ST_DISTANCE возвращаются в градусах. Обычно считается, что 1 градус составляет около 60 миль или 111 км (вы сделали это в своих расчетах)
Кстати, в существующей настройке у вас есть индекс широты и долготы, но, пожалуйста, обратите внимание, что mysql может использовать только один индекс на таблицу, поэтому, если вы не принимаете геопространственные запросы, вы можете преобразовать его в один составной индекс на latitude,longitude
,
Полный запрос.
Теперь вышеприведенный запрос можно изменить следующим образом, чтобы получить "запрос B" в новой форме.
SELECT DISTINCT g1.asciiname, g2.asciiname ,ST_DISTANCE(g1.pt, g2.pt) *111 as distance FROM geoname2 g1
INNER JOIN (SELECT `pt`, asciiname
FROM geoname2
WHERE (fcode='PPLX' OR fcode='PPPL') AND
ST_DISTANCE(`pt`, POINT(@lat,@lng)) <= 1) as g2
WHERE ST_DISTANCE(g1.pt,g2.pt) < 1
AND g1.asciiname != g2.asciiname ORDER BY distance ASC;
Обратите внимание, что я предполагаю, что 1 градус (примерно 111 км, чтобы быть близко друг к другу)