PostgreSQL vs SQL Server – Differences in Spatial Query Results

postgispostgresqlspatialsql server

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?

Best Answer

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  ;

Related Solutions

Postgresql – Problem with shortest distance calculation in PostGIS

Maybe this will help start you out but here is how I am thinking through this.

I can't think of any case, re spherical trig where two straight lines would be closer in the middle than at the edges, unless they cross so I think you can narrow your search down to the vertices of each road plus the nearest point to that vertex on the other road. I am assuming that your roads are modelled as line segments along great circles.

So I don't think that this is a complete solution but, what I would be looking at doing is:

Do these lines intersect? If you assume that all intersections are listed as such in your db, you can assume no.
If so that's the closest point.
If not, then grab the vertices on both roads, draw any lines perpendicular to another line segment on the other, and calculate distance to intersection. Your shortest point will be the minimum of these lines.

That seems the best answer. You have to calculate points. I don;t know of any magic way to do this in PostGIS but it is something where spherical trig may come to the rescue. If the distances are sufficiently small, plane trig may be sufficient as an approximation, of course.

PostGIS – Removing Latitude and Longitude from Table

The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input:

SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need
FROM   addresses a
JOIN   cafes     c ON c.id = a.cafe_id
    , (SELECT lonlat
       FROM   addresses
       WHERE  cafe_id = 10
       AND    type = 'SearchAddress') t -- center cafe
WHERE  ST_DWithin(t.lonlat, a.lonlat, 4828)
ORDER  BY distance;

Table alias was missing: cafes c.
Makes no sense to have a.cafe_id, c.id in the SELECT list, since those are the same by definition. Pick the columns you actually need.
The columns type or state shouldn't be type text (or character varying(255)), I suggest an enum for a hand full of types or a adr_type look-up table and adr_type_id int REFERENCES adr_type (adr_type_Id in addresses.
Either way, I also suggest a partial, covering index for a faster look-up of the center coordinate. Building on "There can only be one of those addresses with that type.":
```
CREATE addresses_search_idx ON addresses (cafe_id, lonlat)
WHERE type = 'SearchAddress';
```
A lot more details discussed in chat (don't know for how long the link will be good).

Best Answer

Related Solutions

Postgresql – Problem with shortest distance calculation in PostGIS

PostGIS – Removing Latitude and Longitude from Table

Related Question