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

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

Pièces jointes

  • Test.xlsm
    47 KB · Affichages: 7

mromain

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

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+
 

Pièces jointes

  • TestPpt.xlsm
    37 KB · Affichages: 3

Staple1600

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

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

XLDnaute Barbatruc
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
82987.png
 

Staple1600

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

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

Staple1600

XLDnaute Barbatruc
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 ;)
PowerQuery:
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é"
 
Dernière édition:

mromain

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

XLDnaute Barbatruc
Re


@mromain
Donc avec ceci, j'obtiens cela dans Excel
Code:
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"
2003Capture.PNG
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.
 
Dernière édition:

mromain

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

XLDnaute Accro
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.
Code:
/*
*/
[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;
Rennes.jpg

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

klin89

XLDnaute Accro
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.
Code:
/*
*/
[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;
Blacy_Thizy.jpg

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

XLDnaute Barbatruc
Re


Avec mes petits neurones sans que le chat qui flatule
(ou je sais elle est facile)
Code:
[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
latest

there is no fate but what we make for ourselves
 

Statistiques des forums

Discussions
311 725
Messages
2 081 940
Membres
101 845
dernier inscrit
annesof