Geography.ToWellKnownText is a Power Query M function that translates a structured geographic point value into its Well-Known Text (WKT) representation. The function returns a text value with the WKT format.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Geography.ToWellKnownText(
input as nullable record,
optional omitSRID as nullable logical,
) as nullable textDescription
Geography.ToWellKnownText reverses the process of Geography.FromWellKnownText, by translating a structured geographic point into its standardized WKT format. This enables seamless exchange of geographic data with systems and formats that adhere to Open Geospatial Consortium standards, including many database systems like SQL Server.
The function supports transforming the following Well-Known Text formats:
- POINT
- LINESTRING
- POLYGON
- MULTIPOINT
- MULTILINESTRING
- MULTIPOLYGON
- GEOMETRY COLLECTION
All of these have a specific record format required for Power Query to be able to transform the values into a Well-known text format.
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 to POINT Format
A geographic point is defined by its longitude and latitude, representing its location on the Earth’s surface. The Geography.ToWellKnownText function translates this point into a standardized WKT POINT format. Here’s an example:
/* Output:
POINT(10 20)
*/
let
GeographyFormat = [ Kind = "POINT", Longitude = 10, Latitude = 20 ],
WellKnownTextFormat = Geography.ToWellKnownText ( GeographyFormat )
in
WellKnownTextFormatThis code segment showcases the conversion of a simple geographic point (longitude 10, latitude 20) into the WKT format.
Conversion to LINESTRING Format
The LINESTRING format in WKT represents a line composed of a series of points. This example illustrates how Geography.ToWellKnownText converts a LINESTRING into an easily interpretable structured format.
/* Output:
LINESTRING(10 10, 20 20, 21 30)
*/
let
GeographyFormat =
[
Kind = "LINESTRING",
Points = {
[ Kind = "POINT", Longitude = 10, Latitude = 10 ],
[ Kind = "POINT", Longitude = 20, Latitude = 20 ],
[ Kind = "POINT", Longitude = 21, Latitude = 30 ]
}
],
WellKnownTextFormat = Geography.ToWellKnownText ( GeographyFormat )
in
WellKnownTextFormatThe output visualizes a LINESTRING, demonstrating the sequence of points (longitude and latitude) that form the line.
For these difficult formats you may want to split your logic up into multiple parts. Instead of a big final expression, try storing the data points in separate variables. A helpful function here is GeographyPoint.From. It returns the record format used in Geography.ToWellKnownText.
// Output: [ Kind = "POINT", Longitude = 10, Latitude = 10 ]
GeographyPoint.From ( 10, 10 )You can adjust the earlier example to a more simple version using the below:
/* Output:
LINESTRING(10 10, 20 20, 25 30)
*/
let
Point1 = GeographyPoint.From ( 10, 10 ),
Point2 = GeographyPoint.From ( 20, 20 ),
Point3 = GeographyPoint.From ( 25, 30 ),
DataPoints = { Point1, Point2, Point3 },
GeographyFormat = [ Kind = "LINESTRING", Points = DataPoints ],
WellKnownTextFormat = Geography.ToWellKnownText ( GeographyFormat )
in
WellKnownTextFormatConversion to POLYGON Format
A POLYGON in WKT format represents a closed area defined by multiple points. This conversion is used to map territories or defined spaces. Here’s an example:
/* Output:
POLYGON((0 0, 0 40, 40 40, 40 0, 0 0))
*/
let
Point1 = GeographyPoint.From ( 0, 0 ),
Point2 = GeographyPoint.From ( 0, 0 ),
Point3 = GeographyPoint.From ( 40, 40 ),
Point4 = GeographyPoint.From ( 40, 0 ),
Point5 = GeographyPoint.From ( 0, 0 ),
DataPoints = { Point1, Point2, Point3, Point4, Point5 },
GeographyFormat =
[
Kind = "POLYGON",
Rings = { [ Kind = "LINESTRING", Points = DataPoints ] }
],
WellKnownTextFormat = Geography.ToWellKnownText ( GeographyFormat )
in
WellKnownTextFormatThe output illustrates the POLYGON as a series of points that enclose an area, emphasizing the structured representation of a polygon in WKT format.
Conversion to MULTIPOINT Format
The MULTIPOINT format in WKT is used to represent a collection of individual geographic points, each with its own longitude and latitude. It’s similar to having multiple distinct locations marked on a map. Here’s an example of how you can create such a format:
/* Output:
MULTIPOINT((0 0), (10 20), (15 20), (30 30))
*/
let
Point1 = GeographyPoint.From ( 0, 0 ),
Point2 = GeographyPoint.From ( 10, 20 ),
Point3 = GeographyPoint.From ( 15, 20 ),
Point4 = GeographyPoint.From ( 30, 30 ),
DataPoints = { Point1, Point2, Point3, Point4 },
GeographyFormat = [ Kind = "MULTIPOINT", Components = DataPoints ],
WellKnownTextFormat = Geography.ToWellKnownText ( GeographyFormat )
in
WellKnownTextFormatThis output organizes each distinct point into a larger structure, transforming separate geographical locations into a unified MULTIPOINT representation in WKT format.
Conversion To MULTILINESTRING Format
MULTILINESTRING in WKT is used to depict a series of separate lines, each defined by a sequence of points. Imagine multiple paths or routes, each with its own unique trajectory.
/* Output:
MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
*/
let
Point1 = GeographyPoint.From ( 0, 0 ),
Point2 = GeographyPoint.From ( 10, 20 ),
DataPoints1 = { Point1, Point2 },
Point3 = GeographyPoint.From ( 15, 20 ),
Point4 = GeographyPoint.From ( 30, 30 ),
DataPoints2 = { Point3, Point4 },
GeographyFormat =
[
Kind = "MULTILINESTRING",
Components =
{
[ Kind = "LINESTRING", Points = DataPoints1 ],
[ Kind = "LINESTRING", Points = DataPoints2 ]
}
],
WellKnownTextFormat = Geography.ToWellKnownText ( GeographyFormat )
in
WellKnownTextFormatIn this example, each line is represented by its constituent points, collectively forming the MULTILINESTRING structure. The format is used to handle multiple linear shapes in a spatial context.
Conversion to MULTIPOLYGON Format
The MULTIPOLYGON format is designed to represent multiple, distinct polygonal shapes. Each polygon is a closed area defined by a series of bounding points. Here’s an example of how you can create such a format:
/* Output:
MULTIPOLYGON (((
4.3715 51.8058,
4.4477 51.9716,
4.4728 51.8948,
4.4744 51.8061,
4.4743 51.8058,
4.3715 51.8058 )))
*/
let
Point1 = GeographyPoint.From ( 4.3715, 51.8058 ),
Point2 = GeographyPoint.From ( 4.4477, 51.9716 ),
Point3 = GeographyPoint.From ( 4.4728, 51.8948 ),
Point4 = GeographyPoint.From ( 4.4744, 51.8061 ),
Point5 = GeographyPoint.From ( 4.4743, 51.8058 ),
Point6 = GeographyPoint.From ( 4.3715, 51.8058 ),
DataPoints = { Point1, Point2, Point3, Point4, Point5, Point6 },
GeographyFormat =
[
Kind = "MULTIPOLYGON",
Components = { [ Kind = "POLYGON", Rings = { [ Kind = "LINESTRING", Points = DataPoints ] } ] }
],
WellKnownTextFormat = Geography.ToWellKnownText ( GeographyFormat )
in
WellKnownTextFormatThis function captures multiple polygons, each outlined by a series of geographical points, illustrating the MULTIPOLYGON structure in the WKT format.
Conversion to GEOMETRYCOLLECTION Format
The final example demonstrates the conversion of a collection of different geometric shapes (points and linestrings) into the WKT GEOMETRYCOLLECTION format. It shows how the Geography.ToWellKnownText function handles diverse geometric types in a single collection. Here’s a representation of the required formatting.
/* Output:
GEOMETRYCOLLECTION(POINT (10 10), POINT (30 30), LINESTRING (15 15, 20 20))
*/
let
Point1 = GeographyPoint.From ( 10, 10 ),
Point2 = GeographyPoint.From ( 30, 30 ),
DataPoints1 = { Point1, Point2 },
Point3 = GeographyPoint.From ( 15, 15 ),
Point4 = GeographyPoint.From ( 20, 20 ),
DataPoints2 = { Point3, Point4 },
GeographyFormat =
[
Kind = "GEOMETRYCOLLECTION",
Components =
DataPoints1 & {
[
Kind = "LINESTRING",
Points = DataPoints2
]
}
],
WellKnownTextFormat = Geography.ToWellKnownText ( GeographyFormat )
in
WellKnownTextFormatThe output captures different geometric types, from points to linestrings, in one comprehensive collection.
Since this is quite a complex example, we’ve added below code for your understanding.
/* Output:
GEOMETRYCOLLECTION(POINT (10 10), POINT (30 30), LINESTRING (15 15, 20 20))
*/
let
GeographyFormat =
[
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 ]
}
]
}
],
WellKnownTextFormat = Geography.ToWellKnownText ( GeographyFormat )
in
WellKnownTextFormatRelated articles
Learn more about Geography.ToWellKnownText 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.ToWellKnownText are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy