Forum Discussion
Geojson multilinestring a IconMap
Estoy accediendo a los siguientes datos
Y haciendo lo siguiente para formar una cadena WKT que se puede visualizar en IconMap
Esto da como resultado
MULTILINERA (-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)
Pero en realidad necesito
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)) para distinguir entre las dos partes de esta multilinea.
La primera 'Columna expandida1.geometry.coordinates' crea dos filas que contienen las dos partes de línea. La siguiente 'Columna expandida1.geometry.coordinates1' crea 8 filas para los 8 conjuntos de coordenadas y en este punto se pierde cualquier distinción entre la línea 1 y 2.
¿Cómo puedo expandir la lista pero de alguna manera envolver las líneas 1 y 2 entre paréntesis para que puedan distinguirse en la cadena resultante?
Hi, @hartp ;
Pruébalo.
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"El resultado final se muestra a continuación:
Saludos
Apoyo a la comunidad Team_ Yalan Wu
Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.
3 Replies
Hi, @hartp ;
Lo he probado, pero todavía no tengo claros sus requisitos. ¿Puedes compartir todo tu código de idioma M original? como:
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"Además, ¿los resultados que desea generar?
¡Buscando una palabra para su respuesta!
Saludos
Apoyo a la comunidad Team_ Yalan Wu
Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.Gracias por probar esto. Este es mi código
Fuente = Json.Document(Web.Contents(https://environment.data.gov.uk/asset-management/id/asset.geojson?_limit=100&area=4-11)),
características = Fuente[características],
#"Convertido a tabla" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Columna expandida1" = Table.ExpandRecordColumn(#"Convertido a tabla", "Columna1", {"tipo", "geometría", "propiedades", "id"}, {"Column1.type", "Column1.geometry", "Column1.properties", "Column1.id"}),
#"Columna expandida1.geometría" = Table.ExpandRecordColumn(#"Columna expandida1", "Columna1.geometría", {"tipo", "coordenadas"}, {"Columna1.geometry.tipo", "Columna1.geometría.coordenadas"}),
#"Columnas eliminadas" = Table.RemoveColumns(#"Columna expandida1.geometry",{"Columna1.propiedades"}),
#"Filas filtradas" = Table.SelectRows(#"Columnas eliminadas", cada una [Column1.geometry.type] = "MultiLineString" o [Column1.geometry.type] = "Polygon"),
#"Columna expandida1.geometry.coordenadas" = Table.ExpandListColumn(#"Filas filtradas", "Columna1.geometry.coordenadas"),
#"Columna expandida1.geometry.coordenadas1" = Table.ExpandListColumn(#"Columna expandida1.geometry.coordenadas", "Columna1.geometría.coordenadas"),
#"Valores extraídos" = Table.TransformColumns(#"Expanded Column1.geometry.coordinates1", {"Column1.geometry.coordinates", cada Text.Combine(List.Transform(_, Text.From), ","), escriba text}),
#"Valor reemplazado" = Table.ReplaceValue(#"Valores extraídos",","," ",Replacer.ReplaceText,{"Column1.geometry.coordinates"}),
#"Texto en mayúsculas" = Table.TransformColumns(#"Valor reemplazado",{{"Column1.geometry.type", Text.Upper, type text}}),
#"Filas agrupadas" = Table.Group(#"Texto en mayúsculas", {"Column1.id"}, {{"MyTable", cada _, escriba tabla [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")),
#"Valores extraídos1" = Table.TransformColumns(#"Added Custom", {"ExtractGeom", each Text.Combine(List.Transform(_, Text.From), ", "), escriba texto}),
#"Added Custom1" = Table.AddColumn(#"Extracted Values1", "ExtractType", each Table.Column([MyTable],"Column1.geometry.type")),
#"Expanded ExtractType" = Table.ExpandListColumn(#"Added Custom1", "ExtractType"),
#"Columnas eliminadas1" = Table.RemoveColumns(#"Expanded ExtractType",{"MyTable"}),
#"Duplicados eliminados" = Table.Distinct(#"Columnas eliminadas1", {"Column1.id"}),
#"Added Custom2" = Table.AddColumn(#"Removed Duplicates", "WKT", each [ExtractType] & " (" & [ExtractGeom] & ")")
en
#"Añadido Custom2"
Esto da como resultado una cuerda de línea larga sin separación entre las dos partes, por lo que dibuja incorrectamente
MULTILINERA (-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)
Lo que realmente necesito es lo siguiente. Esto tiene corchetes adicionales que especulan las dos cadenas de línea que forman la 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))
Hi, @hartp ;
Pruébalo.
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"El resultado final se muestra a continuación:
Saludos
Apoyo a la comunidad Team_ Yalan Wu
Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.