Dans mon cas : SQL ou FTP ?

[michel.leonard]

Salut chers WRInautes, je vous présente mon problème :

-Le but de mon sous-programme est de produire la liste des codes postaux les plus proches d'un code postal donné, avec possibilité d'élargissement du domaine de recherche.
-Pour cela, j'ai prix la liste de touts les codes postaux, j'ai obtenu la latitude et longitude de chaque entité, j'ai calculé pour les 6188 codes postaux leur distance aux autres. Et j'ai mis le tout dans des fichiers bien propres.
-J'ai donc 6188 fichiers dans 97 dossiers (leur département), ce qui me fait moins de 100 fichiers par dossier.
-Si un utilisateur me fait une requête donnée, je suis en mesure d'y répondre par quelques expressions régulières dans un fichier texte de 30ko (correspondant à son code postal).

Mes questions sont les suivantes:
-Mon système est-il optimal ? (j'ai tout pré-calculé, ça me semble idéal, non ?)
Devrais-je plutôt indexer mes 6188 codes postaux dans une base SQL pour récupérer les 30ko de texte ? (est-ce plus rapide que l'accès coté serveur ?)
-Est-il, selon vous, possible d'optimiser encore ce système ?
-Est-ce que les 100ko de mémoire que me prend une requête représente une quantité "acceptable" de mémoire ?

Je vous laisse tester mon sous-système à l'adresse suivante.

L'URL : http://youtroc.com/r/Four+pizza/54580-B1.php (naturellement, vous pouvez modifier le code postal dans l'adresse pour tester avec le vôtre)

Merci de votre lecture, Mike.

 

[Koxin-L.fr]

Bonjour,

Le première chose, serait de faire un test avec une d'un millier de requête entre ton système actuel (lecture de fichier, si j'ai bien compris) et un système basé sur bdd.
Tu serais en mesure de connaitre le temps d'excéution, la charge serveur, etc...

A première vu je dirais qu'une requête SQL bien faite sur une table bien configuré est moins lourde et plus rapide que l'ouverture la lecture et la fermeture d'un fichier.

Et coté gestion, cela me semble plus pratique.

Rod

 

[zeb]

A première vu je dirais qu'une requête SQL bien faite sur une table bien configuré est moins lourde et plus rapide que l'ouverture la lecture et la fermeture d'un fichier.

C'est loin d'être sur dans la mesure ou les deux opérations utilisent des accès disque et que l'option SQL peut quant a elle y ajouter un accès réseau. Après entre un système optimisé et l'autre pas on peut pencher la balance des deux côtés.

Pour cela, j'ai prix la liste de touts les codes postaux, j'ai obtenu la latitude et longitude de chaque entité

http://www.phpsources.org/scripts459-PHP.htm je voie pas trop ce que tu gagne a pré-calculer dans la mesure ou le calcul en lui même est vraiment pas lourd.

 

[spout]

+1 Zeb.
Et en MySQL ça donne: http://www.awelty.fr/blog/developpement-web/php.html

 

[michel.leonard]

http://www.phpsources.org/scripts459-PHP.htm je voie pas trop ce que tu gagne a pré-calculer dans la mesure ou le calcul en lui même est vraiment pas lourd.

J'aimerai bien savoir la mémoire que tu utilises pour calculer dans une liste contenant 6200 entrées latitude longitude, les 20 plus proches d'un point donné, c'est au minimum 30000 calculs à faire en interne par php (type sinus cosinus arctangente). Puis, si ensuite, je te demande les 30 premières, tu dois mettre les 20 premières en cache pour éviter de tout re-calculer. Ce qui revient à tout pré-calculer ...

Le pré-calcul est la meilleure solution... jusqu'à réelle preuve du contraire, une Regex va plus vite que 30000 opérations arithmétiques

 

[zeb]

c'est au minimum 30000 calculs à faire en internes

Il y a environ 40000 communes en France, si tu as besoins de tout calculer pour avoir une idée de ceux qui sont le plus proches c'est que tu n'a pas entendu parler des départements et des régions. ça c'est le premier point.

Ensuite quand on a des longitudes et des latitudes déterminer qui est statistiquement proche relève du calcul algébrique de base avec des intervals :wink:

Bref, avant de se lancer dans des solution technologiques gourmandes il faut avant tout dégrossir le problème par une bonne logique interne.

Edit > après on peut continuer a construire des centrales nucléaires spécialement pour les codeurs mais je pense pas que ça soit une super idée.

 

[zeb]

-Pour cela, j'ai prix la liste de touts les codes postaux, j'ai obtenu la latitude et longitude de chaque entité, j'ai calculé pour les 6188 codes postaux leur distance aux autres. Et j'ai mis le tout dans des fichiers bien propres.

Il t'en manque on dirait j'en ai 6273 dans la base que j'utilise. Il y a toutefois un truc qui interpelle c'est que le code postal n'est pas représentatif géographiquement, le code insee est lui plus proche de la réalité géographique puisqu'il sépare géographiquement les zones en secteur beaucoup plus fin et la tu as environ 40 000 points comme je t'en parlais plus haut.
Si l'objectif de l’application est de donner des commerces a proximité ça serait plus adapté.

 

[michel.leonard]

tu as bien raison sur le chiffre, cependant j'ai exclu saint-bartélamy , ambassade de france au mexique, etc...
Mon objectif , comme préciser dans le topic, est de retourner au plus vite la liste des codes postaux les plus proches d'un point donné, avec possibilité d'extension du domaine de recherche (exemple les 10 codes postaux les plus proches de 06000, les 30 codes postaux les plus proches de 97420) etc... Pour le moment mon programme répond en 0,002sec en consommant 120ko de mémoire moyenne par requète.

 

[michel.leonard]

Je ne calcule pas tout pour savoir quelle sont les plus proches, car l'utilisateur peut me requérir "Les codes postaux plus proches que Marseille, à partir de Nice" ... Comment tu fait sans tout calculer ? Et ton histoire de régions et de département, c'est une méthode que je déteste (c'est pour un site d'annonce)... celui qui habite à 3km de la région voisine, tu le catégorise comment ? L'utilisateur doit pouvoir faire sa recherche près de chez lui,et, si il ne trouve pas, étendre la recherche jusqu'à avoir toute la France.(DOM TOM compris) La latitude et longitude est idéale, google permet de calculer grâce a un api la distance réelle par la route entre 2 points... ce qui est parfait... je pense que tu devrais tester mon lien...

 

[zeb]

histoire de régions et de département

C'est bien sur pas pratique a cause des effets de bord (entre autre), mais c'est surtout pour te mettre le doigts sur le fait qu'il y a moyen d'optimiser un truc en amont pour pas créer une pelleteuse pour déterrer un os (ton système restant bien sur a comparer a autre chose avant de conclure).

La latitude et longitude est idéale

Oui c'est pour ça que je t'en parlais en seconde solution la formule du lien que je t'ai passé te donne une distance kilométrique il me semble (de mémoire) et faire varier un écart sur les longitudes et latitudes va pas chercher bien loin.
Donc avec une requête sur ta base tu as la liste des villes proches si tu veux calculer ou donner une distance entre elle ensuite c'est pas gourmand (note bien au passage que les distances sont totalement inutiles pour déterminer la proximité des points et que tu ne les affiches d'ailleurs pas hormis une).

Maintenant élargir a tout un département ou la France entière même si techniquement c'est faisable, ça n'a aucun sens du point de vue pratique.

Sinon en full SQL Forty a dev un truc pas mal pour calculer des distances avec des coordonnées GPS.

je pense que tu devrais tester mon lien...

Bah j'y suis passé mais ça n'offre pas de moyen de test a proprement parler, c'est une bonne démo en revanche.

 

[michel.leonard]

    SET a = SIN(dla) * SIN(dla) + COS(rla1) * COS(rla2) * SIN(dlo) * SIN(dlo);
    RETURN (6378137 * 2 * ATAN2(SQRT(a), SQRT(1 - a)));

D'accord mais pour une requète donnée tu dois appliquer la fonction "calcul de la distance GPS" à toutes les entrées de ta base, et chaque calcul demande au moins 5 opérations (mini)... ca représente une masse de calcul. Tu penses réellement que sql peut faire ces 30000 opérations a chaque requète de manière aussi productive qu'une regex sur un fichier cache ?

exemple de mon fichier pour 57490 où B1 représente la première "petite ville" (cp finissant par 00) et A1 la première "grande ville" (préfecture)

57490 57490 57500 57890
B1_[St-Avold;Moselle] 57150 57470 57730 57740 57800 57880
B2_[Freyming-Merlebach;Moselle] 57385 57450 57455 57550 57540 57600
B3_[Forbach;Moselle] 57220 57350 57460 57660 57690 57980 57380 57515 57510 57520 57990 57915 57340 57320 57430 57530 57910 57200 57580 57670 57645 57920 57905 67260
B4_[Sarreguemines;Moselle] 57365 57412 57480 57640 57935 67970 57070 57000 57170 57260 57245 57300 57310 57590 57970 57930 57925 57940
A1_B5_[Metz;Moselle]_[Hagondange;Moselle] 57050 57175 57140 57155 57280 57270 57410 57420 57525 57630 57950 67430 54114 54610 57160 57185 57190 57100 57120 57180 57290 57415 57570 57535 57685 57720 57780 57865 57810 57960 67320
B6_[Thionville;Moselle] 54280 54240 54370 54310 54760 57130 57240 57250 57255 57330 57400 57445 57680 57635 57620 57700 57770 57815 57860 57855 67290
B7_[Sarrebourg;Moselle]_[Hayange;Moselle] 54121 54530 54580 54670 54660 54770 54700 54780 54890 54980 57230 57370 57440 57405 57565 57650 57830
B8_[Pont-à-Mousson;Meurthe-et-Moselle] 54150 54110 54136 54130 54270 54220 54380 54390 54340 54420 54425 54640 54690 54790 54750 54800 54820 54940 54910 57655 57790 57710 57820 57840 67340
B9_[Jarny;Meurthe-et-Moselle] 54000 54140 54111 54100 54250 54300 54320 54460 54450 54470 54410 54560 54510 54680 57390 57850 57870 67330 67700 67790
A2_B10_[Nancy;Meurthe-et-Moselle]_[Lunéville;Meurthe-et-Moselle]_[Saverne;Bas-Rhin] (etc...)

Du coup si tu me demande la 9 eme petite ville la plus éloignée de ce point, substr($data,0,strpos($data,'B9')); et c'est réglé...

Et sur mon lien, tu as vu le mode de recherche "départemental" ? Il bascule de Préfecture en préfecture, c'est probablement mieux que de basculer de département en département...

 

[michel.leonard]

Ensuite quand on a des longitudes et des latitudes déterminer qui est statistiquement proche relève du calcul algébrique de base avec des intervals

Oui mais il faut élever plusieurs valeurs au carré, et calculer des racines carrées... c'est quand même un certain nombre d'opérations.

PS : selon Forty : La solution idéale est d'utiliser la puissance des fonctions MySQL pour réaliser ce traitement complexe et d'extraire en une seule requête les données souhaitées sans risquer de surcharger le serveur.

Dans mon système il n'y a pas d'opérations complexes, juste un substr...

 

[zeb]

Note bien que mon, hésitation ne porte que sur la notion d'identification des villes proches (pas sur le calcul de distance) et je ne préjuge pas des services que doit rendre le système ce qui oriente forcement la méthode a un moment donné.

Si je prend ton exemple lié a la commune 54580 j'ai dans ma base les coordonnées longitude et latitude suivante : 49.192612 / 5.975892
Si je souhaite savoir ce qu'il y a à côté j'utilise une requête de ce genre :

SELECT * FROM `France` where
latitude >49.0 AND
latitude <49.4 AND
longitude >5.8 AND
longitude <6.2

ce qui me renvoie 145 communes diverses car l'intervalle est large (zone carrée).
Cette commune est d'ailleurs intéressante car on passe les limites départementales assez rapidement.
Accessoirement ma base comprend d'autres choses comme la population donc si en plus je veux avoir que les commune d'importance "minimale" donc disons plus de 1000 Habitant la même requête me répond qu'il y a 58 communes dans ce cas (adaptable).
Si je souhaite un peut plus proche j'ai juste a changer les paramètres dans tous les cas, ce n'est qu'une requête et les distances ne sont pas utiles.

par exemple :

latitude >49.1 AND
latitude <49.3 AND
longitude >5.9 AND
longitude <6.1

Ne me donne plu que 28 communes.

Le but de mon sous-programme est de produire la liste des codes postaux les plus proches d'un code postal donné, avec possibilité d'élargissement du domaine de recherche.

Bref ce qu'il faudrait comparer c'est une recherche assortie éventuellement d'un calcul de distance (si l’application en a besoin mais c'est pas dans ton énoncé) Sans cela il est impossible de trancher entre du pré-calculé et autre chose.
Mais le calcul dont tu parle qui est potentiellement chronophage n'est pas du tout indispensable a la définition première que tu donne de ton application.

 

[zeb]

selon Forty : La solution idéale est d'utiliser la puissance des fonctions MySQL pour réaliser ce traitement complexe ...

Un peu de bla bla commercial pour vendre la soupe ;-) il est évident que seul les tests et la comparaison peut dire si oui ou non une solution est viable ou pas.

 

[michel.leonard]

Salut, ça me fait plaisir de voir que toi aussi, tu t'intéresses au sujet.
Tu fais une petite erreur dans ton calcul suivant :

latitude >49.0 AND
latitude <49.4 AND
longitude >5.8 AND
longitude <6.2

ton erreur est que tu sélectionnes un carré autour de ta zone de recherche il me semble ce que tu cherches réellement, à savoir un cercle autour du point de ton choix, a plutôt une équation de ce type :

B > latitude² - latitude² > A (valable pour des latitudes et longitudes du meme signe)

Maintenant, tu as peut être raison, la solution SQL est peut être plus adaptée. Je vais tenter les essais...

 

[zeb]

ton erreur est que tu sélectionnes un carré autour de ta zone de recherche

C'est pas a proprement parler une erreur, c'est surtout que de travailler juste avec les coordonnées ne permet pas de définir une sélection circulaire sans introduire des calcul plus complexes (qui ne sont donc pas forcement désirables).
Mais en y réfléchissant bien a surface égale un carré ou un cercle concentriques vont probablement donner le même résultat a 90 % (les exceptions se trouvant dans les pointes du carré et les arcs de cercles de la sélection circulaire)

genre ça :

Si tu dois de plus fournir une distance maximum, elle sera de la demi diagonal du carré ce qui peut se calculer assez facilement en fonction de l'intervalle (juste 1 calcul donc).
Enfin c'est juste pour donner une piste qui a le mérite de ne pas obliger a faire du pré-calcul. Je me penche sur ce souci car certains hébergements limitent le nombre de fichiers disponibles et si la solution impose un fichier par commune ça commence a faire lourd.

 

[michel.leonard]

Effectivement, si j'avais dû inscrire toutes les communes dans une liste, je n'aurai probablement pas envisagé de créer un fichier par commune (trop lourd)... Mon pré-calcul prend environ 150mo rien que pour les codes postaux, ce qui est négligeable pour un serveur d'une certaine capacité. Mais pour toutes les communes on peut estimer le pré-calcul à environ 1Go de données, ce qui est trop important. Souhaiterais tu partager ta base de données avec moi, ou, est-ce privé ? J'aimerai bien tester quand même tester le même système en SQL. Si tu veux je te crées un fichier Zip si toi aussi tu veux tester mon système "cache".
Pour conclure, je pense que le SQL est plus facile à gérer, à modifier et à mettre à jour. Et oui, tu as raison, l'histoire du cercle inscrit dans le carré me semble amplement suffisant... Donc je vais faire des essais SQL. Merci pour votre participation à ce topic chers WRInautes...

 

[zeb]

Pas de souci tu peut trouver un fichier SQL zippé ici, il est surement perfectible vue que je n'ai pas vérifié en détails ce qu'il y a dedans et que je ne me souviens plus d'où il vient.

 

[michel.leonard]

Merci c'est sympa de ta part de partager cette source, tu veux la mienne ou ça ira ?
(ne concerne que les codes postaux [en provenance de L'API YAHOO PLACEFINDER])

 

[zeb]

Merci, non ça ira j'ai déjà pas mal de trucs la dessus et avec les correspondances région, département etc donc ...
Pour les données numériques que tu trouvera dedans, regarde du côté des statistiques population de l'insee tu devrais trouver les correspondances des colonnes.
Par exemple ici tous les champs après la longitude doivent correspondre au colonnes de cette page.