-1

I have two coordinates stored as geometry in a table. They are stored in column named latlng. Now the coordinates are:

1 31.54696470032501, 74.3782182429078

2 31.548894670525055, 74.37833017871426

If I calculate the distance using haversine formula it gives me 0.21Km If I calculate using st_distance it returns 0.00193321354170466 m

Which one should I suppose to be correct?

  • 1
    Does it really say "0.0019... m", or is the "m" an addition of yours? Because the value suggests in my opinion, that you ran st_distance based on a geographic coordinate-system instead of a projected one. Meaning, your "0.0019..." is in decimal degree. – Erik Aug 8 '18 at 6:58
  • yes I added m because this page says so postgis.net/docs/ST_Distance.html. If I am wrong please correct me. – mobileDeveloper Aug 8 '18 at 7:47
  • 1
    The first example in your link shows, what went "wrong". Your projection must be geographic, e.g. WGS 84. – Erik Aug 8 '18 at 7:57
  • With following query distance is still not what haversine formula gives "select st_distance( st_transform(st_setsrid(a.latlng,4326),24370), st_transform(st_setsrid(b.latlng,4326),24370) ) from location a, location b where a.location_id = 27348 and b.location_id = 27349;" st_distance ------------------ 84.6342793261745 – mobileDeveloper Aug 8 '18 at 8:09
  • 1
    PostGIS has spheroidal algebra implicitly packed into the geography type; projections are the exact opposite approach to this. try SELECT ST_Distance(a.latlng::geography, b.latlng::geography) FROM .... unlike with the Haversine, in PostGIS caring for the correct CRS of your data is of highest importance! you will be rewarded with highest precision if you do, though. – ThingumaBob Aug 8 '18 at 9:23
2

The first issue is that you are using latitude-longitude coordinates, while they should be expressed as longitude-latitude. If you swap them, you will get the proper result.

The second issue is that st_distance applied on geometries returns a distance using the underlying coordinate system unit. The coordinates are in degrees... so the length is also in degrees, and it doesn't make sense (the length - in meters - of a degree of latitude is not the same as a degree of longitude). In the comments, you transform the geometries to CRS 24310, whose unit is in yards.

Swapping the lat-long to long-lat and converting yards to meters, you get a distance of 215m.

Alternatively, you can do as @ThingumaBob has suggested, i.e. to cast to geography first (but you still need to swap the lat-long to long-lat)

WITH a AS (select st_makepoint(74.3782182429078,31.54696470032501)  geom),
      b AS (select st_makepoint(74.37833017871426,31.548894670525055)  geom)
select st_distance( st_transform(st_setsrid(a.geom,4326),24370), 
                    st_transform(st_setsrid(b.geom,4326),24370) 
                  ) * 0.9144 as d_trans,
ST_Distance(a.geom::geography, b.geom::geography) d_geog
from a,b;

    d_trans     | d_geog
-----------------+--------------
 215.90051305541 | 214.25682418
(1 row)
  • I am not using lat, long separately. rather I have a latlng column which stores the lat and lng as geometry. Do I need to swap their values ? – mobileDeveloper Aug 8 '18 at 13:59
  • 1
    @mobileDeveloper by swapping the coordinates, the found distance is the one you have mentioned (215m). If it is the proper distance, then yes, you need to swap them. – JGH Aug 8 '18 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.