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

 

[Staple1600]

Bonsoir le fil, mromain, klin89

Désolé du retard de feedback.

Bonsoir klin89,
Merci de de ton intérêt pour la question.

Je vous tiens au courant quand j'aurai réussi à partir du résultat obtenu par PQ à dessiner une carte en adaptant les codes VBA proposés dans mon autre fil

NB: Dans l'idéal, le but que je poursuis est le suivant
A partir d'un fichier lambda*. json issu d'Openstreetmap (ou d'un site OpenData), le traiter avec PQ et dessiner un carte à partir des données de fichier *.json (par VBA avec des shapes)
En gros, quand j'actualise la requête PQ puis que je lance le code VBA idoine, j'obtiens une nouvelle carte en rapport avec le fichier *.json traité par PQ.

 

[mromain]

Bonjour à tous, bonjour Staple,

Ton idée de dessiner les communes m'a titillé et je me suis donc lancé dans l'aventure
Tu trouveras ci-joint mon essai.
Il suffit d'adapter l'emplacement du fichier json et de cliquer sur GO.

A+ et bonne journée

 

[mromain]

Bonjour à tous, bonjour Staple,

Pour le fun, une nouvelle version avec ses avantages et ses inconvénients. Celle-ci utilise PowerPoint pour générer les formes.
Le choix de passer par PowerPoint est motivé par le fait que cette application offre la possibilité d'unir des formes.
On peut ainsi avoir une seule forme (au sens Office du terme) composé de plusieurs polygones.
Par exemple, pour Saint-Malo :

On se retrouve donc au final avec une seule forme par commune.

Coté inconvénients :

• on est obligé de cocher la référence Microsoft PowerPoint XX.0 Object Library
• la génération des formes est sensiblement plus lente...

A+

 

[Staple1600]

Bonjour le fil, mromain

@mromain
Chapeau bas !!!
Je ne m'appelle pas cela un essai mais de l'ouvrage ciselé

Courge après une chimère?
(ce que détaille dans le NB du message#16)

On ne peut pas généraliser la requête PQ et le code VBA pour traiter n'importe quel fichier *.json issu d'Openmapstreet ?

A ce propos quelle différence entre un fichier *.geojson et *.json?

 

[mromain]

Bonjour Staple,

Merci pour ton retour

A ce propos quelle différence entre un fichier *.geojson et *.json?

Je ne m'y connais pas trop, mais après quelques lectures, le geojson semble être un format de fichier basé sur la norme json.

On ne peut pas généraliser la requête PQ et le code VBA pour traiter n'importe quel fichier *.json issu d'Openmapstreet ?

Toujours à mon niveau de connaissance, n'importe quel fichier *.json : je ne pense pas. A moins qu'ils aient tous la même structure.

D'ailleurs, comment fais-tu pour extraire des json depuis d'Openmapstreet ?
J'ai essayé, mais le seul export que j'ai pu faire était au format osm.

De plus, sais-tu s'il est possible d'extraire d'OSM uniquement les données relatives au coordonnées des communes (sans les rues, les points d'intérêts, ...) ?

A+

 

[Staple1600]

Bonjour mromain

Alors pour obtenir un fichier à partir de OMS
Voici un exemple (celui sur lequel je m'exerce actuellement)
Une fois sur la carte, cliquez sur Exporter et partager la carte (icone à gauche de l'écran)
Ensuite avec la liste déroulante qui apparait à droite, on a le choix du type de fichier à télécharger

 

[Staple1600]

Re

Quand j'essaie ton code M (en essayant d'adapter) avec un *.geojson, Excel toussse
(Enfin c'est le cas ici en pays Breton )

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

    Source = Json.Document(File.Contents("C:\Users\STAPLE\agences35.geojson")),
    #"Converti en table1" = Record.ToTable(Source),
    #"En-têtes promus" = Table.PromoteHeaders(#"Converti en table1", [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"type", type text}, {"FeatureCollection", type any}}),
    #"FeatureCollection développé" = Table.ExpandListColumn(#"Type modifié", "FeatureCollection"),
    #"FeatureCollection développé1" = Table.ExpandRecordColumn(#"FeatureCollection développé", "FeatureCollection", {"type", "properties", "geometry"}, {"FeatureCollection.type", "FeatureCollection.properties", "FeatureCollection.geometry"}),
    #"FeatureCollection.geometry développé" = Table.ExpandRecordColumn(#"FeatureCollection développé1", "FeatureCollection.geometry", {"type", "coordinates"}, {"FeatureCollection.geometry.type", "FeatureCollection.geometry.coordinates"}),
    #"FeatureCollection.geometry.coordinates développé" = Table.ExpandListColumn(#"FeatureCollection.geometry développé", "FeatureCollection.geometry.coordinates")
in
    #"FeatureCollection.geometry.coordinates développé"

 

[Staple1600]

Bonjour le fil,

A tâtons, à tâtons , je progresse
Mais ce n'est pas encore cela

J'essaie toujours d'importer un fichier *.geojson avec PQ
J'arrive presque au but mais avec ce code M, j"ai les Lat et les Long mais rien pour les distinguer.
Un petit coup de main serait le bienvenu

let
    Source = Json.Document(File.Contents("C:\Users\STAPLE\test.geojson")),
    features = Source[features],
    #"Converti en table" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table", "Column1", {"type", "geometry"}, {"type", "geometry"}),
    #"geometry développé" = Table.ExpandRecordColumn(#"Column1 développé", "geometry", {"type", "coordinates"}, {"type.1", "coordinates"}),
    #"coordinates développé" = Table.ExpandListColumn(#"geometry développé", "coordinates"),
    #"Lignes filtrées" = Table.SelectRows(#"coordinates développé", each ([type.1] = "Polygon")),
    #"coordinates développé1" = Table.ExpandListColumn(#"Lignes filtrées", "coordinates"),
    #"Valeurs extraites" = Table.TransformColumns(#"coordinates développé1", {"coordinates", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Valeurs extraites", "coordinates", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"coordinates.1", "coordinates.2"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur",{{"coordinates.1", type number}, {"coordinates.2", type number}})
in
    #"Type modifié"

 

[mromain]

Bonjour Staple,

Alors pour obtenir un fichier à partir de OMS
Voici un exemple (celui sur lequel je m'exerce actuellement)
Une fois sur la carte, cliquez sur Exporter et partager la carte (icone à gauche de l'écran)
Ensuite avec la liste déroulante qui apparait à droite, on a le choix du type de fichier à télécharger

Merci, j'ai fait le test.

Par contre, à par des coordonnées, il n'y a pas grand-chose... Notamment le nom ou le code postal des communes.

Est-ce ce que tu entend par il n'y a rien pour les distinguer ?
Si oui, je confirme... Il n'y a rien...

A+

 

[Staple1600]

Re


@mromain
Donc avec ceci, j'obtiens cela dans Excel

Spoiler: Code M replié, pour gain de place

let
    Source = Json.Document(File.Contents("C:\Users\STAPLE\82987.umap")),
    #"Converti en table" = Record.ToTable(Source),
    Value = #"Converti en table"{4}[Value],
    #"Converti en table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table1", "Column1", {"type", "features", "_umap_options"}, {"type", "features", "_umap_options"}),
    #"features développé" = Table.ExpandListColumn(#"Column1 développé", "features"),
    #"features développé1" = Table.ExpandRecordColumn(#"features développé", "features", {"type", "properties", "geometry"}, {"type.1", "properties", "geometry"}),
    #"properties développé" = Table.ExpandRecordColumn(#"features développé1", "properties", {"OBJECTID", "NOM_AGENCE", "CD_DEPT"}, {"OBJECTID", "NOM_AGENCE", "CD_DEPT"}),
    #"geometry développé" = Table.ExpandRecordColumn(#"properties développé", "geometry", {"type", "coordinates"}, {"type.2", "coordinates"}),
    #"_umap_options développé" = Table.ExpandRecordColumn(#"geometry développé", "_umap_options", {"name"}, {"name"}),
    #"coordinates développé" = Table.ExpandListColumn(#"_umap_options développé", "coordinates"),
    #"Lignes filtrées" = Table.SelectRows(#"coordinates développé", each ([type.2] = "Polygon")),
    #"coordinates développé1" = Table.ExpandListColumn(#"Lignes filtrées", "coordinates"),
    #"Valeurs extraites" = Table.TransformColumns(#"coordinates développé1", {"coordinates", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    #"Fractionner la colonne par délimiteur" = Table.SplitColumn(#"Valeurs extraites", "coordinates", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"coordinates.1", "coordinates.2", "coordinates.3"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Fractionner la colonne par délimiteur",{{"coordinates.1", type number}, {"coordinates.2", type number}, {"coordinates.3", Int64.Type}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié",{"coordinates.3"})
in
    #"Colonnes supprimées"

Si je reprends ton exemple du post#17, est-ce que OBJECTID peut correspondre à ShapeID coordinates.1 à Longitude, coordinates.2 à Latitude et NOM_AGENCE à com_name ?

NB: Ici j'ai utilisé le fichier *.umap car c'est ce format qui contient toutes les données pour dessiner la carte.

 

[mromain]

Bonjour Staple,

Si je reprends ton exemple du post#17, est-ce que OBJECTID peut correspondre à ShapeID coordinates.1 à Longitude, coordinates.2 à Latitude et NOM_AGENCE à com_name ?

Je ne pense pas non...

Dans le post #17, le ShapeID servait à identifier les différentes formes d'une même commune. Comme pour Saint-Malo par exemple avec ses petites iles.
Pour toutes les communes n'ayant pas d'ile, donc ayant un seul contour, ShapeID valait toujours 1.

Dans le fichier umap, il n'y a que 5 OBJECTID différents (6 en comptant les null alors que la carte semble contenir bien plus de formes (notamment toutes les petites iles au nord)...

Je pense donc que ce sera plus difficile d'adapter la proposition du post #17.

J'aurais aimé exporter uniquement les données de Saint-Malo au format umap depuis OSM (vu qu'on sait que cette ville a plusieurs contours) afin de voir comment exploiter ce format de fichier.
Malheureusement, je n'ai pas réussi à faire cette manip dans OSM.
Si jamais tu y arrives, je serais bien intéressé de récupérer l'export pour creuser le sujet.

A+

 

[klin89]

Bonsoir à tous,

Via Chat GPT, j'ai formulé cette demande pour générer une requête overpass turbo
requête overpass turbo boundary=administrative de la commune de rennes

Dans overpass turbo, j'ai exécuté la requête et ainsi obtenu les contours de la ville de Rennes.
voir la photo
Après, dans la requête, on doit pouvoir lister plusieurs communes, faudrait poser la question sur la FAQ OSM ou reformuler la demande dans chat GPT.

/*
*/
[out:json][timeout:25];
// Recherche de la relation correspondante à la ville de Rennes
area[name="Rennes"]->.a;
rel(area.a)["admin_level"="8"]["boundary"="administrative"]["name"="Rennes"];
// Extraction des limites de la relation
out geom;

Y'a plus qu'à exporter le fichier sous le format GeoJSON pour récupérer les différents points de contours.
Enfin, je pense

klin89

 

[Staple1600]

Bonsoir le fil, @mromain

@mromain
Pour Saint-Malo, est-ce que cette carte peut t'aider ?

Saint-Malo - uMap

@klin89
Kesaco overpass turbo ?
Et c'est quel langage le code du message#27 ?

 

[klin89]

Re Staple1600
Via une nouvelle requête générée par chat GPT, j'ai dessiné le contour de 2 villages situés près de chez moi.

/*
*/
[out:json][timeout:25];
// Recherche de l'élément correspondant au département de l'Yonne
area[name="Yonne"]->.a;
// Recherche des relations correspondantes aux communes de Thizy et Blacy dans le département de l'Yonne
rel(area.a)["admin_level"="8"]["boundary"="administrative"]["name"~"Thizy|Blacy"];
// Extraction des limites des relations trouvées
out geom;

Voici le lien pour exécuter une requête
overpass turbo
Après, tu fais "exporter" au format GeoJSON pour obtenir le fichier.

Pour te documenter sur Opentreetmap, ce lien :
Tags openstreetmap

klin89

 

[Staple1600]

Re


Avec mes petits neurones sans que le chat qui flatule
(ou je sais elle est facile)

[out:json][timeout:25];
//rel(7465;
rel["name"="Ille-et-Vilaine"];
out meta;
> ;
out skel qt;

PS: Avec Chat-Gpt, Skynet n'est pas loin...
C'est vrai qu' Arnold nous avait bien dit qu'il reviendrait

Spoiler

there is no fate but what we make for ourselves