XL 2016 Affichage villes proches à partir coordonnées GPS

bobland974 · 16 Novembre 2017

Bonjour à tous,

Voilà, cela va bientôt faire deux jours que je tourne le problème dans tous les sens et je n'arrive pas à trouver la solution, je me permets donc de venir vers vous afin de solliciter votre aide.

Je dispose d'un fichier excel avec en
- colonne (A) : nom de la ville
- colonne (F) : coordonnée GPS latitude
- colonne (G) : coordonnée GPS longitude
- colonne H, I, J : nom des 3 villes les plus proches que je désire faire ressortir du tableau
> cf. fichier ville_plus_proche.xls

Je suis parti du fichier de base pour essayer d'en découdre avecu ne formule mais rien n'y fait
(copie de ville_proche.xlsx)

Par quel moyen puis-je réussir à y arriver sans créé autrement autant de colonne (distance) qu'il y a de ligne afin de pouvoir faire ressortir une liste de choix des villes les plus proches. A terme le fichier devrait contenir plusieurs milliers de colonnes.

Y a t il pas une solution plus simple à mettre en oeuvre car vu la quantité de ville dont j'ai à ma disposition cela est compliqué ?

Merci par avance pour votre aide,

job75 · 2 Décembre 2017

Re,

Quant à la durée des calculs VBA n'est pas spécialement rapide, il faudrait utiliser C# ou l"assembleur.

A+

Modeste geedee · 2 Décembre 2017

Bonsour®

job75 à dit:
Quant au rayon de la Terre je l'ai pris, ainsi que les formules trigonométriques, sur cette page web :
http://geodesie.ign.fr/contenu/fichiers/Distance_longitude_latitude.pdf
La Terre étant pratiquement sphérique les différences de rayon on peu d'importance.
A+

pourquoi calculer les distances angulaires pour toutes les communes. ?
et ne pas se limiter aux seuls écarts angulaires inferieur à un seuil paramétrable ,
(ex : 1 degré au risque d'être encore fustigé de 'platiste' , à nos latitudes on peut admettre des seuils identiques)

Code:

=If Abs(Latcom1-Latcom2) < SeuilLat And Abs(LonCom1-LonCom2)<SeuilLon Then
Calcul Distance
End if

Dranreb · 2 Décembre 2017

Au poste 32 j'avais proposé un 1er calcul à peine plus sophistiqué pour mon quadrillage mais multipliant quand même la longitude par le rayon du parallèle à cette latitude. Le problème c'est que sans quadrillage, pour détecter l'écart angulaire il faut quand même confronter la commune à l'autre, alors autant calculer tout de suite la distance, ça ne prend guère de temps. Le quadrillage, lui, évite toute confrontation entre les villes des carrés non adjacents. Le calcul des distances prend moins d'une minute. Par contre j'obtiens pas loin de 7 millions de distances, et là pour les classer ensuite, c'est un peu plus long.

job75 · 3 Décembre 2017

Salut Modeste geedee,

Modeste geedee à dit:
pourquoi calculer les distances angulaires pour toutes les communes. ?
et ne pas se limiter aux seuls écarts angulaires inferieur à un seuil paramétrable ,
(ex : 1 degré au risque d'être encore fustigé de 'platiste' , à nos latitudes on peut admettre des seuils identiques)

Code:

=If Abs(Latcom1-Latcom2) < SeuilLat And Abs(LonCom1-LonCom2)<SeuilLon Then Calcul Distance End if

Si l'on intègre les départements d'outre-mer les seuils ne sont pas identiques.

Et comme dit Bernard le calcul de la distance angulaire ne prend guère plus de temps que de tester le seuil.

Non encore une fois la rapidité ici est une affaire de langage de programmation.

A+

Dranreb · 3 Décembre 2017

Bonjour.

Modeste geedee à dit:
ex : 1 degré au risque d'être encore fustigé de 'platiste'

Pour information: un 'platiste' c'est quelqu'un qui croit dur comme fer que la terre est plate comme une crêpe, allant jusqu'à faire des vidéo sur Youtube pour essayer de le démontrer, partant de prémices forcément faux et d'ailleurs probablement sans rapport avec ceux les conduisant eux même à leur certitude, de sorte que toute réfutation les laisse indifférents …

job75 · 3 Décembre 2017

Bonjour à tous,

J'ai trouvé le fichier joint à cette adresse :

https://www.galichon.com/codesgeo/

Il a le mérite d'exister mais il y a sûrement des erreurs.

En particulier j'ai trouvé 1093 villes dont les coordonnées GPS sont des doublons...

Je m'en servirai cependant dans mon prochain post.

A+

Modeste geedee · 3 Décembre 2017

Bonsour®

job75 à dit:
Bonjour à tous,

J'ai trouvé le fichier joint à cette adresse :

https://www.galichon.com/codesgeo/

Il a le mérite d'exister mais il y a sûrement des erreurs.

Voir également :
https://blog.niap3d.com/download/communes_france/liste_villes_francaise_2015.csv
Fichier CSV 3 Mo
Ce fichier contient la liste des 36 742 lignes. La différence avec le fichier précédent vient de quelques lignes supplémentaires à Mayotte. Les communes françaises sont désignées avec le code INSEE, le code postal, le nom, l'article, la latitude et la longitude, le code commune, le code canton, le code arrondissement et le code département.
subsiste des problèmes d'affichage pour les lettres accentuées...

job75 · 3 Décembre 2017

Re,

Mon fichier (1) était incomplet et quelque peu erroné.

En particulier quand une distance est retenue il faut l'affecter non seulement à la 1ère commune mais aussi à la seconde !

Voici les codes complétés :

Code:

Sub Calcul()
Dim dur#, RT#, dmax&, t, ub&, resu$(), f$, i&, sinLat#, cosLat#, j&, da#, x$, n&
dur = Timer
RT = 6378.137 'rayon terrestre en km
dmax = Int(Val(Feuil1.[P1])) 'distance maximum retenue en km, cellule à adapter éventuellement
t = Feuil1.[A2:D35250] 'plage à adapter éventuellement
ub = UBound(t)
ReDim resu(1 To ub, 1 To 1)
f = String(Len(CStr(dmax)), "0") & ".0 k\m " 'format des distances
For i = 1 To ub - 1
  sinLat = Sin(t(i, 3)): cosLat = Cos(t(i, 3))
  For j = i + 1 To ub
    da = sinLat * Sin(t(j, 3)) + cosLat * Cos(t(j, 3)) * Cos(t(i, 4) - t(j, 4)) 'cosinus de la distance angulaire
    da = Atn(Sqr(Abs(1 - da ^ 2)) / da) 'distance angulaire en radian
    If da * RT < dmax Then
      x = Format(da * RT, f)
      resu(i, 1) = resu(i, 1) & x & t(j, 1) & "#"
      resu(j, 1) = resu(j, 1) & x & t(i, 1) & "#"
      n = n + 1
    End If
Next j, i
Call RAZ
Feuil2.[D2].Resize(ub) = resu 'restitution
Feuil2.Activate
dur = (Timer - dur) / 86400
MsgBox "Nombre de distances retenues " & Format(n, "#,##0") & vbLf & "Durée du calcul " & Minute(dur) & " min " & Second(dur) & " s"
End Sub

Sub Classer()
Dim dur#, i&
dur = Timer
Application.ScreenUpdating = False
With Feuil2.[D2:D35250] 'plage à adapter éventuellement
  If InStr(.Cells(1), "#") = 0 Then Exit Sub 'sécurité
  .TextToColumns .Cells(1), xlDelimited, Other:=True, OtherChar:="#" 'commande Convertir
  For i = 1 To .Count
    .Cells(i).Resize(, Columns.Count - 3).Sort .Cells(i), xlAscending, Orientation:=xlLeftToRight 'tri horizontal de chaque ligne
  Next i
  .Columns(0) = "=COUNTA(" & .Cells(1).Resize(, Columns.Count - 3).Address(0, 0) & ")" 'Nb villes proches
End With
Application.ScreenUpdating = True
dur = (Timer - dur) / 86400
MsgBox "Durée du classement " & Minute(dur) & " min " & Second(dur) & " s"
End Sub

Sub RAZ()
With Feuil2
  .Range("C2:C" & Rows.Count).Resize(, Columns.Count - 2).ClearContents
  With .UsedRange: End With 'actualise la barre de défilement horizontale
End With
End Sub

Fichier (2).

Les 2 communes les plus isolées avec seulement 6 communes dans un rayon de 30 km :

- Bangor (56360)

- Bonifacio (20169).

A+

Dranreb · 3 Décembre 2017

Je n'avais pas joint ma dernière version avec 36455 communes.
Voilà qui est fait.

job75 · 3 Décembre 2017

Re Bernard,

Ton fichier contient 36 466 communes.

J'ai intégré les données dans ce fichier (3).

Edit : avec une distance maximum de 35 km :

- BANGOR est la commune la moins entourée avec 13 communes

- L'ILE-D'YEU a la commune la plus proche la plus éloignée, située à 23,4 km.

On obtient cette distance de 23,4 km par cette formules matricielle dans la 2ème feuille :

Code:

=MAX(--GAUCHE(D2:D36467;4))

A+

Dranreb · 3 Décembre 2017

job75 à dit:
BONIFACIO est entourée par 16 communes.

C'est possible. Le demandeur voulais les 7 plus proches.
Et par la plus isolée il fallait comprendre non pas la moins entourée par des villes comprises dans la limite, mais celle dont la plus proche voisine est la plus éloignée.

job75 · 3 Décembre 2017

Re,

Dranreb à dit:
C'est possible. Le demandeur voulais les 7 plus proches.
Et par la plus isolée il fallait comprendre non pas la moins entourée par des villes comprises dans la limite, mais celle dont la plus proche voisine est la plus éloignée.

D'accord, je viens de modifier mon fichier (3) en mettant une distance maximum de 35 km en P1.

Bonne nuit.

job75 · 4 Décembre 2017

Bonjour le forum,

Juste pour préciser que si l'on enregistre mon fichier (3) après le classement il pèsera 122 Mo.

En effet il y a près de 11 millions de cellules remplies.

A+

Dranreb · 4 Décembre 2017

Bonjour.
De mon coté je m'étais vraiment obstiné dans l'idée de stocker toutes les distances puis de les classer, ce qui était commode pour la suite mais inutile et particulièrement long.
Le seule inconvénient c'est que je ne peux plus indiquer dans les stats le nombre de distances effectivement rapportées, une ou 2 fois chacune, parce qu'une distance rapportée à un moment donné peut être chassée par la suite hors des 7 plus proches demandées.
Mais là le traitement dans son ensemble ne prend plus qu'une trentaine de secondes !

job75 · 4 Décembre 2017

Re,

Il est certain Bernard que ta méthode est optimale pour la durée d'exécution.

Mais quand on voit la complexité de tes codes on se demande si le jeu en vaut la chandelle puisque normalement le travail ne se fera qu'une seule fois.

De mon côté j'ai trouvé le moyen de gagner du temps sur l'exécution de la macro Calcul.

En effet ce qui prend du temps c'est le calcul des sinus et cosinus des angles.

En calculant dès le début les sinus et cosinus des latitudes et en les stockant dans un tableau on gagne 30% (soit 4 minutes) sur la durée d'exécution :

Code:

'---tableau des sinus et cosinus des latitudes (pour gagner du temps)---
ReDim sincos(1 To ub, 1 To 2)
For i = 1 To ub
  sincos(i, 1) = Sin(t(i, 3)): sincos(i, 2) = Cos(t(i, 3))
Next i

Par ailleurs pour passer le temps j'affiche la progression des calculs dans la barre d'état, cela ne fait perdre qu'une vingtaine de secondes.

Fichier (4).

A+

XL 2016 Affichage villes proches à partir coordonnées GPS

XLDnaute Nouveau

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

Pièces jointes

Discussions similaires