10 Replies Latest reply: Jan 16, 2013 11:29 AM by Jairo Martín Miguel RSS

    Reverse Geocoding and for...next loop with inner

      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




      then, I'm loading each address in one row


      37.5442706,-4.7277528CV-240, Montilla, Córdoba, SpainAPPROXIMATESpainAndalusiaCórdobaMontilla CV-240

      When the number of locations is greater than one, the load sentence into the loop generates many resident tables.


      One Location.JPG             Many Locations.JPG                     


      /////////////////// My code ////////////////////////



      load Location,



           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');



                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,


                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,


                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';




      // Load each address in one row


      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,




      /////////////////// My code ////////////////////////