1

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?

1
  • Where are you getting these cords from are they in 4326 or 27700 Nov 26, 2017 at 4:12

2 Answers 2

4

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)

1
  • 3
    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. May 24, 2013 at 15:09
2

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

3
  • unfortunately with this transformation the result is empty
    – Luca
    May 24, 2013 at 9:02
  • 1
    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, 2013 at 12:05
  • 1
    I think the innermost WKID should be 4326 not 27700, but I have a feeling that won't fix everything.
    – mkennedy
    May 24, 2013 at 18:59

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