Geography.FromWellKnownText is a Power Query M function that translates text representing a geographic value in Well-Known Text (WKT) format into a structured record. The function returns a record representing the geographic value.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Geography.FromWellKnownText( input as nullable text ) as nullable recordDescription
Geography.FromWellKnownText converts a string in Well-Known Text (WKT) format to a structured record that represents geographic values. WKT, established by the Open Geospatial Consortium, serves as a common serialization method in databases such as SQL Server. This function makes geographic data in WKT format interpretable and usable within Power Query.
The function supports transforming the following Well-Known Text formats:
- POINT
- LINESTRING
- POLYGON
- MULTIPOINT
- MULTILINESTRING
- MULTIPOLYGON
- GEOMETRY COLLECTION
Examples
Let’s look at an example. Below you can find a record containing multiple lists and records. Within you can find structured geographic points. To be able to work with these, it’s more efficient to transform them into the Well Known Text (WKT) format. Here’s how you can do that:
Conversion from POINT Format
The simplest form of geospatial data is a point, defined by longitude and latitude. In this example, we see how the Geography.FromWellKnownText function takes a WKT POINT format and transforms it into an easily understandable Power Query record.
// Output: [ Kind = "POINT", Longitude = 10, Latitude = 20 ]
Geography.FromWellKnownText( "POINT(10 20)" )This output shows how a single geographic location is encapsulated into a Power Query record with clear labels for its type and coordinates.
Conversion from LINESTRING Format
When dealing with a series of points representing a line, the LINESTRING format comes into play. This example demonstrates the conversion of a WKT LINESTRING into a structured sequence of points within Power Query.
Geography.FromWellKnownText( "LINESTRING(10 10, 20 20, 21 30)" )The output provides a visual representation of a line made up of distinct points, each with its own set of longitude and latitude values.
[
Kind = "LINESTRING",
Points =
{
[ Kind = "POINT", Longitude = 10, Latitude = 10 ],
[ Kind = "POINT", Longitude = 20, Latitude = 20 ],
[ Kind = "POINT", Longitude = 21, Latitude = 30 ]
}
]Conversion from POLYGON Format
A polygon, defined by a series of points that create a closed shape, is crucial for mapping larger areas. The conversion process from WKT POLYGON to a Power Query record is illustrated here, highlighting the structuring of these area-defining points.
Geography.FromWellKnownText( "POLYGON((0 0, 0 40, 40 40, 40 0, 0 0))" )This output visualizes a polygon through a series of points, indicating the closed-loop nature of this geometric shape.
[
Kind = "POLYGON",
Rings =
{
[
Kind = "LINESTRING",
Points =
{
[ Kind = "POINT", Longitude = 0, Latitude = 0 ],
[ Kind = "POINT", Longitude = 0, Latitude = 40 ],
[ Kind = "POINT", Longitude = 40, Latitude = 40 ],
[ Kind = "POINT", Longitude = 40, Latitude = 0 ],
[ Kind = "POINT", Longitude = 0, Latitude = 0 ]
}
]
}
]Conversion from MULTIPOINT Format
The MULTIPOINT format is ideal for representing multiple, independent points. This example showcases how these dispersed points are collectively transformed into a structured MULTIPOINT record in Power Query.
Geography.FromWellKnownText( "MULTIPOINT((0 0), (10 20), (15 20), (30 30))" )In the output below, each point is distinct yet part of a larger multi-point structure, showcasing the function’s ability to return all datapoint from a complex data multipoint well-known text format..
[
Kind = "MULTIPOINT",
Components =
{
[ Kind = "POINT", Longitude = 10, Latitude = 10 ],
[ Kind = "POINT", Longitude = 10, Latitude = 20 ],
[ Kind = "POINT", Longitude = 15, Latitude = 20 ],
[ Kind = "POINT", Longitude = 30, Latitude = 30 ]
}
]Conversion from MULTILINESTRING Format
For scenarios involving several lines, the MULTILINESTRING format is used. This example demonstrates how a WKT MULTILINESTRING is converted into a Power Query record, representing each line as a series of points.
Geography.FromWellKnownText( "MULTILINESTRING((10 10, 20 20), (15 15, 30 15))" )The output elegantly organizes multiple lines, each defined by its constituent points, into a cohesive record structure.
[
Kind = "MULTILINESTRING",
Components =
{
[
Kind = "LINESTRING",
Points = {
[ Kind = "POINT", Longitude = 10, Latitude = 10 ],
[ Kind = "POINT", Longitude = 20, Latitude = 20 ]
}
],
[
Kind = "LINESTRING",
Points =
{
[ Kind = "POINT", Longitude = 15, Latitude = 15 ],
[ Kind = "POINT", Longitude = 30, Latitude = 15 ]
}
]
}
]Conversion from MULTIPOLYGON Format
The MULTIPOLYGON format is used for representing intricate polygonal shapes. This example illustrates how this complex format is converted into a structured MULTIPOLYGON record in Power Query.
Geography.FromWellKnownText(
"MULTIPOLYGON (((
4.37151083818814 51.8058337,
4.44772770816376 51.9716711044139,
4.47281312478682 51.8948221207453,
4.47445171328713 51.8061076713834,
4.47438826214634 51.8058337,
4.37151083818814 51.8058337)))"
)The output provides a detailed record of multiple polygons, each defined by their bounding points.
[
Kind = "MULTIPOLYGON",
Components =
{
[
Kind = "POLYGON",
Rings =
{
[
Kind = "LINESTRING",
Points =
{
[ Kind = "POINT", Longitude = 4.371510838, Latitude = 51.8058337 ],
[ Kind = "POINT", Longitude = 4.447727708, Latitude = 51.9716711 ],
[ Kind = "POINT", Longitude = 4.472813125, Latitude = 51.89482212 ],
[ Kind = "POINT", Longitude = 4.474451713, Latitude = 51.80610767 ],
[ Kind = "POINT", Longitude = 4.474388262, Latitude = 51.8058337 ],
[ Kind = "POINT", Longitude = 4.371510838, Latitude = 51.8058337 ]
}
]
}
]
}
]Conversion from GEOMETRYCOLLETION Format
Finally, the GEOMETRYCOLLECTION format allows for the combination of various geometric shapes. This example demonstrates how such a collection is transformed into a structured GEOMETRYCOLLECTION record.
Geography.FromWellKnownText(
"GEOMETRYCOLLECTION(
POINT (10 10),
POINT (30 30),
LINESTRING (15 15, 20 20))"
)The output of this transformation is a record containing different geometric types, from points to linestrings, in a single collection.
[
Kind = "GEOMETRYCOLLECTION",
Components =
{
[ Kind = "POINT", Longitude = 10, Latitude = 10 ],
[ Kind = "POINT", Longitude = 30, Latitude = 30 ],
[
Kind = "LINESTRING",
Points =
{
[ Kind = "POINT", Longitude = 15, Latitude = 15 ],
[ Kind = "POINT", Longitude = 20, Latitude = 20 ]
}
]
}
]Related articles
Learn more about Geography.FromWellKnownText in the following articles:
- Power Query Geography And Geometry Functions In Power BI And Excel
The article delves into Power Query functions for handling geographic and geometric data in Power BI and Excel, offering practical examples of their use in converting and managing Well Known Text format. » Read more - Working with Geospatial Data in Power BI
The article provides a guide on using geospatial data in Power BI, including converting geospatial data for use in visualizations, extracting longitude and latitude from WKT data, and employing the icon-map visualization to render complex geospatial shapes. It covers the essentials from converting data types to extracting coordinates and using custom visuals for enhanced mapping capabilities. » Read more
Related functions
Other functions related to Geography.FromWellKnownText are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy