Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 36
А что в этом запросе что? Я так понимаю, чтобы найти соседей нужно передать две координаты и дистанцию, а у вас я смотю и координаты передаются и уже заданы какие-то?..
sqrt(POWER(((lat-".$row['lat'].")*110349.7867154), 2) + POWER(((lng-".$row['lng'].")*64505.305504), 2))
lat и lng — названия соответствующих полей в таблице
$row['lat'] и $row['lng'] — координаты точки, вокруг которой мы ищем
110349.7867154 — коэффициент, переводящий градусы широты в метры для Ульяновска
64505.305504 — коэффициент, переводящий градусы долготы в метры для Ульяновска
lat и lng — названия соответствующих полей в таблице
$row['lat'] и $row['lng'] — координаты точки, вокруг которой мы ищем
110349.7867154 — коэффициент, переводящий градусы широты в метры для Ульяновска
64505.305504 — коэффициент, переводящий градусы долготы в метры для Ульяновска
А как получить эти коэффициенты?
я пошел простым путем.
Поставил 2 маркера на карте на одной широте. Получил их координаты и расстояние между ними (да хоть той же встроенной линейкой на Google Map) и получил пропорции по широте (расстояние в метрах / разница в широте)
С долготой точно так же
Поставил 2 маркера на карте на одной широте. Получил их координаты и расстояние между ними (да хоть той же встроенной линейкой на Google Map) и получил пропорции по широте (расстояние в метрах / разница в широте)
С долготой точно так же
но это сработает только для небольшой территории
По уму нужно высчитывать длину широты для каждого места через радиус Земли и значение широты — но опять же будет приближенно — ведь Земля не идеальный шар.
И учитывать что расстояние будет по дуге.
В общем это на порядок сложнее, но — для небольшой территории и мой способ сойдет
По уму нужно высчитывать длину широты для каждого места через радиус Земли и значение широты — но опять же будет приближенно — ведь Земля не идеальный шар.
И учитывать что расстояние будет по дуге.
В общем это на порядок сложнее, но — для небольшой территории и мой способ сойдет
Я бы на вашем месте поставил условия на координаты ±200 метров, что-бы база не считала расстояния для заведомо далеких заведений.
Просто когда у вас 150 заведений и свой сервер — оно достаточно быстро. С увеличением кол-ва заведений нагрузка будет расти линейно и крупный проект может просесть.
Просто когда у вас 150 заведений и свой сервер — оно достаточно быстро. С увеличением кол-ва заведений нагрузка будет расти линейно и крупный проект может просесть.
вполне разумно, реализовать тоже достаточно просто (коэффициенты у нас уже есть). Т.е. если по долготе или по широте расстояние больше 200, то уже можно отбросить этот вариант
WHERE
(lat-".$row['lat'].")*1110349.7867154 < 200
AND ((lng-".$row['lng'].")*64505.305504) < 200
AND sqrt(POWER(((lat-".$row['lat'].")*1110349.7867154), 2) + POWER(((lng-".$row['lng'].")*64505.305504), 2)) < 200
WHERE
(lat-".$row['lat'].")*1110349.7867154 < 200
AND ((lng-".$row['lng'].")*64505.305504) < 200
AND sqrt(POWER(((lat-".$row['lat'].")*1110349.7867154), 2) + POWER(((lng-".$row['lng'].")*64505.305504), 2)) < 200
только значения по модулю, конечно, взять нужно
Я так и думал, что сделаете эту ошибку :)
(lat-".$row['lat'].")*1110349.7867154 — это все преобразования на уровне базы данных. Вам нужно на PHP найти долготу и широту +200 и -200 метров, а запрос к базе должен быть как можно проще:
$row['lat-200'] = $row['lat'] — 200/1110349.7867154;
$row['lat+200'] = $row['lat'] + 200/1110349.7867154;
$row['lng-200'] = $row['lng'] — 200/64505.305504;
$row['lng+200'] = $row['lng'] + 200/64505.305504;
WHERE
(lat >= {$row['lat-200']} AND lat <= {$row['lat+200']})
AND
(lng >= {$row['lng-200']} AND lng <= {$row['lng+200']})
Тогда (наверное) будут использоваться индексы по lat и lng.
(lat-".$row['lat'].")*1110349.7867154 — это все преобразования на уровне базы данных. Вам нужно на PHP найти долготу и широту +200 и -200 метров, а запрос к базе должен быть как можно проще:
$row['lat-200'] = $row['lat'] — 200/1110349.7867154;
$row['lat+200'] = $row['lat'] + 200/1110349.7867154;
$row['lng-200'] = $row['lng'] — 200/64505.305504;
$row['lng+200'] = $row['lng'] + 200/64505.305504;
WHERE
(lat >= {$row['lat-200']} AND lat <= {$row['lat+200']})
AND
(lng >= {$row['lng-200']} AND lng <= {$row['lng+200']})
Тогда (наверное) будут использоваться индексы по lat и lng.
Вообще, если не особо нужна точность, можно только этими условиями и ограничиться.
кому ошибка, а кому хитрость
сайт работает на виртуальном хостинге. Загрузка базы данных не считается, а оперативка для PHP жестко лимитирована. Пусть в базе считается :)
сайт работает на виртуальном хостинге. Загрузка базы данных не считается, а оперативка для PHP жестко лимитирована. Пусть в базе считается :)
В корне не верный подход. Когда есть варианты использовать индексы и не использовать, двух мнений быть не может. А 4 деления на php это никак не нагрузка.
Офтопик: У наc вот тоже как бы нагрузка на базу не считается. Но в один день хостер взял и отключил акааунт с требованием оптимизировать базу. Пришлось патчить CMS (чертов неткат).
Офтопик: У наc вот тоже как бы нагрузка на базу не считается. Но в один день хостер взял и отключил акааунт с требованием оптимизировать базу. Пришлось патчить CMS (чертов неткат).
Для крупных проектов запросы нужно кешировать.
Я бы сделал дополнительную таблицу многие-ко-многим, где для каждой кафешки есть ее ближайшие соседи. Расстояния рассчитываются один раз при добавлении новой кафешки. При выборках (которых много) получется простой и быстрый join, или можно и двумя запросами (есть мнение что для MySQL иногда так выгоднее).
Минус такого подхода — нет гибкости в задании расстояни до соседа. Т.е. если вы выбрали однажды 200 метров, а через месяц решили, что лучше искать соседей в 500 метрах, то таблицу придется пересчитывать.
В вашем же случае СУБД не имеет возможности использовать индексы.
Минус такого подхода — нет гибкости в задании расстояни до соседа. Т.е. если вы выбрали однажды 200 метров, а через месяц решили, что лучше искать соседей в 500 метрах, то таблицу придется пересчитывать.
В вашем же случае СУБД не имеет возможности использовать индексы.
этот функционал делался с прицелом на пользователей, чтобы они могли найти заведение рядом с работой, домом или просто рядом от заданной точки. Соответственно возможность указать расстояние самим пользователем очень важна для нас. (найти этнический ресторан в 10 минутах ходьбы от дома или недорогую кафешку с бизнес-ланчем в 5 минутах от работы).
Даже если ограничить пользователя в выборе и сделать таблицу для каждого варианта — это не даст существенного выигрыша. Как я писал запрос на 150 заведений отрабатывается за 0,004 секунды на виртуальном хостинге.
Даже если ограничить пользователя в выборе и сделать таблицу для каждого варианта — это не даст существенного выигрыша. Как я писал запрос на 150 заведений отрабатывается за 0,004 секунды на виртуальном хостинге.
Или как вариант: опять-таки таблица многие-ко-многим с уже просчитанными расстояниями между каждой кафешкой, тогда можно одним условием WHERE выбирать. И индексы так же прекрасно будут работать, и расстояние можно выбирать более гибко — хочешь в одном запросе 200 метров, хочешь 500.
Разместите карту внизу, под списком заведений или уменьшите раза в 2. Будет гораздо удобней.
Что то мне подсказывает что стоит заменить
на
для начала
sqrt(POWER(((lat-".$row['lat'].")*1110349.7867154), 2) + POWER(((lng-".$row['lng'].")*64505.305504), 2)) < 200
на
POWER(((lat-".$row['lat'].")*1110349), 2) + POWER(((lng-".$row['lng'].")*64505), 2) < 40000
для начала
Хм, а поясните смысл выборки близлежаших кафешек, ведь на ваш сайт человек заходит из дома/работы, соответственно ему без разницы на сколько близко/далеко находятся эти кафешки, ему важно:
— насколько близко/далеко находятся кафешки от места где он живет или работает
— насколько они соответствуют тому куда он хочет пойти.
Т.е. при посещении страницы заведения прежде всего должна выводиться информация о заведениях такого же типа (музыка, кухня, цены, курение, время работы) и т.д.
Если же развивать вашу идею то необходимо описать что в районе 200 метров находятся так же кафешки… и сделать ползунок или кнопки, расширяющие область поиска.
— насколько близко/далеко находятся кафешки от места где он живет или работает
— насколько они соответствуют тому куда он хочет пойти.
Т.е. при посещении страницы заведения прежде всего должна выводиться информация о заведениях такого же типа (музыка, кухня, цены, курение, время работы) и т.д.
Если же развивать вашу идею то необходимо описать что в районе 200 метров находятся так же кафешки… и сделать ползунок или кнопки, расширяющие область поиска.
На самом деле было 2 цели:
1. ненавязчиво предложить походить по сайту.
2. дать людям выбор, если им кафешка не понравится
1. ненавязчиво предложить походить по сайту.
2. дать людям выбор, если им кафешка не понравится
Creating a Store Locator with PHP, MySQL & Google Maps
code.google.com/intl/ru/apis/maps/articles/phpsqlsearch.html
code.google.com/intl/ru/apis/maps/articles/phpsqlsearch.html
Данная задача усложняется в разы, когда в БД много объектов, и много городов. Приведенная вами формула расчета верна только для города Ульяновск.
Более расширенный вариант:
public static double CalculateDistanceLatLon(double lat1, double lon1,
double lat2, double lon2)
{
int R = 6371; // km earth radius
double dLat = ((lat2 — lat1) * 3.14 / 180);
double dLon = ((lon2 — lon1) * 3.14 / 180);
double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
Math.Cos(lat1 * 3.14 / 180) * Math.Cos(lat2 * 3.14 / 180) *
Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 — a));
return R * c;
}
public static double ToRad(this double value)
{
return value * Math.PI / 180;
}
Целесообразно использовать заранее индексированные данные и связанные данные, а не рассчитывать на лету.
К примеру для выборки ближайших городов с некими объектами, в неком сайте, уходит порядка 5-20 секунд в зависимости от количества объектов в городах участвующих в выборке. Использован такой же
подход, как и у Вас. Предстоит оптимизировать.
У кого есть соображения по оптимизации?
Более расширенный вариант:
public static double CalculateDistanceLatLon(double lat1, double lon1,
double lat2, double lon2)
{
int R = 6371; // km earth radius
double dLat = ((lat2 — lat1) * 3.14 / 180);
double dLon = ((lon2 — lon1) * 3.14 / 180);
double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
Math.Cos(lat1 * 3.14 / 180) * Math.Cos(lat2 * 3.14 / 180) *
Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 — a));
return R * c;
}
public static double ToRad(this double value)
{
return value * Math.PI / 180;
}
Целесообразно использовать заранее индексированные данные и связанные данные, а не рассчитывать на лету.
К примеру для выборки ближайших городов с некими объектами, в неком сайте, уходит порядка 5-20 секунд в зависимости от количества объектов в городах участвующих в выборке. Использован такой же
подход, как и у Вас. Предстоит оптимизировать.
У кого есть соображения по оптимизации?
вариант SQL
ALTER FUNCTION [dbo].[n_geoCalculateDistance](@lat1 float,@lon1 float,@lat2 float,@lon2 float)
RETURNS real AS
BEGIN
declare @distance decimal(19,3)
declare @earthRadius int
declare @deltaLat float
declare @deltaLon float
declare @a float
declare @c float
declare @d decimal(19,3)
set @earthRadius = 6371 --радиус земли в километрах
set @deltaLat = dbo.n_geoToRadian(@lat1 — @lat2)
set @deltaLon = dbo.n_geoToRadian(@lon1 — @lon2)
set @a =
sin(@deltaLat / 2) * sin(@deltaLat / 2) +
cos(dbo.n_geoToRadian(@lat1)) * cos(dbo.n_geoToRadian(@lat2)) * sin(@deltaLon / 2)
* sin(@deltaLon / 2)
set @c = 2 * atn2(sqrt(@a), sqrt(1 — @a))
set @d = @earthRadius * @c
return cast (@d as decimal(10,3)) — дистанция между двумя точками в километрах
END
например Ваш вариант.
select с.id, с.name,
[dbo].[n_geoCalculateDistance](с.lat, с.lon, @latMapCenter, @lonMapCenter) as distance
from cafe с where с.citid = @mySearchCity
order by distance desc
для тысяч объектов — приемлемо… а когда десятки тысяч?
ALTER FUNCTION [dbo].[n_geoCalculateDistance](@lat1 float,@lon1 float,@lat2 float,@lon2 float)
RETURNS real AS
BEGIN
declare @distance decimal(19,3)
declare @earthRadius int
declare @deltaLat float
declare @deltaLon float
declare @a float
declare @c float
declare @d decimal(19,3)
set @earthRadius = 6371 --радиус земли в километрах
set @deltaLat = dbo.n_geoToRadian(@lat1 — @lat2)
set @deltaLon = dbo.n_geoToRadian(@lon1 — @lon2)
set @a =
sin(@deltaLat / 2) * sin(@deltaLat / 2) +
cos(dbo.n_geoToRadian(@lat1)) * cos(dbo.n_geoToRadian(@lat2)) * sin(@deltaLon / 2)
* sin(@deltaLon / 2)
set @c = 2 * atn2(sqrt(@a), sqrt(1 — @a))
set @d = @earthRadius * @c
return cast (@d as decimal(10,3)) — дистанция между двумя точками в километрах
END
например Ваш вариант.
select с.id, с.name,
[dbo].[n_geoCalculateDistance](с.lat, с.lon, @latMapCenter, @lonMapCenter) as distance
from cafe с where с.citid = @mySearchCity
order by distance desc
для тысяч объектов — приемлемо… а когда десятки тысяч?
еще есть вариант без велосипедов, сейчас в популярных СУБД обычно вся эта логика уже реализована
dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html (есть в mysql, начиная с версии 4.1)
www.postgresql.org/docs/8.3/static/functions-geometry.html
и т.д.
dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html (есть в mysql, начиная с версии 4.1)
www.postgresql.org/docs/8.3/static/functions-geometry.html
и т.д.
Ну да, только прикол в том, что у нас с Вами технологии немного другие. Если видели подобное для MS SQL, с удовольствием воспользуюсь.
www.google.ru/search?q=mssql+spatial :)
Как я понял, все это добро есть в MS SQL начиная с Microsoft SQL Server 2008, для более ранних версий (вроде) есть отдельный open-source проект на CodePlex.
Теория «в популярных СУБД обычно эта логика уже реализована» подтверждается)
Как я понял, все это добро есть в MS SQL начиная с Microsoft SQL Server 2008, для более ранних версий (вроде) есть отдельный open-source проект на CodePlex.
Теория «в популярных СУБД обычно эта логика уже реализована» подтверждается)
Всё просто!
private static final double EARTH_RADIOUS = 6372795;
public static double distance(Coordinate c1, Coordinate c2) {
double s1 = Math.toRadians(c1.getLatitude());
double s2 = Math.toRadians(c2.getLatitude());
double d1 = Math.toRadians(c1.getLongitude());
double d2 = Math.toRadians(c2.getLongitude());
return acos( sin(s1)*sin(s2) + cos(s1)*cos(s2)*cos(d2-d1)) * EARTH_RADIOUS;
}
private static final double EARTH_RADIOUS = 6372795;
public static double distance(Coordinate c1, Coordinate c2) {
double s1 = Math.toRadians(c1.getLatitude());
double s2 = Math.toRadians(c2.getLatitude());
double d1 = Math.toRadians(c1.getLongitude());
double d2 = Math.toRadians(c2.getLongitude());
return acos( sin(s1)*sin(s2) + cos(s1)*cos(s2)*cos(d2-d1)) * EARTH_RADIOUS;
}
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Найти соседей на Google Maps