Discussion :

[StringBuilder]

J'ai ce code qui effectue des requêtes de géolocalisation avec l'API de Google Maps :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
let noRows = NoOfRows('EnteteFacture')-1;
 
for i=0 to $(noRows)
 
	let address=peek('VilleClient',$(i),'EnteteFacture');
 
 LocationData:
	LOAD
	'$(address)' as VilleClient,
	[result/geometry/location/lng] as longitude,
	[result/geometry/location/lat] AS latitude
	FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)&output=xml&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse]);
 
next

Seulement, j'envoie :
- une bonne partie de "VilleClient" vides
- énormément de fois la même adresse

Comment modifier ma boucle pour :
- ne prendre que les "VilleClient" non vide
- ne prendre que les valeurs distinctes que "VilleClient"

[StringBuilder]

Après avoir tenté de lire la documentation du langage de script (absolument imbitable au passage), j'ai fini par pondre ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
Personnes:
LOAD * INLINE
[
	ID, VILLE
	1, 21000 DIJON
	2, 69000 LYON
	3, 21 DIJON
	4, 75000 PARIS
	5, 61000 ALENCON
	6, 63000 CLERMONT FERRAND
	7, 75000 PARIS
	8,
	9,
	10, 61000 ALENCON
];
 
let noRows = NoOfRows('Personnes')-1;
 
FOR i=0 TO $(noRows)
 
	let address=peek('VILLE',$(i),'Personnes');
 
 	if len('$(address)') > 0 then
		LocationData:
		LOAD
			'$(address)' AS VILLE,
			[result/geometry/location/lng] AS longitude,
			[result/geometry/location/lat] AS latitude
			FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)&output=xml&oe=utf8&sensor=false] (XmlSimple, TABLE IS [GeocodeResponse])
			where isnull(Lookup('VILLE', 'VILLE', '$(address)'))
			;
    end if
next

Je n'arrive pas à comprendre pourquoi "EXISTS" ne veut pas fonctionner, et que c'est seulement isnull(lookup()) qui semble fonctionner.

Accessoirement, comment je peux être sûr que ça marche bien ?

Personnes << INL8FCD 10 lignes récupérées
LocationData << GeocodeResponse 1 lignes récupérées
LocationData << GeocodeResponse 2 lignes récupérées
LocationData << GeocodeResponse 3 lignes récupérées
LocationData << GeocodeResponse 4 lignes récupérées
LocationData << GeocodeResponse 5 lignes récupérées
LocationData << GeocodeResponse 6 lignes récupérées
LocationData << GeocodeResponse 6 lignes récupérées
LocationData << GeocodeResponse 6 lignes récupérées

Pourquoi si je déplace ma clause "where" dans le IF, ça ne marche pas ?
Car je me demande si dans ce cas, ça ne fait pas quand même la requpete à GOOGLE, ce que je ne veux surtout pas (trop lent, et pas terrible, c'est un coup à se faire blacklister, vu que j'ai plus de 600 000 lignes dans ma table des personnes...

[StringBuilder]

Après test sur quelques centaines de lignes, je confirme, la clause "where" est exécutée après la clause "from" : mon script fait donc plein de requêtes inutiles aux API.

Pourtant, le script suivant ne marche pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Personnes:
LOAD * INLINE
[
	ID, VILLE
	1, 21000 DIJON
	2, 69000 LYON
	3, 21000 DIJON
	4, 75000 PARIS
	5, 61000 ALENCON
	6, 63000 CLERMONT FERRAND
	7, 75000 PARIS
	8,
	9,
	10, 61000 ALENCON
];
 
let noRows = NoOfRows('Personnes')-1;
 
FOR i=0 TO $(noRows)
 
	let address=peek('VILLE',$(i),'Personnes');
 
 	if len('$(address)') > 0 and isnull(Lookup('VILLE', 'VILLE', '$(address)', 'LocationData')) then
		LocationData:
		LOAD
			'$(address)' AS VILLE,
			[result/geometry/location/lng] AS longitude,
			[result/geometry/location/lat] AS latitude
			FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)&output=xml&oe=utf8&sensor=false] (XmlSimple, TABLE IS [GeocodeResponse])
			;
    end if
next

Le script n'entre jamais dans le IF.

Pourquoi ?

[StringBuilder]

Quand ça veut pas, ça veut pas...

Apparement on peut pas faire de FOR imbriqués...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
FOR i = 0 TO $(noRows)
	let address = peek('VILLE', $(i), 'Personnes');
	let noRows2 = NoOfRows('LocationData') - 1;
	let found = 'NO';
	for j = 0 to $(noRows2)
		if peek('VILLE', $(j), 'LocationData') = '$(address)' then
			let found = 'YES';
			exit for;
		end if
	next j
	if len('$(address)') > 0 and '$(found)' = 'NO' then
		LocationData:
		LOAD
		'$(address)' AS VILLE,
		[result/geometry/location/lng] AS longitude,
		[result/geometry/location/lat] AS latitude
		FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)&output=xml&oe=utf8&sensor=false] (XmlSimple, TABLE IS [GeocodeResponse])
//		where isnull(Lookup('VILLE', 'VILLE', '$(address)', 'LocationData'))
		;
	end if
next i

J'ai un plantage (sans explication) sur "for j = 0 to"

A moins que ce ne soit à cause du $noRows2 qui est null ou -1 au premier appel ?

[FORMULARY]

Quelques pistes :
1) QV est sensible à la casse. Vérifie bien l'orthographe de tes variables ...
2) Dans ton lokup() tu indiques 'VILLE' deux fois. Donc tu recherche la ville qui s'appelle 'VILLE'.

[StringBuilder]

Ah, si, ouf, ça marche !

En fait NoOfRows() ramène la chaîne vide si la table n'existe pas.

Donc en faisant un test sur len('$(noRows2)') > 0 avant la boucle, ça marche !

[StringBuilder]

2) Dans ton lokup() tu indiques 'VILLE' deux fois. Donc tu recherche la ville qui s'appelle 'VILLE'.

Non, je récupère la valeur de "VILLE" pour laquelle "VILLE" est égale à une valeur. C'est une façon simple de vérifier que la valeur rechercher existe bien (lookup renvoie NULL quand il ne trouve rien).

[StringBuilder]

Bon, je passe de 635 000 requêtes aux APIs de Google à moins de 50

[StringBuilder]

Je me permet de revenir vers vous, car cette solution, si elle marche, n'est pas du tout performante.

Donc si quelqu'un sait comment améliorer la chose, je suis preneur...

En effet, ça tourne depuis 9h et c'est toujours pas fini... En fait, la boucle imbriquée, c'est une horreur avec un gros volume...

[StringBuilder]

21h que ça tourne, et c'est toujours pas fini...

[PhunkyBob]

Je vous confirme que 21h pour faire 50 requêtes Google, ce n'est pas normal.

Vous cherchez à faire quoi exactement ?


Si vous voulez connaitre les lat/lon des villes pour les associer aux personnes, il suffit de charger une table "Villes" qui contient toutes les lat/lon de vos villes, puis de faire une jointure sur votre table de personnes.

[StringBuilder]

Bon, éclair de lucidité...

J'ai fini par faire tomber en marche la fonction lookup.
En fait, ça plantait au premier appel car la table LocationData n'existait pas encore.

J'en ai donc chié des bulles (pour parler poliment) depuis 1h30 pour effectuer les tests nécessaires pour ne plus planter au premier appel, ni aux suivants.

J'ai couplé aussi la chose à un stockage dans un QVD des coordonnées GPS déjà connues (c'est pas tous les jours qu'une ville change d'adresse).

Reste plus qu'à attendre que mon script termine, le corriger, et re-tester pour voir si ça va plus vite...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
LET vListqvdexists = isnull(QvdCreateTime('GPS.qvd'));
 
if $(vListqvdexists) = 0 then
	LocationData:
	LOAD
	VILLE,
	longitude,
	latitude
	FROM GPS.qvd (qvd);
end if;
 
let noRows = NoOfRows('Personnes') - 1;
FOR i = 0 TO $(noRows)
	let address = peek('VILLE', $(i), 'Personnes');
	if len('$(address)') > 0 then
		let tableNotExists = isnull(TableNumber('LocationData'));
		if $(tableNotExists) then
			let valueNotExists = -1;
		ELSE
			let valueNotExists = isnull(lookup('VILLE', 'VILLE', '$(address)', 'LocationData'));
		end if
 
		if $(valueNotExists) then
			LocationData:
			LOAD
			'$(address)' AS VILLE,
			[result/geometry/location/lng] AS longitude,
			[result/geometry/location/lat] AS latitude
			FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(address)&output=xml&oe=utf8&sensor=false] (XmlSimple, TABLE IS [GeocodeResponse]);
		end if
	end if
next i
 
if not IsNull(TableNumber('LocationData')) then
	STORE LocationData INTO GPS.qvd (QVD);
end if

[StringBuilder]

Je vous confirme que 21h pour faire 50 requêtes Google, ce n'est pas normal.

Vous cherchez à faire quoi exactement ?


Si vous voulez connaitre les lat/lon des villes pour les associer aux personnes, il suffit de charger une table "Villes" qui contient toutes les lat/lon de vos villes, puis de faire une jointure sur votre table de personnes.

En en fait, il y a bien plus de 50 requêtes (je m'étais planté hier, j'avais laissé un test dans ma boucle qui la faisait sortir au bout de 50 itérations )

J'ai pour le moment 4000 requêtes Google qui ont tourné.

Mais c'est pas les requêtes Google qui sont lentes : avant ma tentative d'optimisation, en 4 heures, j'avais fait 500 000 requêtes à Google (et du coup j'ai été blacklisté pendant 24 heures )

Pour faire simple :
- Je charge de la base de données 630 000 lignes de factures, associées à des adresses, et un champ "ville" qui contient le code postal + nom de la ville.
- Je souhaite afficher ces données sur une carte, afin de localiser la répartition du CA sur le territoire.

Donc première tentative, envoyer les 630 000 "villes" à Google pour en récupérer les coordonnées.
Sauf qu'on est limité à 15000 requêtes à l'API par 24 heures, et que de toute façon j'ai énormément de doublons, donc clairement pas 630 000 coordonnées GPS différentes

Donc lorsque j'alimente la table "LocationData", je veux vérifier avant d'insérer chaque ligne :
- Que la "ville" que je souhaite insérer n'est pas vide
- Que la "ville" n'est pas déjà présente dans la table

Sauf que "exists()" ne fonctionne visiblement pas dans une condition.
Et que exists() est évalué dans le LOAD après le FROM, donc que je charge la ligne ou pas, je faisait la requête à Google.
Que lookup() semble assez capricieux, et notamment n'aime pas du tout chercher dans une table qui n'existe pas.

J'étais donc parti sur deux boucles :
- une qui lit les 630 000 "villes" avec doublon
- et une autre à l'intérieur qui parcourait les coordonnées GPS déjà chargées pour voir si la ville avait déjà été chargée

Sauf que c'est LENT, TRES LENT. C'est pas l'API de Google qui est lente, mais le langage de script.

Donc je viens de réécrire pour ne plus avoir que la première boucle, et utiliser lookup() pour me passer de la seconde boucle.
En espérant que les fonctions intégrées sont plus rapides que les fonctions interprétées.

[PhunkyBob]

Donc première tentative, envoyer les 630 000 "villes" à Google pour en récupérer les coordonnées.

Si vous ne souhaitez récupérer que les coordonnées des villes (pas des adresses), je pense que vous vous y prenez de la mauvaise façon.

Il ne faut pas récupérer les coordonnées des 630 000 villes, mais récupérer les coordonnées de toutes les villes DISTINCTES qui sont dans votre table qui contiennent les villes.
Etant donné qu'il n'y a "que" 36 500 communes en France, il est donc inutile de boucler sur les 630 000 valeurs.

De plus, aller taper Google pour récupérer les lat/lon, c'est se faire chier pour rien.
Il faut 2 minutes dans un moteur de recherche pour récupérer un fichier CSV qui contient la liste de toutes les communes / CP / lat/lon de France.

(cela nécessite néanmoins un léger travail sur les noms, pour que le matching soit fait sur le nom --majuscules, accents, ...--)

Que lookup() semble assez capricieux, et notamment n'aime pas du tout chercher dans une table qui n'existe pas.

Très bizarre qu'une fonction refuse de vous retourner une donnée qui n'existe pas... Vous devriez en parler aux développeurs de QlikView

Sauf que c'est LENT, TRES LENT. C'est pas l'API de Google qui est lente, mais le langage de script.

Je vous conseille de faire des tables intermédiaires qui ne contiennent que les données nécessaires (par exemple une table qui contient la liste des villes DISTINCTES) puis faire des jointures, plutôt que boucler sur toutes les valeurs de ligne.