# How get distance in degrees to calculate buffers in AWS Athena?

Athena only allows to calculate the distance of the buffer in decimal degrees but this value varies with respect to the latitude in the globe, tate to obtain a distance according to the following formula but it is not consistent in Mexico.

Athena function like this : ST_Buffer(geometry, double) Athena geospatial functions

So, is posible obtain the corresponding distance in decimal degrees over a custom point in map , ex : get the decimal degree for point x, y like that distance in meters is 300 mts

Currently I use the following formula to approximate the decimal degrees but some buffers are quite horrible although it meets the minimum required

`````` SELECT
ST_Buffer(ST_GeometryFromText( shape_wkt) ,
abs(5000.0 * 360.0 / (2.0 * pi() * cos( latitud )* 6400000.0) )   )   AS
dinamic_buffer_5000
``````
• 5000 is buffer in meters
• 6400000.0 earth radius in meters

Some useffull questions :

• Degrees are angular units; there is no direct distance equivalent (it's a partial differential equation, since the size of 1 degree longitude changes by latitude). You can approximate, but it will be bad-to-awful, depending on the angle of the points being measured. Commented Jul 14, 2022 at 16:42
• Thanks for your feedback Vince! Commented Jul 15, 2022 at 2:50

In another search within the presto documentation I find this gem that gives me good approximate results....

Supporting geospatial computation on different SRID

which formula is the following:

``````  SELECT ST_Buffer(ST_GeometryFromText( shape_wkt) , 5000.0 / (111195 * cos(radians(latitude)) )) AS dinamic_buffer_5000
``````

FORMULA :

``````DEGREE DISTANCE = DISTANCE METERS / (111195 * cos(radians(latitude)) )
``````

Googling the value 111195, I find that it is the radius of the earth expressed in meters with the following formula:

``````Earth radius 6371000*pi/180 = earth radius in meters
``````

Plots results : buffers slightly elliptical but consistent in shape, the further south the figure is elongated

A possible alternative is the following

To obtain the decimal degrees relative to a point one could:

1. Generate a second point at a distance d for this you would have to implement this formula, where the bearing does not matter
2. With this second point calculate the distance in Athena that will return the distance in decimal degrees, as input for the buffer function.

As an approximate is good alternative

Now how implement the second point ?....Here is the formula

I will try to convert to SQL code if can :

After a test I realize that even with the difference of distance it is not possible to obtain the buffer in an optimal way.

In this case the distance to the lower point was 300 meters, after obtaining the distance in decimal degrees with Athena an oblate shape is obtained, it changes the degree of inclination of the point by 90 degrees but it only generates a slightly larger shape.

Destination point given distance and bearing from start point

Source code (zory im edit for test my sql ):

``````destinationPoint(distance, bearing, radius=6371e3) {
// sinφ2 = sinφ1⋅cosδ + cosφ1⋅sinδ⋅cosθ
// tanΔλ = sinθ⋅sinδ⋅cosφ1 / cosδ−sinφ1⋅sinφ2
// see mathforum.org/library/drmath/view/52049.html for derivation

console.log("distance", distance);
console.log("bearing", Number(bearing));
console.log("bearing angulo ", angulo );

console.log("lon",this.lon);
console.log("lat",this.lat);
const sinφ2 = Math.sin(rad_lat) * Math.cos(dist_ang) + Math.cos(rad_lat) * Math.sin(dist_ang) * Math.cos(angulo);
const φ2 = Math.asin(sinφ2); //lat
console.log("φ2",φ2); //lat

console.log("sinφ2",sinφ2);
const y = Math.sin(angulo) * Math.sin(dist_ang) * Math.cos(rad_lat);
const x = Math.cos(dist_ang) - Math.sin(rad_lat) * sinφ2;

console.log("y",y);
console.log("x",x);
const λ2 = rad_lon + Math.atan2(y, x); //lon
console.log("λ2",λ2);
const lat = φ2.toDegrees();//lat
const lon = λ2.toDegrees();//lon
console.log("lon2",lon);
console.log("lat2",lat);
return new LatLonSpherical(lat, lon);
}
``````