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

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"

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"
>
>
>

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"
>>
>>
>>
>

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:38 AM, Jyoti Leeka <leekajyoti(at)gmail(dot)com> wrote:

> 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.681
>>> 986|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"
>>>
>>>
>>>
>>
>