Forum Discussion
How to import topoJSON file to make a shape map?
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 t...
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
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
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!:)
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
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!
