Longtude and Latitude

[#1]

HelloEveryone,

I have a problem in finding out the way how to get the longitude and latitude of the locations i have in my tables.I can do it manually but the data i have is huge.  I  have the postal codes of locations  and i want the longitude and latitude of these locations and after that i can join them with left join in a table to display on Maps. Thanks In advance

BR!

Sam

[christophebrault]

Hi,

To do this, you can use the geocoding API from google

https://developers.google.com/maps/documentation/geocoding/

Look at the XML output format.

In the script, you can do something like that :

let NoOfRow =NoOfRows('PostalCode');
for i=0 to $(NoOfRow)
let vPostalCode=peek('YOURTABLE',$(i),'PostalCode');
GeocodeResponse:
LOAD     '$(vPostalCode)'as PostalCode,
[result/geometry/location/lat] as latitude,
    [result/geometry/location/lng] as longitude
FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(vPostalCode),YOURCOUNTRY&sensor=false] (XmlSimple, Table is [GeocodeResponse]);
sleep 500;
next;

I use a SLEEP 500 to slow execution of the script and respect limits of the API.

I hope this will help you.

Regards

[#3]

thanks a lot christophebrault...but when i use it at script level the above code..it gave me a script line error..can you tell me how to use it ?below is my script

let NoOfRow =NoOfRows('PostalCode');

for i=0 to $(NoOfRow)

let vPostalCode=peek('map',$(i),'PostalCode');

GeocodeResponse:

LOAD     '$(vPostalCode)'as PostalCode,

[result/geometry/location/lat] as latitude,

    [result/geometry/location/lng] as longitude

FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(vPostalCode),GERMANY&sensor=false] (XmlSimple, Table is [GeocodeResponse]);

sleep 500;

next;

map:

LOAD CustomerNo,

     CustomerName, 

     CustomerAddr1,

      CustomerCity,

     CustomerCountry

FROM CustomerInfo.qvd (qvd);

[#4]

sorry ! PostalCode is also included in the qvd file from where i m loading data.

[christophebrault · Accepted solution]

map:

LOAD CustomerNo,

     CustomerName,

     CustomerAddr1,

      CustomerCity,

     CustomerCountry,

PostalCode

FROM CustomerInfo.qvd (qvd);

let NoOfRow =NoOfRows('map');

for i=0 to $(NoOfRow)

let vPostalCode=peek('PostalCode',$(i),'map');

GeocodeResponse:

LOAD     '$(vPostalCode)'as PostalCode,

[result/geometry/location/lat] as latitude,

    [result/geometry/location/lng] as longitude

FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(vPostalCode),GERMANY&sensor=false] (XmlSimple, Table is [GeocodeResponse]);

sleep 500;

next;

sorry, i made a mistake and inverse table and field in peek() and NoOfRow(). I didn't check in QlikView...

This should work if we have the same name of fields. Geocoding works with city, andress and lots of informations.

if you want an precise location for each client, you can do :

let NoOfRow =NoOfRows('map');

for i=0 to $(NoOfRow)

let vPostalCode=peek('PostalCode',$(i),'map');

let vCustomerAddr1=peek('CustomerAddr1',$(i),'map');

let vCustomerCity=peek('CustomerCity',$(i),'map');

let vCustomerCountry=peek('CustomerCountry',$(i),'map');

let vCustomerNo=peek('CustomerNo',$(i),'map');

GeocodeResponse:

LOAD     '$(vCustomerNo)'as CustomerNo,

[result/geometry/location/lat] as latitude,

    [result/geometry/location/lng] as longitude

FROM [http://maps.googleapis.com/maps/api/geocode/xml?address=$(CustomerAddr1),$(CustomerCity),$(vPostalCode),$(CustomerCountry)&sensor=false] (XmlSimple, Table is [GeocodeResponse]);

sleep 500;

next;

Hope this will work fine for you

[#6]

thanks a lot christophebrault ...its getting the longitude and latitude

i have one more question on google maps i can't see the  dots or location now...but when i found the longitude and latitude manually i do get the points or dots on the map....can you help me with this? is there any thing to do with the number format european or american?

Best Regards!

[christophebrault]

Your problem is that Client are not situated on your map object ?

for example, if your Sales by clients, use CustomerNo as Dimension and Sum(Sales) in the third expression (after longitude and latitude)

In Property\Style, you can choose bubble or point, relative or not etc...

in the script you are using this ?

max_zoom_level = 12; // Max Zoom level can be set manualy
// Variables required for calculating map
// No need to change these
var_pi180=                    '=pi()/180';
var_lat_offset=          '0';
var_mc2=                    '=256*pow(2,$(var_zoom))';
var_mc1=                    '=256*pow(2,($(var_zoom)-1))';
var_mid_lat=          '=min(latitude)+(1+var_lat_offset)*((max(latitude)-min(latitude))/2)';
var_mid_long=          '=min(longitude)+(max(longitude)-min(longitude))/2';
var_zoom=                    '=max(aggr(if(max( round(256*pow(2,(_zoom_level -1)))+( longitude  *((256*pow(2,_zoom_level ))/360)) )-min( round(256*pow(2,(_zoom_level -1)))+( longitude  *((256*pow(2,_zoom_level ))/360)) ) <map_size_x AND max((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))-min((256*pow(2,(_zoom_level-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,_zoom_level))/(2*pi()))))<map_size_y,_zoom_level,1),_zoom_level))';
var_maptype=          '=if(isnull(only(maptype)),fieldvalue( '&chr(39)&'maptype'&chr(39)&', 1 ),maptype)';
map_size_x=              '640';
map_size_y=     '400';
SET HidePrefix='_' ;
// Field required for calcualting best zoom level
_zoom_level:
Load RecNo( ) as _zoom_level autogenerate(max_zoom_level);
maptype:
LOAD * INLINE [
    maptype
    roadmap
          mobile
          satellite
          terrain
          hybrid 
];

[#8]

Thanks christophebrault . i really appreciate your help..yes i am having the map working but the only problem is longitude and latitude  are not connected to other fields of the same table..i dont know why i have customers with different postal codes and some with same postal codes...but when i clik on longitude aur latitude it takes me directly to the street view...i have one other sample application in which i enter the data manually for the longitude and latitude that application is working properly.any suggestions

[christophebrault]

I think i don't understand your issue.

You says that for the same Postal Code, you have different Long and lat ?

Finally, you geocode only with the PostalCode in your script ?

[#10]

yeah i have thing there is something wrong with my actual data as everything is running great with sample application...thanks a lot for your help christophebrault...have a nice day