Postgres returning in-correct results with distance join queries

From: Jyoti Leeka <leekajyoti(at)gmail(dot)com>
To: pgsql-in-general(at)postgresql(dot)org
Subject: Postgres returning in-correct results with distance join queries
Date: 2017-01-04 19:52:58
Message-ID: CAPaVtGdijgSeA5RdCjgEVUMufK1gGzVgo2jwdTdSSKDtTMt-Ow@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-in-general

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;

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((0.178773,-127.681841|0.178711,-127.681962|0.179125,-127.682083|0.179176,-127.682006|0.179153,-127.681986|0.179143,-127.681962|0.179147,-127.681935|0.179166,-127.681913|0.179195,-127.681897|0.179244,-127.681886|0.179284,-127.681887|0.179336,-127.681904|0.179464,-127.681757|0.179489,-127.681736|0.179429,-127.681680|0.179370,-127.681516|0.179221,-127.681331|0.179184,-127.681185|0.179051,-127.681264|0.178822,-127.681499|0.178761,-127.681698|0.178796,-127.681703|0.178839,-127.681721|0.178857,-127.681736|0.178861,-127.681740|0.178871,-127.681756|0.178873,-127.681782|0.178859,-127.681809|0.178843,-127.681825|0.178812,-127.681839|0.178773,-127.681841))
B SRID=4326;POINT(0.628912,-127.700922)

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.0995 km to be precise.
Found using Chris Veness's geodesy formulae​ (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-in-general by date

  From Date Subject
Next Message Jyoti Leeka 2017-01-04 21:03:35 Re: Postgres returning in-correct results with distance join queries
Previous Message Ashutosh Bapat 2016-11-29 09:05:50 Re: Invoices Table Design Question