1

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.

Where am I going wrong?

I am using PostgreSQL 9.6devel and Postgis version which I am using is: POSTGIS="2.2.1 r14555"

  • Possible duplicate of Distance from point stored in geometry with SRID 4326 – kttii Jan 4 '17 at 21:28
  • SRID 4326 is measured in degrees. though you can cast to geography – kttii Jan 4 '17 at 21:30
  • 4
    I believe normal convention for storing geometry is (x, y) or (longitude, latitude) - you may have those mixed up. – Mintx Jan 4 '17 at 21:37
  • 1
    Having coordinates at 127 degrees south makes it easier to see what is happening here. If you think "longitude, latitude", you're less likely to fall in this trap. – Vince Jan 5 '17 at 0:18
1

SRID 4326 is measured in degrees, though you can cast it to geography and let ST_DWithin utilize meters:

SELECT s1.name, s2.name 
FROM spatialTest s1, spatialTest s2
WHERE ST_DWithin(s1.the_geo::geography, s2.the_geo::geography, 50000);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.