[MySql] Aiuto ad implementare la funzione haversine in una query:distanza tra 2 punti

[AndreaNobili]

Ciao ragazzi,
allora ho un bel problema dal quale non riesco proprio a venire a capo.

Quello che devo fare fà in parte riferimento a queste slide: http://www.scribd.com/doc/2569355/Ge...rch-with-MySQL

Io ho una tabella poi che contiene una lista di punti di interesse, la cui struttura è la seguente:

mysql> describe poi;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| nome      | varchar(50)  | NO   | PRI |         |       | 
| lon       | float        | NO   | PRI | 0       |       | 
| lat       | float        | NO   | PRI | 0       |       | 
| alt       | float        | NO   | PRI | 0       |       | 
| tipologia | tinyint(4)   | YES  |     | NULL    |       | 
| wikiLynk  | varchar(100) | YES  |     | NULL    |       | 
+-----------+--------------+------+-----+---------+-------+

e che contiene dei record del tipo:

mysql> mysql> select * from poi;
+-------------+---------+---------+-----+-----------+-----------------------------------------------------------------+
| nome        | lon     | lat     | alt | tipologia | wikiLynk                                                        |
+-------------+---------+---------+-----+-----------+-----------------------------------------------------------------+
| Colosseo    | 41.8913 | 12.4911 | 0.5 |         0 | http://it.wikipedia.org/wiki/Colosseo                           | 
| Foro Romano | 41.9552 | 12.6768 |   0 |         0 | http://it.wikipedia.org/wiki/Foro_Romano                        | 
| San Pietro  | 41.9457 | 12.6755 |   0 |         0 | http://it.wikipedia.org/wiki/Basilic...ro_in_Vaticano | 
| Pantheon    | 41.9554 | 12.5876 |   0 |         0 | http://it.wikipedia.org/wiki/Pantheon_%28Roma%29                | 
+-------------+---------+---------+-----+-----------+-----------------------------------------------------------------+

Praticamente devo implementare una query che usando la funzione haversine mi restituisce tutti i POI (i punti di interesse) presenti entro un certo raggio dalla posizione dell'utente (espressa sotto forma di longitudine e latitudine)

Stavo vedendo queste slide: http://www.scribd.com/doc/2569355/Ge...rch-with-MySQL ma mi sono bloccato :muro: :muro: :muro:

Praticamente...alla slide 6 dà questa formula (Haversine Formula) che da quello che ho capito serve a calcolare la DISTANZA TRA 2 PUNTI IDENTIFICATI DALLE LORO COORDINATE GPS DI LATITUDINE E DI LONGITUDINE (almeno questo è quello che ho capito...me lo confermate?)

FORMULA PER CALCOLARE LA DISTANZA TRA 2 PUNTI IMPLEMENTATA IN MySql

3956 * 2 * ASIN ( SQRT (POWER(SIN((orig.lat - dest.lat)*pi()/180 / 2), 2) +  COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) *  POWER(SIN((orig.lon - dest.lon) * pi()/180 / 2), 2)  ) ) as distance

Dove 3956 dovrebbe essere il raggio della terra espresso in Km

Ora...usando questa formula devo implementare una query che mi restituisce tutti i POI in un certo raggio centrato nelle coordinate dell'utente.

Quindi si passa alla slide 7 (dove mi incasino di brutto).

Praticamente fà così:

set @orig_lat=122.4058; 
set @orig_lon=37.7907;
set @dist=10;

SELECT *, 3956 * 2 * ASIN(SQRT(
POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2),
2) + COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) *
pi()/180) * POWER(SIN((@orig_lon – dest.lon) *
pi()/180 / 2), 2) )) as distance
FROM hotels dest
having distance < @dist
ORDER BY distance limit 10;

Allora...analizzando un attimo questa query:
nelle prime 3 righe setta 3 variabili nel database contenenti la latitudine e la longitudine dell'utente e la distanza entro cui si vuole effettuare la ricerca (il range di ricerca appunto)

Ok...fin quì ci stò...vabbè la sua tabella si chiama hotels e non poi...vabbè basta sostituire...

Però in questo codice non riesco a capire per lui chi siano: dest.lat e dest.lon, cosa rappresentano? Cioè per ogni poi presente nella mia tabella ci va a mettere la rispettiva latitudine e longitudine per eseguire il test?

Io ho provato a modificare la query così ma non mi funziona:

set @orig_lat=41.889758; 
set @orig_lon=12.4989331;
set @dist=10;

SELECT *, 3956 * 2 * ASIN(SQRT(
POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2),
2) + COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) *
pi()/180) * POWER(SIN((@orig_lon – dest.lon) *
pi()/180 / 2), 2) )) as distance
FROM poi dest
having distance < @dist
ORDER BY distance limit 10;

mi da il seguente messaggio di errore:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '– dest.lon) *
pi()/180 / 2), 2) )) as distance
FROM poi dest
having distance <' at line 4

mmm...mi ci sono spaccato la testa ma visto che non ho mai fatto query di questo tipo non sò proprio dove andare a mettere le mani...c'è qualcuno che mi sà aiutare?

Grazie
Andrea

[nicola75ss]

Hai provato con la query riportata qui?

http://stackoverflow.com/questions/5...ersine-formula

[AndreaNobili]

Ciao,
mi stò veramente impiccando...se puoi ti prego di aiutarmi (o chiunque passi di quà e ne sà qualcosa)...

Allora il problema è il seguente:

Ho la tabella poi fatta così:

mysql> describe poi;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| nome      | varchar(50)  | NO   | PRI |         |       | 
| lon       | float        | NO   | PRI | 0       |       | 
| lat       | float        | NO   | PRI | 0       |       | 
| alt       | float        | NO   | PRI | 0       |       | 
| tipologia | tinyint(4)   | YES  |     | NULL    |       | 
| wikiLynk  | varchar(100) | YES  |     | NULL    |       | 
+-----------+--------------+------+-----+---------+-------+

e che contiene i seguenti record:

mysql> select * from poi;
+--------------------------+---------+---------+-----+-----------+-----------------------------------------------------------------+
| nome                     | lon     | lat     | alt | tipologia | wikiLynk                                                        |
+--------------------------+---------+---------+-----+-----------+-----------------------------------------------------------------+
| Colosseo                 | 41.8913 | 12.4911 | 0.5 |         0 | http://it.wikipedia.org/wiki/Colosseo                           | 
| Foro Romano              | 41.9552 | 12.6768 |   0 |         0 | http://it.wikipedia.org/wiki/Foro_Romano                        | 
| San Pietro               | 41.9457 | 12.6755 |   0 |         0 | http://it.wikipedia.org/wiki/Basilic...ro_in_Vaticano | 
| Pantheon                 | 41.9554 | 12.5876 |   0 |         0 | http://it.wikipedia.org/wiki/Pantheon_%28Roma%29                | 
| Basilica Tempio Pausania | 40.9254 | 9.49686 |   0 |       127 | Voce non presente                                               | 
+--------------------------+---------+---------+-----+-----------+-----------------------------------------------------------------+

Ho trovato 2 possibili soluzioni che non funzionano bene nessuna delle due :-/

1) BASANDOMI SULLE SLIDE:

Ho la seguente query in cui imposto in delle variabili la latitudine e la longitudine del punto in cui si vuole centrare la ricerca ed il raggio di ricerca:

set @orig_lat=41.889758; 
set @orig_lon=12.4989331;
set @dist=10;

SELECT *, 6371 * 2 * ASIN(SQRT(
POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2),
2) + COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) *
pi()/180) * POWER(SIN((@orig_lon - dest.lon) *
pi()/180 / 2), 2) )) as distance
FROM poi dest
having distance < @dist
ORDER BY distance limit 10;

*NB: 6371 è il raggio terrestre medio in Km, perchè vorrei ottenere il risultato in Km e non in miglia

PROBLEMA: Se eseguo questa query con raggio di ricerca 10 (intende 10 Km, giusto?) mi dà sempre empty set, il che è IMPOSSIBILE visto che come si può vedere nella tabella i punti di interesse sono quasi tutti vicinissimi al centro di ricerca

mysql> set @orig_lat=41.889758;
Query OK, 0 rows affected (0,00 sec)

mysql> set @orig_lon=12.4989331;
Query OK, 0 rows affected (0,00 sec)

mysql> set @dist=10;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT *, 6371 * 2 * ASIN(SQRT(
    -> POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2),
    -> 2) + COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) *
    -> pi()/180) * POWER(SIN((@orig_lon - dest.lon) *
    -> pi()/180 / 2), 2) )) as distance
    -> FROM poi dest
    -> having distance < @dist
    -> ORDER BY distance limit 10;
Empty set (0,00 sec)

Se invece aumento molto il raggio di ricerca me li fa vedere tutti:

mysql> set @orig_lat=41.889758; 
Query OK, 0 rows affected (0,00 sec)

mysql> set @orig_lon=12.4989331;
Query OK, 0 rows affected (0,00 sec)

mysql> set @dist=5000;
Query OK, 0 rows affected (0,00 sec)

mysql> 
mysql> SELECT *, 6371 * 2 * ASIN(SQRT(
    -> POWER(SIN((@orig_lat - abs(dest.lat)) * pi()/180 / 2),
    -> 2) + COS(@orig_lat * pi()/180 ) * COS(abs(dest.lat) *
    -> pi()/180) * POWER(SIN((@orig_lon - dest.lon) *
    -> pi()/180 / 2), 2) )) as distance
    -> FROM poi dest
    -> having distance < @dist
    -> ORDER BY distance limit 10;
+--------------------------+---------+---------+-----+-----------+-----------------------------------------------------------------+------------------+
| nome                     | lon     | lat     | alt | tipologia | wikiLynk                                                        | distance         |
+--------------------------+---------+---------+-----+-----------+-----------------------------------------------------------------+------------------+
| San Pietro               | 41.9457 | 12.6755 |   0 |         0 | http://it.wikipedia.org/wiki/Basilic...ro_in_Vaticano | 4318.06921545874 | 
| Foro Romano              | 41.9552 | 12.6768 |   0 |         0 | http://it.wikipedia.org/wiki/Foro_Romano                        | 4318.54355356955 | 
| Pantheon                 | 41.9554 | 12.5876 |   0 |         0 | http://it.wikipedia.org/wiki/Pantheon_%28Roma%29                | 4326.62197008417 | 
| Colosseo                 | 41.8913 | 12.4911 | 0.5 |         0 | http://it.wikipedia.org/wiki/Colosseo                           | 4331.29413001815 | 
| Basilica Tempio Pausania | 40.9254 | 9.49686 |   0 |       127 | Voce non presente                                               | 4547.73875398629 | 
+--------------------------+---------+---------+-----+-----------+-----------------------------------------------------------------+------------------+
5 rows in set (0,00 sec)

Sono veramente disperato...non ne riesco proprio a venire a capo...

[B]2 SOLUZIONE TROVATA QUÌ: http://stackoverflow.com/questions/5...ersine-formula

Mi baso sulla queery che questo sito dà per corretta, cioè:

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance 
FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

e l'ho adattata alla mia tabella:

mysql> set @orig_lat=41.889758; 
Query OK, 0 rows affected (0,00 sec)

mysql> set @orig_lon=12.4989331;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT nome, (6371 * acos(cos(radians(@orig_lat)) * cos(radians(lat)) * cos(radians(lon) - radians(@orig_lon) ) + sin(radians(@orig_lat)) * sin(radians(lat)))) AS distance FROM poi HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;
Empty set (0,00 sec)

Ma come puoi vedere anche quì mi dà empty set a meno da non aumentare moltissimo il valore numerico della condizione di distanza (ad esempio distance < 5000) allora si comporta come la prima query e mi restituisce tutti i record presenti nella tabella poi

Per favore se hai un attimo di tempo mi potresti aiutare? Sono veramente disperato, è da ieri che ci stò impazzendo e Lunedi dovrei portare in revisione questa prima parte del progetto dal mio professore...se non risolvo faccio una figuraccia > > >

Grazie
Andrea

[AndreaNobili]

Risolto da me, avevo invertito i campi lat e lon nella tabella