Diffrence in result from PostgreSQL (PostGIS) and SQL Server (Spatial)

Question

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?

Answer 1

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  ;