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