Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi friends,
I'm trying to get the physical address for my longitude and latitude values. When I use my code with only one location it's working fine. The problem comes when I want to use this code into a For...Next loop. In this case, the row calculated in each interation doesn't go to the same resident table (when I do this in other contexts, the rows calculated in each interation go to the same resident table like an append). How can I do this???
I'm loading my locations from xlsx file. For each of these locations I'm getting the different parts of the address
Latlong | Proximity | Address_Type | Value |
37.5442706,-4.7277528 | APPROXIMATE | administrative_area_level_1 | Andalusia |
37.5442706,-4.7277528 | APPROXIMATE | administrative_area_level_2 | Córdoba |
37.5442706,-4.7277528 | APPROXIMATE | country | Spain |
37.5442706,-4.7277528 | APPROXIMATE | locality | Montilla |
37.5442706,-4.7277528 | APPROXIMATE | route | CV-240 |
then, I'm loading each address in one row
Latlong | Address | Proximity | Country | Area1 | Area2 | Locality | Postal_Code | Route | Street_number |
37.5442706,-4.7277528 | CV-240, Montilla, Córdoba, Spain | APPROXIMATE | Spain | Andalusia | Córdoba | Montilla | CV-240 |
When the number of locations is greater than one, the load sentence into the loop generates many resident tables.
/////////////////// My code ////////////////////////
Location:
load Location,
Longitude,
Latitude,
Latitude&','&Longitude as Latlong
from Locations.xlsx (ooxml, embedded labels, table is Sheet1);
for i = 0 to NoOfRows('Location') -1
let v_latlong = peek('Latlong', $(i), 'Location');
Aux:
first 1 // There are several results, but we only want the first one
load '$(v_latlong)' as Latlong,
formatted_address as Address,
[geometry/location_type] as Proximity,
%Key_result_93EFE47D0E261AF3
from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result]);
inner join
load %Key_result_93EFE47D0E261AF3,
long_name as Value,
%Key_address_component_3D78D9B402C68EA5
from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result/address_component]);
inner join
load type%Table as Address_Type,
%Key_address_component_3D78D9B402C68EA5 as %Key_address_component_3D78D9B402C68EA5
from [http://maps.google.com/maps/api/geocode/xml?latlng=$(v_latlong)&oe=utf8&sensor=false] (XmlSimple, Table is [GeocodeResponse/result/address_component/type])
where type%Table <> 'political';
next
// Load each address in one row
Result:
load Latlong as aLatlong,
Address as aAddress,
Proximity as aProximity,
Max (if(Address_Type = 'street_number' , Value)) as Street_number,
MaxString(if(Address_Type = 'route' , Value)) as Route,
Max (if(Address_Type = 'postal_code' , Value)) as Postal_Code,
MaxString(if(Address_Type = 'locality' , Value)) as Locality,
MaxString(if(Address_Type = 'administrative_area_level_2', Value)) as Area2,
MaxString(if(Address_Type = 'administrative_area_level_1', Value)) as Area1,
MaxString(if(Address_Type = 'country' , Value)) as Country
resident Aux
group by Latlong,
Address,
Proximity;
/////////////////// My code ////////////////////////
It works perfect. Thaks jonbroughavone and rperezlaso.