# How to unit test distance calculation beetween to points?

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.

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. Nov 18 '15 at 10:37
• See my edit above Nov 18 '15 at 19:26