0

Parsing formatted_address from Google Geocode JSON

 I am trying to extract the formatted_address field from the results of a Geo code query from Google maps. I have tried the following code without success.

 'JSON OUTPUT' := null;
let googlecoder := http("GET", "https://maps.googleapis.com/maps/api/geocode/json?latlng=37.249024,-80.01945599999999
&location_type=ROOFTOP&result_type=street_address&key=MYKEY");
if googlecoder.error then
      alert(text(googlecoder.error))
else
       'JSON OUTPUT' := text(googlecoder.result);
end

This dumps the entire JSON into my 'JSON OUTPUT' field.   This is the output:

{"plus_code":{"compound_code":"6XXJ+J6 Roanoke, VA, USA","global_code":"869X6XXJ+J6"},"results":[{"address_components":[{"long_name":"4235","short_name":"4235","types":["street_number"]},{"long_name":"Grandin Road Extension Southwest","short_name":"Grandin Rd Ext SW","types":["route"]},{"long_name":"Roanoke","short_name":"Roanoke","types":["locality","political"]},{"long_name":"Virginia","short_name":"VA","types":["administrative_area_level_1","political"]},{"long_name":"United States","short_name":"US","types":["country","political"]},{"long_name":"24018","short_name":"24018","types":["postal_code"]},{"long_name":"2053","short_name":"2053","types":["postal_code_suffix"]}],"formatted_address":"4235 Grandin Rd Ext SW, Roanoke, VA 24018, USA","geometry":{"location":{"lat":37.2482271,"lng":-80.02038879999999},"location_type":"ROOFTOP","viewport":{"northeast":{"lat":37.2495760802915,"lng":-80.01903981970848},"southwest":{"lat":37.24687811970851,"lng":-80.02173778029149}}},"place_id":"ChIJC7wM-m8LTYgROeEacqoFimY","plus_code":{"compound_code":"6XXH+7R Roanoke, Virginia, United States","global_code":"869X6XXH+7R"},"types":["street_address"]}],"status":"OK"}

 I have emboldened an underlined the field that I am trying to extract. 

 I then modified the code as follows:

 

'JSON OUTPUT' := null;
let googlecoder := http("GET", "https://maps.googleapis.com/maps/api/geocode/json?latlng=37.249024,-80.01945599999999
&location_type=ROOFTOP&result_type=street_address&key=MYKEY");
if googlecoder.error then
alert(text(googlecoder.error))
else
'JSON OUTPUT' := text(googlecoder.result.results.formatted_address);
end 

 

This does not return any results.

 I have exhaustively researched  on the Google website, stack overflow and everywhere else I can find on the Internet for a solution. I also researched the NINOX Manual and Have tried everything that was suggested. I hope somebody can help me with this issue. 

8 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

    Using your first example where you store the result like this...

     

    'JSON OUTPUT' := text(googlecoder.result);

     

    you add this line...

     

    item('JSON OUTPUT'.results, 0).formatted_address

    • Sean
    • 4 yrs ago
    • Reported - view

    ...After the if-then-else statement.

    • Sean
    • 4 yrs ago
    • Reported - view

    Ignore my second post, otherwise you would have to test for a valid response again.

    • Wireditpro
    • 4 yrs ago
    • Reported - view

    Thank you very much! I will give this a try . I was wondering where I can learn more about the "item" function that you show in your example? I looked for things like this in the NINOX manual and it doesn't show anything about this. If you could point me in the right direction, that would be awesome!

    • Wireditpro
    • 4 yrs ago
    • Reported - view

    You might be able to help me with another question. I have been trying to use a field that I store the latitude and longitude coordinates, and I want to be able to embed that in the URL so I don't have to type the coordinates every time. So another words I want to do this:

     

    http("GET", "https://maps.googleapis.com/maps/api/geocode/json?latlng=Latitude,Longitude
    &location_type=ROOFTOP&result_type=street_address&key=MYKEY")

    • Sean
    • 4 yrs ago
    • Reported - view

    The sparse information on the item() function can be found here...

     

    https://ninoxdb.de/en/manual/api/http-calls

    https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language

     

    I can't test this so I don't know if it will work, but you can try building the https string and assigning it to a variable and then put the variable in the function.

     

    let httpString := "https://maps.googleapis.com/maps/api/geocode/json?latlng=" + Latitude + "," + Longitude + "&location_type=ROOFTOP&result_type=street_address&key=MYKEY"

     

    Then use the function like this...

     

    http("GET", httpString)

     

    Is MYKEY just a placeholder in your example? Meaning, in your code do you use your actual key.

    • Wireditpro
    • 4 yrs ago
    • Reported - view

     When you’re creating a string idea, I wanted to let you know it works.  I figured it out about an hour after I posted this message. The mykey is a placeholder.  Thank you very much for the links. I would like to thank you again for all the help.  I was really stuck trying to extracting the formatted_address field.

    • Sean
    • 4 yrs ago
    • Reported - view

    You're welcome and thanks for letting me know you can use a variable in the http() function... that doesn't work in all parts of Ninox.

     

    I don't know if you have tried this, but it can be helpful in determining how to use the item() function. I pasted the JSON output you posted to a Formula field and exited the formula editor and went back into the formula editor. When I went back into it, the JSON text was nicely indented which made it easier to read. It is actually a multi-dimensional array and you can chain the item() function to get to the address components like this...

     

    item('JSON OUTPUT'.results, 0).item(address_components, 5).short_name + "-" + item('JSON OUTPUT'.results, 0).item(address_components, 6).short_name

     

    That builds the ZIP+4 zip code. You can also assign the second dimension of the array to a variable like this...

     

    let myAddress := item(myJSON.results, 0).address_components;
    item(myAddress, 0).short_name, etc.

     

    ...which would be less cumbersome if you used those components a lot.

Content aside

  • 4 yrs agoLast active
  • 8Replies
  • 2038Views