Multilinestring geojson to IconMap

[Anonymous]

I'm accessing the following data 

https://environment.data.gov.uk/asset-management/mapping-api/Asset?assets=http://environment.data.go...

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? 

[v-yalanwu-msft · Accepted solution]

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.

[v-yalanwu-msft]

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.

[Anonymous]

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