Power Query Retravailler un fichier JSON avec PQ

[Staple1600]

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

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

 

[klin89]

Re à tous,

Précédemment, je ne parvenais pas à ressortir le canton dans une requête overpass turbo.
J'ai enfin trouvé en analysant directement les objets et leurs tags sur la carte Openstreetmap.
Voici un exemple de requête affichant une commune au sein de son canton et de son département.

[out:xml][timeout:100];
//la commune
area["ref:INSEE"="89197"]->.a;
rel(area.a)["admin_level"="8"]["boundary"="administrative"];
out geom;
//le canton
>;
is_in;
rel(pivot)
["ref:INSEE"="8907"]["boundary"="political"];
out geom;
//le département
>;
is_in;
rel(pivot)
["ref:INSEE"="89"]["boundary"="administrative"];
out geom;
{{style:
  relation{text:name}
  node {width: 0;opacity:0;fill-opacity:0;}
}}

Pour le canton, j'ai remplacé "boundary"="administrative" par "boundary"="political"
Pour les EPCI, on utilise "boundary"="local_authority"

On voit qu'il y a 3 polygones.
mromain, je te réponds dès que j'ai un peu de temps.

klin89

 

[Staple1600]

Bonsoir @klin89

Est-ce que tu vois comment boucler pour créer N tableaux avec lesquels on dessinera N shapes ?

 

[klin89]

Re Staple1600,

Pour répondre au post #77, on pourrait peut-être passer par un dictionnaire pour former les shapes, les données de chaque commune figurant dans une variable tableau à 2 dimensions.
Données représentées au post #67 avec 5 colonnes

Sub test()
Dim a, w, e, dico As Object, txt As String
Dim i As Long, ii As Byte
Set dico = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
'la feuille où figurent les données représentées au post #67
a = Sheets(1).Range("A1").CurrentRegion.Value2
    For i = 2 To UBound(a, 1)
       'txt = la commune et l'index forme la clé du dico
        txt = Join$(Array(a(i, 2), a(i, 3)), "|")
        If Not dico.exists(txt) Then
            ReDim w(1 To UBound(a, 2), 1 To 1)
        Else
            w = dico(txt)
            ReDim Preserve w(1 To UBound(w, 1), 1 To UBound(w, 2) + 1)
        End If
        For ii = 1 To UBound(a, 2)
            w(ii, UBound(w, 2)) = a(i, ii)
        Next
        'on associe l'item à la clé soit un tableau à 2 dimensions
        'soit l'équivalent de la variable Qry_JSON dans le code
        'figurant au post #66 de mromain
        dico(txt) = w
    Next
    For Each e In dico
       'w = Qry_JSON
        w = Application.Transpose(dico(e))
        MaxLatitude = Application.Max(Application.Index(w, 0, 5))
        MaxLongitude = Application.Max(Application.Index(w, 0, 4))
        MinLatitude = Application.Min(Application.Index(w, 0, 5))
        MinLongitude = Application.Min(Application.Index(w, 0, 4))
        'à compléter avec le code de mromain
 
 
    Next
Set dico = Nothing
Application.ScreenUpdating = True
End Sub

On peut ainsi inclure le code de mromain dans la 2ème boucle en s'appuyant sur la variable w pour former vPoints.
A voir....
klin89

 

[Staple1600]

Bonjour le fil

@klin89
Merci pour ta suggestion et ton code
Je vais tester la chose dans les jours qui viennent
Et je vous redis

@mromain
Ce que j'évoquais plus bas dans le fil
Faire les calculs de X et Y (dans PowerQuery en ajoutant deux colonnes)
C'est une piste à suivre ou pas ?

 

[klin89]

Bonjour à tous,

mromain, pas de soucis avec le fichier du post #34, cela reproduit bien les contours de toutes les formes.
L'exemple proposé est issu d'un fichier Json généré via une requête overpass turbo pour mon village entouré des communes limitrophes.

Par contre, j'ai fait un test avec ce que je proposais au post #78 et j'obtiens un joli puzzle à reconstituer pour les enfants
Evidemment, ça empile toutes les shapes

Je vais laisser faire les experts en géomatique, c'est préférable
klin89

 

[mromain]

Bonjour à tous,

@Staple1600 :

Faire les calculs de X et Y (dans PowerQuery en ajoutant deux colonnes)
C'est une piste à suivre ou pas ?

C'est faisable oui.
Tu trouveras un exemple ci-dessous. Il s'agit d'une requête pour les fichiers geojson générés par overpass-turbo où les calculs de Top et Left sont faits dans la requête.

let
    fnExtractGeometryInfos = (geometry as record) as table =>
        let
            fnExtractPointsCoordinates = (list as list) as list => List.Transform(list{1}{0}, each [ShapeID = list{0}, Longitude=_{0}, Latitude=_{1}]),
            shapesList =
                let
                    list = geometry[coordinates],
                    listCount = List.Count(list),
                    geoType = geometry[type]
                in
                    if geoType = "Polygon" then {{1, geometry[coordinates]}} else if geoType = "MultiPolygon" then List.Zip({{1 .. listCount}, list}) else {},
            shapeCoordinates = List.Combine(List.Transform(shapesList, fnExtractPointsCoordinates)),
            toTable = if List.Count(shapeCoordinates) > 0 then Table.FromRecords(shapeCoordinates) else #table({"ShapeID", "Longitude", "Latitude"}, {}),
            changeTypes = Table.TransformColumnTypes(toTable,{{"ShapeID", Int64.Type}, {"Longitude", type number}, {"Latitude", type number}})
        in
            changeTypes,

    Source = Json.Document(File.Contents("C:\...\fichier.geojson")),
    RecupFeatures = Source[features],
    ToTable = Table.FromList(RecupFeatures, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandDatas = Table.ExpandRecordColumn(ToTable, "Column1", {"properties", "geometry"}, {"properties", "geometry"}),
    ExpandProperties = Table.ExpandRecordColumn(ExpandDatas, "properties", {"ref:INSEE", "name"}, {"ref:INSEE", "name"}),
    TransformShapesCoordonates = Table.TransformColumns(ExpandProperties,{{"geometry", fnExtractGeometryInfos, type table}}),
    ExpandShapesCoordonates = Table.ExpandTableColumn(TransformShapesCoordonates, "geometry", {"ShapeID", "Longitude", "Latitude"}, {"ShapeID", "Longitude", "Latitude"}),
   
    AddColumnPlanedLongitude = Table.AddColumn(ExpandShapesCoordonates, "PlanedLongitude", each [Longitude]*Number.Cos([Latitude]*Number.PI/180)),
    CoordinatesArea = [MinLongitude=List.Min(AddColumnPlanedLongitude[PlanedLongitude]),
                       MaxLongitude=List.Max(AddColumnPlanedLongitude[PlanedLongitude]),
                       MinLatitude=List.Min(AddColumnPlanedLongitude[Latitude]),
                       MaxLatitude=List.Max(AddColumnPlanedLongitude[Latitude])],
    ShapeMaxArea = [MaxHeight=500, MaxWidth=750],
    ShapeArea = let ratioCoordinates = Number.Abs((CoordinatesArea[MaxLongitude]-CoordinatesArea[MinLongitude])/(CoordinatesArea[MaxLatitude]-CoordinatesArea[MinLatitude])) in
                if ratioCoordinates <= Number.Abs(ShapeMaxArea[MaxWidth]/ShapeMaxArea[MaxHeight])
                then [MaxWidth=ShapeMaxArea[MaxHeight] * ratioCoordinates, MaxHeight=ShapeMaxArea[MaxHeight]]
                else [MaxWidth=ShapeMaxArea[MaxWidth], MaxHeight=ShapeMaxArea[MaxWidth]/ratioCoordinates],
    CoefConvert = [aLat = -ShapeArea[MaxHeight] / (CoordinatesArea[MaxLatitude] - CoordinatesArea[MinLatitude]),
                   bLat = -aLat * CoordinatesArea[MaxLatitude],
                   aLong = ShapeArea[MaxWidth]/ (CoordinatesArea[MaxLongitude] - CoordinatesArea[MinLongitude]),
                   bLong = -aLong * CoordinatesArea[MinLongitude]],
    AddColumnTop = Table.AddColumn(AddColumnPlanedLongitude, "Top", each CoefConvert[aLat] * [Latitude] + CoefConvert[bLat]),
    AddColumnLeft = Table.AddColumn(AddColumnTop, "Left", each CoefConvert[aLong] * [PlanedLongitude] + CoefConvert[bLong]),
    SelectColumns = Table.SelectColumns(AddColumnLeft,{"ref:INSEE", "name", "ShapeID", "Longitude", "Left", "Latitude", "Top"})
in
    SelectColumns

Concernant cette requête :

• l'étape AddColumnPlanedLongitude sert à appliquer la projection ("aplanir les Longitudes") ;
• l'étape CoordinatesArea sert à récupérer les min et max des Latitude et Longitude ;
• l'étape ShapeMaxArea sert à définir la zone de dessin (la hauteur maximale ou la largeur maximale) ;
• l'étape ShapeArea sert à définir la zone de dessin finale (en fonction de la ShapeMaxArea et de la CoordinatesArea) ;
• l'étape CoefConvert sert à calculer les "coefficients de conversion" ;
• les étapes AddColumnTop et AddColumnLeft servent à rajouter les colonnes Top et Left.

PAR CONTRE :

• je me suis aperçu d'une erreur dans mon calcul de "conversion Latitude/Longitude en Top/Left" dans le fichier proposé dans le post #34.
  Je viens de mettre à jour le fichier. La correction améliore un petit peu le rendu.
  L'exemple de calcul de présent dans la requête PowerQuery ci-dessus prend en compte cette correction.
• suite au post#72 de @p56, j'ai pu lire un peu sur les différents modes de projection (notamment les différentes projections de Lambert).
  C'est un sujet assez complexe, tout ce que je peux dire, c'est que les calculs que j'ai proposés sont très approximatifs. Ils fonctionnent plutôt pas mal dans les hautes latitudes de la France, mais déjà moins bien dans les basses.

@klin89 :

Par contre, j'ai fait un test avec ce que je proposais au post #78 et j'obtiens un joli puzzle à reconstituer pour les enfants
Evidemment, ça empile toutes les shapes

Hé oui. Quand on calcule les Top et Left, il faut le faire sur l'ensemble du jeu de données afin que les formes aient la même échelle et soient bien positionnées les unes par rapport aux autres.

A+

 

[Staple1600]

Bonjour le fil

@mromain
Je teste ta nouvelle requête PQ quand je retrouverai mon Office 365
(Je suis actuellement sur un vieux XP avec XL 2003)

 

[klin89]

Re à tous,

@mromain,
Génial la requête du post #81
Par contre, j'ai remarqué que la requête générait des lignes vides en fin du tableau structuré, soit autant de lignes vides que de shapes.
J'ai testé plusieurs requêtes overpass turbo avec un nombre différent de shapes pour en arriver à ce constat.
Voir la capture d'écran ci-dessous :

Ça n'a peut-être aucune incidence au final ?

klin89

 

[mromain]

Bonjour à tous,

@klin89

Par contre, j'ai remarqué que la requête générait des lignes vides en fin du tableau structuré, soit autant de lignes vides que de shapes.

Effectivement, je viens de le constater sur un fichier généré par overpass-turbo.

Ces lignes vides peuvent être supprimées en fin de requête.
Tu peux même les supprimer après l'étape ExpandProperties où on voit que les colonnes ref:INSEE et name contiennent des valeurs nulles.

A+

 

[Staple1600]

Bonjour le fil, @mromain , @klin89

@mromain
Les fichiers *.geojson générés par overpass-turbo ont un format/syntaxe particulier ?
Ou on peut traiter tout type de fichier ayant l'extension *.geojson ?

Si oui, il faut que je retrouve la syntaxe overpass pour générer un geojson avec par exemple toutes les communes du 35.

 

[Staple1600]

Re,

En attendant d'avoir les communes du 35 depuis overpass-turbo, j'ai essayé
en utilisant ce fichier (comme précédemment dans le fil) directement depuis le web

Source = Json.Document(Web.Contents("https://data.explore.star.fr/api/explore/v2.1/catalog/datasets/communes-france/exports/geojson?lang=fr&timezone=Europe%2FBerlin")),

Je n'ai pas d'erreur mais je ne suis pas sur de ce j'obtiens dans Excel.
(Les colonnes ref:INSEE et name sont vides, ce qui est normal au vu du fichier)

 

[klin89]

Bonsoir à tous,

Essaie cette requête overpass, elle renvoie 333 POIs

[out:xml][timeout:100];
area["ref:INSEE"="35"]->.a;
rel(area.a)["boundary"="administrative"]["admin_level"="8"];
out geom;
{{style:
node{width: 0;opacity:0;fill-opacity:0;}
}}
>;
is_in;
rel(pivot)
["admin_level"="5"];
out geom;

Pour mon département (Yonne), cela me renvoie 424 communes alors que l'Insee annonce 423 communes.
Peut-être une fusion de communes qui n'est pas encore mise à jour dans Openstreetmap.
A vérifier.
Edit :
Requête overpass récupérant le contour des communes situées en deçà d'un rayon de 4 kms à partir d'un point géographique.

[out:xml][timeout:25];
rel(around:4000,47.47966,4.05892)["boundary"="administrative"]["admin_level"="8"];
{{style:
relation{text:name}
node{width: 0;opacity:0;fill-opacity:0;}
}}
out geom;

klin89

 

[Staple1600]

Bonjour le fil, @mromain, @klin89

@klin89
Merci pour la requête Overpass

Question:
Qu'est ce qui explique la différence de taille entre les deux fichiers *.geojson ?
• celui du site de la Star : 3.55 Mo
• celui d'overpass : 30 Mo

 

[Staple1600]

Bonjour le fil

En utilisant le fichier *.geojson obtenue avec la requête OPT fournie par @klin89, et la requête PQ de @mromain du message#81, j'obtiens une feuille Excel avec:
384600 lignes et 333* lignes vides.
* : 333 comme le nombre de communes.

Je suis toujours largué pour finaliser le code VBA pour dessiner toutes les shapes.

 

[mromain]

Bonjour à tous,

@Staple1600

Question:
Qu'est ce qui explique la différence de taille entre les deux fichiers *.geojson ?
• celui du site de la Star : 3.55 Mo
• celui d'overpass : 30 Mo

C'est toujours une histoire de "précision" des cartes, comme présenté au post #60.

En utilisant le fichier *.geojson obtenue avec la requête OPT fournie par @klin89, et la requête PQ de @mromain du message#81, j'obtiens une feuille Excel avec:
384600 lignes et 333* lignes vides.
* : 333 comme le nombre de communes.

Ces lignes vides peuvent être supprimées. Il s'agit de features (entrées des fichiers json/geojson) ne représentant pas des Polygon ou MultiPolygon)

Je suis toujours largué pour finaliser le code VBA pour dessiner toutes les shapes.

Tu trouveras ci-joint le fichier du post #34 retouché à cette fin.
Il est dorénavant compatible avec les différentes sources abordées dans ce fil :

• les fichiers issus des requêtes overpass-turbo présentées par @klin89 ;
• le fichier issu de data.bretagne.bzh ;
• le fichier issu de data.opendatasoft.com représentant les cantons d'Ille-et-Vilaine ;
• le fichier issu de data.explore.star.fr.

Chaque format de fichier a ses particularités, mais celles-ci sont prises en compte dans la requête.
Si d'autres formats de fichiers json/geojson doivent être traités, il faudra alors retoucher la requête.

Le code utilise toujours PowerPoint pour permettre de merger les formes de type MultiPolygon – permettre par exemple que la commune de Saint-Malo, qui est composée de plusieurs polygones, soit représentée en une seule forme (au sens Office du terme) et non en un groupe de forme.
Il est possible de modifier ce comportement pour que les formes de type MultiPolygon soient représentées en groupe de formes. Pour ce, il faut modifier la valeur du nom Dessin!MergePolygons (la mettre à FAUX).

Enfin, le mode de projection des coordonnées à été revu. Dans cette version, on utilise la projection de Mercator.

A+