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

       

      LatlongProximityAddress_TypeValue
      37.5442706,-4.7277528APPROXIMATEadministrative_area_level_1Andalusia
      37.5442706,-4.7277528APPROXIMATEadministrative_area_level_2Córdoba
      37.5442706,-4.7277528APPROXIMATEcountrySpain
      37.5442706,-4.7277528APPROXIMATElocalityMontilla
      37.5442706,-4.7277528APPROXIMATErouteCV-240

       

      then, I'm loading each address in one row

       

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

       

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