Geometry.ToWellKnownText

Updated on

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 text

Description

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
  WellKnownTextFormat

This 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
  WellKnownTextFormat

Conversion 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
  WellKnownTextFormat

In 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
  WellKnownTextFormat

This 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
  WellKnownTextFormat

This 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
  WellKnownTextFormat

In 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
  WellKnownTextFormat

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

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

Other functions related to Geometry.ToWellKnownText are:

BI Gorilla Blog

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/geometry-towellknowntext