Discussion :

[Repti31]

Bonjour à tous,

je travail actuellement sur un projet qui utilise la géolocalisation.
Et notamment la fameuse BDD Geonames.
J'ai réussi à intégré toute les données dans mon MySQL (je vous dis pas combien de temps ça m'a pris ).
Merci à ce site : http://codigofuerte.github.io/GeoNam...QL-DataImport/
Pour optimiser les tables pour certaine requêtes j'ai suivi ce site : http://www.developpeur-php-independa...geonames-suite
Cependant j'ai eu un problème pour créer l'index

Code :

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

ALTER TABLE `geoname` ADD INDEX `alternatenames` USING BTREE(`alternatenames`);

Car mysql me répond que la clef est supérieur à la taille maximum qui est de 3072, c'est un varchar(4000), ça passe pas...

Je souhaite récupérer les villes autour d'une localisation avec un périmètre.
Bon ça fonctionne, cependant mon exécution si je cible sur la France dure entre 4-5s.
Pour le monde complet je suis à 60-61s.
Ces temps de traitement ne me vont pas et j'essaye de trouver des idées pour arriver à un temps correct.

Ma requête actuelle FR (PHP) :
4-5s

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
$formule = "(6366*acos(cos(radians($latitude))*cos(radians(g2.latitude))*cos(radians(g2.longitude)-radians($longitude))+sin(radians($latitude))*sin(radians(g2.latitude))))";
$sql = "
    SELECT DISTINCT
        g1.geonameid,
        g1.name,
        g1.fclass
    FROM geoname g1
    LEFT JOIN geoname g2 ON g1.geonameid = g2.geonameid
    WHERE $formule <= '$perimetre'
    AND g1.fcode LIKE 'PPL%'
    AND g1.country = 'FR'
";

EXPLAIN :

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
 
array (size=2)
  0 => 
    array (size=9)
      'select_type' => string 'SIMPLE' (length=6)
      'table' => string 'g1' (length=2)
      'type' => string 'ref' (length=3)
      'possible_keys' => string 'PRIMARY,fcode,country,fcode_2' (length=29)
      'key' => string 'country' (length=7)
      'key_len' => string '9' (length=1)
      'ref' => string 'const' (length=5)
      'rows' => string '282632' (length=6)
      'Extra' => string 'Using where; Using temporary' (length=28)
  1 => 
    array (size=9)
      'select_type' => string 'SIMPLE' (length=6)
      'table' => string 'g2' (length=2)
      'type' => string 'eq_ref' (length=6)
      'possible_keys' => string 'PRIMARY' (length=7)
      'key' => string 'PRIMARY' (length=7)
      'key_len' => string '4' (length=1)
      'ref' => string 'geoname.g1.geonameid' (length=20)
      'rows' => string '1' (length=1)
      'Extra' => string 'Using where; Distinct' (length=21)

La version monde :
60-61s

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
$formule = "(6366*acos(cos(radians($latitude))*cos(radians(g2.latitude))*cos(radians(g2.longitude)-radians($longitude))+sin(radians($latitude))*sin(radians(g2.latitude))))";
$sql = "
    SELECT DISTINCT
        g1.geonameid,
        g1.name,
        g1.fclass
    FROM geoname g1
    LEFT JOIN geoname g2 ON g1.geonameid = g2.geonameid
    WHERE $formule <= '$perimetre'
    AND g1.fcode LIKE 'PPL%'
";

EXPLAIN :

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
 
array (size=2)
  0 => 
    array (size=9)
      'select_type' => string 'SIMPLE' (length=6)
      'table' => string 'g1' (length=2)
      'type' => string 'ALL' (length=3)
      'possible_keys' => string 'PRIMARY,fcode,fcode_2' (length=21)
      'key' => null
      'key_len' => null
      'ref' => null
      'rows' => string '10584103' (length=8)
      'Extra' => string 'Using where; Using temporary' (length=28)
  1 => 
    array (size=9)
      'select_type' => string 'SIMPLE' (length=6)
      'table' => string 'g2' (length=2)
      'type' => string 'eq_ref' (length=6)
      'possible_keys' => string 'PRIMARY' (length=7)
      'key' => string 'PRIMARY' (length=7)
      'key_len' => string '4' (length=1)
      'ref' => string 'geoname.g1.geonameid' (length=20)
      'rows' => string '1' (length=1)
      'Extra' => string 'Using where; Distinct' (length=21)

Si quelqu'un connais bien l'optimisation de requête et à des idées pour arrivé à une exécution requête monde de moins d'une seconde je suis preneur !!

[SQLpro]

MySQL étant l'un des plus mauvais SGDBR en terme de performance et de plus fortement bugué dans sa partie spatiale, je ne saurait trop vous conseiller d'aller sur un autre SGBDR comme PostGreSQL ou SQL Server qui optimisent ce genre de chose sans aucun problème....

À lire sur MySQmerde.... :
http://blog.developpez.com/sqlpro/p9...oudre_aux_yeux

A +

[Repti31]

J'ai testé de mettre en local un postgreSQL et effectivement sur la même requête je passe de 60s avec MySQL à 10s avec PostgreSQL.
Cependant je trouve ça quand même long 10s. Je vais essayer de voir si je peu pas encore améliorer ça.

Étant hébergé chez OVH pour passer sur du PostgreSQL ça va me couter de l'argent :'( mais bon je crois que je vais pas avoir le choix.

[Repti31]

J'ai oublié de te remercier pour ta réponse SQLPro, donc merci !!

En passant par la table postalcodes j'en suis à 6708.43 ms :
Requête :

Code :

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

1
2
3
4
5
6
 
SELECT DISTINCT
    pc1.placename
FROM postalcodes pc1
LEFT JOIN postalcodes pc2 ON pc1.placename = pc2.placename
WHERE (6366*acos(cos(radians(48.0012))*cos(radians(pc2.latitude))*cos(radians(pc2.longitude)-radians(2.7536))+sin(radians(48.0012))*sin(radians(pc2.latitude)))) <= '10'

Schéma :


Si quelqu'un à encore une idée pour améliorer le temps d’exécution de ma requête je suis preneur

[SQLpro]

Trois remarques...
1) Pourquoi fais tu des calculs immondes alors qu'il existe des opérateurs spatiaux ???
par exemple pour savoir si 2 géométries sont distantes de moins de 10 mètres, il suffit de faire :
ST_Distance(Geo1, Geo2) < 10
2) Pourquoi passer un nombre sous forme de chaine de caractères ? Ceci affecte les performances car oblige à un transtypages ! Les BD relationnelles ont des données FORTEMENT typées !!! Donc 10 et non pas '10' !
3) poser un index spatial permet d'accélérer certaines recherches

A +