How to unit test distance calculation beetween to points?

Question

I want to write unit test for this query

select a.id, b.id
from channel a, channel b
where ST_Distance_Spheroid(
    a.coordinates,
    b.coordinates,
    'SPHEROID["WGS 84",6378137,298.257223563]'
) <= 1000.0 and a.id != b.id

What this query does is it find all points that are near each other.

What I'm thinking is this: create three points with known distance to each other. Run the query and then I test if points that are closer then some threshold are returned and so on.

The problem is I don't know how to get points of known distance from each other.

What I've tried is go to google maps pick some points and extract their coordinates from url (in EPSG:4326?) format. Then with google maps measure distance beetween them.

I've added this points to the database and computed distance with ST_Distance_Spheroid and I had different results from what Google Maps measurements tells me (about 100 meters difference).

So I don't know who to trust Google Maps or Postgis.

Here is my test data from google maps in geojson format.

[
  {
    "coordinates": {
      "type": "Point",
      "coordinates": [
        37.6154764,
        55.7573887
      ]
    }
  },
  {
    "coordinates": {
      "type": "Point",
      "coordinates": [
        37.5790479,
        55.8075773
      ]
    }
  },
  {
    "coordinates": {
      "type": "Point",
      "coordinates": [
        37.6176973,
        55.7578234
      ]
    }
  }
]

And Google maps that the distance beetween the first and the third one is about 250 meters while the ST_Distance_Spheroid gives about 147 meters, which is significant difference for me.

Answer 1

Because your coordinate lon and lat are similar in magnitude your error is not as obvious, but I'm 100% sure that you're supplying either PostGIS or Google the coordinates in the wrong order. PostGIS wants its coordinates in longitude/latitude order. Google may want its coordinates in a different order. Make sure you're using the right one.

For example

select st_distance('POINT(37.6154764 55.7573887)'::geography,
                   'POINT(37.6176973 55.7578234)'::geography);
 st_distance  
--------------
 147.59498555
(1 row)

select st_distance('POINT(55.7573887 37.6154764)'::geography, 
                   'POINT(55.7578234 37.6176973)'::geography);
 st_distance  
--------------
 249.46582393
(1 row)