3

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.

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)
  • If I would switch coordinates order the results are just plain wrong with difference in kilometers or so. So I think I copied google maps coordinates into this geojson in the right order. – user1685095 Nov 18 '15 at 10:37
  • See my edit above – Paul Ramsey Nov 18 '15 at 19:26

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.