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

 

[mromain]

Bonjour à tous,

En complément, voici le même fichier où la projection de Mercator n'est plus réalisée en VBA mais au sein de la requête PowerQuery.

A+

 

[Staple1600]

Bonjour le fil

@mromain

Superbe ouvrage

Il va me falloir du temps et moult Guronsan pour appréhender ton code VBA dans toute sa splendeur
(Pour le code M, je devrais moins souffrir

Merci encore pour tes productions dans ce fil.

Merci à tous les intervenants d'ailleurs.

Oserai-je dire que le tour de la question a été réalisé ?

 

[klin89]

Bonsoir à tous,

Pour faire suite à la discussion, je me demandais s'il était possible d'adapter le code de mromain et la requête Power Query pour ressortir toutes les formes représentant les lieux dits d'une commune à partir d'un fichier JSON.
Le fichier JSON étant structuré comme ceci

est issu du lien suivant :

https://cadastre.data.gouv.fr/data/etalab-cadastre/2021-07-01/geojson/communes/
J'ai testé avec le fichier de mromain mais cela me renvoie une erreur due sûrement à la structure des données.
Edit: pour décompresser les fichiers au format json.gz, j'utilise 7-zip

klin89

 

[p56]

Bonjour à tous,
@klin89 : si ça peut aider voici les données en xls et la carte de ce cadastre (carte selon Lambert CC47)

(sans PQ, car je ne sais pas l'utiliser)
P.

 

[klin89]

Bonjour à tous,

Merci Pierrot pour ton retour.

En fait, l'idée est d'obtenir le contour d'une commune et de ses zones habitées ( le village, les hameaux voire les fermes isolées) qui la composent à l'aide du fichier fourni par Etalab.
Ne pouvant réaliser cette opération via une requête overpass, je pensais procéder en plusieurs étapes via la requête Power Query de mromain pour récupérer toutes les coordonnées géographiques

• Extraire le contour de la commune (ça c'est fait)
• Extraire le contour de tous les lieux-dits du fichier JSON Etalab
• Supprimer manuellement toutes les coordonnées géographiques des zones qui ne m'intéressent pas
• Combiner tous ces éléments dans une même feuille Excel
• Convertir le fichier Excel en JSON via un outil disponible sur le net (lequel ? je n'y connais rien)
• De là, appeler le fichier JSON créé, via la macro de mromain pour obtenir une carte des zones habitées de la dite commune.

J'en appelle donc aux conseils éclairés des spécialistes du forum pour me dire si je fais fausse route dans ma façon de procéder.
Evidemment, il n'y a aucun caractère d'urgence dans ma demande tant le sujet m'intéresse depuis le début.

Merci à tous, klin89

 

[p56]

Bonjour à tous,

@klin89 : il me semble qu'on peut faire plus simple. PQ doit pouvoir lire facilement des données xlsx.
A ta place je ferais :
* lecture des json et/ou geojson avec PQ (ou autre méthode)
* sélection des zones à dessiner et simple copier/coller dans un xlsx
* et de là lecture du xlsx obtenu (par PQ ou autre) pour dessin de la carte

Voici un exemple :
* un xlsx avec quelques formes choisies au hasard et des colorations arbitraires
* un xlsm avec la carte obtenue à partir du xlsx

P.

 

[klin89]

Re Pierrot,

Je vais explorer ta solution, le chemin va être long, je ne m'imaginais pas que cela représentait autant de formes tous ces lieux-dits.
Sinon, en explorant les objets dans Openstreetmap, j'ai pu ressortir les zones habitées d'une commune rurale via le tag : landuse = residential dans la requête overpass suivante :

[out:json];
area["ref:INSEE"="89197"]->.searchArea;
(
  way["landuse"="residential"](area.searchArea);
  relation["landuse"="residential"](area.searchArea);
);
out geom;
>;
is_in;
rel(pivot)
["boundary"="administrative"]["ref:INSEE"="89197"];
out geom;
{{style:
  node {width: 0;opacity:0;fill-opacity:0;}
}}

Le résultat me parait probant même si tout n'est pas parfait, les hameaux n'étant pas tous tagués (Cisery par exemple), d'autres comme le village de Montréal empiétant sur la commune de Guillon.
Reste à effectuer des mises à jour dans Openstreetmap pour obtenir le résultat souhaité.

J'ai généré le fichier GeoJSON et l'ai appelé via la macro de mromain mais j'obtiens cette erreur.

Si tu passes par là mromain, peux-tu jeter un oeil pour voir ce qui cloche.
Merci d'avance klin89

 

[mromain]

Bonjour à tous,

@klin89

En retouchant la requête QRY_GeoJson ainsi, ça marche sur ton dernier exemple :

let
    fnExtractGeometryInfos = (geometry as record) as table =>
        let
            fnExtractPointsCoordinates = (list as list) as list => List.Transform(list{1}{0}, each [PolygonID = 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({"PolygonID", "Longitude", "Latitude"}, {}),
            changeTypes = Table.TransformColumnTypes(toTable,{{"PolygonID", Int64.Type}, {"Longitude", type number}, {"Latitude", type number}})
        in
            changeTypes,

    fnCleanText = (textToConvert as text) as text => Text.FromBinary(Text.ToBinary  (textToConvert, 1251 ), TextEncoding.Ascii),

    SourceJson = Json.Document(File.Contents(GetWbkParam("Path_GeoJson"))),
    ListFeatures = try SourceJson[features] otherwise SourceJson,
    ToTable = Table.FromList(ListFeatures, Splitter.SplitByNothing(), {"Feature"}, null, ExtraValues.Error),
    AddColFeatureIndex = Table.AddIndexColumn(ToTable, "Index", 1, 1, Int64.Type),
    AddColShapeName = Table.AddColumn(AddColFeatureIndex, "ShapeName", each try [Feature][properties][name] otherwise try [Feature][com_name]{0} otherwise try [Feature][properties][com_name]{0} otherwise try [Feature][nom_canton_min] otherwise "Feature "&Text.From([Index])),
    AddColGeometry = Table.AddColumn(AddColShapeName, "geometry", each try [Feature][geometry] otherwise try [Feature][geo_shape][geometry] otherwise null),
    FilterPolygonMultipolygon = Table.SelectRows(AddColGeometry, each ([geometry][type] = "Polygon" or [geometry][type] = "MultiPolygon")),
    AddColShapeID = Table.AddIndexColumn(FilterPolygonMultipolygon, "ShapeID", 1, 1, Int64.Type),
    AddColSortName = Table.AddColumn(AddColShapeID, "SortName", each Text.FromBinary(Text.ToBinary([ShapeName], 1251), TextEncoding.Ascii)),
    SortByName = Table.Sort(AddColSortName,{{"SortName", Order.Descending}}),
    TransformShapesCoordonates = Table.TransformColumns(SortByName,{{"geometry", fnExtractGeometryInfos, type table}}),
    ExpandShapesCoordonates = Table.ExpandTableColumn(TransformShapesCoordonates, "geometry", {"PolygonID", "Longitude", "Latitude"}, {"PolygonID", "Longitude", "Latitude"}),

    ParamsProjection = [MaxHeight=500, MaxWidth=750, R=6378137, x0=0, y0=0],
    AddColLeft = Table.AddColumn(ExpandShapesCoordonates, "Left", each ParamsProjection[R]*([Longitude]-ParamsProjection[x0])*Number.PI/180),
    AddColTop = Table.AddColumn(AddColLeft, "Top", each -ParamsProjection[R] * Number.Log(Number.Tan((90+[Latitude])*Number.PI/360)) + ParamsProjection[y0]),
    SizingLeftTop = 
        let 
            minLeft=List.Min(AddColTop[Left]), 
            maxLeft=List.Max(AddColTop[Left]), 
            minTop=List.Min(AddColTop[Top]), 
            maxTop=List.Max(AddColTop[Top]), 
            ratio = if ((maxLeft-minLeft)/ParamsProjection[MaxWidth]) > ((maxTop-minTop)/ParamsProjection[MaxHeight]) then ParamsProjection[MaxWidth]/(maxLeft-minLeft) else ParamsProjection[MaxHeight]/(maxTop-minTop)
        in 
            Table.TransformColumns(AddColTop, {{"Left", each (_-minLeft) * ratio, type number}, {"Top", each (_-minTop) * ratio, type number}}),
    
    SelectColumns = Table.SelectColumns(SizingLeftTop,{"ShapeID", "ShapeName", "PolygonID", "Left", "Top"})
in
    SelectColumns

Je te joins le fichier retouché.

@Staple1600

Oserai-je dire que le tour de la question a été réalisé ?

Je ne pense pas non , mais ça avance !

A+

 

[klin89]

Waouh, c'est génial mromain

Par contre, je ne comprends pas qu'il ne me renvoie pas le contour de la commune.

.


Ici le même fichier importé dans l'éditeur JOSM, on voit bien le contour de la commune

Ci-dessous avec une autre commune c'est tout bon, le contour est bien présent, y'a un truc que je pige pas

Fichier GeoJSON créé avec la requête overpass du post #97
klin89

 

[mromain]

Bonjour klin89, le forum,

Étrange... Je n'ai pas le même comportement chez moi.
Je viens de régénérer le fichier geojson avec ta requête du post #97 et voici le résultat que j'ai obtenu (avec le dernier ficher fourni au post #98) :

Je te joins le fichier geojson obtenu.
Peux-tu faire le test de ton côté ?

Bonne journée

A+

 

[klin89]

Re mromain

Bon, ben ça fonctionne je ne sais pas ce que j'ai fait avec ce fichier.

Pour le fun, je me demandais ce que cette requête overpass pouvait renvoyer dans Excel.
Ici les nodes représentent le parc éolien du département de l'Yonne, une éolienne n'accepte qu'une seule coordonnée géographique.
184 petits points à dessiner, je ne sais pas si cela sera bien lisible !
Si j'ai bien compris, il faut à nouveau ajuster la requête Power Query.

[out:json];

area["ref:INSEE"="89"]->.boundaryarea;
rel(area.boundaryarea)["boundary"="administrative"]["admin_level"="6"];
out geom;
area["name"="Yonne"]->.a;
(node(area.a)["power"="generator"]["generator:source"="wind"];);

out geom;

[out:json][timeout:25];
area["ref:INSEE"="89"]->.a;
(
relation["boundary"="administrative"]["admin_level"="6"](area.a);
node["power"="generator"]["generator:source"="wind"](area.a);
);
out geom;

Merci pour ton retour, klin89

 

[mromain]

Bonjour klin89 le forum,

Tu trouveras ci-joint le fichier modifié pour dessiner également les points.
Plusieurs remarques :

• La requête que tu as fourni au post #101 ne donne pas le même résultat chez moi.
  Comme tu peux le voir sur l'image ci-dessous, le département de la Côte-d'Or est également renvoyé. C'est étrange...
  
  
• Dans ta requête, il y a plusieurs points qui sont renvoyés : les points des éoliennes, mais également les points des chef-lieux des départements.
  C'est dur de les différencier (pour la macro c'est la même chose) et ils sont donc tous dessinés.
• La représentation visuelle des points n'est pas parfaite. En effet, contrairement aux cartes de overpass-turbo où la taille des points est identique quelque soit le niveau de zoom de la carte, dans Office, si tu zoome ou étire la carte, les points vont également grossir.
• J'ai rajouté sur la feuille cachée Sh_Int une forme nommée Shp_Point. C'est cette forme qui sert de modèle pour représenter un point. Il est tout à fait possible de la modifier pour changer la représentation d'un point. La seule règle à suivre est qu'elle se nomme bien Shp_Point.

Voici ce que ça donne :

A+

 

[klin89]

Bonsoir à tous,

Super mromain ,
J'ai testé avec cette requête overpass ressortant les hameaux d'une commune.

[out:json];
area["ref:INSEE"="89197"]->.a;
( node["place"="hamlet"](area.a);
  rel["boundary"="administrative"]["admin_level"="9"](area.a);
);
out geom;

Edit : j'obtiens bien le contour de l'Yonne et la localisation des éoliennes, pas de Côte d'Or. Comme je tâtonne beaucoup avec les requêtes overpass, je t'ai rajouté une requête modifiée au post #101

J'analyserai plus en détail le post #102 ce week-end.
En tout cas merci pour ce superbe travail.
klin89

 

[Staple1600]

Bonjour le fil

@mromain
Effectivement, on a fait un tour.
Mais rien n'empêche d'en entamer un second
Ou pour une image plus parlante: Une fois, dans l'arbre JSON, passer de branche en branche
PS: Pour ma part, je suis toujours bloqué à produire un VBA que je puisse appréhender sans risquer de me claquer un neurone (donc sans module de classe)
Dommage que je ne puisse pas toucher à la base de registre du PC du taf
(pour pouvoir utiliser le contrôle WebBrowser, pour simplement afficher dedans les cartes OSM, en switchant seulement les urls selon les besoins)

@klin89
Pour l'éditeur JOSM, il s'agit bien de celui-ci ?

Fr:WikiStart – JOSM

josm.openstreetmap.de

@p56
Merci pour tes contributions sans PQ

 

[klin89]

Bonjour à tous,

@Staple1600
Oui c'est ça, un peu déroutant pour un non-initié.
Sinon pour explorer les objets, je passe par ici :
openstreetmap

klin89