Incorrect results returned by postgres

Question

I ran the following commands in posgresql 9.6:

./bin/createdb testSpatial
./bin/psql -d testSpatial -c "CREATE EXTENSION postgis;"
create table test(name character varying(250), lat_long character varying(90250), the_geom geometry);
\copy test(name,lat_long) FROM 'test.csv' DELIMITERS E'\t' CSV HEADER;
CREATE INDEX spatial_gist_index ON test USING gist (the_geom );
UPDATE test SET the_geom = ST_GeomFromText(lat_long,4326);

On running: select * from test; I get the following output:

name |                                                                                          lat_long                                       
                                                   |                                                                                            
                                                  the_geom                                                                                      

------+-----------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------+--------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
 A    | POLYGON((-0.061225 -128.427791,-0.059107 -128.428264,-0.056311 -128.428911,-0.054208 -128.426510,-0.055431 -128.426324,-0.057363 -128.42
6124,-0.059315 -128.425843,-0.061225 -128.427791)) | 0103000020E61000000100000008000000D42B6519E258AFBFBE50C076B00D60C07DE9EDCF4543AEBFBC41B456B
40D60C08063CF9ECBD4ACBFA1BC8FA3B90D60C07BF65CA626C1ABBF58AD4CF8A50D60C0BF805EB87361ACBFFFAF3A72A40D60C0B83A00E2AE5EADBF4D81CCCEA20D60C01F1153228
95EAEBF60C77F81A00D60C0D42B6519E258AFBFBE50C076B00D60C0
 B    | POINT(1.978165 -128.639779)                                                                                                             
                                                   | 0101000020E61000002D78D15790A6FF3F5D35CF11791460C0
(2 rows)

After this I ran a query: To find all "name" which are within 5 meters of each other. For doing so, I wrote the following command.

testSpatial=# select s1.name, s2.name from test s1, test s2 where ST_DWithin(s1.the_geom, s2.the_geom, 5);
 name | name 
------+------
 A    | A
 A    | B
 B    | A
 B    | B
(4 rows)

To my surprise I am getting incorrect output as "A" and "B" are 227.301 km away from each other (as calculated using haversine distance here: http://andrew.hedges.name/experiments/haversine/). Can someone please help me understand as to where am I going wrong.

Answer 1

You have defined your geometry as follows

 the_geom geometry

ie, it's not geography. But the ST_DWithin docs say

For Geometries: The distance is specified in units defined by the spatial reference system of the geometries. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID.

For geography units are in meters and measurement is defaulted to use_spheroid=true, for faster check, use_spheroid=false to measure along sphere.

So you are actually searching for places that are within 5 degrees of each other. A degree is roughly equal to 111km so you are looking for places that are about 550 km from each other rather than 5 meters.

Additionally, it doesn't make much sense to store strings like POINT(1.978165 -128.639779) in your table. It's completely redundant. It's information that can be generated quite easily from the geography column.