problema di ordinamento

pierom

Nuovo Utente
15 Apr 2015
12
0
0
Problema visibile in http://www.ristomagic.com Provincia: udine perchè c'è qualche ristorante. Se Tua posizione -> Localizzami e poi ricerca per Voce di Menu vedi che l'ordine di distanza non c'è (se c'è è casuale). Vorrei metterlo, come è negli altri 2 tipi di ricerca.

Se un utente si fà localizzare, devo presentargli la lista dei ristoranti in ordine di distanza.
Faccio tre tipi di ricerca. I primi due non ho problemi, mentre con il terzo non ne esco.

fla_risto è la tabella con i dati del ristorante, che contiene lat e lng
menu tabella dei piatti di Menu. Campi: id, owner (che è l'id di fla_risto )

$latitude, $longitude sono deli'utente
lat lng sono i campi del ristorante

Attualmente ho messo:
PHP:
$query_lat2 = " (((acos(sin((".$latitude."*$pigreco/180)) * 
	sin((`lat`*$pigreco/180))+cos((".$latitude."*$pigreco/180)) * 
	cos((`lat`*$pigreco/180)) * cos(((".$longitude."- `lng`)
	*$pigreco/180))))*180/$pigreco)*60*1.1515*1.609344) ";
	
 $query = "SELECT *, MATCH(des) AGAINST('$keyword*' IN BOOLEAN MODE) AS attinenza FROM menus ";
	$query .= " WHERE MATCH(des) AGAINST('$keyword*' IN BOOLEAN MODE) ";
	$query .= " AND owner IN (
			SELECT id FROM fla_risto 
			WHERE ...
			AND ...
			
            
       if($calc_dist == true){
		$query .= " AND ($query_lat2 <= $raggio_di_interesse )";
	}
	$query .= ")"; // chiude  AND owner IN (
	$query .= " ORDER BY menus.owner ASC, attinenza DESC ";
grazie in anticipo
 
Ultima modifica di un moderatore:

flameseeker

Utente Attivo
27 Nov 2013
699
0
0
Ciò di cui hai bisogno è la formula dell'enisenoverso.

Pseudocodice:
Codice:
# earth_radius -> Raggio della terra
# user_*  latitudine e longitudine di partenza
# destination_*  latitudine e longitudine di arrivo

delta_longitude = destination_longitude - user_longitude
delta_latitude = destination_latitude - user_latitude
haversine_formula = (sin(delta_latitude/2))^2 + cos(user_latitude) * cos(destination_latitude) * (sin(delta_longitude/2))^2 

linear_distance = earth_radius * (2 * atan2(sqrt(haversine_formula), sqrt(1-haversine_formula)))
Ti basta tradurre la formula usando le funzioni trigonometriche di mysql, associarla ad un alias che corrisponda al linear_distance dello pseudocodice e infine ordinare la query per questo valore.
 

pierom

Nuovo Utente
15 Apr 2015
12
0
0
forse ho le idee confuse, ma riesco a dare un elenco in ordine di distanza con query 'normali' (i primi due tipi di ricerca): Non so invece come mettere ORDER BY distance nella query che ricerca una voce di menu dei piatti fatti da ogni ristorante. Cioè, supponiamo risto_1 faccia "gnocchi al gorgonzola" e risto_2 faccia "gnocchi di casa". Un utente cerca "gnocchi". Io devo mostrargli prima il ristorante più vicino a lui. Come imbastisco la query? Quella che ho fatto elenca tutti i gnocchi senza considerare la distanza. Grazie.
 

flameseeker

Utente Attivo
27 Nov 2013
699
0
0
Dovresti spostare il calcolo della distanza nella select e associarlo quindi ad un alias, a quel punto la condizione sull'alias la realizzi mediante l'istruzione HAVING (così da non ripetere il calcolo) e in seguito puoi usarlo anche nell'ORDER BY per riordinare i record.


In ogni caso, la tua query mi sembra molto voluminosa e incline a problemi di performance, ti fornisco un esempio di come gestirei personalmente la questione per cercare di tenere tutto più ordinato.

Primo: Creare una funzione che isoli il calcolo della distanza...
Codice:
-- 6371 è il raggio della terra in km, la funzione ritornerà quindi il valore della distanza in km.

CREATE FUNCTION `GEODISTANCE`(`start_latitude` DECIMAL(8,6), `start_longitude` DECIMAL(9,6), `end_latitude` DECIMAL(8,6), `end_longitude` DECIMAL(9,6)) RETURNS decimal(11,6)
RETURN 6371 * 2 * ASIN(
	SQRT(
		POWER(SIN((start_latitude - end_latitude) * pi()/180 / 2), 2) + 
		COS(start_latitude * pi()/180) * 
		COS(end_latitude * pi()/180) * 
		POWER(SIN((start_longitude - end_longitude) * pi()/180 / 2), 2)
	)
);
Secondo: Creo le tabelle che contengono rispettivamente i ristoranti in elenco, le loro coordinate e i piatti contenuti nei menù degli stessi.
Codice:
CREATE TABLE IF NOT EXISTS `restaurants` (
  `restaurantid` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `latitude` decimal(8,6) DEFAULT NULL,
  `longitude` decimal(9,6) DEFAULT NULL,
  PRIMARY KEY (`restaurantid`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

INSERT INTO `restaurants` (`restaurantid`, `name`, `latitude`, `longitude`) VALUES
	(1, 'Carlo\'s', 41.403380, 2.174030),
	(2, 'Pizzeria Italia', 41.463380, 2.184500),
	(3, 'La Maison', 41.493380, 2.124500);


CREATE TABLE IF NOT EXISTS `menus` (
  `menuid` int(10) NOT NULL AUTO_INCREMENT,
  `restaurantid` int(10) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`menuid`),
  KEY `restaurantid` (`restaurantid`),
  FULLTEXT KEY `name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

INSERT INTO `menus` (`menuid`, `restaurantid`, `name`) VALUES
	(1, 1, 'Gnocchi Alla Gorgonzola'),
	(2, 1, 'Spaghetti Di Mare'),
	(3, 1, 'Linguine alla Bolognese'),
	(4, 2, 'Gnocchi Alla Sorrentina'),
	(5, 2, 'Spaghetti Di Mare'),
	(6, 2, 'Linguine alla Bolognese'),
	(7, 3, 'Gnocchi Fatti In Casa'),
	(8, 3, 'Spaghetti Di Mare'),
	(9, 3, 'Linguine alla Bolognese');

Terzo: Creiamo una vista che associ le voci dei menù ai ristoranti (e ci semplifichi la creazione delle query)..
Codice:
CREATE VIEW `restaurants_menus` AS select `restaurants`.`restaurantid` AS `restaurantid`,`menus`.`menuid` AS `menuid`,`restaurants`.`name` AS `name`,`menus`.`name` AS `menu_entry`,`restaurants`.`latitude` AS `latitude`,`restaurants`.`longitude` AS `longitude` from (`restaurants` join `menus` on((`restaurants`.`restaurantid` = `menus`.`restaurantid`)));

A questo punto, la nostra query di ricerca diventa molto semplice:
Codice:
-- Coordinate utente: 41.40838, 2.12403

SELECT *, 
	MATCH(menu_entry) AGAINST('Gnocchi*') AS relevance,
	GEODISTANCE(41.40838, 2.12403, latitude, longitude) AS distance_km
FROM restaurants_menus 
HAVING relevance > 0
ORDER BY distance_km, relevance DESC
Se poi vogliamo limitare la ricerca entro una distanza massima specifica basterà aggiungere un ulteriore clausola:
Codice:
-- Coordinate utente: 41.40838, 2.12403

SELECT *, 
	MATCH(menu_entry) AGAINST('Gnocchi*') AS relevance,
	GEODISTANCE(41.40838, 2.12403, latitude, longitude) AS distance_km
FROM restaurants_menus 
HAVING relevance > 0 AND distance_km <= 8
ORDER BY distance_km, relevance DESC
 

pierom

Nuovo Utente
15 Apr 2015
12
0
0
ho letto. Ora provo a vedere se ci capirò qualcosa. Al momento vado a sensazione. La tua credo sia la soluzione. Ho anche la sensazione che sarà dura per me, ma ci provo. Ti devo in tutti i casi una birra. Vai mai a Udine?
 

flameseeker

Utente Attivo
27 Nov 2013
699
0
0
Se copi tutte le istruzioni preparatorie in un database di test, le query di ricerca che ti ho mostrato dovrebbero funzionare senza problemi, questo può aiutarti a capirci sicuramente meglio.

Nel caso, ho copia-incollato tutto su questo fiddle.
Nel fiddle, l'editor di destra compone il database di test, quello di sinistra ti permette di eseguire le query sul database.

Se posso chiare eventuali dubbi non ti fare scrupoli ;)

Vai mai a Udine?
Mai stato, ma grazie comunque.
 

pierom

Nuovo Utente
15 Apr 2015
12
0
0
nel mio PC ho VERSION() 5.0.83-community-nt
Chiedo scusa, ma ho scoperto ora che esistonoo la FUNCTION e VIEW. Spero di non fare domande tanto idiote.
Ho adattato ma... insieme vuoto. Una domanda: questa query (installati FUNCTION e VIEW dovrebbe restituirmi qualcosa? (mi dà MySQL ha restituito un insieme vuoto (i.e. zero righe). ( La query ha impiegato 0.0053 sec )). grazie

SELECT *,
GEODISTANCE(41.40838, 2.12403, 40.40838, 244521) AS distance_km
FROM restaurants_menus
 

flameseeker

Utente Attivo
27 Nov 2013
699
0
0
Uhm.. mysql 5.0 è un po vecchiotto, dubito anche ci siano provider che offrono una versione inferiore alla 5.1.
Ti consiglierei di aggiornare la tua versione locale, dalla 5.1 MySql ha introdotto moltissime migliorie e piccoli cambiamenti.

Quanto ai dubbi che hai sollevato cercherò di fare ulteriore chiarezza, purtroppo immagino che si tratta di molte novità da assorbire tutte in una volta se non le conoscevi, ti consiglio quindi eventualmente poi di consultare le pagine di documentazione dal sito stesso di mysql e magari qualche guida pratica per capire meglio i concetti riguardo le viste.


Tornando a noi.

L'istruzione CREATE FUNCTION non fa nient'altro che definire una funzione personalizzata, esattamente come se la dichiarassi in php o in un qualunque altro linguaggio, penso sia abbastanza chiaro e nell'esempio l'ho semplicemente usata per realizzare una funzione che, dati due coppie di coordinate, ritorna la distanza in chilometri.

Codice:
-- La dichiarazione della funzione
`GEODISTANCE`(
    `start_latitude` DECIMAL(8,6), 
    `start_longitude` DECIMAL(9,6), 
    `end_latitude` DECIMAL(8,6), 
    `end_longitude` DECIMAL(9,6)
)
Quindi se effettui questa query nel database..
Codice:
SELECT GEODISTANCE(41.40838, 2.12403, 40.40838, 2.44521)
..ti ritroverai come risultato
Codice:
114.423577
che significa semplicemente che tra le coordinate lat 41.40838 long 2.12403 e le coordinate lat 40.40838 long 2.44521 ci sono circa 114 Km di distanza.

CREATE VIEW serve invece a realizzare una tabella fittizia.
Questa tabella assume le colonne e i dati che vengono dichiarati e ritornati dalla query usata per creare la VIEW.

Esaminando l'esempio che ti ho scritto (facendo un po di ordine per renderlo più leggibile)
Codice:
CREATE VIEW `restaurants_menus` AS 
select `restaurants`.`restaurantid` AS `restaurantid`,
       `menus`.`menuid` AS `menuid`,
       `restaurants`.`name` AS `name`,
       `menus`.`name` AS `menu_entry`,
       `restaurants`.`latitude` AS `latitude`,
       `restaurants`.`longitude` AS `longitude` 
from (`restaurants` 
    join `menus` on((`restaurants`.`restaurantid` = `menus`.`restaurantid`))
);
noterai che nel database crea un oggetto vista, simile ad una tabella, avente come colonne i campi definiti nel select della query usata per creare la vista e come dati quelli che la stessa ritornerebbe.

Si può dire che una vista sia come un alias riferito ad una query, col vantaggio che la si può interrogare come se fosse una comunissima tabella.

Nel caso dell'esempio realizzo la vista per creare una tabella fittizia chiamata "restaurants_menu" la quale associa i ristoranti che ho nella tabella restaurants, ma soprattutto le loro coordinate, alle singole voci dei menù presenti nella tabella menus.

Di conseguenza, quando nella query di ricerca uso questa istruzione..
Codice:
GEODISTANCE(41.40838, 2.12403, latitude, longitude) AS distance_km
..i campi latitude e longitude sono quelli associati al ristorante che utilizzo come punto d'arrivo nella funzione al fine di ottenere la distanza in km dall'utente (le coordinate che ho scritto a mano ma che tu hai come variabili $latitude e $longitude).

Ritornando poi a dare un occhio anche alla query di ricerca, giusto per chiarire qualche altro aspetto..
Codice:
SELECT *, 
	MATCH(menu_entry) AGAINST('Gnocchi*') AS relevance,
	GEODISTANCE(41.40838, 2.12403, latitude, longitude) AS distance_km
FROM restaurants_menus 
HAVING relevance > 0 AND distance_km <= 8
ORDER BY distance_km, relevance DESC
noterai che, sia il risultato del MATCH che della funzione GEODISTANCE, li associo a degli alias chiamati rispettivamente relevance e distance_km.

Mysql non permette nella clausola WHERE di verificare delle condizioni sugli alias, ma mette a disposizione la clausola HAVING per poterlo fare:
Codice:
HAVING relevance > 0 AND distance_km <= 8
In questo caso stiamo indicando alla query di ritornare solo i record che hanno qualcosa a che vedere con la ricerca effettuata e soprattutto solo su ristoranti che siano entro 8 km di distanza.

Un altro vantaggio nell'usare gli alias è che ci permettono di essere usati anche nella clausola ORDER BY..
Codice:
ORDER BY distance_km, relevance DESC
..che sostanzialmente risolve il tuo problema originario.
 
Ultima modifica:

pierom

Nuovo Utente
15 Apr 2015
12
0
0
grazie tantissime. version() 5.0.92-enterprise-gpl-log è quella che ho con Aruba. Sto leggendo e facendo esperimenti. Mi perdo probabilmente in poco. Trovo errore nell'uso dell'alias della funzione. Probabilmente perchè sbaglio qualcosa nelle query che mi invento. Hai spiegato benissimo. Voglio tentare di assimilare i concetti e sto leggendo di functions, store procedures, views. Mi dispiace abusare della tua gentilezza. Spero di arrivare in fondo. Ti saprò dire. Grazie ancora
 

pierom

Nuovo Utente
15 Apr 2015
12
0
0
ci sono, nel PC tutto OK, ma in aruba no. la funzione GEODISTANCE è stata installata correttamente. rimessa, mi dice
#1304 - FUNCTION GEODISTANCE already exists. I files credo di averli inviati tutti, dove sarà l'errore? Sto parlando da solo, logicamente. Approfitto per ringraziarti di nuovo
 

pierom

Nuovo Utente
15 Apr 2015
12
0
0
ma la funzione non ho modo di vederla? finora per sapere se l'ho installata la reinstallo e mi dice che c'è già. Preferirei leggerla. Si può? grazie
 

pierom

Nuovo Utente
15 Apr 2015
12
0
0
fatto. flameseeker mi sei stato di immenso aiuto. Adesso ti chiedo un parere. Secondo te le ricerche come si fanno in ristomagic.com, possono avere una utilità?
 

flameseeker

Utente Attivo
27 Nov 2013
699
0
0
version() 5.0.92-enterprise-gpl-log è quella che ho con Aruba.
Oh, aruba riesce sempre a sorprendermi :)

fatto. flameseeker mi sei stato di immenso aiuto. Adesso ti chiedo un parere. Secondo te le ricerche come si fanno in ristomagic.com, possono avere una utilità?
Non c'è di che, per il resto penso che l'idea sia buona.
Vedo questo sito molto utile a livello turistico per persone che dal loro smartphone vi accedono per cercare velocemente un posto per mangiare un piatto tipico della località.

Hai fatto bene ad usare boostrap e rendere il sito responsive infatti, l'unico appunto che potrei farti è che non è molto intuitiva la creazione di una ricerca e richiede molti passaggi prima di poter essere completata.

Potrebbe essere un idea cercare di ridurre al minimo le operazioni per iniziare ad effettuare una ricerca di base e poi eventualmente personalizzarla in seguito nella pagina dei risultati mediante i filtri, in stile google.

Un ultima cosa, mi pare che la pagina in cui mostri i risultati della ricerca stoni non poco, visivamente parlando, rispetto al resto del sito. Potresti provare a vedere se qualche elemento di boostrap può aiutarti a rendere visivamente più "coerente" il tutto.


Edit

Ti devo portare a conoscenza di un errore che ho commesso:
Codice:
SELECT *, 
	MATCH(menu_entry) AGAINST('Gnocchi*') AS relevance,
	GEODISTANCE(41.40838, 2.12403, latitude, longitude) AS distance_km
FROM restaurants_menus 
HAVING relevance > 0 AND distance_km <= 8
ORDER BY distance_km, relevance DESC
L'uso dell'HAVING in questo caso è sbagliato in termini di performance, la query richiede un fullscan delle tabelle restaurants e menus prima di applicare i filtri indicati.

Questo significa che con grossi risultati la query potrebbe causare rallentamenti non indifferenti.
La soluzione a tutto questo è di restringere il recordset mediante l'uso del WHERE prima di applicare i filtri contenuti nell'HAVING e per fare ciò, basta clonare la clausola di MATCH come segue..
Codice:
SELECT *, 
	MATCH(menu_entry) AGAINST('Gnocchi*') AS relevance,
	GEODISTANCE(41.40838, 2.12403, latitude, longitude) AS distance_km
FROM restaurants_menus 
WHERE MATCH(menu_entry) AGAINST('Gnocchi*') > 0
HAVING distance_km <= 8
ORDER BY distance_km, relevance DESC
..in questo modo la query prima restringe il numero di risultati su cui lavorare e poi verifica la distanza, impiegando a pieno la potenza dell'indice fulltext.


Esaminando entrambe le query con EXPLAIN, vedremo infatti che la prima:
explain1.png
raccoglie tutte le righe di tutte e due le tabelle, inoltre -peggio ancora- non adopera nessun indice per effettuare la ricerca e non è questo che vogliamo da una query che dovrebbe lavorare su un su un numero enorme di risultati.

Modificando la query come ho indicato e sottoponendola al vaglio di EXPLAIN..
explain2.png
..finalmente notiamo che i record che sono stati pescati dalla nostra ricerca sono stati ristretti ai soli risultati attinenti ma soprattutto che sono stati usati l'indice fulltext per la ricerca e l'indice primario della seconda tabella per gestire l'associazione dei dati. Questa query adesso è infinitamente più performante rispetto alla precedente e si comporta come ci aspettiamo che faccia.

Se hai bisogno di qualche chiarificazione sugli altri dati che vengono ritornati dall'EXPLAIN ti segnalo questa pagina sulla documentazione ufficiale.
 
Ultima modifica:

pierom

Nuovo Utente
15 Apr 2015
12
0
0
scusa, ma ho letto solo ora il tuo msg. Avevo la pagina vecchia che non mostra i nuovi msg con il refresh ( o sono io imbranato? più probabile).
Le tue osservazioni sono buone, preziose per me. Graficamente sono un disastro, ma ho tentato di migliorare. Ora le pagine 'cerca piatto' e 'cerca voce di menu' mi sembrano + in linea (spero).
Ora provo a mettere i filtri nella pagina dove si vede la lista degli items. ciao
 

pierom

Nuovo Utente
15 Apr 2015
12
0
0
ciao flameseeker, ho provato a migliorare. Ora i filtri sono da ogni pagina con effetto al click. Ci sono riuscito?
PS hai avuto una parte importantissima. Come posso contraccambiarti?