st_distance_sphere from PostGIS returns unexpected value?

1
votes

I am working with PostgreSQL v9.1.11 and PostGIS 2.1.0. I am using st_distance_sphere to calculate the distance between various points and am getting unusual results. According to the documentation, that function should return the distance in meters between the 2 points supplied. Here are two examples that I have run:

select st_distance_sphere(
  st_setsrid(st_makepoint(33.44, -82.60), 4326),  -- location near Warrenton, GA
  st_setsrid(st_makepoint(33.533, -82.517), 4326) -- locaton near Thomson, GA
)

which returns 9325.862 meters, or 5.8 miles, which seems correct

select st_distance_sphere(
  st_setsrid(st_makepoint(33.44, -82.60), 4326),  -- location near Warrenton, GA
  st_setsrid(st_makepoint(32.819, -97.361), 4326) -- location near Forth Worth, TX
)

which returns 9873.560 meters, or 6.1 miles, which ain't even close.

I have read and re-read the PostGIS docs and can't find anything I am doing wrong. Is that function just misbehaving?

2
postgresqlpostgis
Ask here gis.stackexchange.com - huckfinn

2 Answers

1
votes

Verifying your queries I've obtained the following error:

enter image description here

Looking back in the documentation we can see this:

ST_Distance_Sphere — Returns linear distance in meters between two lon/la points

That means the first coordinate for a point must be the longitude, and the second the latitude.

Therefore you'll have the following, properly, coordinates for your locations:

(-82.60, 33.44)   -- location near Warrenton, GA
(-82.517, 33.533  -- locaton near Thomson, GA
(-97.361, 32.819) -- location near Forth Worth, TX

Now, let's run the queries again:

select st_distance_sphere(
  st_setsrid(st_makepoint(-82.60, 33.44), 4326),  -- location near Warrenton, GA
  st_setsrid(st_makepoint(-82.517, 33.533), 4326) -- locaton near Thomson, GA
)

Result: 12891.3730143974 meters = 8.01 miles

select st_distance_sphere(
  st_setsrid(st_makepoint(-82.60, 33.44), 4326),  -- location near Warrenton, GA
  st_setsrid(st_makepoint(-97.361, 32.819), 4326) -- location near Forth Worth, TX
)

Result: 1375107.45231354 meters = 854.45 miles

0
votes

I found that using st_point and geometry is a way to do this:

select ST_Distance_Sphere(st_point(lng_1, lat_1)::geometry
                         ,st_point(lng_2, lat_2)::geometry) as distance

And worked for data stored at my DB, greetings!