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

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