Dans mon cas : SQL ou FTP ?

Discussion dans 'Développement d'un site Web ou d'une appli mobile' créé par michel.leonard, 17 Mai 2012.

Tags:
  1. michel.leonard
    michel.leonard WRInaute occasionnel
    Inscrit:
    1 Juin 2010
    Messages:
    366
    J'aime reçus:
    0
    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.
     
  2. Koxin-L.fr
    Koxin-L.fr WRInaute passionné
    Inscrit:
    15 Janvier 2012
    Messages:
    1 891
    J'aime reçus:
    8
    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
     
  3. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 181
    J'aime reçus:
    1
    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.

    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.
     
  4. spout
    spout WRInaute accro
    Inscrit:
    14 Mai 2003
    Messages:
    9 193
    J'aime reçus:
    333
  5. michel.leonard
    michel.leonard WRInaute occasionnel
    Inscrit:
    1 Juin 2010
    Messages:
    366
    J'aime reçus:
    0
    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
     
  6. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 181
    J'aime reçus:
    1
    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.
     
  7. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 181
    J'aime reçus:
    1
    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é.
     
  8. michel.leonard
    michel.leonard WRInaute occasionnel
    Inscrit:
    1 Juin 2010
    Messages:
    366
    J'aime reçus:
    0
    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.
     
  9. michel.leonard
    michel.leonard WRInaute occasionnel
    Inscrit:
    1 Juin 2010
    Messages:
    366
    J'aime reçus:
    0
    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...
     
  10. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 181
    J'aime reçus:
    1
    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).
    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.
    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.
     
  11. michel.leonard
    michel.leonard WRInaute occasionnel
    Inscrit:
    1 Juin 2010
    Messages:
    366
    J'aime reçus:
    0
    Code:
        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)
    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...
     
  12. michel.leonard
    michel.leonard WRInaute occasionnel
    Inscrit:
    1 Juin 2010
    Messages:
    366
    J'aime reçus:
    0
    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...
     
  13. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 181
    J'aime reçus:
    1
    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.

    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.
     
  14. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 181
    J'aime reçus:
    1
    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.
     
  15. michel.leonard
    michel.leonard WRInaute occasionnel
    Inscrit:
    1 Juin 2010
    Messages:
    366
    J'aime reçus:
    0
    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 :

    Code:
    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 :

    Code:
    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...
     
  16. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 181
    J'aime reçus:
    1
    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 :
    [​IMG]

    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.
     
  17. michel.leonard
    michel.leonard WRInaute occasionnel
    Inscrit:
    1 Juin 2010
    Messages:
    366
    J'aime reçus:
    0
    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...
     
  18. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 181
    J'aime reçus:
    1
    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.
     
  19. michel.leonard
    michel.leonard WRInaute occasionnel
    Inscrit:
    1 Juin 2010
    Messages:
    366
    J'aime reçus:
    0
    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])
     
  20. zeb
    zeb WRInaute accro
    Inscrit:
    5 Décembre 2004
    Messages:
    12 181
    J'aime reçus:
    1
    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.
     
Chargement...
Similar Threads - SQL FTP Forum Date
Réunir 2 requetes sql (ORDER BY ASC et DESC) Développement d'un site Web ou d'une appli mobile 29 Août 2021
Mysql : Impact convertion champ numérique SMALLINT vers BIGINT Développement d'un site Web ou d'une appli mobile 23 Août 2021
Récupérer le premier et dernier enregistrement sql Développement d'un site Web ou d'une appli mobile 16 Juillet 2021
Quel SGBDR autre que MySQL/MariaDB ? Administration d'un site Web 12 Janvier 2021
encodage texte sur requete mysql Demandes d'avis et de conseils sur vos sites 21 Octobre 2020
Requête MySql imbriquée Développement d'un site Web ou d'une appli mobile 8 Octobre 2020
Supprimer les doublons d'une table mysql Développement d'un site Web ou d'une appli mobile 16 Juin 2020
Mysql migration utf8->utf8mb4 Développement d'un site Web ou d'une appli mobile 17 Août 2019
recherche lettres dans mysql Développement d'un site Web ou d'une appli mobile 11 Juillet 2019
cache mysql maison Développement d'un site Web ou d'une appli mobile 18 Février 2019