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

Question

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 :

• gps-coordinates-in-degrees-to-calculate-distances

• Calculate distance in meters using results in degrees

• calculating-latitude-longitude-x-miles-from-point

Answer 1

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

Answer 2

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

    const dist_ang = distance / radius; // angular distance in radians
    const angulo = Number(bearing).toRadians();

    const rad_lat = this.lat.toRadians();
    const rad_lon = this.lon.toRadians(); 
    console.log("distance", distance);
    console.log("radius", radius);
    console.log("angular distance in radians", dist_ang);
    console.log("bearing", Number(bearing));
    console.log("bearing angulo ", angulo );
    
    console.log("lat.toRadians", rad_lat);
    console.log("lon.toRadians", rad_lon); 
    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);
}