Show the address of a GPS position

Is there a way to show the nearest approximate address, instead of the map, in a LATLONG field?
Or have a table with several specific addresses and detect when it is within a certain radius and be able to show in a text field “the place” of the table of which it is closest?
How could I do it?
Thank you so much

0 15 4,721
  • UX
15 REPLIES 15

Jonathon
Participant V

Yes; you can create a related locations table with location names or address, and a lat/long for each location. Then you may use the devices Latlong and DISTANCE() formula to select the closest location from the list of locations.

MultiTech
Participant V

Unfortunately there is no address geocoding available. About a year ago or so I had a client that needed something like that - and unless you want to go the route @Jonathon was talking about, Appsheet would need to add functionality to the platform in order to find the closest address to a geopoint.

I remember a lengthy email string with @Adam about this; any chances this might be something they’ll add know that AppSheet is part of Google cloud?

Bahbus
Participant V

You could try to figure out how to do something with this maybe? https://developers.google.com/maps/documentation/geocoding/intro#ReverseGeocoding

You can create a script that would run in the background, using your own Google credits (meaning you’ll have to create the GCP account and pay for the geocoding - unless your usage doesn’t crest a certain threshold, then I think it’s free up to a certain point.

But yes, you can create two fields

  1. for the lat/long, and
  2. for the geocoded address.

The lat/long comes from the app, then once that is submitted to the back end (a key thing here) the script runs the lat/long through your GCP geocoding and populates it into the second column.


The problem here is that there is no “in-app” way to accomplish this. The best scenario would be the following:

  1. User inputs lat/long
  2. user syncs their app data to the cloud
  3. the cloud geocodes the lat/long
  4. the user re-syncs their app to grab the result.

In practice, the geocoding system can lag sometimes and when the user hits the second sync - to get the address data - sometimes you’ll have to hit it twice (where the first sync happened before the geocoding could finish).

yuvalsh
Participant I

Hi, could we get it the opposite way?
Can we get the coordinate of an address type cell and fill it into a lat long or text type cell?

Unfortunately the answer I gave above still stands - as far as I know.

If you are on google sheet as backend data source, then you need to write GAS to convert address into latlong. When we search on google, bunch of sample code should be available, luckily.
But unluckily, there is no other easier way should be available such as native spreadsheet nor appsheet function to achieve the same.

@MultiTech_Visions

but Appsheet can show a map of address markers,
how do they achieve it? don’t they have the coordinates results in their db?
see here : “Once your addresses are geo-coded, AppSheet caches the results on our servers to avoid the cost of geo-coding the addresses again.”

Valid point, but just there is no mechanism currently available to capture that latong and dump somewhere within Appsheet.
Actually, I raised feature request before.

OK, Thanks:)
I use the app sheet data on external map and thought to spare the time this way…
Anyway i found this app script simple and implemented it very easily for my needs (for now, with no QA). [just the app script part , nothing about looker]

MultiTech
Participant V

See here:

I think there’s a formula in google spreadsheets you can use to get a place’s name based on a search query. I’ll go to investigate that and I’ll show you the formula

Mike_T
Participant V

Here is a script I found that will reverse geocode from the long/lat back to the nearest address. You will have to split the long and lat in your spreadsheet and then add a helper column to do the reversegeocode:

Someone else posted the link:

Down the page you will find a list of “types” of address. In my situation I just want the state code so I replaced “locality” in the cell formula with “administrative_area_level_1”

I do think you will run into Google’s 1,000 geocode limit if you try this with too many codes. I’m hoping this is a daily limit and not lifetime

I believe Google’s limits are, in fact, daily.

Here is the code I mentioned, it can be used to get the latitude and longitude of a place based on a written address. Just put the code in your google appscript editor and use it in your cells like:

=geocode_google(“SAN PEDRO SULA HONDURAS”)

/**
     * Returns latitude and longitude values for given address using the Google Maps Geocoder.
     *
     * @param {string} address - The address you get the latitude and longitude for.
     * @customfunction
     */
    function GEOCODE_GOOGLE(address) {
        if (address.map) {
            return address.map(GEOCODE_GOOGLE)
        } else {
            var r = Maps.newGeocoder().geocode(address)
            for (var i = 0; i < r.results.length; i++) {
                var res = r.results[i]
                return res.geometry.location.lat + ", " + res.geometry.location.lng
            }
        }
    }

    /**
     * Returns latitude and longitude values for given address using the Yandex Geocoder.
     *
     * @param {string} address - The address you get the latitude and longitude for.
     * @customfunction
     */
    function GEOCODE_YANDEX(address) {
        if (address.map) {
            return address.map(GEOCODE_YANDEX)
        } else {
            input = encodeURI(address)
            var r = UrlFetchApp.fetch(
                "https://geocode-maps.yandex.ru/1.x/?format=json&geocode=" +
                input + "&results=1&lang=en-US", {
                    "method": "get"
                })
            var res = JSON.parse(r)
            try {
                res = res.response.GeoObjectCollection.featureMember[0].GeoObject.Point.pos
                res = res.split(" ")[1] + ", " + res.split(" ")[0]
                return res
            } catch (e) {
                return ""
            }
        }
    }
Top Labels in this Space