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

[Anonymous]

Create a placeholder for the Aux table before the For loop starts, then concatenate into it.

Jonathan

[#3]

Thanks for the answer jonbroughavone but I don't know what is a placeholder. Could you explain me how to use it???

[Anonymous]

Sorry, think that might just be my name for it.

Create a blank Aux table, using an inline statement like this:

Aux:

LOAD * INLINE [

   Latlong

];

Create with now rows, and you don't have to include all the field names (in fact it would be better not to in case you change them later).

You can then concatenate into this table instead of needing to create it every loop. This would be with this sort of syntax:

CONCATENATE (Aux) LOAD

....

This should stop the multiple tables appearing at least.

Jonathan

[#5]

This solution stops the multiple tables appearing, but the table Aux is empty. The implementation is in the attached file.

[Anonymous]

Aux table has 7 rows in the file you sent.

Jonathan

[#7]

Aux table has 0 rows in the file (7 fields).

[Anonymous]

Strange. Are you sure you sent me the same file.

See Document settings > Tables:

See the preview of the table viewer:

[#9]

Ok jonbroughavone. The file is the same, but the data loaded no. The example I've set you (like my first attached file) works fine with only one location. If you reload the data you could see the problem. Sorry for my explanations and thanks for your answers.

[rperezlaso · Accepted solution]

Try this code please, this is the solution:

Location:
 load Location,
 Longitude, 
 Latitude,
 Latitude&','&Longitude as Latlong
 from Locations.xlsx (ooxml, embedded labels, table is Sheet1);
 
 Table:
 load * Inline [Latlong];
 
 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';
 
 Concatenate(Table)
 Load *
 resident Aux;
 
 drop table Aux;
 
 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 Table
 group by Latlong,
 Address,
 Proximity;