11 Replies Latest reply on Apr 19, 2018 11:50 AM by Pavel_p

    Localising results in Google Places Autocomplete plugin (InSum)

    Scott Wesley

      I've experimented with InSum's Google Places Autocomplete Plugin

      https://github.com/insum-labs/apex-plugin-google-places-autocomplete

      which is a fairly simple wrapper for a google maps API.

       

      I wanted to localise the results, and it seems there are parameters allow you to do so

      https://developers.google.com/places/web-service/autocomplete#place_types

       

      I thought it may be a matter of changing this PL/SQL assignment

          l_js_params := '?key=' || l_api_key || '&libraries=places';
      

      to this

          l_js_params := '?key=' || l_api_key || '&libraries=places&location=-31,115&radius=50000';
      

       

      to get addresses within 50km of the given location, but I still get results from other countries, even if I also add the strictbounds parameter

       

       

      Any suggestions?

        • 1. Re: Localising results in Google Places Autocomplete plugin (InSum)
          Mahmoud_Rabie

          Hi Scott

           

          Did you try putting rewriting location pairs (  latitude,longitude)  like this

          &location=-31,76999,115,44696

           

          • 2. Re: Localising results in Google Places Autocomplete plugin (InSum)
            Scott Wesley

            My provided example already does so? I removed the decimal points for anonymity by rounding.

             

            Your example appears to use commas to represent decimal? Would that not confuse the parser?

             

            • 3. Re: Localising results in Google Places Autocomplete plugin (InSum)
              Pavel_p

              Hi Scott,

              I did some testing and according to the documentation

              Results outside of the defined area may still be displayed. You can use the components parameter to filter results to show only those places within a specified country.

              but the strictbounds parameter

              strictbounds — Returns only those places that are strictly within the region defined by location and radius.

              Well, I'm not sure if this parameter even works and if so, then it surely works in mysterious ways, so I just can confirm the behavior you already described. The only thing that worked for me to at least return results located in a given country was adding (for instance)

              components=country:uk

              Another strange thing I noticed is that the service is somehow "benevolent", so if I change the radius parameter to "radiussssss", the service still somehow processes the request.

              In my opinion a well-behaved web service should honestly crash and return 400 Bad Request instead of processing the request.

              Regards,

              Pavel

              1 person found this helpful
              • 4. Re: Localising results in Google Places Autocomplete plugin (InSum)
                Scott Wesley

                Thanks, Pavel.

                 

                I'm not sure why I didn't try the components=country:au option, but unfortunately that didn't work for me either.

                 

                I did have success modifying the jquery.ui.autoComplete.js file that comes with the plugin, adding the following just before the autocomplete.addListener call, inspired by this.

                    autocomplete.setComponentRestrictions(
                            {'country': ['au']});
                

                 

                But I would love for this to be parameterised. I also tried exploring other restriction options, but all raised errors (though I suspect a couple were WLS/ORDS not properly honouring my file server adjustments, but that's another issue)

                 

                I agree with the feedback, would be great to know when things are wrong.

                • 5. Re: Localising results in Google Places Autocomplete plugin (InSum)
                  Billy~Verreynne

                  Perhaps the Google AI has simply developed a twisted sense of humour after one to many call with flaky and wrong parameters, to this web service?

                   

                  Or ask pretty please like this granny did.

                   

                   

                  • 6. Re: Localising results in Google Places Autocomplete plugin (InSum)
                    Mahmoud_Rabie

                    Sorry Scott,

                     

                    Your example appears to use commas to represent decimal? Would that not confuse the parser?

                    I meant the following

                    &location=-31.76999,115.44696

                    • 7. Re: Localising results in Google Places Autocomplete plugin (InSum)
                      Pavel_p

                      Hi Scott,

                      I gave it some more testing and I must apologize - my previous post was entirely incorrect and misleading. I used SoapUI for testing (as always) and the previous post was based only on this testing. If I create a request according to the parameters you provided (attaching image because it's entirely unbelievable, only the API key was changed after the request)

                      maps_api_call.jpg

                      returned values are

                      {
                        "predictions":    [
                                  {
                              "description": "1 Smith Street, Brooklyn, NY, USA",
                              "id": "c2f5ca26adeb45eeb50ecdd024e86d53199d7ec0",
                              "matched_substrings":          [
                                              {
                                    "length": 1,
                                    "offset": 0
                                  },
                                              {
                                    "length": 5,
                                    "offset": 2
                                  }
                              ],
                              "place_id": "ChIJw6Ntc0xawokRXTJziLq6Mo8",
                              "reference": "CkQ5AAAA1kjYpNpaW06LguLihfuNpMAXg-EMO39ShnFaD-N74HkniZ0d_B1yRw4iug8HXYwYisX0Z9-ex5clsDzKbtduLxIQFK95nVSpSFsE1etY1Hs0BBoUA6uuQc4rZF8tRyqN0shIl-Lv8Pk",
                              "structured_formatting":          {
                                  "main_text": "1 Smith Street",
                                  "main_text_matched_substrings":            [
                                                    {
                                        "length": 1,
                                        "offset": 0
                                    },
                                                    {
                                        "length": 5,
                                        "offset": 2
                                    }
                                  ],
                                  "secondary_text": "Brooklyn, NY, USA"
                              },
                              "terms":          [
                                              {
                                    "offset": 0,
                                    "value": "1"
                                  },
                                              {
                                    "offset": 2,
                                    "value": "Smith Street"
                                  },
                                              {
                                    "offset": 16,
                                    "value": "Brooklyn"
                                  },
                                              {
                                    "offset": 26,
                                    "value": "NY"
                                  },
                                              {
                                    "offset": 30,
                                    "value": "USA"
                                  }
                              ],
                              "types":          [
                                  "street_address",
                                  "geocode"
                              ]
                            },
                                  {
                              "description": "1 Smith Street, Fitzroy VIC, Australia",
                              "id": "c6de86a56e10cbf238d8f2fca85044fe7f35a83f",
                              "matched_substrings":          [
                                              {
                                    "length": 1,
                                    "offset": 0
                                  },
                                              {
                                    "length": 5,
                                    "offset": 2
                                  }
                              ],
                              "place_id": "ChIJtxVab91C1moRDYouKxQaxmE",
                              "reference": "CkQ-AAAADtbyGFiiZhIMCqWsFLH48UNs1gMCqrE83Dy-YIhNbrgaC_-iRwJS3XYi6afixs2lPtfefS4J8X3zIgOnUEqCWhIQKZwxcMB0v32BOMMHu-gxFxoUfRGKvs7kKD5N84NIOMkKv4NKSr0",
                              "structured_formatting":          {
                                  "main_text": "1 Smith Street",
                                  "main_text_matched_substrings":            [
                                                    {
                                        "length": 1,
                                        "offset": 0
                                    },
                                                    {
                                        "length": 5,
                                        "offset": 2
                                    }
                                  ],
                                  "secondary_text": "Fitzroy VIC, Australia"
                              },
                              "terms":          [
                                              {
                                    "offset": 0,
                                    "value": "1"
                                  },
                                              {
                                    "offset": 2,
                                    "value": "Smith Street"
                                  },
                                              {
                                    "offset": 16,
                                    "value": "Fitzroy"
                                  },
                                              {
                                    "offset": 24,
                                    "value": "VIC"
                                  },
                                              {
                                    "offset": 29,
                                    "value": "Australia"
                                  }
                              ],
                              "types":          [
                                  "street_address",
                                  "geocode"
                              ]
                            },
                                  {
                              "description": "1 West Smithfield, London, UK",
                              "id": "07b42b78b3820966a67d36b34517dd9602bb3758",
                              "matched_substrings":          [
                                              {
                                    "length": 1,
                                    "offset": 0
                                  },
                                              {
                                    "length": 5,
                                    "offset": 7
                                  }
                              ],
                              "place_id": "ChIJ-99RsFMbdkgRIo-9MjyVDjg",
                              "reference": "CkQ1AAAAKmwSnXSUCkYjWF6zq3FNwkUZU7BnsedUfWfHcG0SbyN1LAfOv8hJz6_SrmIu3RTSo2bvu6WLK45jSo2pOZwZUxIQt0EMEDSGP08TbpIc0UYulBoUxVtmgpgCzmHG4ITI6yfAtLLkHyc",
                              "structured_formatting":          {
                                  "main_text": "1 West Smithfield",
                                  "main_text_matched_substrings":            [
                                                    {
                                        "length": 1,
                                        "offset": 0
                                    },
                                                    {
                                        "length": 5,
                                        "offset": 7
                                    }
                                  ],
                                  "secondary_text": "London, UK"
                              },
                              "terms":          [
                                              {
                                    "offset": 0,
                                    "value": "1"
                                  },
                                              {
                                    "offset": 2,
                                    "value": "West Smithfield"
                                  },
                                              {
                                    "offset": 19,
                                    "value": "London"
                                  },
                                              {
                                    "offset": 27,
                                    "value": "UK"
                                  }
                              ],
                              "types":          [
                                  "street_address",
                                  "geocode"
                              ]
                            },
                                  {
                              "description": "1 South Smith Boulevard, Arlington, VA, USA",
                              "id": "4392911d18414413116876df0e534491d0908a17",
                              "matched_substrings":          [
                                              {
                                    "length": 1,
                                    "offset": 0
                                  },
                                              {
                                    "length": 5,
                                    "offset": 8
                                  }
                              ],
                              "place_id": "ChIJE5e0CjG3t4kR50iuSUOfsC4",
                              "reference": "ClRDAAAADg92RMAqBQQUGQZUYdgTIV7JRLSZaEMpQ9UBn6kbVx5N4BeAIBYjGRclzUfrQjq_g-hrLkmYufxxr8Uq1zpe97sCP2eORWHGter-LwXdjEISEDwU8EYEnVhSBPCNKLRPj_waFLBb3hKGfkCaGSiEKnMdxBgaQJml",
                              "structured_formatting":          {
                                  "main_text": "1 South Smith Boulevard",
                                  "main_text_matched_substrings":            [
                                                    {
                                        "length": 1,
                                        "offset": 0
                                    },
                                                    {
                                        "length": 5,
                                        "offset": 8
                                    }
                                  ],
                                  "secondary_text": "Arlington, VA, USA"
                              },
                              "terms":          [
                                              {
                                    "offset": 0,
                                    "value": "1"
                                  },
                                              {
                                    "offset": 2,
                                    "value": "South Smith Boulevard"
                                  },
                                              {
                                    "offset": 25,
                                    "value": "Arlington"
                                  },
                                              {
                                    "offset": 36,
                                    "value": "VA"
                                  },
                                              {
                                    "offset": 40,
                                    "value": "USA"
                                  }
                              ],
                              "types":          [
                                  "street_address",
                                  "geocode"
                              ]
                            },
                                  {
                              "description": "1 Smith Road, Kensington, Germiston, South Africa",
                              "id": "b585cabf7d60171dcff1eb9b7b717fb3d32ec45b",
                              "matched_substrings":          [
                                              {
                                    "length": 1,
                                    "offset": 0
                                  },
                                              {
                                    "length": 5,
                                    "offset": 2
                                  }
                              ],
                              "place_id": "ChIJg7veaf4RlR4RAC16b7DE7zI",
                              "reference": "ClRJAAAAPuqrKrHsTtArTUMdR_Cm--GYPQshMqdUwIgWzdpCshy34icVP-qxxX7BrTXRIi0mq-VIzbcL9qSpXj2TjBTHyqndE4dO0lauVofoH_TgVgcSEFmxiHkz-woZjBXv8yx2Fq0aFNeYm1qRMOW7gFwOdH9EYeKcs_QE",
                              "structured_formatting":          {
                                  "main_text": "1 Smith Road",
                                  "main_text_matched_substrings":            [
                                                    {
                                        "length": 1,
                                        "offset": 0
                                    },
                                                    {
                                        "length": 5,
                                        "offset": 2
                                    }
                                  ],
                                  "secondary_text": "Kensington, Germiston, South Africa"
                              },
                              "terms":          [
                                              {
                                    "offset": 0,
                                    "value": "1"
                                  },
                                              {
                                    "offset": 2,
                                    "value": "Smith Road"
                                  },
                                              {
                                    "offset": 14,
                                    "value": "Kensington"
                                  },
                                              {
                                    "offset": 26,
                                    "value": "Germiston"
                                  },
                                              {
                                    "offset": 37,
                                    "value": "South Africa"
                                  }
                              ],
                              "types":          [
                                  "street_address",
                                  "geocode"
                              ]
                            }
                        ],
                        "status": "OK"
                      }
                      

                      So I came to conclusion that the parameter does not probably work. Then I tried to add another parameter (as Billy Verreynne suggested) just for fun ask_politely=please,thanks and ran the service with the same result. This started to be really suspicious because something was telling me that the guys at Google can write REST APIs, so just for verification I copied the generated URL (please, try the link yourself, just replace the API key)

                      https://maps.googleapis.com/maps/api/place/autocomplete/json?input=1%20Smith&location=-31,115&radius=50000&strictbounds&…

                      to clipboard and pasted it to the URL window in FF browser. And ups, I (quite surprisingly) received an entirely different JSON

                      {
                         "predictions" : [
                            {
                               "description" : "1 Smith Court, Yanchep WA, Australia",
                               "id" : "0fd4492b0071e22bb9f36eb9ecad21ecf4d5084a",
                               "matched_substrings" : [
                                  {
                                     "length" : 7,
                                     "offset" : 0
                                  }
                               ],
                               "place_id" : "EiQxIFNtaXRoIENvdXJ0LCBZYW5jaGVwIFdBLCBBdXN0cmFsaWE",
                               "reference" : "CmRSAAAAgf5hTvth2e7B4JPvheOIBzXKFbi3syUas5jRCfBy1Wp_guyPgC03b6rFDj0kpJDPtCCy-hTtszDd_pNi4N8T6jIJ832zt-WfGNfcMRI2u7mYKn1ZSgYV88_fsJ5vWBD7EhCgKXzqL4bHzXY6ZYfjV2EvGhSvxq3YEivEmI1FrJU59MhZAFl7Mw",
                               "structured_formatting" : {
                                  "main_text" : "1 Smith Court",
                                  "main_text_matched_substrings" : [
                                     {
                                        "length" : 7,
                                        "offset" : 0
                                     }
                                  ],
                                  "secondary_text" : "Yanchep WA, Australia"
                               },
                               "terms" : [
                                  {
                                     "offset" : 0,
                                     "value" : "1 Smith Court"
                                  },
                                  {
                                     "offset" : 15,
                                     "value" : "Yanchep"
                                  },
                                  {
                                     "offset" : 23,
                                     "value" : "WA"
                                  },
                                  {
                                     "offset" : 27,
                                     "value" : "Australia"
                                  }
                               ],
                               "types" : [ "route", "geocode" ]
                            }
                         ],
                         "status" : "OK"
                      }
                      

                      which is probably the expected result and so far I have no idea at all why otherwise 100% reliable SoapUI gave me such confusing results and what's wrong with the plugin (so far I'm not able to setup either a wallet or a reverse httpd proxy).

                      Since the plugin obviously works for you (somehow), it makes me think that you successfully imported the site's certificate into the wallet and I would be really grateful if you could share the steps how you did it.

                      Thanks,

                      Pavel

                      • 8. Re: Localising results in Google Places Autocomplete plugin (InSum)
                        Mahmoud_Rabie

                        Hi Scott

                         

                        But I would love for this to be parameterised.

                         

                        I have added a custom component attribute:

                        Restricted Countries (JSON)

                        to the plugin to restrict the searched countries.

                        In addition, I have updated the JS file.

                         

                        For example, this is the setting of the first autocomplete in the below demo

                         

                        https://apex.oracle.com/pls/apex/f?p=147423

                        test/test

                         

                        If it solves your problem, tell me to share the details and code with you.

                         

                        Regards

                        Mahmoud

                        • 9. Re: Localising results in Google Places Autocomplete plugin (InSum)
                          Mahmoud_Rabie

                          Scott Wesley ,

                           

                          Did the previous solution work with you?

                           

                          Regards

                          Mahmoud

                          • 10. Re: Localising results in Google Places Autocomplete plugin (InSum)
                            Scott Wesley

                            Mahmoud - I had the location in pairs in the OP, I just removed decimal for anonymity.

                            &location=-31,115

                             

                            Pavel - I have a great network engineer that handles anything related to "wallet", "certificate", or "proxy". I'm not sure what he's done.

                             

                            Mahmoud - that would be great, I'm not familiar enough with plugins to know how you might have coupled the component attribute with the .js file code.

                            We just need to turn restriction on/off, for site vs client address.

                             

                            That may help me with another problem.

                            • 11. Re: Localising results in Google Places Autocomplete plugin (InSum)
                              Pavel_p

                              Scott, just to modify a bit my response from your other thread, this code

                              declare
                                l_resp            clob;
                                l_parm_names      apex_application_global.vc_arr2;
                                l_parm_values      apex_application_global.vc_arr2;
                              
                                --json values
                                l_values          apex_json.t_values;
                                l_prediction_count number;
                              begin
                                --set request parameters
                                l_parm_names(1) := 'key';
                                l_parm_values(1) := 'YOUR_API_KEY';
                                l_parm_names(2) := 'input';
                                l_parm_values(2) := '1 smith';
                                l_parm_names(3) := 'location';
                                l_parm_values(3) := '-31,115';
                                l_parm_names(4) := 'radius';
                                l_parm_values(4) := '50000';
                                --specify other parameters accordingly...
                              
                                l_resp := apex_web_service.make_rest_request(p_url => 'https://maps.googleapis.com/maps/api/place/autocomplete/json',
                                p_http_method => 'GET',
                                p_parm_name => l_parm_names,
                                p_parm_value => l_parm_values,
                                p_wallet_path => 'file://u01/app/oracle/product/12.2/db_1/owm/wallets/oracle',
                                p_wallet_pwd => 'wallet_pwd'
                                --,p_https_host => p/*VARCHAR2*/
                                );
                              
                                if apex_web_service.g_status_code = 200 then --200 = OK
                                  dbms_output.put_line('Service called successfully');
                                  --dbms_output.put_line(l_resp);
                              
                                  --parse the response clob
                                  apex_json.parse(
                                  p_values => l_values,
                                  p_source => l_resp
                                  );
                              
                                  dbms_output.put_line('Response status: ' ||
                                  apex_json.get_varchar2(
                                    p_values => l_values,
                                    p_path => 'status')
                                  );
                              
                                  l_prediction_count := apex_json.get_count(p_path => 'predictions', p_values => l_values);
                                  dbms_output.put_line('Service returned ' || l_prediction_count || ' results');
                                  for i in 1 .. l_prediction_count loop
                                    dbms_output.put_line(apex_json.get_varchar2(
                                    p_values => l_values,
                                    p_path => 'predictions[' || i || '].description'));
                              
                                    --do something reasonable with returned suggestions like store them into APEX collection...
                                  end loop;
                                else
                                  raise_application_error(-20000, 'Web service call failed with code ' || apex_web_service.g_status_code);
                                end if;
                              end;
                              

                              returns this

                              Service called successfully
                              Response status: OK
                              Service returned 5 results
                              1 Smith Street, Perth WA, Australia
                              1 Smith Street, Dianella WA, Australia
                              1 Smiths Avenue, Redcliffe WA, Australia
                              1 Smith Street, Mosman Park WA, Australia
                              1 Smith Street, Marybrook WA, Australia
                              

                              which is hopefully what you want and need. I can't help myself but in this particular case I would rather apply the DIY principle and ditch the plugin.