Forum Discussion
Multilinestring geojson to IconMap
I'm accessing the following data
And doing the following to it to form a WKT string that can be visualised in IconMap
This results in
MULTILINESTRING (-2.76283 54.66905,-2.76329 54.66897,-2.76365 54.6689,-2.76406,54.66883,-2.76406 54.66883,-2.76433 54.66878,-2.76458 54.66873,-2.76512 54.66864)
But I actually need
MULTILINESTRING ((-2.76283 54.66905,-2.76329 54.66897,-2.76365 54.6689,-2.76406,54.66883),(-2.76406 54.66883,-2.76433 54.66878,-2.76458 54.66873,-2.76512 54.66864)) to distinguish between the two parts of this multilinestring.
The first 'Expanded Column1.geometry.coordinates' creates two rows which contain the two line parts. The next 'Expanded Column1.geometry.coordinates1' creates 8 rows for the 8 sets of coordinates and at this point any distinction between line 1 and 2 is lost.
How can I expand the list but in some way wrap line 1 and 2 in brackets so they can be distinguished in the resulting string?
Hi， Anonymous ；
Try it.
let Source = Json.Document(Web.Contents("https://environment.data.gov.uk/asset-management/mapping-api/Asset?assets=http://environment.data.gov.uk/asset-management/id/asset/183048")), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "geometry", "properties", "id"}, {"Column1.type", "Column1.geometry", "Column1.properties", "Column1.id"}), #"Expanded Column1.geometry" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.geometry", {"type", "coordinates"}, {"Column1.geometry.type", "Column1.geometry.coordinates"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.geometry",{"Column1.properties"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Column1.geometry.type] = "MultiLineString" or [Column1.geometry.type] = "Polygon"), #"Expanded Column1.geometry.coordinates" = Table.ExpandListColumn(#"Filtered Rows", "Column1.geometry.coordinates"), #"Added Index" = Table.AddIndexColumn(#"Expanded Column1.geometry.coordinates", "Index", 0, 1, Int64.Type), #"Expanded Column1.geometry.coordinates1" = Table.ExpandListColumn(#"Added Index", "Column1.geometry.coordinates"), #"Extracted Values" = Table.TransformColumns(#"Expanded Column1.geometry.coordinates1", {"Column1.geometry.coordinates", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Replaced Value" = Table.ReplaceValue(#"Extracted Values",","," ",Replacer.ReplaceText,{"Column1.geometry.coordinates"}), #"Uppercased Text" = Table.TransformColumns(#"Replaced Value",{{"Column1.geometry.type", Text.Upper, type text}}), #"Grouped Rows" = Table.Group(#"Uppercased Text", {"Column1.id", "Index"}, {{"MyTable", each _, type table [Column1.type=text, Column1.geometry.type=text, Column1.geometry.coordinates=nullable text, Column1.id=text, Index=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ExtractGeom", each Table.Column([MyTable],"Column1.geometry.coordinates")), #"Extracted Values1" = Table.TransformColumns(#"Added Custom", {"ExtractGeom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}), #"Added Custom1" = Table.AddColumn(#"Extracted Values1", "ExtractType", each Table.Column([MyTable],"Column1.geometry.type")), #"Expanded ExtractType" = Table.ExpandListColumn(#"Added Custom1", "ExtractType"), #"Removed Columns1" = Table.RemoveColumns(#"Expanded ExtractType",{"MyTable"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns1", {"ExtractGeom"}), #"Added Custom2" = Table.AddColumn(#"Removed Duplicates", "WKT", each " (" & [ExtractGeom] & ")"), #"Grouped Rows1" = Table.Group(#"Added Custom2", {"Column1.id", "ExtractType"}, {{"group", each _, type table [Column1.id=text, Index=number, ExtractGeom=text, ExtractType=text, WKT=text]}}), #"Added Custom3" = Table.AddColumn(#"Grouped Rows1", "ExtractGeom", each Table.Column([group],"WKT")), #"Extracted Values2" = Table.TransformColumns(#"Added Custom3", {"ExtractGeom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Added Custom4" = Table.AddColumn(#"Extracted Values2", "WKT", each [ExtractType]&"("&[ExtractGeom]&")"), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom4",{"group"}) in #"Removed Columns2"The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi， Anonymous ；
Try it.
let Source = Json.Document(Web.Contents("https://environment.data.gov.uk/asset-management/mapping-api/Asset?assets=http://environment.data.gov.uk/asset-management/id/asset/183048")), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "geometry", "properties", "id"}, {"Column1.type", "Column1.geometry", "Column1.properties", "Column1.id"}), #"Expanded Column1.geometry" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.geometry", {"type", "coordinates"}, {"Column1.geometry.type", "Column1.geometry.coordinates"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.geometry",{"Column1.properties"}), #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Column1.geometry.type] = "MultiLineString" or [Column1.geometry.type] = "Polygon"), #"Expanded Column1.geometry.coordinates" = Table.ExpandListColumn(#"Filtered Rows", "Column1.geometry.coordinates"), #"Added Index" = Table.AddIndexColumn(#"Expanded Column1.geometry.coordinates", "Index", 0, 1, Int64.Type), #"Expanded Column1.geometry.coordinates1" = Table.ExpandListColumn(#"Added Index", "Column1.geometry.coordinates"), #"Extracted Values" = Table.TransformColumns(#"Expanded Column1.geometry.coordinates1", {"Column1.geometry.coordinates", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Replaced Value" = Table.ReplaceValue(#"Extracted Values",","," ",Replacer.ReplaceText,{"Column1.geometry.coordinates"}), #"Uppercased Text" = Table.TransformColumns(#"Replaced Value",{{"Column1.geometry.type", Text.Upper, type text}}), #"Grouped Rows" = Table.Group(#"Uppercased Text", {"Column1.id", "Index"}, {{"MyTable", each _, type table [Column1.type=text, Column1.geometry.type=text, Column1.geometry.coordinates=nullable text, Column1.id=text, Index=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ExtractGeom", each Table.Column([MyTable],"Column1.geometry.coordinates")), #"Extracted Values1" = Table.TransformColumns(#"Added Custom", {"ExtractGeom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}), #"Added Custom1" = Table.AddColumn(#"Extracted Values1", "ExtractType", each Table.Column([MyTable],"Column1.geometry.type")), #"Expanded ExtractType" = Table.ExpandListColumn(#"Added Custom1", "ExtractType"), #"Removed Columns1" = Table.RemoveColumns(#"Expanded ExtractType",{"MyTable"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns1", {"ExtractGeom"}), #"Added Custom2" = Table.AddColumn(#"Removed Duplicates", "WKT", each " (" & [ExtractGeom] & ")"), #"Grouped Rows1" = Table.Group(#"Added Custom2", {"Column1.id", "ExtractType"}, {{"group", each _, type table [Column1.id=text, Index=number, ExtractGeom=text, ExtractType=text, WKT=text]}}), #"Added Custom3" = Table.AddColumn(#"Grouped Rows1", "ExtractGeom", each Table.Column([group],"WKT")), #"Extracted Values2" = Table.TransformColumns(#"Added Custom3", {"ExtractGeom", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Added Custom4" = Table.AddColumn(#"Extracted Values2", "WKT", each [ExtractType]&"("&[ExtractGeom]&")"), #"Removed Columns2" = Table.RemoveColumns(#"Added Custom4",{"group"}) in #"Removed Columns2"The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi, Anonymous ;
I have tested it, but I am still a little unclear about your requirements. Can you share all your original M language code? such as：
let Source = Json.Document(Web.Contents("https://environment.data.gov.uk/asset-management/mapping-api/Asset?assets=http://environment.data.gov.uk/asset-management/id/asset/183048")), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "type", "label", "geometry", "asset-type", "asset-sub-type"}, {"id", "type", "label", "geometry", "asset-type", "asset-sub-type"}), #"Expanded geometry" = Table.ExpandRecordColumn(#"Expanded Column1", "geometry", {"type", "coordinates"}, {"geometry.type", "geometry.coordinates"}), #"Expanded geometry.coordinates" = Table.ExpandListColumn(#"Expanded geometry", "geometry.coordinates"), #"Expanded asset-type" = Table.ExpandRecordColumn(#"Expanded geometry.coordinates", "asset-type", {"id", "label"}, {"asset-type.id", "asset-type.label"}), #"Expanded asset-sub-type" = Table.ExpandRecordColumn(#"Expanded asset-type", "asset-sub-type", {"id", "label"}, {"asset-sub-type.id", "asset-sub-type.label"}), #"Expanded geometry.coordinates1" = Table.ExpandListColumn(#"Expanded asset-sub-type", "geometry.coordinates"), #"Expanded geometry.coordinates2" = Table.ExpandListColumn(#"Expanded geometry.coordinates1", "geometry.coordinates") in #"Expanded geometry.coordinates2"Also, the results you want to output?
Looking forword to your reply!
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks for having a go at this. This is my code
Source = Json.Document(Web.Contents(https://environment.data.gov.uk/asset-management/id/asset.geojson?_limit=100&area=4-11)),
features = Source[features],
#"Converted to Table" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"type", "geometry", "properties", "id"}, {"Column1.type", "Column1.geometry", "Column1.properties", "Column1.id"}),
#"Expanded Column1.geometry" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.geometry", {"type", "coordinates"}, {"Column1.geometry.type", "Column1.geometry.coordinates"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.geometry",{"Column1.properties"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [Column1.geometry.type] = "MultiLineString" or [Column1.geometry.type] = "Polygon"),
#"Expanded Column1.geometry.coordinates" = Table.ExpandListColumn(#"Filtered Rows", "Column1.geometry.coordinates"),
#"Expanded Column1.geometry.coordinates1" = Table.ExpandListColumn(#"Expanded Column1.geometry.coordinates", "Column1.geometry.coordinates"),
#"Extracted Values" = Table.TransformColumns(#"Expanded Column1.geometry.coordinates1", {"Column1.geometry.coordinates", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Replaced Value" = Table.ReplaceValue(#"Extracted Values",","," ",Replacer.ReplaceText,{"Column1.geometry.coordinates"}),
#"Uppercased Text" = Table.TransformColumns(#"Replaced Value",{{"Column1.geometry.type", Text.Upper, type text}}),
#"Grouped Rows" = Table.Group(#"Uppercased Text", {"Column1.id"}, {{"MyTable", each _, type table [Column1.type=text, Column1.geometry.type=text, Column1.geometry.coordinates=text, Column1.id=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "ExtractGeom", each Table.Column([MyTable],"Column1.geometry.coordinates")),
#"Extracted Values1" = Table.TransformColumns(#"Added Custom", {"ExtractGeom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
#"Added Custom1" = Table.AddColumn(#"Extracted Values1", "ExtractType", each Table.Column([MyTable],"Column1.geometry.type")),
#"Expanded ExtractType" = Table.ExpandListColumn(#"Added Custom1", "ExtractType"),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded ExtractType",{"MyTable"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns1", {"Column1.id"}),
#"Added Custom2" = Table.AddColumn(#"Removed Duplicates", "WKT", each [ExtractType] & " (" & [ExtractGeom] & ")")
in
#"Added Custom2"
This results in one long linestring with no seperation between the two parts so it draws incorrectly
MULTILINESTRING (-2.76283 54.66905,-2.76329 54.66897,-2.76365 54.6689,-2.76406,54.66883,-2.76406 54.66883,-2.76433 54.66878,-2.76458 54.66873,-2.76512 54.66864)
What I actually need is the following. This has additional brackets which sperate the two linestrings that form the MultiLineString
MULTILINESTRING ((-2.76283 54.66905,-2.76329 54.66897,-2.76365 54.6689,-2.76406,54.66883),(-2.76406 54.66883,-2.76433 54.66878,-2.76458 54.66873,-2.76512 54.66864))