Skip to main content
Solved

Formatting geo coordinates (split latitude/longitude) for Mapbox

  • November 12, 2021
  • 4 replies
  • 98 views
Ricardo11
Forum|alt.badge.img+16

Hello community, I’m trying to average geographic coordinates.

First I split the coordinate into lat & lon fields, for example: 41.3310413 and 19.7828039

  • In Google Sheets I’m using average() with correct result
  • In Airtable the rollup with average() fails with NaN

Does anyone know another way of averaging coordinates?

PS: Also tried to remove the dot with SUBSTITUTE(Lat,".","",1) but am getting an error.

Best answer by Ricardo11

Solution and final setup:

  • Coordinates / text field: 40.096975653171484, 19.753827595322253
  • Lat / number field: IF(Coordinates,VALUE(TRIM(LEFT(Coordinates,FIND(",",Coordinates)-1))))
  • Lon / number field: IF(Coordinates,VALUE(TRIM(RIGHT(Coordinates,LEN(Coordinates)-FIND(",",Coordinates)))))

This enable the rollup with average(values), I’m using it with the Mapbox API to plot POIs on maps. The average of the POIs is needed to center the map.

4 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • November 12, 2021

Is Lat a number field?

    Ricardo11
    Forum|alt.badge.img+16
    • Ricardo11
    • Author
    • Inspiring
    • November 12, 2021

    Is Lat a number field?


    Good point, the original coordinates is a text field as otherwise it couldn’t store the 41.3310413,19.7828039 value. I believe because of this the consecutive formula split lat/lon fields can’t be recognized as numbers. Perhaps it’s also the dot, I tried to get around this with the substitute function but failed.

    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • November 12, 2021

    Good point, the original coordinates is a text field as otherwise it couldn’t store the 41.3310413,19.7828039 value. I believe because of this the consecutive formula split lat/lon fields can’t be recognized as numbers. Perhaps it’s also the dot, I tried to get around this with the substitute function but failed.


    Yeah, just make sure both your Lat and Long fields are number fields (or a formula field resulting in a number), and then you should be fine.

      Ricardo11
      Forum|alt.badge.img+16
      • Ricardo11
      • Author
      • Inspiring
      • Answer
      • November 13, 2021

      Solution and final setup:

      • Coordinates / text field: 40.096975653171484, 19.753827595322253
      • Lat / number field: IF(Coordinates,VALUE(TRIM(LEFT(Coordinates,FIND(",",Coordinates)-1))))
      • Lon / number field: IF(Coordinates,VALUE(TRIM(RIGHT(Coordinates,LEN(Coordinates)-FIND(",",Coordinates)))))

      This enable the rollup with average(values), I’m using it with the Mapbox API to plot POIs on maps. The average of the POIs is needed to center the map.

      Terms & ConditionsAccessibility statement