7

I am trying to figure out a way to convert the result of presto geo spatial function ST_DISTANCE to meters.

If I run the this example query: SELECT ST_Distance(ST_Point(0.3476, 32.5825),ST_Point(0.0512, 32.4637))

The result I get from Presto is: 0.3193217812802629. The actual distance between these two places is 40,000m.

The presto documentation states that ST_DISTANCE: Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units.

What I can understand about spatial ref is at links such as these: http://webhelp.esri.com/arcgiSDEsktop/9.3/index.cfm?TopicName=Defining_a_spatial_reference

Which leads me to believe I need to figure you what spatial-ref Presto is using.

If I check the prest docs here: https://github.com/prestodb/presto/blob/master/presto-geospatial/src/main/java/com/facebook/presto/geospatial/GeoFunctions.java

I can guess that is using the ESRI libraries so I assume the ESRI spatial ref? But that is where I get a bit lost as to where to proceed?

Thank you for your help..

user2293727
  • 71
  • 1
  • 4

2 Answers2

4

I would recommend using Presto’s great_circle_distance() function instead of ST_Distance(). It will interpret your coordinates as WGS84 (aka EPSG:4326), and find the distance between them in kilometres by treating the shape of the earth as a sphere.

ST_Distance() would be appropriate if the coordinate system being used was already projected into a system that used metres or miles or some other unit, but there's no trivial way to do that in Presto.

Brideau
  • 4,564
  • 4
  • 24
  • 33
0

From looking at the docs, it appears that presto supports a geometry type but not a geography type. That means it's not working with Latitude and Longitude, which is what I assume you're supplying as those point parameters. It's just an arbitrary 2D grid and so the resulting units are in whatever units you supplied as input.

The distance, in meters, between two points which are both approximately 32.5 meters "up" from the origin and about 0.5 meters "left" from the origin (how presto will have interpreted your points) is, indeed, 0.3193217812802629, the value that was returned to you.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448