Chris Webb's BI Blog: Power Query Geography And Geometry Functions In Power BI A...
source link: https://blog.crossjoin.co.uk/2020/08/16/power-query-geography-and-geometry-functions-in-power-bi-and-excel/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Power Query Geography And Geometry Functions In Power BI And Excel
In the August 2020 release of Power BI Desktop a couple of new Power Query functions were added: Geography.FromWellKnownText, Geography.ToWellKnownText, GeographyPoint.From, Geometry.FromWellKnownText, Geometry.ToWellKnownText and GeometryPoint.From. These functions (which are coming soon to Power Query in Excel too), make it easier to work with geographic and geometric data in the Well Known Text format. You can have all kinds of fun with these functions if you have a visual (like the Icon Map custom visual) that can display Well Known Text data, but I’ll leave that kind of thing for future blog posts. In this post I’ll explain how the basics of how the functions actually work.
Let’s start with points. The Geography.FromWellKnownText and Geometry.FromWellKnownText functions convert Well Known Text values into records, which makes it simple to do things like extract latitude and longitude values without having to do complex parsing of the text itself. For example:
Geometry.FromWellKnownText("POINT (0.1 0.2)")Returns a record that looks like this:
Whereas the following:
Geography.FromWellKnownText("POINT (0.1 0.2)")…returns a similar record but one with fields for latitude and longitude rather than x and y:
Now you know what the record format of a point looks like it’s easy to go in the other direction and convert a record into Well Known Text format. For example the M expression:
Geography.ToWellKnownText([Kind="POINT", Longitude=0.1, Latitude=0.3])returns the text value
POINT(0.1 0.3)You can also generate a point by passing a longitude and latitude (and other optional parameters) to GeographyPoint.From and GeometryPoint.From. For example:
GeographyPoint.From(0.1, 0.3)…also returns a POINT record:
Points can then be used to build more complex objects, which in turn can be combined into more complex objects still. For example the following M query takes three points and creates a LINESTRING object that joins those three points into a line:
letPoint1 = GeographyPoint.From(0,0),Point2 = GeographyPoint.From(1,1),Point3 = GeographyPoint.From(0,1),PointList = {Point1,Point2,Point3},LineRecord = [Kind="LINESTRING", Points=PointList],WKTLineString = Geography.ToWellKnownText(LineRecord)inWKTLineStringThe output is this:
LINESTRING(0 0, 1 1, 0 1)Similarly, multiple LINESTRING objects can be combined into a MULTILINESTRING. For example:
letPoint1 = GeographyPoint.From(0,0),Point2 = GeographyPoint.From(1,1),PointList1 = {Point1,Point2},Point3 = GeographyPoint.From(0,1),Point4 = GeographyPoint.From(1,0),PointList2 = {Point3, Point4},LineRecord1 = [Kind="LINESTRING", Points=PointList1],LineRecord2 = [Kind="LINESTRING", Points=PointList2],LineRecordList = {LineRecord1,LineRecord2},MultiLineRecord = [Kind="MULTILINESTRING", Components=LineRecordList],WKTMultiLineString = Geography.ToWellKnownText(MultiLineRecord)inWKTMultiLineString…returns:
MULTILINESTRING((0 0, 1 1), (0 1, 1 0))Finally, the most useful application for this is to extract latitudes and longitudes or x and y co-ordinates from a column of values in WKT format, something like this:
All you need to do to extract latitude and longitude values from these points is add a custom column as a step with the expression
Geography.FromWellKnownText([MyPoints])
The result is a column of records which can be expanded to get latitude and longitude values:
Like this:
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK