1

I have two points and I have to calculate difference between them.

I using below query in SQL Server.

SELECT geography::Point(27.185425, 88.124582, 4326)
       .STDistance(geography::Point(27.1854258, 88.124500, 4326));

Its running fine and result is :

8.12599260290125

Now with same point when I am running below query in PostgreSQL.

SELECT ST_Distance(ST_GeomFromText('POINT(27.185425 88.124582)',4326),
                   ST_GeomFromText('POINT(27.1854258 88.124500)', 4326));

and Now result is :

8.20039023523232

Can anyone tell me why there is difference in results when calculating distance between two points?

  • You shouldn't put points in WKT in PostGIS. You should construct points using ST_MakePoint(lon,lat)::geography – Evan Carroll Nov 8 '17 at 3:40
5

A couple of things that have caught you out here.

In the SQL Server version using the Point, the order of the coordinate is Lat Lon, eg Y X. The OGC Point construct is X Y or Lon Lat.

The next is that the PostGIS query you've posted actually returns 8.20039023523232e-005 which while appearing similar to the SQL Server result, is quite a lot smaller.

The last issue is that the PostGIS query is treating the objects as Geometries and doing a cartesian distance on them. You need to explicitly make them Geographies.

Here's a few ways to do the PostGIS query. I've swapped the coordinate order

SELECT ST_Distance(ST_GeomFromText('POINT(88.124582 27.185425)',4326)::geography,
                   ST_GeomFromText('POINT(88.124500 27.1854258)',4326)::geography);

SELECT ST_Distance(ST_GeogFromText('SRID=4326;POINT(88.124582 27.185425)'),
                   ST_GeogFromText('SRID=4326;POINT(88.124500 27.1854258)'));

-- Adapted from the PostGIS Manual
SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist 
FROM (SELECT
    ST_GeogFromText('SRID=4326;POINT(88.124582 27.185425)') As gg1,
    ST_GeogFromText('SRID=4326;POINT(88.124500 27.1854258)') As gg2
    ) As foo  ; 
  • You shouldn't put points in WKT. You should construct points using ST_MakePoint(lon,lat)::geography – Evan Carroll Nov 8 '17 at 3:39

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.