Newsgroup: it.comp.www.php
Titolo: Gestione distanza tra due punti

Ciao a tutti, ho una tabella con un elenco di records con indicazione
di latiduine e longitudine
conoscendo un nuovo punto riferimento (nuova lati e nuova longi), come
posso recuperare tutti i records che hanno distanza <= X KM?
Grazie mille!!!


wrote:


Ti conviene farlo fare al database, via query, che sarà del tipo
	SELECT * FROM cities WHERE
		distanza(city.lat, city.lon. $mylat, $mylon) < $km;
Ti rimane da avere la funzione "distanza".
Alcuni RDBMS hanno già (o possono avere) il necessario supporto GIS. Ad
esempio per PostgreSQL c'e' PostGIS.
Un sistema da allezziti per fare qualcosa del genere è (il giro di bake
mi serve per non far fare calcoli inutili a MySQL: su tabelle grosse ci
se n'avvede):
	function radians($num)
	{
		// "Now I, even I, ..."
		return 3.1415926535897932384626433832795*$num)/180.0;
	}
	function bake($num, $how)
	{
		return is_numeric($num) ? $how($num) : "$how($num)";
	}
	function sql_distance($alat, $alon, $blat, $blon)
	{
		$r	= 6371;	// Raggio terrestre, pressappoco
		$alat	= bake($alat, 'radians');
		$alon	= bake($alon, 'radians');
		$blat	= bake($blat, 'radians');
		$blon	= bake($blon, 'radians');
		$calat	= bake($alat, 'cos'); $salat = bake($alat, 'sin');
		$calon	= bake($alon, 'cos'); $salon = bake($alon, 'sin');
		$cblat	= bake($blat, 'cos'); $sblat = bake($blat, 'sin');
		$cblon	= bake($blon, 'cos'); $sblon = bake($blon, 'sin');
		return str_replace('+-', '-',
"$r*acos($calat*$cblat*($calon*$cblon+$salon*$sblon)+$salat*$sblat)");
	}
 
	// Prendiamo Roma come punto 1, "lat" e "lon" come punto 2
	// LAT E LON DEVONO ESSERE GRADI DECIMALI!
	$distanza	= sql_distance(41.87, 12.45, 'lat', 'lon');
	$query = "SELECT * FROM cities WHERE $distanza < $km;";
O anche:
	$distanza	=
sql_distance('city1.lat','city1.lon','city2.lat','city2.lon');
	SELECT 
city1.name
, 
city2.name
, $distanza AS km FROM cities
	AS city1 JOIN cities AS city2 WHERE 
city1.name
 < 
city2.name
	ORDER BY 
city1.name
;
per avere una tabella delle distanze... Qui non ci sono numeri che possano
essere precalcolati, e il bake e' del tutto sprecato. Stet.
Per esempio, quello che serve a te, se ho capito bene:
	$query = "SELECT *, $distanza as km FROM cities WHERE $distanza <
800;";
Deve espandersi, per controllo, in:
SELECT *,
6371*acos(0.74466112049491*cos(radians(lat))*(0.97648451454477*cos(radians(lon))+0.21558755264223*sin(radians(lon)))+0.66744274332954*sin(radians(lat)))
as km FROM cities WHERE
6371*acos(0.74466112049491*cos(radians(lat))*(0.97648451454477*cos(radians(lon))+0.21558755264223*sin(radians(lon)))+0.66744274332954*sin(radians(lat)))
< 800;
che da' come risposta (ho preso un database di citta' a casaccio)
+------------+--------+---------+------------------+
| city       | lat    | lon     | km               |
+------------+--------+---------+------------------+
| Rome       |   41.9 |   12.45 | 3.33601748243776 |
| Naples     | 40.833 |   14.25 | 189.387450308023 |
| Venice     | 45.433 |   12.33 | 396.304851779985 |
| Milan      |  45.45 |   9.167 | 477.618696161917 |
| Marseilles |  43.33 |    5.33 | 604.744450300992 |
| Zürich     |  47.35 |  8.5167 | 684.062595736299 |
| Munich     | 48.133 | 11.5833 | 699.726500274517 |
| Belgrade   | 44.867 |  20.533 | 732.983794069663 |
| Lyons      |  45.75 |   4.833 | 747.604103246996 |
| Vienna     | 48.233 |   16.33 | 770.132425845306 |
+------------+--------+---------+------------------+
...se vuoi stampare $distanza e anche usarla, come qui, ti conviene usare
una query diversa:
SELECT... $distanza as km FROM cities ... HAVING km < 800
("HAVING" ti risparmia di rifare due volte i calcoli).
Un controllo a campione e' piu' o meno quello:
http://www.distanza.org/Roma/Vienna
 = 765
http://www.distanza.org/Roma/Marsiglia
 = 603
E Tokio mi dà 9867 contro i "miei" 9860.99432570571.
Close enough for government work. Se devi inviare missili balistici, devi
comunque usare equazioni piu' precise (la distanza Roma-Tokio non era ne'
9860 ne' 9867 km, era 9894).
http://en.wikipedia.org/wiki/Great-circle_distance
A proposito... non controllate i calcoli usando Google Earth.
http://code.google.com/p/earth-issues/issues/detail?id=948
BTW, torno ora da una serata di bagordi, ho il tasso alcolemico pari alla
frequenza della CPU, sicche', come si dice, caveat emptrix.
Leonardo
-- 
Enough, enough!  But, stranger, ere we part,
Glancing farewell to each nefarious bier,
This warning would I beg you take to heart:
There is an end to e'en the worst career!

esempio per PostgreSQL c'e' PostGIS.
E specificamente:
ST_distance_sphere(point, point)
    Returns linear distance in meters between two lat/lon points. Uses a
spherical earth and radius of 6.370.986 meters. Faster than
distance_spheroid(), but less accurate. Only implemented for points.
ST_distance_spheroid(point, point, spheroid)
    Returns linear distance between two lat/lon points given a particular
spheroid. See the explanation of spheroids given for length_spheroid().
Currently only implemented for points.
Dove 'spheroid' potrebbe essere:
	SPHEROID["GRS_1980",6378137,298.257222101]
ST_distance_sphere in MySQL (okay - ormai siamo lontanucci dal PHP) viene
fuori una roba cosi' [1]
DELIMITER $$
DROP FUNCTION IF EXISTS `fanniemae`.`Distance`$$
CREATE DEFINER=`markie`@`%` FUNCTION  `fanniemae`.`Distance`(lat1 DOUBLE,
long1 DOUBLE, lat2 DOUBLE, long2 DOUBLE) RETURNS double
BEGIN
DECLARE R DOUBLE;
DECLARE dLat DOUBLE;
DECLARE dlong DOUBLE;
DECLARE A DOUBLE;
DECLARE C DOUBLE;
SET R = 6371; #Earth Radius in Kilometers
SET dLat = RADIANS(lat2-lat1);
SET dlong = RADIANS(long2-long1);
SET A = (SIN(dLat/2) * SIN(dLat/2)) + (COS(RADIANS(lat1)) *
COS(RADIANS(lat2)) * (SIN(dLong/2) * SIN(dLong/2)));
SET C = 2 * ATAN2(SQRT(A), SQRT(1-A));
RETURN R * C;
END;
 $$
DELIMITER ;
Leonardo
[1]
http://www.xml.lt/Blog/2008/05/01/Calculating+great-circle+distance+in+MySQL+and+Propel


Ti ringrazio per la risposta dettagliata ma mi sono un po' perso.
uso mySQL e condivio la tua scelta di far fare il calcolo a mySQL,
anche perchè vorrei avere già i record filtrati come risultato....
quindi cosa dovrei fare?? creare una funziona in mySQL? (come creo una
funzione da PhpMyAdmin? e quale devo creare esattamente??)
poi da PHP come procedo?
Grazie mille

poi da PHP come procedo?
Se non hai la funzione definita, usi Ctrl+C/Ctrl+V :-)
Se ce l'hai... be', l'adoperi:
	SELECT * FROM cities WHERE distanza(...) < ...
Leonardo


Scusa ma mi sfugge una cosa, in mySQL devo definire questo:
DELIMITER $$
DROP FUNCTION IF EXISTS `fanniemae`.`Distance`$$
CREATE DEFINER=`markie`@`%` FUNCTION  `fanniemae`.`Distance`(lat1
DOUBLE,
long1 DOUBLE, lat2 DOUBLE, long2 DOUBLE) RETURNS double
BEGIN
DECLARE R DOUBLE;
DECLARE dLat DOUBLE;
DECLARE dlong DOUBLE;
DECLARE A DOUBLE;
DECLARE C DOUBLE;
SET R = 6371; #Earth Radius in Kilometers
SET dLat = RADIANS(lat2-lat1);
SET dlong = RADIANS(long2-long1);
SET A = (SIN(dLat/2) * SIN(dLat/2)) + (COS(RADIANS(lat1)) *
COS(RADIANS(lat2)) * (SIN(dLong/2) * SIN(dLong/2)));
SET C = 2 * ATAN2(SQRT(A), SQRT(1-A));
RETURN R * C;
END;
 $$
DELIMITER ;
e non questo:
??
DROP FUNCTION IF EXISTS `distanza`$$
CREATE FUNCTION `distanza`(lat1 DOUBLE,
long1 DOUBLE, lat2 DOUBLE, long2 DOUBLE) RETURNS double
quale delle due??
ultimo dubbio:
in PHP eseguo la query con
SELECT * FROM cities WHERE distanza(lati1, longi1, lati2, long2) < 200
(inteso com Km)
ovvero con lati e longi dai due punti per i quali voglio calcolare la
distanza
Corretto??

distanza
E'satto.


Ma scusa,  non capisco perchè una funzione del tipo
CREATE FUNCTION `distanza`(lat1 DOUBLE,
long1 DOUBLE, lat2 DOUBLE, long2 DOUBLE) RETURNS double
può calcolare la distanza tra due coordinate (lati e longi)
dove sarebbe il calcolo???
scusa la domanda banale ma voglio capire e non fare semplici copia/
incolla

dove sarebbe il calcolo???
Scusa, ma tu ti ricordi solo l'ultimo messaggio a cui rispondi, oppure ti
vai a ricontrollare il thread? :-D
E' chiaro che non ti reincollo tutte le volte tutto il codice delle volte
precedenti.
Il codice che fa il calcolo e' passato tre o quattro messaggi fa, dove il
corpo della funzione recitava
	DECLARE R DOUBLE;
	DECLARE dLat DOUBLE;
	DECLARE dlong DOUBLE;
	[omissis]
	RETURN R * C;
E due messaggi fa ho solo rinominato la funzione in "distanza", visto che
la dichiarazione di funzione precedente non poteva andarti bene. Ma tutto
il codice della funzione e' sempre li'.


Forse non ci eravamo capiti ma soprattutto ho fatto confusione io!!
scusa....
infatti ti avevo chiesto
--
Scusa ma mi sfugge una cosa, in mySQL devo definire questo:
DELIMITER $$
DROP FUNCTION IF EXISTS `fanniemae`.`Distance`$$
CREATE DEFINER=`markie`@`%` FUNCTION  `fanniemae`.`Distance`(lat1
DOUBLE,
long1 DOUBLE, lat2 DOUBLE, long2 DOUBLE) RETURNS double
BEGIN
DECLARE R DOUBLE;
DECLARE dLat DOUBLE;
--
e mi semnbrava di aver capito di non considerare questa porzione ma
solo l'SQL
perdona ma non vorrei fare casino, in definitiva cosa devo utilizzare
(intendo tutto il blocco)
Grazie mille!!!!!

:((( vorrei capire esattamente cosa devo creare nel DB :(
Graaaaaazie

:((( vorrei capire 
Sono giunto alla conclusione di non essere in grado di aiutarti.
Spiacente.
Il mio consiglio e' di provare a leggere un manuale di (My)SQL, poi uno di
PHP. Altrimenti, siamo al livello di apprendisti stregoni che discutano di
oscuri incantesimi.


Potresti semplicemente dirmi se è corretto.
Devo creare questa funzione nel  mio DB
DROP FUNCTION IF EXISTS `MioDB`.`Distanza`$$
CREATE DEFINER=`markie`@`%` FUNCTION  `MioDB`.`Distanza`(lat1
DOUBLE,
long1 DOUBLE, lat2 DOUBLE, long2 DOUBLE) RETURNS double
BEGIN
DECLARE R DOUBLE;
DECLARE dLat DOUBLE;
DECLARE dlong DOUBLE;
DECLARE A DOUBLE;
DECLARE C DOUBLE;
SET R = 6371; #Earth Radius in Kilometers
SET dLat = RADIANS(lat2-lat1);
SET dlong = RADIANS(long2-long1);
SET A = (SIN(dLat/2) * SIN(dLat/2)) + (COS(RADIANS(lat1)) *
COS(RADIANS(lat2)) * (SIN(dLong/2) * SIN(dLong/2)));
SET C = 2 * ATAN2(SQRT(A), SQRT(1-A));
RETURN R * C;
END;
 $$
DELIMITER ;
e poi usarla come nella tua qery di esempio
E' corretto?
Grazie infinite

E' corretto?
No.
Stai procedendo nel modo SBAGLIATO. Stai trattando SQL (e PHP) come se
fossero degli incantesimi, e mi chiedi come pronunciare "Abracadabra".
Se tu capissi cosa stai facendo - cosa che e' VITALE nello studio, nel
lavoro e in generale - avresti gia' trovato la soluzione rileggendo il
thread.
Per altro non e' neanche che ti si chiedano anni di studi e sacrifici,
le pagine relative alle funzioni sul manuale MySQL sono sei o sette; e
dopo saprai scriverti le funzioni per conto tuo.
Dal mio punto di vista, la situazione e' questa: se hai un po' di sale
in zucca, ti ho gia' detto il necessario. Se non ce l'hai, fornirti l'
abracadabra che chiedi
a) Non risolve niente: domani, te ne servira' un altro. Anzi, piu' uno
   ti fornisce abracadabra, piu' ti incazzerai il giorno che non ti si
   dovesse fornire l'ennesimo. A quel punto, meglio se t'incazzi ora e
   si chiude subito la faccenda.
b) E' addirittura dannoso. Armato d'una conoscenza parziale, finiresti
   per affrontare qualsiasi problema (per es.: lavorativo) in modo non
   corretto, o incompleto. Se uno non sa portare un aereo, insegnargli
   come decollare non e' fargli un favore, anzi.


Apprezzo i tuo suggerimenti ed i consigli.
Potresti dirmi dove sbaglio???
Anche leggendo questo post (indicato nella tua risposta)
http://www.xml.lt/Blog/2008/05/01/Calculating+great-circle+distance+in+MySQL+and+Propel
si fa riferimento alla funzione mySQL da creare
quella che inizia da DELIMITER $$  ... fino a .... $$ DELIMITER ;
creata questa funzione nel PHP (quindi nella query SQL che utilizzo in
PHP) faccio:
SELECT * FROM cities WHERE distanza(lati1, longi1, lati2, long2) <
200
In che punto ho scritto una fesseria?

Nessun aiuto :(((