Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Is it possible to hook a dataset with lat long in it to something within Power BI that will bring back an approximate address that I could display on a map? If yes, how accurate is that address? Am I better off seeking an outside service to hook into the dataset before bringing it into Power BI?
Solved! Go to Solution.
Hey,
what you are looking for is called "reverse geocoding".
Here is a little example, how to "reverse geocode" using the google maps api:
https://www.codexworld.com/get-address-from-latitude-longitude-using-google-maps-api-php/
This excellenct video shows how you can use the google maps api from Power Query
https://www.youtube.com/watch?v=87UsaOYD-ZM
This video also a link to how to obtain an api key.
Also consider, due to the lack of incremental data loads in Power Query, you send each row of the ever growing dataset to the api whenever you refresh the dataset. This then may also conflict with api limitations that just answers a certain amount of requests per time unit. For this reason I would recommend to use SQL Server and the integration of R (available since SQL Server 2016). And then use R script to get the address, this is described here:
https://datavisualizationineconomics.blogspot.de/2015/11/reverse-geocode-using-google-api-and.html
Please be aware that this solution calls into the world wide web from inside the SQL Server, and you may need some good arguments to convince your DBAs to allow this. One Argument is always "the value of location based information", at least I often use this argument 😉
Hopefully this provides some ideas.
Regards
Tom
Hey,
what you are looking for is called "reverse geocoding".
Here is a little example, how to "reverse geocode" using the google maps api:
https://www.codexworld.com/get-address-from-latitude-longitude-using-google-maps-api-php/
This excellenct video shows how you can use the google maps api from Power Query
https://www.youtube.com/watch?v=87UsaOYD-ZM
This video also a link to how to obtain an api key.
Also consider, due to the lack of incremental data loads in Power Query, you send each row of the ever growing dataset to the api whenever you refresh the dataset. This then may also conflict with api limitations that just answers a certain amount of requests per time unit. For this reason I would recommend to use SQL Server and the integration of R (available since SQL Server 2016). And then use R script to get the address, this is described here:
https://datavisualizationineconomics.blogspot.de/2015/11/reverse-geocode-using-google-api-and.html
Please be aware that this solution calls into the world wide web from inside the SQL Server, and you may need some good arguments to convince your DBAs to allow this. One Argument is always "the value of location based information", at least I often use this argument 😉
Hopefully this provides some ideas.
Regards
Tom
Hello Tom,
This seems like to be the answer to what I am looking for. However, I am completely new to coding and do not even know what to do with the PHP code given in the link you provided. Can you give me an idea of what I should be doing with it?
I have csv file with million rows of lat long and I need to be able to identify the district, subdistrict, zipcode of each coordinate
My data could possibly be too large for excel. Would it be possible to do it in MS Access or Power BI too? If not I can stick with Excel.
Bests
Suthipat
User | Count |
---|---|
128 | |
108 | |
99 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |