Reverse Geocoding and for...next loop with inner

[#1]

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 ////////////////////////

[#2]

It works perfect. Thaks jonbroughavone and rperezlaso.