Parse dBGeography to latitude and longitude in PowerBI

Question

Is there a way to parse dBGeography into latitude or longitude with Power Query? Or the only way I can do that is to have 2 additional columns in my dB, which doesn't seem a very nice solution.

EDIT: dB the value is something like this: 0xE6100000010C17D9CEF753D347407593180456965EC0

score 1 · Accepted Answer · answered Jul 03 '20 at 07:55

You can do this from PowerQuery by selecting the Transform Data option from the toolbar.

In the below code I create a copy of the column, then:

Replace "POINT (" with nothing (blank)
Replace ")" with nothing (blank)
Split the point on the " " (space) to get two fields.

The first will be latitude, the second longitude.

let
    Source = Sql.Database("my.database.windows.net", "mydb"),
    dbo_FactTest = Source{[Schema="dbo",Item="FactTest"]}[Data],
    #"Removed Columns1" = Table.RemoveColumns(dbo_FactTest,{"MyTextData"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns1", "geo", "geo - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"geo - Copy", "geo - original"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","POINT (","",Replacer.ReplaceText,{"geo"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"geo"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "geo", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"geo.1", "geo.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"geo.1", type number}, {"geo.2", type number}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"geo.1", "Lat"}, {"geo.2", "Long"}})
in
    #"Renamed Columns1"

That seems more like C# way of getting it. In the dB the value saved is : `0xE6100000010C17D9CEF753D347407593180456965EC0` how do I extract Lat and Long from it? — Fab C, Jul 03 '20 at 08:01
We use this type of approach to storing geos https://stackoverflow.com/questions/30322924/how-to-store-longitude-latitude-as-a-geography-in-sql-server-2014 can you point me to the docs for your approach? — Murray Foxcroft, Jul 03 '20 at 08:16

score 1 · Answer 2 · answered Jul 03 '20 at 08:38

From your edit it appears you are using SQL 2008 (or an upgrade with data stored in the older format) where points are saved as hex. What you need to do is use SQL to extract the latitude and longitude in a custom PowerBI query. In PowerBI, when selecting your database as a source, click the "Advanced Options" and you can place your custom select statement that parses out the hex to something you can work with. See the screen shot below.

Note: The "Adavanced Option" is only available from setting up an new data source, using the "Recent Sources" option does not allow you to capture your own query.

I found this StackOverflow answer to help wit the conversion in your SQL select statement.

Call .STAsText() / .ToString() / .AsTextZM() on the value to see it in human readable form.

Parse dBGeography to latitude and longitude in PowerBI

2 Answers2