Converting addresses to latitude and longitude

[metin_inksen]

Hi everyone,

I've got a SQL table with shipping data like the following:

id	first name	last name	date	shipping address line
1	george	smith	11/7/21	381. Sok Hilal apartmanı daire 3 Merkez Afyonkarahisar
2	john	jackson	11/7/21	Basın Sitesi, Atatürk Eğitim ve Araştırma Hastanesi,Acil Servis/başhekimlik önü 35360 Karabağlar İzmir
3	christian	forth	11/7/21	Bahriye Üçok Cad. No:13 Kat:3 Beşevler Çankaya Ankara

I would like to show customer's orders on the map but there is a problem. I only have address line, not latitude or longitude. In short, I'm trying to convert address line to GPS coordinates so I can show them on the map.  

target=_blankhttps://community.qlik.com/t5/Qlik-Design-Blog/How-to-resolve-addresses-into-Qlik-Sens...

I've tried the solution above but the addresses on the data is not regular, it is messy as you see so I can't parse the address as street, district, city etc. 

target=_blankhttps://community.qlik.com/t5/QlikView-App-Dev/How-to-convert-Address-into-Latitude-Lo...

Then, I've tried the solution above. I got an API key from Google for GoogleMaps but when I try to load data, I got "This statement only works with lib:// paths in this script mode" error. This is the script I use for retrieve GoogleMaps API:

Table1:
LOAD
    FirstName,
    LastName,
    Address,
    City,
    State,
    Sales
FROM [lib://State DD/state.xlsx]
(ooxml, embedded labels, table is Sheet1);

let noRows = NoOfRows('Table1')-1;

for i=0 to $(noRows)

	let d=peek('LastName',$(i),'Table1') & ', ' & peek('FirstName',$(i),'Table1');
	let p=peek('Population',$(i),'Table1');
	
	let address=peek('Address',$(i),'Table1') & ' ' & peek('City',$(i),'Table1') & ' ' & peek('State',$(i),'Table1');	

	Data:
	LOAD
	'$(d)' as Name,
	'$(p)' as Population,
	'$(address)' as Address,
	subfield([Response/Placemark/Point/coordinates], ',' ,1) AS longitude,
	subfield([Response/Placemark/Point/coordinates], ',' ,2) AS latitude
	FROM [http://maps.google.com/maps/geo?q=$(address)&output=xml&oe=utf8&sensor=false&key=AIzaSyBG2kg7rAyq1HZMM8uREYhMWOdWjBIQULA] (XmlSimple, Table is [kml]);

next

 As a result, how can I convert irregular addresses to latitude and longitude for showing orders by their addresses on the map? Thanks a lot. 

[Joseph_Musekura · Accepted solution]

Hi @metin_inksen 

In Qlik sense, you can use an operation called "AddressPointLookup" that takes each address in a table and returns point geometries (longitude, latitude)
But this operation requires a specific license to be used in Qlik Sense. You need to have GeoAnalytics installed 

Regards,

[Skashid]

Hi @metin_inksen ,

Me too facing the same error "This statement only works with lib:// paths in this script mode" after using the solution given in the below link

target=_blankhttps://community.qlik.com/t5/QlikView-App-Dev/How-to-convert-Address-into-Latitude-Lo....

as you have suggested the "AddressPointLookup" operation can u please suggest some other work around for converting the address to Latitude and Longitude as for "AddressPointLookup" we need to have Geo Analytics installed and we don't have the one installed.

Thanks in advance 🙂