topic QlikView with Google maps in QlikView

QlikView with Google maps

Tue, 04 Jan 2011 15:56:00 GMT

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

QlikView with Google maps

Tue, 04 Jan 2011 16:34:19 GMT

Check that your seprate decimal is "."

QlikView with Google maps

Tue, 04 Jan 2011 16:39:31 GMT

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

what is a separator decimal?

thanks

QlikView with Google maps

Wed, 05 Jan 2011 09:30:34 GMT

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

QlikView with Google maps

Wed, 05 Jan 2011 09:45:17 GMT

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°.

QlikView with Google maps

Mon, 17 Jan 2011 21:16:27 GMT

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

QlikView with Google maps

Mon, 17 Jan 2011 21:45:02 GMT

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

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

QlikView with Google maps

Fri, 21 Jan 2011 17:57:59 GMT

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.

QlikView with Google maps

Mon, 24 Jan 2011 11:19:33 GMT

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"

QlikView with Google maps

Mon, 24 Jan 2011 20:27:48 GMT

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.

QlikView with Google maps

Mon, 24 Jan 2011 20:34:54 GMT

no problems, i want to try and implement the process you have done as i'm sure it will come in very useful to be able to do it all through the app. Unfortunately the app i'm developing will be used by a number of our clients and i know that each set of streets will not have a borough or city attached to it which will make it difficult to use.

Thanks

Lewis

Re: QlikView with Google maps

Wed, 13 Jun 2012 12:21:48 GMT

Hi All,

GIS/Maps Analytics in QlikView is a very interesting topic indeed.

However, there are many technical limitations in using Google Maps (GMaps) to display QlikView KPI indicators. Indeed, the drill-down, pan and selection capabilities are quite limited. Moreover, you cannot display several indicators at the same time and on the same map (background + foreground indicators), you are also limited to draw bubbles and therefore you cannot display pie/bar charts nor "real" heat maps. Finally, you cannot display QlikView KPIs on specific Zones or Sales Areas with GMaps such as the Bricks in the Pharmaceutical sector (Brick = aggregation of zipcodes).

On top of that, there are also a few "commercial" aspects to be considered since many QlikView customers & channel partners use GMaps API "FREE" Key inside their QlikView server or rich client without complying with GMaps license terms...Indeed, for "professional internal use" (intranet, extranet or internet with username/password authentication to QlikView application), you must buy GMaps API "Premier" which costs about 10,000 $ USD per year or 8,500 euros per year + possible extra fees depending upon the total number of daily hits.

A few alternatives exist such as Open Street Map (totally FREE) or other WMS/TMS services. I hereby suggest you to have a look at our new mapping extension for QlikView 11 SR1 namely "GeoQlik v11.6":

http://www.geoqlik.com/en/geoqlik-for-qlikview/geoqlik-v11.html

Please find hereafter a few screenshots of GeoQlik V11.6 using WMS/TMS services to display QlikView KPIs:

Thanks and best regards,

David

@ Business Geografic