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:

Staple1600

XLDnaute Barbatruc
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

XLDnaute Accro
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
cadastre-89197-lieux_dits.jpg

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
 

Pièces jointes

  • cadastre-89197-lieux_dits.zip
    136 KB · Affichages: 1
Dernière édition:

klin89

XLDnaute Accro
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

XLDnaute Occasionnel
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
Capture d’écran 2023-05-07 103912.jpg


P.
 

Pièces jointes

  • Carte_exemple_Klin89.xlsm
    48.1 KB · Affichages: 3
  • Commune_Klin89.xlsx
    84.8 KB · Affichages: 3

klin89

XLDnaute Accro
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 :
CSS:
[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é.

Guillon.jpg

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


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

mromain

XLDnaute Barbatruc
Bonjour à tous,

@klin89

En retouchant la requête QRY_GeoJson ainsi, ça marche sur ton dernier exemple :
PowerQuery:
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+
 

Pièces jointes

  • Mercator_DrawGeojson2.xlsm
    63.7 KB · Affichages: 4

klin89

XLDnaute Accro
Waouh, c'est génial mromain 🙃

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


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


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

INSEE_89381_Sceaux.jpg

Fichier GeoJSON créé avec la requête overpass du post #97
klin89
 
Dernière édition:

mromain

XLDnaute Barbatruc
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) :
Resultat.png


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

Bonne journée

A+
 

Pièces jointes

  • Requete_Post.97.geojson.zip
    20.7 KB · Affichages: 4

klin89

XLDnaute Accro
Re mromain :)

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

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.
CSS:
[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;
Yonne_Eoliennes.jpg

CSS:
[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
 
Dernière édition:

mromain

XLDnaute Barbatruc
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...
    OverpassTurbo.png
  • 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 :
Resultat.png


A+
 

Pièces jointes

  • Mercator_DrawGeojson3.xlsm
    79.4 KB · Affichages: 6

klin89

XLDnaute Accro
Bonsoir à tous,

Super mromain :),
J'ai testé avec cette requête overpass ressortant les hameaux d'une commune.
VB:
[out:json];
area["ref:INSEE"="89197"]->.a;
( node["place"="hamlet"](area.a);
  rel["boundary"="administrative"]["admin_level"="9"](area.a);
);
out geom;
Les_Hameaux.jpg


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
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
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 ?

@p56
Merci pour tes contributions sans PQ ;)
 

Statistiques des forums

Discussions
311 732
Messages
2 081 995
Membres
101 857
dernier inscrit
mt60400