Geometry.ToWellKnownText is a Power Query M function that translates a structured geometric 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
Geometry.ToWellKnownText(
input as nullable record,
optional omitSRID as nullable logical,
) as nullable textDescription
Geometry.ToWellKnownText takes structured geometric data and encodes it into WKT format. This standardization, defined by the Open Geospatial Consortium, enables the geometric data to be communicated and understood by a wide range of geospatial and database applications, ensuring compatibility and interoperability.
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
In geometry, a point is the simplest shape, represented by an X and Y coordinate. These coordinates define the position of the point in a two-dimensional space.
You can provide Geometry.ToWellKnownText with a record value containing the fields Kind, X and Y respectively and the function will try to recognize the Well Known Text format.
/* Output: POINT(10 20) */
let
GeometryFormat = [ Kind = "POINT", X = 10, Y = 20 ],
WellKnownTextFormat = Geometry.ToWellKnownText( GeometryFormat )
in
WellKnownTextFormat
This code takes a geometric point (with X as 10 and Y as 20) and converts it into the WKT format, which is a standard text representation used in spatial databases and systems.
Conversion to LINESTRING Format
A LINESTRING is a shape consisting of multiple points connected in a line. In spatial terms, it represents a path or a route from one point to another.
/* Output:
LINESTRING(10 10, 20 20, 21 30)
*/
let
GeometryFormat =
[
Kind = "LINESTRING",
Points = {
[ Kind = "POINT", X = 10, Y = 10 ],
[ Kind = "POINT", X = 20, Y = 20 ],
[ Kind = "POINT", X = 21, Y = 30 ]
}
],
WellKnownTextFormat = Geometry.ToWellKnownText ( GeometryFormat )
in
WellKnownTextFormatThis output shows the LINESTRING as a series of coordinates, where each pair of X and Y values is a point on 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 GeometryPoint.From. It returns the record format used in Geometry.ToWellKnownText.
// Output: [ Kind = "POINT", X = 10, Y = 10 ]
GeometryPoint.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 = GeometryPoint.From ( 10, 10 ),
Point2 = GeometryPoint.From ( 20, 20 ),
Point3 = GeometryPoint.From ( 25, 30 ),
DataPoints = { Point1, Point2, Point3 },
GeometryFormat = [ Kind = "LINESTRING", Points = DataPoints ],
WellKnownTextFormat = Geometry.ToWellKnownText ( GeometryFormat )
in
WellKnownTextFormatConversion to POLYGON Format
A POLYGON in geometry represents a closed area defined by several points. Imagine connecting dots to form a shape like a triangle or a rectangle.
To transform a POLYGON into WKT you can use:
/* Output:
POLYGON((0 0, 0 40, 40 40, 40 0, 0 0))
*/
let
Point1 = GeometryPoint.From ( 0, 0 ),
Point2 = GeometryPoint.From ( 0, 0 ),
Point3 = GeometryPoint.From ( 40, 40 ),
Point4 = GeometryPoint.From ( 40, 0 ),
Point5 = GeometryPoint.From ( 0, 0 ),
DataPoints = { Point1, Point2, Point3, Point4, Point5 },
GeometryFormat =
[
Kind = "POLYGON",
Rings = { [ Kind = "LINESTRING", Points = DataPoints ] }
],
WellKnownTextFormat = Geometry.ToWellKnownText ( GeometryFormat )
in
WellKnownTextFormatIn this example, the points define the corners of the polygon, and the WKT format neatly outlines this area.
Conversion to MULTIPOINT Format
In geometry, a MULTIPOINT is a collection of several individual points, each with its own X and Y coordinates. It’s similar to having multiple distinct locations marked on a map.
When converting a MULTIPOINT to WKT using Geometry.ToWellKnownText, the function takes these individual points and combines them into a single, standardized text format:
/* Output:
MULTIPOINT((0 0), (10 20), (15 20), (30 30))
*/
let
Point1 = GeometryPoint.From ( 0, 0 ),
Point2 = GeometryPoint.From ( 10, 20 ),
Point3 = GeometryPoint.From ( 15, 20 ),
Point4 = GeometryPoint.From ( 30, 30 ),
DataPoints = { Point1, Point2, Point3, Point4 },
GeometryFormat = [ Kind = "MULTIPOINT", Components = DataPoints ],
WellKnownTextFormat = Geometry.ToWellKnownText ( GeometryFormat )
in
WellKnownTextFormatThis output represents each point in the MULTIPOINT format, providing a concise and organized way to handle multiple points in a single data structure.
Conversion To MULTILINESTRING Format
A MULTILINESTRING consists of several lines, each line being a sequence of points connected together. Imagine a series of separate paths or routes, each with its own starting and ending points.
Using Geometry.ToWellKnownText we can create a MULTILINESTRING using:
/* Output:
MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
*/
let
Point1 = GeometryPoint.From ( 0, 0 ),
Point2 = GeometryPoint.From ( 10, 20 ),
DataPoints1 = { Point1, Point2 },
Point3 = GeometryPoint.From ( 15, 20 ),
Point4 = GeometryPoint.From ( 30, 30 ),
DataPoints2 = { Point3, Point4 },
GeometryFormat =
[
Kind = "MULTILINESTRING",
Components =
{
[ Kind = "LINESTRING", Points = DataPoints1 ],
[ Kind = "LINESTRING", Points = DataPoints2 ]
}
],
WellKnownTextFormat = Geometry.ToWellKnownText ( GeometryFormat )
in
WellKnownTextFormatThis function organizes each line with its respective points into the MULTILINESTRING format, showcasing a structured approach to handling multiple line-shaped data.
Conversion to MULTIPOLYGON Format
MULTIPOLYGON in geometry represents a collection of several polygons. Each polygon is a closed shape defined by a series of points. A MULTIPOLYGON could represent multiple distinct areas or zones.
To convert your MULTIPOLYGON data to WKT, you can use:
/* 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 = GeometryPoint.From ( 4.3715, 51.8058 ),
Point2 = GeometryPoint.From ( 4.4477, 51.9716 ),
Point3 = GeometryPoint.From ( 4.4728, 51.8948 ),
Point4 = GeometryPoint.From ( 4.4744, 51.8061 ),
Point5 = GeometryPoint.From ( 4.4743, 51.8058 ),
Point6 = GeometryPoint.From ( 4.3715, 51.8058 ),
DataPoints = { Point1, Point2, Point3, Point4, Point5, Point6 },
GeometryFormat =
[
Kind = "MULTIPOLYGON",
Components = { [ Kind = "POLYGON", Rings = { [ Kind = "LINESTRING", Points = DataPoints ] } ] }
],
WellKnownTextFormat = Geometry.ToWellKnownText ( GeometryFormat )
in
WellKnownTextFormatIn this output, each set of points forms an individual polygon, and the MULTIPOLYGON format contains these multiple polygons, offering a clear and structured representation of complex shapes in a unified manner.
Conversion to GEOMETRYCOLLECTION Format
The GEOMETRYCOLLECTION format is a mix of different geometric shapes, such as points, lines, and polygons, all in one collection.
An example that converts a GEOMETRYCOLLECTION to WKT is:
/* Output:
GEOMETRYCOLLECTION(POINT (10 10), POINT (30 30), LINESTRING (15 15, 20 20))
*/
let
Point1 = GeometryPoint.From ( 10, 10 ),
Point2 = GeometryPoint.From ( 30, 30 ),
DataPoints1 = { Point1, Point2 },
Point3 = GeometryPoint.From ( 15, 15 ),
Point4 = GeometryPoint.From ( 20, 20 ),
DataPoints2 = { Point3, Point4 },
GeometryFormat =
[
Kind = "GEOMETRYCOLLECTION",
Components =
DataPoints1 & {
[
Kind = "LINESTRING",
Points = DataPoints2
]
}
],
WellKnownTextFormat = Geometry.ToWellKnownText ( GeometryFormat )
in
WellKnownTextFormatSince 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
GeometryFormat =
[
Kind = "GEOMETRYCOLLECTION",
Components =
{
[ Kind = "POINT", X = 10, Y = 10 ],
[ Kind = "POINT", X = 30, Y = 30 ],
[
Kind = "LINESTRING",
Points =
{
[ Kind = "POINT", X = 15, Y = 15 ],
[ Kind = "POINT", X = 20, Y = 20 ]
}
]
}
],
WellKnownTextFormat = Geometry.ToWellKnownText ( GeometryFormat )
in
WellKnownTextFormatRelated articles
Learn more about Geometry.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 Geometry.ToWellKnownText are:
2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy