Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Power Query Retravailler un fichier JSON avec PQ

Staple1600

XLDnaute Barbatruc
Bonjour

Pour faire suite à ma discussion : Carte choroplèthe, je cherche à manipuler un fichier *.json avec PowerQuery

J'arrive bien à faire les 1ères étapes
PowerQuery: 
let
    Source = Json.Document(File.Contents("C:\Users\STAPLE\Documents\communes-france.json")),
    #"Converti en table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table", "Column1", {"geo_point_2d", "geo_shape", "year", "reg_code", "reg_name", "dep_code", "dep_name", "arrdep_code", "arrdep_name", "ze2020_code", "ze2020_name", "bv2012_code", "bv2012_name", "epci_code", "epci_name", "ept_code", "ept_name", "com_code", "com_current_code", "com_name", "com_name_upper", "com_name_lower", "com_area_code", "com_type", "ze2010_name", "ze2010_code", "com_cataeu2010_code", "com_cataeu2010_name", "com_uu2010_code", "com_uu2010_status", "com_au2010_code", "com_cateaav2020_code", "com_cateaav2020_name", "com_uu2020_code", "com_uu2020_status", "com_aav2020_code", "com_cv_code", "com_in_ctu", "com_siren_code", "com_is_mountain_area"}, {"Column1.geo_point_2d", "Column1.geo_shape", "Column1.year", "Column1.reg_code", "Column1.reg_name", "Column1.dep_code", "Column1.dep_name", "Column1.arrdep_code", "Column1.arrdep_name", "Column1.ze2020_code", "Column1.ze2020_name", "Column1.bv2012_code", "Column1.bv2012_name", "Column1.epci_code", "Column1.epci_name", "Column1.ept_code", "Column1.ept_name", "Column1.com_code", "Column1.com_current_code", "Column1.com_name", "Column1.com_name_upper", "Column1.com_name_lower", "Column1.com_area_code", "Column1.com_type", "Column1.ze2010_name", "Column1.ze2010_code", "Column1.com_cataeu2010_code", "Column1.com_cataeu2010_name", "Column1.com_uu2010_code", "Column1.com_uu2010_status", "Column1.com_au2010_code", "Column1.com_cateaav2020_code", "Column1.com_cateaav2020_name", "Column1.com_uu2020_code", "Column1.com_uu2020_status", "Column1.com_aav2020_code", "Column1.com_cv_code", "Column1.com_in_ctu", "Column1.com_siren_code", "Column1.com_is_mountain_area"}),
    #"Column1.geo_point_2d développé" = Table.ExpandRecordColumn(#"Column1 développé", "Column1.geo_point_2d", {"lon", "lat"}, {"Column1.geo_point_2d.lon", "Column1.geo_point_2d.lat"}),
    #"Column1.geo_shape développé" = Table.ExpandRecordColumn(#"Column1.geo_point_2d développé", "Column1.geo_shape", {"type", "geometry", "properties"}, {"Column1.geo_shape.type", "Column1.geo_shape.geometry", "Column1.geo_shape.properties"}),
    #"Column1.geo_shape.geometry développé" = Table.ExpandRecordColumn(#"Column1.geo_shape développé", "Column1.geo_shape.geometry", {"coordinates", "type"}, {"Column1.geo_shape.geometry.coordinates", "Column1.geo_shape.geometry.type"})
in
    #"Column1.geo_shape.geometry développé"
Mais dans le tableau structuré, j'ai plusieurs colonnes avec comme valeurs : [List]

Je n'arrive pas à trouver quelles actions je dois choisir dans le ruban pour avoir dans le tableau Excel le fichier JSON correctement "interprété".

Ci-dessous le lien pour télécharger le fichier JSON ( 3 837 Ko)
fichier JSON
 
Dernière édition:
Trier par date Trier par votes

Staple1600

XLDnaute Barbatruc
Bonjour le fil,

@mromain
Merci pour les explications.

Malgré mes relectures des différents codes proposés, je sèche lamentablement pour dessiner les shapes avec AddPolyline
(C'est ballot )

Pour tester, j'ai copié le tableau obtenu avec PQ dans une feuille vierge
Puis plein d'espoir, je suis allé dans VBA
Code: 
Sub Test_dans_la_Semoule()
Dim Qry_JSON() As Variant
ReDim Qry_JSON(1 To Worksheets(1).ListObjects(1).DataBodyRange.Rows.Count, 1 To 3)
Qry_JSON = Worksheets(1).ListObjects(1).DataBodyRange.Offset(0, 2).Resize(UBound(Qry_JSON, 1), UBound(Qry_JSON, 2)).Value
MsgBox Qry_JSON(1, 1) 'Shp_Id
MsgBox Qry_JSON(1, 2) ' long
MsgBox Qry_JSON(1, 3) ' lat
'Jusqu'ici tout va bien
End Sub
Ensuite j'ai eu un coup de mou, suis allé me chercher une bière et j'ai trinqué en maudissant le prof de maths qui m'a fâché avec elles au XXième siècle.

PS: J'ai compris qu'on déclarer un array en Single pour y mettre les coordonnées et
que les 1ères valeurs doivent être égale aux deux dernières.
 
Vote positif 0 Vote négatif

mromain

XLDnaute Barbatruc
Bonjour à tous, bonjour Staple,

En fait, il y a plusieurs points / étapes :
  • il faut déjà "aplanir" les coordonnées
    1 degré de longitude n’a pas la même distance en fonction de la latitude :
  • ensuite, il faut convertir les latitudes et longitudes en Top et Left (système de coordonnées des Shapes Office)
  • et finalement tu pourras dessiner les formes : 1 AddPolyline par forme.

ton exemple qui passe avec Powerpoint (avec des modules de classe) est trop complexe pour moi
Cliquez pour agrandir...
Les modules de classe sont au final assez basiques. Ils permettent juste de structurer les données :
Le jeu de données contient plusieurs villes (Cls_Cities), composé donc de villes (Cls_City), elles-mêmes composées de plusieurs formes (Cls_Shape) composées de plusieurs coordonnées (Cls_Point).

Si tu prends l’exemple fourni au post #34, la partie intéressante est dans la méthode Cls_Cities.CalculatePointsCoordinates. C’est elle qui "aplani" les coordonnées et fait la conversion vers le "référentiel Top/Left".

A+
 
Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc
Bonjour le fil, @mromain

Au coin, Staple !
(et tu gardes le bonnet d'âne vissé sur la tête)
J'ai isolé les données de la ville d'Acigné
Créé un tableau nommé Tableau1
Ajouté une seconde feuille
Puis j'ai mis ceci dans mon VBE

VB: 
Sub test()
Dim Qry_JSON() As Variant, i&, shp As Shape
Dim l_d_maxWidth    As Double, l_d_maxHeight  As Double
Dim l_d_aLat As Double, l_d_bLat As Double
Dim l_d_aLong As Double, l_d_bLong As Double, l_d_ratioDegToRad As Double
ReDim Qry_JSON(1 To Worksheets(1).ListObjects(1).DataBodyRange.Rows.Count, 1 To 3)
Qry_JSON = Worksheets(1).ListObjects(1).DataBodyRange.Resize(UBound(Qry_JSON, 1), UBound(Qry_JSON, 2)).Value
MaxLatitude = Evaluate("=MAX(Tableau1[Latitude])")
MaxLongitude = Evaluate("=MAX(Tableau1[Longitude])")
MinLatitude = Evaluate("=MIN(Tableau1[Latitude])")
MinLongitude = Evaluate("=MIN(Tableau1[Longitude])")
XX = 500
YY = 700
 'calculer la zone de dessin
    If Abs((MaxLongitude - MinLongitude) / (MaxLatitude - MinLatitude)) <= (XX / YY) Then
        l_d_maxWidth = XX * Abs((MaxLongitude - MinLongitude) / (MaxLatitude - MinLatitude))
    Else
        l_d_maxWidth = YY
        l_d_maxHeight = YY / Abs((MaxLongitude - MinLongitude) / (MaxLatitude - MinLatitude))
    End If
l_d_aLat = -l_d_maxHeight / (MaxLatitude - MinLatitude)
l_d_bLat = -l_d_aLat * MaxLatitude
l_d_aLong = l_d_maxWidth / (MaxLongitude - MinLongitude)
l_d_bLong = -l_d_aLong * MinLongitude
l_d_ratioDegToRad = 4 * Atn(1) / 180
ReDim vPoints(1 To UBound(Qry_JSON, 1), 1 To 2) As Single
For i = 1 To UBound(Qry_JSON, 1)
vPoints(1, 1) = l_d_aLat * Qry_JSON(i, 3) + l_d_bLat
vPoints(1, 2) = (l_d_aLong * Qry_JSON(i, 2) + l_d_bLong) * Cos(Qry_JSON(i, 2) * l_d_ratioDegToRad)
Next
Set shp = Feuil2.Shapes.AddPolyline(vPoints)
End Sub
 
Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc

Avec un i au lieu d'un 1, je peux retirer mon bonnet d'âne
La honte !
Enrichi (BBcode): 
Sub test()
Dim Qry_JSON() As Variant, i&, shp As Shape
Dim l_d_maxWidth    As Double, l_d_maxHeight  As Double
Dim l_d_aLat As Double, l_d_bLat As Double
Dim l_d_aLong As Double, l_d_bLong As Double, l_d_ratioDegToRad As Double
ReDim Qry_JSON(1 To Worksheets(1).ListObjects(1).DataBodyRange.Rows.Count, 1 To 3)
Qry_JSON = Worksheets(1).ListObjects(1).DataBodyRange.Resize(UBound(Qry_JSON, 1), UBound(Qry_JSON, 2)).Value
MaxLatitude = Evaluate("=MAX(Tableau1[Latitude])")
MaxLongitude = Evaluate("=MAX(Tableau1[Longitude])")
MinLatitude = Evaluate("=MIN(Tableau1[Latitude])")
MinLongitude = Evaluate("=MIN(Tableau1[Longitude])")
XX = 500
YY = 700
 'calculer la zone de dessin
    If Abs((MaxLongitude - MinLongitude) / (MaxLatitude - MinLatitude)) <= (XX / YY) Then
        l_d_maxWidth = XX * Abs((MaxLongitude - MinLongitude) / (MaxLatitude - MinLatitude))
    Else
        l_d_maxWidth = YY
        l_d_maxHeight = YY / Abs((MaxLongitude - MinLongitude) / (MaxLatitude - MinLatitude))
    End If
l_d_aLat = -l_d_maxHeight / (MaxLatitude - MinLatitude)
l_d_bLat = -l_d_aLat * MaxLatitude
l_d_aLong = l_d_maxWidth / (MaxLongitude - MinLongitude)
l_d_bLong = -l_d_aLong * MinLongitude
l_d_ratioDegToRad = 4 * Atn(1) / 180
ReDim vPoints(1 To UBound(Qry_JSON, 1), 1 To 2) As Single
For i = 1 To UBound(Qry_JSON, 1)
vPoints(i, 1) = l_d_aLat * Qry_JSON(i, 3) + l_d_bLat
vPoints(i, 2) = (l_d_aLong * Qry_JSON(i, 2) + l_d_bLong) * Cos(Qry_JSON(i, 2) * l_d_ratioDegToRad)
Next
Set shp = Feuil2.Shapes.AddPolyline(vPoints)
End Sub
 
Vote positif 0 Vote négatif

mromain

XLDnaute Barbatruc
Bonjour à tous, bonjour Staple,

Avec un i au lieu d'un 1, je peux retirer mon bonnet d'âne
Cliquez pour agrandir...


J'ai fait le même test que toi, avec les coordonnées de Acigné dans un tableau avec les colonnes ShapeID, Longitude et Latitude (dans cet ordre, c'est important).
J’ai eu à retoucher ton code pour régler 2 petits soucis :
  • L'ordre des données dans le tableau vPoints ;
  • La formule (au niveau du cos).
Enrichi (BBcode): 
Sub test()
Dim Qry_JSON() As Variant, i&, shp As Shape
Dim l_d_maxWidth    As Double, l_d_maxHeight  As Double
Dim l_d_aLat As Double, l_d_bLat As Double
Dim l_d_aLong As Double, l_d_bLong As Double, l_d_ratioDegToRad As Double
    ReDim Qry_JSON(1 To Worksheets(1).ListObjects(1).DataBodyRange.Rows.Count, 1 To 3)
    Qry_JSON = Worksheets(1).ListObjects(1).DataBodyRange.Resize(UBound(Qry_JSON, 1), UBound(Qry_JSON, 2)).Value
    MaxLatitude = Evaluate("=MAX(Tableau1[Latitude])")
    MaxLongitude = Evaluate("=MAX(Tableau1[Longitude])")
    MinLatitude = Evaluate("=MIN(Tableau1[Latitude])")
    MinLongitude = Evaluate("=MIN(Tableau1[Longitude])")
    XX = 500
    YY = 700
    'calculer la zone de dessin
    If Abs((MaxLongitude - MinLongitude) / (MaxLatitude - MinLatitude)) <= (XX / YY) Then
        l_d_maxWidth = XX * Abs((MaxLongitude - MinLongitude) / (MaxLatitude - MinLatitude))
    Else
        l_d_maxWidth = YY
        l_d_maxHeight = YY / Abs((MaxLongitude - MinLongitude) / (MaxLatitude - MinLatitude))
    End If
    l_d_aLat = -l_d_maxHeight / (MaxLatitude - MinLatitude)
    l_d_bLat = -l_d_aLat * MaxLatitude
    l_d_aLong = l_d_maxWidth / (MaxLongitude - MinLongitude)
    l_d_bLong = -l_d_aLong * MinLongitude
    l_d_ratioDegToRad = 4 * Atn(1) / 180
    ReDim vPoints(1 To UBound(Qry_JSON, 1), 1 To 2) As Single
    For i = 1 To UBound(Qry_JSON, 1)
        vPoints(i, 2) = l_d_aLat * Qry_JSON(i, 3) + l_d_bLat
        vPoints(i, 1) = (l_d_aLong * Qry_JSON(i, 2) + l_d_bLong) * Cos(Qry_JSON(i, 3) * l_d_ratioDegToRad)
    Next
    Set shp = Feuil2.Shapes.AddPolyline(vPoints)
    shp.Top = 0
    shp.Left = 0
End Sub


Là ça semble bien dessiner le contour de la commune.

Dis-moi ce que t’en penses.

Bonne journée
 
Dernière édition:
Vote positif 0 Vote négatif

klin89

XLDnaute Accro
Re mromain, staple1600

Pour tester, j'ai dû rentrer les valeurs en dur ci-dessous sinon cela me renvoie une erreur 2015
VB: 
    MaxLatitude = Evaluate("=MAX(Tableau1[Latitude])")
    MaxLongitude = Evaluate("=MAX(Tableau1[Longitude])")
    MinLatitude = Evaluate("=MIN(Tableau1[Latitude])")
    MinLongitude = Evaluate("=MIN(Tableau1[Longitude])")

Pour ma commune et ses 606 points, voilà ce que j'obtiens


Par contre, j'ai voulu testé avec 7 communes limitrophes (5196 points) mais je n'obtiens rien avec le tableau structuré de 3 colonnes comme indiqué par Romain.

Le tableau structuré se présente comme ceci avec la requête Power Query de Romain (overpass turbo)

Comment obtenir toutes les communes désignées dans le tableau ?

klin89
 
Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc
Bonsoir le fil, @mromain, @klin89

Je sais pas ce que j'ai bouiné
Je viens de retester
Ca fonctionnait plus
(ni avec ta modif @mromain)

Un petit MsgBox a éclairé ma lanterne
En l'état
Msg Qry_JSON(1,2) renvoie : Arbrissel
(oui j'ai changé de commune )

J'ai donc modifié comme suit
Enrichi (BBcode): 
Qry_JSON = Worksheets(1).ListObjects(1).DataBodyRange.Offset(, 2).Resize(UBound(Qry_JSON, 1), UBound(Qry_JSON, 2)).Value
Et la plus d'erreur, et j'obtiens une forme.

@klin89
je pense que la prochaine étape c'est de boucler sur les Shapes_ID et donc de remplir N tableaux pour dessiner N Shapes avec AddPolyline.
Mais comment assembler tout cela ? Pour le moment, je repose mes neurones
 
Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc
Re

Qui a raison Wikipédia ou VBA ?
(en rouge la Shape obtenu par le code)

Au moins, c'est ressemblant

Question au passage
On pourrait faire les calculs directement dans PQ (en ajoutant deux colonnes), voire des fonctions personnalisées dans PQ

Mais là encore, je me tire encore une balle dans le pied des mes neurones
 
Vote positif 0 Vote négatif

mromain

XLDnaute Barbatruc
Bonsoir à tous,

@klin89 : est-ce que le fichier du post #34 ne répond pas à ta demande ? Normalement, il marche avec en entrée des fichier créés sur OverPassTurbo.

@Staple1600 : je pense qu'il faut avoir l'ensemble des données dans un même tableau, ce afin que toutes les Shapes aient la même échelle et qu'elles soient bien positionnées les unes par rapport aux autres.

A+
 
Vote positif 0 Vote négatif

mromain

XLDnaute Barbatruc
Bonsoir Staple,

On s'est croisé, je viens juste de voir ton précédent message.
Il s'agit de quelle commune ?
J'avais contrôlé pour Acigné avec Google Map et je n'avais pas vu d'écart...

J'y rejetterai un œil demain.

Bonne soirée
 
Vote positif 0 Vote négatif

p56

XLDnaute Occasionnel
Bonjour à tous,

Juste une réflexion en passant : pour dessiner des cartes, toutes les méthodes de conversion entrainent des "déformations" : aplanir du sphérique n'est JAMAIS parfait. Ceci dit pour positionner un point quelconque ou un tracé sur une carte si le référentiel de conversion est le même pour le dessin et pour le-les point-s à placer, le rendu sera correct, relativement.

Ainsi pour la France métropolitaine on peut par exemple se référer au Lambert (93 ou Lambert CC diverses).
Pour reprendre l'exemple d'Arbrissel, le Lambert 93 et le Lambert CC48 (ou même CC49) donnent tous les 2 un dessin très semblable à celui de wikipédia (contour en bleu sur ma démo).


En revanche, pour d'autres endroits du globe, d'autres méthodes de conversion sont à préférer.
Ici un exemple pour Nouméa - quasi aux antipodes de la France métropolitaine (contours en rouge)


A noter : dans ces 2 exemples les contours dessinés par Vba sont un peu différents des cartes "internet" : les référentiels de conversion n'étant pas les mêmes

Si ça peut aider pour vos essais, sur mon site je propose un convertisseur de Wgs84 vers Lambert/Lambert CC/Utm (du Lambert II étendu est aussi présent mais pas terrible) =>http://tatiak.canalblog.com/archives/2020/02/13/38021455.html

P.
 
Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc
Bonsoir le fil

@p56
Merci de remuer le couteau dans la plaie
Je lis ceci
p1 = Atn((Z_n / Sqr(X_n * X_n + Y_n * Y_n)) / (1 - (a_n * e2_n * Cos(p0)) / (Sqr((X_n * X_n + Y_n * Y_n) * (1 - e2_n * Pow(Sin(p0), 2))))))
Cliquez pour agrandir...
Et j'ai une soudaine et brutale suée.
Je me rappelle les maths du lycée
Je revois la tête du prof
BONG
Je viens de faire un collapsus et je suis tombé de ma chaise.

Heureusement, il me reste une bière dans le frigo.

PS: J'ai mis ton classeur dans ma besace pour plus tard
Il parait qu'il est jamais trop tard pour se mettre aux maths

@mromain
On est bien d'accord que pour dessiner la carte, il faut créer N shapes avec AddPolyline pour chaque commune
en se basant sur le Shape_ID ?
Donc il faudra créer plusieurs tableaux vPoints
(un par commune ou par Shape_ID si on généralise pour traiter différents fichiers JSON)
 
Vote positif 0 Vote négatif

mromain

XLDnaute Barbatruc
Bonjour à tous,

@p56 : merci pour les infos t le partage. Ça fait de la bonne lecture

@Staple1600 :
On est bien d'accord que pour dessiner la carte, il faut créer N shapes avec AddPolyline pour chaque commune
Cliquez pour agrandir...
Oui tout à fait.
en se basant sur le Shape_ID ?
Cliquez pour agrandir...
En fait, sur les requêtes de ce fil, c'est le couple commune + ShapeID qui identifie une forme.
Les communes avec un seul contour ont toutes un seul ShapeID (qui vaut 1).
Les communes avec plusieurs contours ont plusieurs ShapeID (de 1 à nombre de contours).
Donc il faudra créer plusieurs tableaux vPoints
(un par commune ou par Shape_ID si on généralise pour traiter différents fichiers JSON)
Cliquez pour agrandir...
Oui tout à fait. Un tableau vPoints par AddPolyline.

A+
 
Vote positif 0 Vote négatif

Staple1600

XLDnaute Barbatruc
Bonsoir le fil

@mromain
C'est pour le plaisir de titiller mes neurones, sinon je ferais plus simple
Un Webbrowser dans un userform et hop j'affiche directement la carte depuis une URL OSM
(Le PC de mon taf (Excel 2019) est en 64 bits, je ne peux pas utiliser ce contrôle, car je ne peux bidouiller la base de registre, mon Windows étant bridé par la DSN)

Je vais réexaminer les divers codes VBA du fil, en espérant que la lumière surgisse car pour le moment, je rame

Si jamais vous avez une allumette pour ma lanterne , n'hésitez pas
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…