Re: Postgres returning in-correct results with distance join queries

The actual contents of my file testSpatial.csv in postgres compatible form
are:

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)

On Thu, Jan 5, 2017 at 2:33 AM, Jyoti Leeka <leekajyoti(at)gmail(dot)com> wrote:

> I inserted values as (longitude, latitude) in postgres
>
> On Thu, Jan 5, 2017 at 1:22 AM, Jyoti Leeka <leekajyoti(at)gmail(dot)com> wrote:
>
>> 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"
>>
>>
>>
>