Сравните один запрос с несколькими результатами в PHP
У меня есть два ввода текста. Вот Так:
Итак, у меня есть какой-то динамический поиск ajax. Я передаю входные данные и делаю два разных выбора mysql. Что-то вроде этого:
Таблица итоговая карта - ВЫБЕРИТЕ 1
id -------- latitud-----longitud---
1 | 6.2523915 | -75.5737028 |
2 | 6.2640349 | -75.5990783 |
3 | 6.2642411 | -75.5999791 |
4 | 6.2638461 | -75.5982590 |
-------------------------------------
Таблица итоговая карта - ВЫБЕРИТЕ 2
id -------- latitud-----longitud---
6 | 6.262669 | -75.596799 |
7 | 6.258019 | -75.598001 |
8 | 6.253668 | -75.599374 |
9 | 6.250724 | -75.602335 |
-------------------------------------
Итак, я хочу сравнить каждое отдельное поле "широта и долгота" со всеми полями "широта" и "длина" в SELECT2:
У меня есть это Php, я должен внести некоторые улучшения, но могу сказать, что это сработало:
<?php
$buscar = $_POST['b'];
$buscarcarrera = $_POST['c'];
$whatIWant = substr($buscar, strpos($buscar, "Calle") + 5);
$whatIWant2 = substr($buscarcarrera, strpos($buscarcarrera, "Carrera") + 5);
$vacio = "Calle50A";
$vacioc = "Carrera50A";
if (preg_match('/[A-Za-z]/', $whatIWant))
{
buscar($buscar, "", $buscarcarrera, "");
}
else
{
buscar($buscar, $vacio, $buscarcarrera, $vacioc);
}
function buscar($b, $exclusion, $buscarcarrera, $exclusion2)
{
$con = mysql_connect('localhost', 'root', '');
mysql_select_db('map', $con);
$sql = mysql_query("SELECT * FROM finalmap WHERE calle LIKE '%" . $b . "%' AND calle not in ('$exclusion')", $con);
$contar = mysql_num_rows($sql);
if ($contar == 0)
{
echo "No se han encontrado resultados para '<b>" . $b . "</b>'.";
}
else
{
while ($row = mysql_fetch_array($sql))
{
$nombre = $row['calle'];
$id = $row['id'];
$lat = $row['latitud'];
$lon = $row['longitud'];
}
}
$sql2 = mysql_query("SELECT * FROM finalmap WHERE calle LIKE '%" . $buscarcarrera . "%' AND calle not in ('$exclusion2')", $con);
$contar2 = mysql_num_rows($sql2);
if ($contar2 == 0)
{
echo "No se han encontrado resultados para '<b>" . $b . "</b>'.";
}
else
{
while ($row2 = mysql_fetch_array($sql2))
{
$nombre2 = $row2['calle'];
$id2 = $row2['id'];
$lat2 = $row2['latitud'];
$lon2 = $row2['longitud'];
}
}
}
function distance($lat1, $lon1, $lat2, $lon2, $unit)
{
$theta = $lon1 - $lon2;
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist * 60 * 1.1515;
$unit = strtoupper($unit);
if ($unit == "K")
{
return ($miles * 1.609344);
}
else
if ($unit == "N")
{
return ($miles * 0.8684);
}
else
{
return $miles;
}
}
echo distance(32.9697, -96.80322, 29.46786, -98.53506, "M") . " Miles<br />";
echo distance(32.9697, -96.80322, 29.46786, -98.53506, "K") . " Kilometers<br />";
echo distance(32.9697, -96.80322, 29.46786, -98.53506, "N") . " Nautical Miles<br />";
?>
Затем: как можно сравнить каждое из значений, используя функцию, чтобы определить близость между координатами (используя мою функцию distance()
)?. Моя проблема в том, что я не знаю, как сравнить расстояние между каждой точкой 1-го запроса с каждой точкой 2-го запроса, со всеми возможными комбинациями.
Я хочу, чтобы у меня было что-то вроде этой функции сравнения (лат1, лон1, лат2, лон2); (lat1, lon1, lat3, lon3), (lat1, lon1, lat4, lon4), (lat1, lon1, lat5, lon5), (lat1, lon1, lat6, lon6) и так далее.
Заранее большое вам спасибо за любую оказанную помощь
9 answers
Ваша логика совершенно неверна, и вы сильно усложняете то, что ищете. Это намного проще, чем вы думаете.
Во-первых: чтобы значительно ускорить ваш скрипт и запросы, а также защитить его от любых атак, используйте подготовленные PDO инструкции и протоколы безопасности. Mysql никакие подготовленные заявления не являются устаревшими более десяти лет!!!
Мой опыт (25 лет программирования) показывает, что я никогда не доверяю тому, что поступает на ваш сервер, поэтому я я немного боюсь безопасности.... В этом учебном пособии объясняются первые основные шаги, которые вам нужно сделать, и объясняется, каким должен быть этот скрипт (скопируйте все коды в том виде, в каком они есть).
Обратите внимание, что использование локального IP-адреса вместо локального хоста ускорит подключение к mysql от 2 до 5 в зависимости от платформы разработчика (lamp, wamp и т. Д.)
установите параметры выполнения
ini_set('memory_limit', '64M'); // memory limit used by script
set_time_limit(0); // no time limit
Установите защищенное соединение с mysql
try {
$con = new PDO('mysql:host=127.0.0.1;dbname=map;charset=UTF8','root','password');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
Установите второй уровень протокол безопасности, чтобы проверить, действительно ли это вызов ajax
$isAjax = isset($_SERVER['HTTP_X_REQUESTED_WITH']) AND
strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) === 'xmlhttprequest';
if (!$isAjax) {
$user_error = 'Access denied - not an AJAX request from allowed computer...';
trigger_error($user_error, E_USER_ERROR);
exit;
}
Второе: Используйте функцию расстояния ВНУТРИ вашего запроса mysql, чтобы правильно отсортировать результаты, которые вы ищете. Пожалуйста, обратите внимание, что используемое здесь расстояние mysql отличается от того, которое вы используете в настоящее время (быстрее и точнее). Это даст вам расстояние между контрольными точками из таблицы 1 и всеми остальными из таблицы 2 в километрах, упорядоченное по расстоянию (ближайшему как первому ряд).
Мы предполагаем, что вы получаете свои результаты от своего ajax следующим образом:
if($isAjax){
$param4 = '%' . $_POST['b']. '%'; // MYSQL LIKE bind param buscar
$param5 = '(' . $_POST['c'] . ')'; //MYSQL NOT IN bind param buscarcarrera
}
Мы предполагаем, что вы получите свои результаты от первого запроса следующим образом (упрощенный):
$sql1_array = array(
array('id' => 1, 'Lati' => 6.2523915, 'Longi' => -75.5737028),
array('id' => 2, 'Lati' => 6.2640349, 'Longi' => -75.5990783)
);
(примечание: всегда используйте "lati" и "longi", чтобы избежать столкновения с функцией PHP, такой как "long")
Ваше подготовленное заявление:
$sql2 = "SELECT *, ( 6371 * acos( cos(radians(?))
* cos(radians(Latitude)) * cos(radians(Longitude) - radians(?)) +
sin( radians(?) ) * sin(radians(Latitude)))) AS distance
FROM finalmap WHERE calle LIKE ? AND calle NOT IN ?
ORDER BY distance LIMIT 10";
$stmt = $con->prepare($sql2);
Третий: Выполните цикл с каждым результатом запроса 1, чтобы получить расстояния по сравнению с таблицей 2. В цикле вы привязываете свои параметры к вашему подготовленному stmt. Это значительно ускорит ваши запросы, так как соединение с базой данных является постоянным, а запрос подготовлен с помощью PDO (скомпилирован и находится в памяти).
foreach ($sql1_array as $key => $value) {
$stmt->bindParam(1, $value['Lati'], PDO::PARAM_INT);
$stmt->bindParam(2, $value['Longi'], PDO::PARAM_INT);
$stmt->bindParam(3, $value['Lati'], PDO::PARAM_INT);
$stmt->bindParam(4, $param4, PDO::PARAM_STR);
$stmt->bindParam(5, $param5, PDO::PARAM_STR);
$stmt->execute();
$count = $stmt->rowCount();
if ($count >= 1) {
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
/* $result ordered by distance */
|**************************************|
|field 0 | field 1 | field 2 | distance|
|**************************************|
| 1 | a1 | a2 | 0.00123 | 1 meters
| 2 | b1 | b2 | 0.01202 | 12 meters
| 3 | c1 | c2 | 0.23453 | 234 meters
| 4 | d1 | d2 | 1.58741 | 1km and 587 meters
|**************************************|
Я думаю, что у вас все готово! Наслаждаться.
Вы должны сделать что-то вроде этого:
function buscar($b, $exclusion, $buscarcarrera, $exclusion2)
{
$nombre1 = array();
$id1= array();
$lat1= array();
$lon1= array();
$nombre2 = array();
$id2= array();
$lat2= array();
$lon2= array();
$con = mysql_connect('localhost', 'root', '');
mysql_select_db('map', $con);
$sql = mysql_query("SELECT * FROM finalmap WHERE calle LIKE '%" . $b . "%' AND calle not in ('$exclusion')", $con);
$contar = mysql_num_rows($sql);
if ($contar == 0)
{
echo "No se han encontrado resultados para '<b>" . $b . "</b>'.";
} else {
while ($row = mysql_fetch_array($sql))
{
if(array_key_exists('calle',$row) && array_key_exists('id',$row) && array_key_exists('latitud',$row) && array_key_exists('longitud',$row)) {
// ignore data with a missing field
$nombre1[] = $row['calle'];
$id1[] = $row['id'];
$lat1[] = $row['latitud'];
$lon1[] = $row['longitud'];
}
}
}
$sql2 = mysql_query("SELECT * FROM finalmap WHERE calle LIKE '%" . $buscarcarrera . "%' AND calle not in ('$exclusion2')", $con);
$contar2 = mysql_num_rows($sql2);
if ($contar2 == 0)
{
echo "No se han encontrado resultados para '<b>" . $b . "</b>'.";
} else {
while ($row2 = mysql_fetch_array($sql2))
{
if(array_key_exists('calle',$row2) && array_key_exists('id',$row2) && array_key_exists('latitud',$row2) && array_key_exists('longitud',$row2)) {
// ignore data with a missing field
$nombre2[] = $row2['calle'];
$id2[] = $row2['id'];
$lat2[] = $row2['latitud'];
$lon2[] = $row2['longitud'];
}
}
}
$nbData1 = count($nombre1);
$nbData2 = count($nombre2);
if($nbData1 > 0 && $nbData2 > 0) {
// there are data in both 1st and 2nd query
$distances = array();
// loop on all data from 1st query
for($i1 = 0; $i1 < $nbData1; $i1++) {
// loop on all data from 2nd query
for($i2 = $i1; $i2 < $nbData2; $i2++) {
// storing result of the distance
$distances[] = array('i1' => $i1,
'i2' => $i2,
'dist' => array('M' => distance($lat1[$i1], $lon1[$i1], $lat2[$i2], $lon2[$i2], 'M'),
'K' => distance($lat1[$i1], $lon1[$i1], $lat2[$i2], $lon2[$i2], 'K'),
'N' => distance($lat1[$i1], $lon1[$i1], $lat2[$i2], $lon2[$i2], 'N')
)
)
}
}
}
foreach($distances as $distance) {
// use each result
$i1 = $distance['i1'];
$lat1 = $distance['lat1'];
$lon1 = $distance['lon1'];
$i2 = $distance['i1'];
$lat2 = $distance['lat2'];
$lon2 = $distance['lon2'];
$dist = $distance['dist'];
$distM = $dist['M'];
$distK = $dist['K'];
$distN = $dist['N'];
echo "Have fun with theses data !\n";
}
}
Итак, сохраняем содержимое ваших строк.
Затем проанализируйте все ваши данные, чтобы рассчитать все возможные расстояния.
Затем используйте результат по своему усмотрению.
Это намного лучше для обработки вычисления расстояния во 2-м запросе, и это быстрее, поэтому функция distance() не нужна. Также кажется, что ваша формула неверна.
$buscar = $_POST['b'];
$buscarcarrera = $_POST['c'];
$whatIWant = substr($buscar, strpos($buscar, "Calle") + 5);
$whatIWant2 = substr($buscarcarrera, strpos($buscarcarrera, "Carrera") + 5);
$vacio = "Calle50A";
$vacioc = "Carrera50A";
if (preg_match('/[A-Za-z]/', $whatIWant))
{
buscar($buscar, "", $buscarcarrera, "");
}
else
{
buscar($buscar, $vacio, $buscarcarrera, $vacioc);
}
function buscar($b, $exclusion, $buscarcarrera, $exclusion2)
{
$con = mysql_connect('localhost', 'root', '');
mysql_select_db('map', $con);
$sql = mysql_query("SELECT * FROM finalmap WHERE calle LIKE '%" . $b . "%' AND calle not in ('$exclusion')", $con);
$contar = mysql_num_rows($sql);
if ($contar == 0)
{
echo "No se han encontrado resultados para '<b>" . $b . "</b>'.";
}
else
{
while ($row = mysql_fetch_array($sql))
{
$nombre = $row['calle'];
$id = $row['id'];
$lat = $row['latitud'];
$lon = $row['longitud'];
$dLat = '((latitud-'.$lat.')*PI()/180)';
$dLong = '((longitud-'.$lon.')*PI()/180)';
$a = '(sin('.$dLat.'/2) * sin('.$dLat.'/2) + cos('.$lat.'*pi()/180) * cos(map_coords_1*pi()/180) * sin('.$dLong.'/2) * sin('.$dLong.'/2))';
$sub_sql = '2*atan2(sqrt('.$a.'), sqrt(1-'.$a.'))';
$results = mysql_query(
"SELECT DISTINCT
id, calle, " . $sub_sql . " AS distance FROM finalmap
WHERE
calle LIKE '%" . $buscarcarrera . "%' AND calle not in ('$exclusion2')
ORDER BY
distance
", $con);
if ($results == 0)
{
echo "No se han encontrado resultados para '<b>" . $buscarcarrera . "</b>'.";
}
else
{
while ($row2 = mysql_fetch_array($results)) {
echo "Calle: " . $row2['calle'] . " Miles - " . $row2['distance']*3956;
echo "Calle: " . $row2['calle'] . " Kilometers - " . $row2['distance']*6367;
echo "Calle: " . $row2['calle'] . " Nautical Miles - " . $row2['distance']*3435;
}
}
}
}
}
Я даю ему первую попытку.
$output_array = array();
$i = 0;
while ($row = mysql_fetch_array($sql))
{
$j = 0;
while ($row2 = mysql_fetch_array($sql2))
{
$out_array[$i][$j][0] = $row2['latitud'] - $row['latitud']
$out_array[$i][$j][1] = $row2['longitud'] - $row['longitud']
++$j;
}
++$i;
}
print "<pre>";
print_r($out_array);
print "</pre>";
Предварительный просмотр (со случайными числами):
Array
(
[0] => Array
(
[0] => Array
(
[0] => 6
[1] => 9
)
[1] => Array
(
[0] => 6
[1] => 9
)
[2] => Array
(
[0] => 6
[1] => 9
)
............
............
............
Массив[0][0][0] == разница между 1-й широтой результата (таблица 1) И 1-й широтой результата (таблица 2)
Массив[0][1][0] == разница по широте (таблица 1-го разрешения) И (таблица 2-го разрешения)
Тот же случай для longitud.
Я думаю, что перекрестное соединение таблицы выполнит вашу работу.
Допустим, первая таблица - это таблица 1, а вторая - таблица 2. Итак, запрос:
Select Table1.id, Table1.latitud, Table1.longitude,
Table2.id, Table2.latitud, Table2.longitude
from
Table1 , Table2
Это даст вам всю комбинацию, которая вам требовалась.
Я думаю, что ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ сработает. Я просто смущен тем, что вы предоставите значение длины и широты таблицы 1 или нет.
Поэтому я пишу оба варианта.
Допустим, первая таблица - это таблица 1, а вторая - таблица 2
- Когда вы предоставите значение lat и long таблицы 1
SELECT * FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.latitude=t2.latitud OR t2.longitude=t1.longitude WHERE t1.latitude='#enter the value#' AND t1.longitude='#enter the value#'
- Это обеспечит результат фильтрации равного значения в обеих таблицах
SELECT * FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.latitude=t2.latitud OR t2.longitude=t1.longitude
Дайте мне знать, если понадобится что-нибудь еще.
Благодарю вас.
Однако вы можете легко сгенерировать необходимый результирующий набор в SQL без необходимости писать кучу PHP-кода.
Также было бы намного проще, если бы вы на самом деле показали, как вы хотите, чтобы ваши результаты выглядели в виде таблицы. Я в замешательстве относительно того, что вы на самом деле пытаетесь сделать. Я предполагаю, что вы хотите сопоставить два запроса, когда либо широты точно совпадают ИЛИ долготы точно совпадают.
Обновление: Я нашел SQL, похороненный в ваш PHP-код. Что-то вроде этого должно сработать:
"SELECT t1.latitud AS lat1, t1.longitud AS lon1,
t2.latitud AS lat2, t2.longitud AS lon2
FROM finalmap AS t1
INNER JOIN finalmap AS t2 ON t1.latitud=t2.latitud OR t1.longitud=t2.longitud
WHERE t1.calle LIKE '%" . $b . "%' AND t1.calle not in ('$exclusion')
AND t2.calle LIKE '%" . $buscarcarrera . "%' AND t2.calle NOT IN ('exclusion2');"
Если вы хотите получить все результаты из SELECT 1, даже если в SELECT 2 нет совпадений, измените INNER JOIN
на LEFT JOIN
.
Я также заметил, что вы проверяете, имеют ли поля значения после возврата запроса. Вы можете выполнить эти проверки в предложении WHERE
в SQL-запросе таким образом, вы не получите никаких результатов, которые вам на самом деле не нужны. Это просто зависит от того, как выглядит "без значения" (NULL? пустая строка? и т.д.) относительно того, как бы вы это проверили.
Вы можете взять декартово произведение обеих таблиц, чтобы найти все возможные комбинации.
В противном случае возьмите два цикла, например
for(int i=0;i < n; i++)
for(int j=0; j< m; j++)
someProcess(a[i][0], a[i][1], b[j][0], b[j][1]);
И это даст вам все возможные комбинации.
Затем ваша функция должна иметь логику для геокодирования/обратного геокодирования для вычисления расстояния.
Я надеюсь, что это поможет!!
Я бы поддержал этот ответ, но добавил другой важный момент:
Четвертое: не используйте вычисление расстояния и сортировку с помощью специально закодированной формулы haversine ни в PHP, ни в MySQL. MySQL поддерживает пространственные функции в течение длительного времени. Более того, при работе с ГИС лучше перейти на PostgreSQL + расширение PostGIS
Пятое: Используйте MySQL или любые другие пространственные индексы СУБД при сортировке по расстоянию. Я сомневаюсь, что вы можете что-либо закодировать более эффективный с вашим подходом "сравнение каждого расстояния".
Решение: просмотрите этот ответ и примените следующие запросы к вашей таблице БД:
ALTER TABLE `finalmap` ENGINE=MYISAM; -- changing your storage engine to MyISAM. It supports spatial indexes in MySQL
ALTER TABLE `finalmap` ADD `pt` POINT NOT NULL; -- adding POINT() spatial datatype for zip cetner. Eventually, you may remove the old lat/lng decimal columns
UPDATE `finalmap` SET `pt` = POINT(latitud,longitud);
ALTER TABLE `finalmap` ADD SPATIAL INDEX(`pt`);
Затем, предполагая, что вы получаете координаты на входе (скажем, (6.25,-75.6)), которые вам нужно сравнить со всеми записями в вашей таблице и найти ближайшую, вам нужно:
SELECT *, ST_Distance_Sphere(`pt`,POINT('6.25','-75.6')) as `dst`
FROM `fnialmap`
ORDER BY `dst`
LIMIT 1;
И, наконец, шестое: пожалуйста, назовите свои столбцы и переменные на английском языке. Может быть так, что кто-то другой поддерживает ваш код в будущем, и им придется иметь дело с "широтой" и "длиной"