Help

Formatting geo coordinates (split latitude/longitude) for Mapbox

Topic Labels: Formulas
Solved
Jump to Solution
1218 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Ricardo
7 - App Architect
7 - App Architect

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.

image

1 Solution

Accepted Solutions
Ricardo
7 - App Architect
7 - App Architect

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.

See Solution in Thread

4 Replies 4

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.

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.

Ricardo
7 - App Architect
7 - App Architect

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.