Help Needed Reverse GeoCoding Using Longitude & Latitude as Input

Hi
I have a (csv) file list of (Longitude & Latitude) records with More than 300,000 Coordinates need to find their Address Including (Street, City, Country,…etc) through reverse geocoding, I didn’t found useful resources to do this through power bi , like this one : https://data-savvy.com/2018/07/12/powerbi-1-how-to-get-address-by-passing-existing-coordinates-to-google-maps-reverse-geocoding-api/

If anyone understands how to do it from the mentioned link above please I need your help urgently?

Thank you so much in advanced

Hi @MAAbdullah47,
I use Google Geocoding in my web application development work, having wrapped the Geocoding API in an ASP.NET page into which I pass the address parameters. I do this on an individual record basis, so it doesn’t handle massive numbers of records at once.

I reviewed the link you provided, and it does include all the information to get you started with reverse geocoding, or the standard geocoding that I do. The fact that you can do it directly in Power BI is amazing because it means you do not need to create your own custom method of calling the API for each record in the data source before loading it into Power BI.

You will need to use the links in that article to learn about setting up the Google account, getting the API key, and the functions needed to call the API itself. There is no easy way around that learning, and the article already provides a step-by-step once you have the account configured.

What I can do is give you a couple tips about the Google account itself. First, they just changed their licensing so that you have to set up a billing account in case you go over the allotted free usage level. The pricing guide can be found here. The API Key instructions can be found here.

The free usage level is basically staying under the $200 credit they give each month. I believe that is something like 100,000 requests. Given that you have 300,000 coordinates, you will exceed the free level. If you then repeat all those requests each time the report loads, that will get expensive. So, you’re best bet is figuring out a way to reverse geocode the coordinates one time each.

An alternative may be to find a service that will take your file of coordinates and convert it en masse. Please post back with any info you have on a final solution.

John C. Pratt

Enterprise%20DNA%20Expert%20-%20Small

Hi,
300.000 is a lot! Fully agree, given that Google has changed their pricing it will become expensive. Alternative is batch reverse geocoding through an Excel add-in, have a go (free trial) at
https://www.cdxtech.com/cdxzipstream/versions/
If this works for you a one time cost of $ 199,95 will give you a tool and it will do a lot more then reverse geocoding. You need to get a Bing API to get this working for this amount of addresses which comes at a cost too.

I never rely on the embedded geocoding and always pre-geocode my addresses with a tool.

Paul

Enterprise%20DNA%20Expert%20-%20Small

Thank you so much ,
Can we load the file contains (Lat & Lon) as input and getting the address ? (There is a Video for the opposite side Input is address and output is (Lat & lon).
Is it work geocoding for my country (Saudi Arabia) ?

You will have to check the relevant documentation on this website, there are videos to help you, including how to obtain a bing api key, pretty straight forward process. Bing maps covers many countries, the website will show you which countries are supported.

Paul

Enterprise%20DNA%20Expert%20-%20Small

I have a stored procedure in SQL Server that calls the Google API and stores the results in the table, that way only need to process new records in each ETL load. i though you got a certain number of geocodes free each 24 hour period, 2,500 IIRC.

1 Like

@benaud,

I would dearly love to know the SQL code to call the Google API from a stored procedure. If that is something you could share with us, or at least point us to an article describing how to do it, that would be awesome! That could help @MAAbdullah47 as well.

John C. Pratt
Enterprise%20DNA%20Expert%20-%20Small

Hi John,

OK, i have a generic stored procedure that calls the API and returns XML. You can pass in either a Latitude/Longitude or an Address for a reverse lookup, the call to google is not that different. In some cases i have a customer address, in others i have GPS data from where our vehicles stopped.

This could be done outside of SQL using powershell or similar, but i used SQL as i wanted it to be inside SQL and that’s the environment i’m most comfortable in. You will need to enable sp_OACreate etc and check permissions… also we don’t use a proxy server… good luck if you do…

I have an error output table… I don’t THROW or RAISERROR i just want to continue on, the error table allows me to see what actually failed.

CREATE TABLE MetaData.Errors(
    	[ID] [int] IDENTITY(1,1) PRIMARY KEY,
    	[InsertDate] [datetime] NULL,
    	[ControlName] [varchar](100) NULL,
    	[Parameter1] [varchar](100) NULL,
    	[Parameter2] [varchar](100) NULL,
    	[Parameter3] [varchar](100) NULL,
    	[Parameter4] [varchar](100) NULL,
    	[Parameter5] [varchar](100) NULL,
    	[Parameter6] [varchar](100) NULL,
    	[ErrorMessage] [varchar](500) NULL,
    	[Output1] [varchar](max) NULL,
    	[Output2] [varchar](max) NULL,
    	[Output3] [varchar](max) NULL,
    	[Output4] [varchar](max) NULL,
    	[Output5] [varchar](max) NULL,
    	[Output6] [varchar](max) NULL,
    	[Output7] [varchar](max) NULL
    )    

Stored Procedure:

CREATE PROCEDURE [dbo].[usp_GeoCode]
	@Type INT = 1,
	@Latitude VARCHAR(40) = '', 
	@Longitude VARCHAR(40) = '',
	@Address VARCHAR(500) = '',
	@URL VARCHAR(800) OUTPUT,
	@XML XML OUTPUT
AS
BEGIN
	BEGIN TRY
		DECLARE @Response VARCHAR(MAX), @Obj INT, @Result INT, @HTTPStatus INT, @ErrorMsg VARCHAR(MAX);

		DECLARE @Key VARCHAR(100) = 'ABCDEFGHIJKLMNOP';

		DROP TABLE IF EXISTS #xml
		CREATE TABLE #xml ( yourXML XML )
		
		IF @Type = 1
			SET @URL = 'https://maps.google.com/maps/api/geocode/xml?sensor=false&latlng=' + @Latitude + ',' + @Longitude + '&key=' + @Key
		IF @Type = 2
		BEGIN
			SET @Address = REPLACE(@Address,CHAR(13) + CHAR(10),' ')
			SET @Address = REPLACE(@Address,'Cnr', ' Corner ')
			SET @Address = REPLACE(@Address,'&',  ' and ')
			SET @Address = REPLACE(@Address,' ', '+')
			SET @URL = 'https://maps.google.com/maps/api/geocode/xml?sensor=false&address=' + @Address + '&key=' + @Key
		END

		EXEC @Result = sp_OACreate 'MSXML2.ServerXMLHttp', @Obj OUT 
		BEGIN TRY 
			EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @URL, false 
			EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded' 
			EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
			EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 
			INSERT INTO #xml 
			EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'
		END TRY 
		BEGIN CATCH 
			SET @ErrorMsg = ERROR_MESSAGE()
		END CATCH 
		EXEC @Result = sp_OADestroy @Obj 

		IF (@ErrorMsg IS NOT NULL) OR (@HTTPStatus <> 200) 
		BEGIN 
			SET @ErrorMsg = 'Error in usp_geocode: ' + ISNULL(@ErrorMsg, 'HTTP result is: ' + CAST(@HTTPStatus AS varchar(10))) 
			RAISERROR(@ErrorMsg, 16, 1, @HTTPStatus) 
			RETURN 
		END 

		SELECT @XML = yourXML FROM #XML

	END TRY

	BEGIN CATCH
		DECLARE @ErrorMessage VARCHAR(MAX), @ProcName VARCHAR(100)
		SET @ErrorMessage = 'Failure: ' + ERROR_MESSAGE()
		SET @ProcName = (SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.objects WHERE object_id = @@PROCID)

		INSERT INTO MetaData.Errors	
			(InsertDate,ControlName,ErrorMessage,Parameter1,Parameter2,Parameter3,Parameter4,Output1,Output2,Output3)
		SELECT GETDATE(), @ProcName, @ErrorMessage, @Type, @Latitude, @Longitude, @Address, CAST(@XML AS VARCHAR(MAX)), @ErrorMsg, @HTTPStatus

	END CATCH
	

END

There is the call of the Stored Procedure… This would be inside a cursor, or while loop.

EXEC usp_GeoCode @Type = 1, @Latitude = @Latitude, @Longitude = @Longitude, @URL = @URL OUTPUT, @XML = @XML OUTPUT

or

EXEC usp_GeoCode @Type = 2, @Address = @SourceAddress, @URL = @URL OUTPUT, @XML = @XML OUTPUT

The processing of the XML output… Note that the XML contains lots of info and i have only pulled the fields that i wanted. Also in the Cursor or while loop.

	UPDATE a
	SET a.URL = @URL,
		a.XMLData = @XML,
		a.FullAddress = @XML.value('(/GeocodeResponse/result/formatted_address) [1]', 'varchar(500)'),
		a.Address = ISNULL(@XML.value('(/GeocodeResponse/result/address_component[type="street_number"]/long_name) [1]', 'varchar(40)'), '???') + ' ' + 
					ISNULL(@XML.value('(/GeocodeResponse/result/address_component[type="route"]/long_name) [1]', 'varchar(40)'), '???'),
		a.Suburb = @XML.value('(/GeocodeResponse/result/address_component[type="locality"]/long_name) [1]', 'varchar(40)'),
		a.State = @XML.value('(/GeocodeResponse/result/address_component[type="administrative_area_level_1"]/short_name) [1]', 'varchar(40)'),
		a.PostalCode = @XML.value('(/GeocodeResponse/result/address_component[type="postal_code"]/long_name) [1]', 'varchar(20)'),
		a.Country = @XML.value('(/GeocodeResponse/result/address_component[type="country"]/long_name) [1]', 'varchar(30)'),
		a.LocationType = @XML.value('(/GeocodeResponse/result/geometry/location_type) [1]', 'varchar(40)'), 
		a.Latitude = @XML.value('(/GeocodeResponse/result/geometry/location/lat) [1]', 'varchar(40)'), 
		a.Longitude = @XML.value('(/GeocodeResponse/result/geometry/location/lng) [1]', 'varchar(40)'), 
		a.Type1 = @XML.value('(/GeocodeResponse/result/type) [1]', 'varchar(100)'),
		a.Type2 = @XML.value('(/GeocodeResponse/result/type) [2]', 'varchar(100)'),
		a.Type3 = @XML.value('(/GeocodeResponse/result/type) [3]', 'varchar(100)'),
		a.Type4 = @XML.value('(/GeocodeResponse/result/type) [4]', 'varchar(100)'),
		a.Type5 = @XML.value('(/GeocodeResponse/result/type) [5]', 'varchar(100)'),
		a.Type6 = @XML.value('(/GeocodeResponse/result/type) [6]', 'varchar(100)'),
		a.PlaceID = @XML.value('(/GeocodeResponse/result/place_id) [1]', 'varchar(500)'),
		a.PartialMatch =  @XML.value('(/GeocodeResponse/result/partial_match) [1]', 'varchar(10)'),
		a.UpdateDate = GETDATE()
	FROM MyAddressTable a
	WHERE ID = @ID

`
Use at your own risk…

I can tell you that 300,000 geocodes will cost roughly $1800 AUD

1 Like

@benaud, brilliant! I will have all Type 2 records. Love the wrapping of the calls in a Try…Catch, the example of dumping errors into a table, and the details on retrieving the XML node values from the results. I see you even store the entire XML result on the Address table for future reference so you don’t need to make the call again. Thanks for the great example!

John C. Pratt
Enterprise%20DNA%20Expert%20-%20Small