Postgres returning in-correct results with ST_DWithin()

From: Jyoti Leeka <leekajyoti(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Postgres returning in-correct results with ST_DWithin()
Date: 2017-01-02 10:02:45
Message-ID: CAPaVtGetfcChZ_NyHYQza6zNTp+K+stqF81K96VkseaY4kkTVQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I created a table containing locations along with their spatial coordinates
expressed in (latitude, longitude (in degrees)) in postgres. Commands which
I used for the same are:

create table spatialTest(name character varying(100), the_geo
geography);

\copy spatialTest(name,the_geo) FROM 'testSpatial.csv' DELIMITERS E'\t'
CSV HEADER;

testSpatial.csv contains the following values:

A SRID=4326;POLYGON((2.469674 -126.401421,2.468683
-126.401096,2.468221 -126.401230,2.466333 -126.401485,2.465378
-126.401701,2.463752 -126.401963,2.463043 -126.402638,2.463816
-126.402829,2.464138 -126.403007,2.464224 -126.403237,2.463494
-126.403440,2.463280 -126.403682,2.462743 -126.403542,2.462475
-126.403446,2.462250 -126.404465,2.462368 -126.404898,2.463172
-126.405707,2.463848 -126.406070,2.464449 -126.405496,2.464771
-126.405248,2.465822 -126.405274,2.466970 -126.405095,2.468730
-126.404261,2.469642 -126.403344,2.470822 -126.402790,2.471101
-126.402555,2.470168 -126.401714,2.469674 -126.401421))
B SRID=4326;POINT(2.336691 -126.846931)

Now I want to find all spatial locations which are within a distance of 50
km of each other. For doing so I used the following command:

select s1.name, s2.name
from spatialTest s1,
spatialTest s2
where ST_DWithin(s1.the_geo, s2.the_geo, 50000);

However, to my surprise I found that although A and B are separated from
each other by a distance greater than 50 km (50.995 km to be precise. Found
using Chris Veness's geodesy formulae​ (
http://stackoverflow.com/questions/27461634/calculate-distance-between-a-point-and-a-line-segment-in-latitude-and-longitude))
yet they are returned by postgres as results. Can someone please help me
figure out as to where am I going wrong.

I am using PostgreSQL 9.6devel and Postgis version which I am using is:
POSTGIS="2.2.1 r14555"

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message cnliou9 2017-01-02 15:40:14 BUG #14483: Prepared statement does not re-plan while it should
Previous Message Michael Paquier 2017-01-01 09:53:52 Re: BUG #14243: pg_basebackup failes by a STATUS_DELETE_PENDING file