How to import topoJSON file to make a shape map?

[Anonymous]

Hi everyone!

Does anybody know how to import a topoJSON file to represent some points in a shape map? 

When I try to import it, query assistant openns and I dont know how to continue.

This is the structure of the topoJSON file:

{"type":"Topology","objects":{"Espiras":{"type":"GeometryCollection","geometries":[{"type":"Point","coordinates":[538036,487702],"properties":{"Elevation":8.29999999926,"RefName":"(2012)","Text":"(2012)"}}....

Thank you in advanced!

[v-yuta-msft]

@Anonymous,

After imported to power query, click "convert to table", then use power query below to find the point coordinates:

let
    Source = Json.Document(File.Contents("C:\Users\JimmyTao\Desktop\TPO_JSON.json")),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{1}[Value],
    Espiras = Value[Espiras],
    geometries = Espiras[geometries],
    geometries1 = geometries{0},
    coordinates = geometries1[coordinates]
in
    coordinates

Finally you can achieve:

You may also refer to the appendix.

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft

Thank you so much! It works well:) My last question is: In the file I have 250 records, and each record has a pair of coordinates. Is there any way to obtain the 250 points without doing manually? I mean, is it possible to do a loop?

Thank you in advanced!:)

[v-yuta-msft · Accepted solution]

@Anonymous,

Yes, but don't need to write a loop maually in M code.(power bi will do automatically), suppose a new point(point2) has been added to your json file like below:

{
   "type":"Topology",
   "objects":{
      "Espiras":{
         "type":"GeometryCollection",
         "geometries":[
            {
               "type":"Point1",
               "coordinates":[
                  538036,
                  487702
               ],
               "properties":{
                  "Elevation":8.29999999926,
                  "RefName":"(2012)",
                  "Text":"(2012)"
               }
            },
            {
               "type":"Point2",
               "coordinates":[
                  500000,
                  400000
               ],
               "properties":{
                  "Elevation":8,
                  "RefName":"(2013)",
                  "Text":"(2013)"
               }
            }
         ]
      }
   }
}

Click query editors and use M code below:

let
    Source = Json.Document(File.Contents("C:\Users\JimmyTao\Desktop\TPO_JSON.json")),
    objects = Source[objects],
    Espiras = objects[Espiras],
    geometries = Espiras[geometries],
    #"Converted to Table" = Table.FromList(geometries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "coordinates", "properties"}, {"Column1.type", "Column1.coordinates", "Column1.properties"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded Column1", {"Column1.coordinates", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Column1.properties"})
in
    #"Removed Columns"

Regards,

Jimmy Tao

[PaulDBrown]

@v-yuta-msft 

I am hoping you might help me with a similar request. I'm trying to use the following (this is only an extract) topoJson script for a shape map:

{"type":"Topology","objects":{"barris":{"type":"GeometryCollection","geometries":[{"type":"Polygon","arcs":[[0,1,2,3,4,5,6]],"properties":{"ID_ANNEX":"01","ANNEXDESCR":"Grup - I","ID_TEMA":"0104","TEMA_DESCR":"Unitats Administratives","ID_CONJUNT":"010413","CONJ_DESCR":"Barris","ID_SUBCONJ":"01041301","SCONJ_DESC":"Barri","ID_ELEMENT":"0104130101","ELEM_DESCR":"Límit de barri","NIVELL":"ADM_03_PL","NDESCR_CA":"Límit de barri (polígon)","NDESCR_ES":"Límite de barrrio (polígono)","NDESCR_EN":"Neighborhood boundary (polygon)","TERME":"080193","DISTRICTE":"01","BARRI":"01","AEB":"-","SEC_CENS":"-","GRANBARRI":"01","ZUA":"-","AREA_I":"-","LITERAL":"01","PERIMETRE":5521.647,"AREA":1100286.137,"ORD_REPRES":3,"CODI_UA":"01","TIPUS_UA":"BARRI","NOM":"el Raval","WEB1":"http://lameva.barcelona.cat/ciutatvella/ca/home/el-barri-del-raval","WEB2":"http://www.bcn.cat/estadistica/catala/dades/inf/barris/a2015/barri1.pdf","WEB3":"http://www.bcn.cat/estadistica/catala/documents/barris/01_CV_Raval_2017.pdf","DOCUMENTA":null,"RANGESCALA":"1-50000","TIPUS_POL":null,"GRUIX_ID":"6","GRUIXDIMEN":70,"ESTIL_ID":"0","ESTIL_QGIS":"Sòlid","VALOR1QGIS":"0","VALOR2QGIS":"0","COL_FARCIT":"41","FCOL_DESCR":"NULL","FHEX_COLOR":"#E66900","COL_DESCR":"NULL","HEX_COLOR7":"#73A88F"}}

I have succeeded to get this far:

let
    Source = Json.Document(File.Contents("C:\Users\Paul\Desktop\bcn-geodata-master\bcn-geodata-master\barris\barris.json")),
    objects = Source[objects],
    Barris = objects[barris],
    geometries = Barris[geometries],
    
    #"Converted to Table" = Table.FromList(geometries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "coordinates", "properties"}, {"Column1.type", "Column1.coordinates", "Column1.properties"}),
    #"Expanded Column1.properties" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.properties", {"ID_ANNEX", "ANNEXDESCR", "ID_TEMA", "TEMA_DESCR", "ID_CONJUNT", "CONJ_DESCR", "ID_SUBCONJ", "SCONJ_DESC", "ID_ELEMENT", "ELEM_DESCR", "NIVELL", "NDESCR_CA", "NDESCR_ES", "NDESCR_EN", "TERME", "DISTRICTE", "BARRI", "AEB", "SEC_CENS", "GRANBARRI", "ZUA", "AREA_I", "LITERAL", "PERIMETRE", "AREA", "ORD_REPRES", "CODI_UA", "TIPUS_UA", "NOM", "WEB1", "WEB2", "WEB3", "DOCUMENTA", "RANGESCALA", "TIPUS_POL", "GRUIX_ID", "GRUIXDIMEN", "ESTIL_ID", "ESTIL_QGIS", "VALOR1QGIS", "VALOR2QGIS", "COL_FARCIT", "FCOL_DESCR", "FHEX_COLOR", "COL_DESCR", "HEX_COLOR7"}, {"ID_ANNEX", "ANNEXDESCR", "ID_TEMA", "TEMA_DESCR", "ID_CONJUNT", "CONJ_DESCR", "ID_SUBCONJ", "SCONJ_DESC", "ID_ELEMENT", "ELEM_DESCR", "NIVELL", "NDESCR_CA", "NDESCR_ES", "NDESCR_EN", "TERME", "DISTRICTE", "BARRI", "AEB", "SEC_CENS", "GRANBARRI", "ZUA", "AREA_I", "LITERAL", "PERIMETRE", "AREA", "ORD_REPRES", "CODI_UA", "TIPUS_UA", "NOM", "WEB1", "WEB2", "WEB3", "DOCUMENTA", "RANGESCALA", "TIPUS_POL", "GRUIX_ID", "GRUIXDIMEN", "ESTIL_ID", "ESTIL_QGIS", "VALOR1QGIS", "VALOR2QGIS", "COL_FARCIT", "FCOL_DESCR", "FHEX_COLOR", "COL_DESCR", "HEX_COLOR7"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.properties",{"Column1.coordinates", "ANNEXDESCR", "ID_TEMA", "TEMA_DESCR", "ID_ANNEX", "ID_CONJUNT", "CONJ_DESCR", "ID_SUBCONJ", "SCONJ_DESC", "ID_ELEMENT", "ELEM_DESCR", "NIVELL", "NDESCR_CA", "NDESCR_ES", "NDESCR_EN", "TERME", "AEB", "SEC_CENS", "ZUA", "AREA_I", "LITERAL", "ORD_REPRES", "CODI_UA", "TIPUS_UA", "WEB1", "WEB2", "WEB3", "DOCUMENTA", "RANGESCALA", "TIPUS_POL", "GRUIX_ID", "GRUIXDIMEN", "ESTIL_ID", "ESTIL_QGIS", "VALOR1QGIS", "VALOR2QGIS", "COL_FARCIT", "FCOL_DESCR", "FHEX_COLOR", "COL_DESCR", "HEX_COLOR7", "Column1.type"})
in
    #"Removed Columns"

where I've removed a number of columns which seemed to me superfluous. 

And this is where I'm stuck, since I know nothing about jason or topojson scripts and what is needed to create a shape map. 

I see in your example above that you aim for the "coordinates". This script, however, seems to offer "Perimeter" and "Area". 

Can you help please?

Thanks!