# PostGIS, distance between two users

I have a simple table (location_test) with three column (name, geom , jid). geom is a geometry column with SRID 27700. I set the user's geometry position with:

``````UPDATE location_test SET geom = ST_SetSRID(st_makepoint(48.9 , 2.4),27700) WHERE location_test.jid = 'luca';
UPDATE location_test SET geom = ST_SetSRID(st_makepoint(48.9 , 2.4),27700) WHERE location_test.jid = 'filo' ;
UPDATE location_test SET geom = ST_SetSRID(st_makepoint(48.9 , 2.4),27700) WHERE location_test.jid = 'gianlu';

UPDATE location_test SET geom = ST_SetSRID(st_makepoint(55.8 , 37.6),27700) WHERE location_test.jid = 'mango';
``````

3 people are in Paris (lat 48.9 ,lon 2.4) and the last one is in Moscow (lat 55.8 ,lon 37.6).

When I query for people in Paris, within 100 meters, "mango" is included, but why? the query is:

``````SELECT * FROM location_test WHERE ST_DWithin(geom, ST_GeomFromText('POINT( 48.9  2.4 )',27700) , 100.0);
``````

in SRID 27700 units are meters, so 100.0 is in meters i think.

could someone help me?

-

## 2 Answers

EPSG:277000 = OSGB 1936 / British National Grid proj4 is +proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +datum=OSGB36 +units=m +no_defs.

So You have points in degrees in metric systems and you use meters to find distance between users ?

First use ST_SetSRID(...., 4326) (that would be WGS84 and it uses degrees ) then follow @Aragon Answer to ST_Transform data from 4326 to 900913 or 3857 (which is current EPSG code for google mercerator), or you can also use some better srid to calculate distance between points.

If you really have data all over earth , you could consider using geography type for your data. It can handle "most" accurate distance calculations etc. for data all over earth. When using geometry it up to you know which srid is best. Remeber in PostGIS all calculation when using geometry data type are done in used srid ( meter, feet, or degrees)

-
Indeed, you declare that your SRID is the British National Grid, but then you use lon/lat coordinates. Either transform your data into the BNG when inserting (ST_Transform(ST_SetSRID(geom, 4326), 27700) or use the geography type, not geometry. – Paul Ramsey May 24 '13 at 15:09

try to use metric transformation:

``````SELECT * FROM location_test WHERE ST_DWithin(geom,  ST_Transform(ST_GeomFromText(
'POINT( 48.9  2.4 )',27700), 900913) , 100.0);
``````

i hope it helps you...

-
unfortunately with this transformation the result is empty – Luca May 24 '13 at 9:02
web mercator is lousy on distance calculations.. Try casting to geography? I think PostGIS tries to find the best possible projection, and then uses web mercator as last possibility – atlefren May 24 '13 at 12:05
I think the innermost WKID should be 4326 not 27700, but I have a feeling that won't fix everything. – mkennedy May 24 '13 at 18:59