SQL-запрос для показа объявлений на основе близости к пользователю
Итак, я внедряю систему рекламы на моем сайте. В настоящее время я сохраняю координаты широты / долготы участника в зависимости от города, который они вводят при регистрации. Каждому объявлению будет также предоставлена возможность указывать географические координаты и указывать радиус в милях (хотя я также хотел бы указать опцию в КМ).
Sample tables
--------------users
user_id = 7
name = Charles
lat = xxxxxx.xx
lon = xxxxxx.xx
--------------ads
ad_id = 1121
advertiser_id = 42
ad_type = 728x90
proximity = 50
lat = xxxxxx.xx
lon = xxxxxx.xx
У меня есть этот фрагмент кода, который просматривает мою базу данных пользователей и возвращает пользователей, которые находятся на указанном расстоянии. Я полагаю, что могу отредактировать его так, чтобы он работал для моих нужд Однако вместо того, чтобы показывать всех пользователей на указанном расстоянии, я хочу показывать объявления, которые находятся на расстоянии, указанном самими объявлениями, поскольку один рекламодатель может указать близость, отличную от другой, и т. Д.
Вот оригинальный запрос о близости пользователей, который я использую:
SELECT users.user_id
, ( 3959 * acos( cos( radians('".$lat."') ) *
cos( radians( users.lat ) ) *
cos( radians( users.lng ) -
radians('".$lng."') ) +
sin( radians('".$lat."') ) *
sin( radians( users.lat ) )
) ) AS distance
FROM users
WHERE account_type = '1'
AND `active` = '1'
HAVING distance <= '".$dist."'"Поэтому я думаю, что если я сначала создам запрос, чтобы получить объявления, которые соответствуют зоне и имеют требования близости, а затем выберите их широту / долготу и географические координаты текущих пользователей, а затем …… Я не могу показаться обернуть голову вокруг этого. Затем я выполню несколько расчетов на основе разных настроек близости каждого объявления, соответствующего зоне. Если одно объявление хочет показывать людей в радиусе 100 миль, тогда расчет будет отличаться от того, кто хочет, чтобы его объявление показывалось только тем, кто находится в пределах 20 миль. Вероятно, есть более простой способ сделать это. Будет ли это то, что может помочь создать приложение для построения запросов?
Может как то так?
SELECT ads.ad_id
, ( 3959 * acos( cos( radians('".$user_lat."') ) *
cos( radians( ads.lat ) ) *
cos( radians( ads.lng ) -
radians('".$user_lng."') ) +
sin( radians('".$user_lat."') ) *
sin( radians( ads.lat ) )
)) AS distance
FROM ads
WHERE ad_type = '728x90'
AND `active` = '1'
HAVING distance <= ads.proximity"Решение
Окончательное решение запроса, которое я придумал, было таким:
SELECT ads.ad_id
, proximity AS P, ( 3959 * acos( cos( radians('".$usr_lat."') ) *
cos( radians( ads.lat ) ) *
cos( radians( ads.lon ) -
radians('".$usr_lon."') ) +
sin( radians('".$usr_lat."') ) *
sin( radians( ads.lat ) )
) ) AS distance
FROM ads
WHERE ads.type = '728x90'
AND ads.active = '1'
HAVING distance <= P
OR P='0'
ORDER BY distance ASC
Я добавил OR P = ‘0’ для показа объявлений, в которых не указана близость.
Другие решения
Вы также можете дополнительно ограничить объем данных, которые вы должны рассчитать, прежде чем выполнять все вычисления.
Вы можете ограничить диапазон данных, для которого вы должны рассчитать расстояния, когда вы ограничиваете значения широты / долготы теми, которые находятся поблизости. (формулы приходят из этих слайдов (стр. 12 и далее) Это работает, когда вы рассчитываете по милям … в противном случае вы должны отрегулировать «69» с правильным значением для километров.
1° of latitude ~= 69 miles
1° of longitude ~= cos(latitude)*69
Если вы хотите рассчитать долготу и широту для меньшего прямоугольника, вы можете использовать (с вашими переменными выше):
$lon1 = $lon - $dist / abs(cos(deg2rad($lat))*69);
$lon2 = $lon + $dist / abs(cos(deg2rad($lat))*69);
$lat1 = $lat - ($dist / 69);
$lat2 = $lat + ($dist / 69);
а затем вы измените свой запрос следующим образом:
SELECT ads.ad_id
, proximity AS P, ( 3959 * acos( cos( radians('".$usr_lat."') ) *
cos( radians( ads.lat ) ) *
cos( radians( ads.lon ) -
radians('".$usr_lon."') ) +
sin( radians('".$usr_lat."') ) *
sin( radians( ads.lat ) )
) ) AS distance
FROM ads
WHERE ads.type = '728x90'
AND ads.active = '1'
AND ads.lon BETWEEN $lon1 AND $lon2
AND ads.lat BETWEEN $lat1 AND $lat2
HAVING distance <= P
OR P='0'
ORDER BY distance ASC
Это должно значительно улучшить общую скорость, поскольку вы используете только подмножество всех координат.
И еще одно замечание: если у вас есть широта / долгота / расстояние в качестве числовых полей, вы не должны использовать » вокруг значений, так как это вызывает неявное преобразование, которое также может занять некоторое время …