QlikView with Google maps

[#1]

Hi,

I was wondering if there was a way that my map can read an actual address rather than the latitude and longitude of an area.

I have a large number of addresses, however I'm lacking the latitudes and longitudes required to illustrate them on a map.

Hope someone can help.

Thanks

[#2]

Check that your seprate decimal is "."

[#3]

thanks for the reply, however, any chance you could elaborate?

what is a separator decimal?

thanks

[#4]

What you are looking for is a geocoding service which will give you lat/long given an address. Google provides such a service:http://code.google.com/intl/fr/apis/maps/documentation/geocoding/

for example:

http://maps.googleapis.com/maps/api/geocode/xml?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&sensor=false

[#5]

Hi bruanm

I dnt knw wat u want exactly. f want to convrt lat&long to ur data means its very useful to .

Normal 0 false false false EN-US X-NONE X-NONE

Conversion :

Convert 102° 45' 54" to decimal form.

Clearly, I've got 102°, but how do I convert the minutes and seconds to decimal form? By using the definitions and doing the divisions. The 45' means 45/60 of a degree, since each degree contains sixty minutes. Simplification and long division gives me 45/60 = 3/4 = 0.75. So the 45' is 0.75°.

Now I need to deal with the 54". Since each minute is sixty seconds, then I get 54/60 = 9/10 = 0.9. But this is minutes. Now I need to convert the 0.9 of a minute to degrees:

(0.9 minutes)(1 degree / 60 minutes) = 0.015 degrees

So 102° 45' 54" = 102° + 0.75° + 0.015° = 102.765°.

[#6]

If I have got this right then you have a lot of address data and no corresponding longitude and latitude data.

I had a similar issue but have come across the Free downloadable GeoCoding app

http://mugunthkumar.com/windows/mk-geocoder/

If you download this app then you can extract you locations to a text file, pass them through this app (with the appropriate Google Map API web key) and then read in the created text file to your QlikView app (it will also produce an exceptions report of the address's it can't match on). After a couple of experiments i have found that the best way to use this is to also include a primary key field into the address details you pass through this app. That way with a bit of manipulation through excel you can read the results straight back into QlikView.

hope this helps, its not 100% bit if you run the results through Excel then its easy enough to identify any incorrect co-ords.

Lewis

[#7]

Hi

Have a look to http://community.qlik.com/media/p/87334.aspx

I created to display a map with physical address using GoogleMaps geocoder.

JJ

[#8]

Hi Lewis !

I'm trying to use this app, but i'm having all exceptions. Could you show me an example of your direction file (to use it as an example) .

Besides, in google api V3, you don´t need an API Key anymore. Do you know if there is any new geocoder file?

Thanks in advance !!

Pablo.

[#9]

Pablo

The first time I used it i got lots of address's that couldn't be matched. For the best results i have done the following:

My street data is contained within an SQL database and all i had to work from was the street name and nothing else. What i did know though was that all the locations were in a specific borough of London so i was able to use that to limit my search - the application will not return anything if you just run it against 'High Street' for example as most towns and cities in the UK have a 'High Street'

SQL Query for locations

SELECT CONVERT(CHAR(10), [individual street ID value]),[Street description] + ', [London Borough] London' FROM [Street Table]

I included the street value (primary Key value) in the query in order that I retained the link back to the street description for re-importing and linking the data back.

Copy results into Excel and save as CSV

I than ran the CSV file and the app returned all but one match. I then opened the geocoded txt file back in Excel and just looked for any anomalies in the coordinates. I found a few where certain words from the street description in my data base had not been entered correctly or if it contained words like 'Car Park' which it also didn't like - basically I then corrected the information and re-ran it.

I think its anly going to be 100% accurate if the original data source contains things such as the zip/postcode

This is part of the CSV file that i created

3,"Albion Way, Lewisham London"

5,"Algernon Road, Lewisham London"

7,"Bertrand Street, Lewisham London"

9,"Blessington Road, Lewisham London"

11,"Belmont Grove, Lewisham London"

13,"Belmont Hill, Lewisham London"

15,"Bonfield Road, Lewisham London"

[#10]

Hi Lewis;

Thanks a lot !!

I've followed the solution as in http://community.qlik.com/media/p/87334.aspx
(Thanks to Jean-Jacques Jesua)

During the LOAD i call the api as in :

for i=1 to 2000;
let vExcelFieldValue = FieldValue('FullAddress',$(i));
set ErrorMode = 0;
sleep 100 ;

Adresses:
ADD LOAD @1,
@2,
@3 as latitude,
@4 as longitude,
now() as Quand,
'$(vExcelFieldValue)' as FullAddress
FROM
[http://maps.google.com/maps/geo?q=$(vExcelFieldValue)&region=AR&output=csv&oe=utf16&sensor=false&key=$(gmap_key)]
(txt, codepage is 1252, explicit labels, delimiter is ',', msq);

next i ;

(using some intervals in order to correct any mistakes in the adresses)

I found it very simple and usefull to not use any other app.

As the google API is still in function, at least during this year (then we will need to use version 3), i do not have any trouble.

Thanks for your response.

Cheers,

Pablo.