Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Formatting geo coordinates (split latitude/longitude) for Mapbox

Topic Labels: Formulas
Solved
398 4
cancel
Showing results for
Did you mean:
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.

1 Solution

Accepted Solutions
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.

4 Replies 4
18 - Pluto

Is Lat a number field?

7 - App Architect

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.

18 - Pluto

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.

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.