use geospark sql to calculate the distance between two points

Question

I use geospark sql to calculate the distance between two points

like this:

sql("""
 SELECT ST_Distance(ST_Transform(ST_GeomFromText('Point (102.756444 25.038064 )'),"epsg:4236","epsg:3857"),ST_Transform(ST_GeomFromText('Point (102.745025 25.040838 )'),"epsg:4236","epsg:3857"))
""").show(false)

the result is 1316m

but I use postgresql get a different result

select ST_Distance(ST_GeographyFromText('POINT (102.756444 25.038064)
'),ST_GeographyFromText('Point (102.745025 25.040838)')) 

the result is 1192m

Which one is right? how to use geospark sql to get a correct result?

Answer 1

For geometry types returns the minimum 2D Cartesian (planar) distance between two geometries, in projected units (spatial ref units).postgis is geography , need use corrected by cos(lat) to account for distortion like this

sql("""
 SELECT ST_Distance(ST_Transform(ST_GeomFromText('Point (102.756444 25.038064 )'),"epsg:4236","epsg:3857"),ST_Transform(ST_GeomFromText('Point (102.745025 25.040838 )'),"epsg:4236","epsg:3857"))*cos(25.038064*3.1415926/180)
""").show(false)