postgresql - Postgres Postgis ST_DWithin query is not accurate - Stack Overflow

I have the following table:

CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  location GEOMETRY(Point, 4326)
);

Now if I insert two:

INSERT INTO locations (name, location)
VALUES ('Berlin', ST_SetSRID(ST_MakePoint(13.405, 52.52), 4326));

INSERT INTO locations (name, location)
VALUES ('Krakow', ST_SetSRID(ST_MakePoint(19.945, 50.0647), 4326));

and use the following query:

select * from locations where ST_DWithin(location::geography, ST_GeographyFromText('SRID=4326;POINT(13.405 52.52)'), 531000);

I only get Berlin. The distance between them is only 530100 though. So even with 900 meters more, it is not returned. It is only returned when I change it to 531500. But that's like 1400 meters from the original point away, which is far too much.

Am I doing something wrong here?

I have the following table:

CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  location GEOMETRY(Point, 4326)
);

Now if I insert two:

INSERT INTO locations (name, location)
VALUES ('Berlin', ST_SetSRID(ST_MakePoint(13.405, 52.52), 4326));

INSERT INTO locations (name, location)
VALUES ('Krakow', ST_SetSRID(ST_MakePoint(19.945, 50.0647), 4326));

and use the following query:

select * from locations where ST_DWithin(location::geography, ST_GeographyFromText('SRID=4326;POINT(13.405 52.52)'), 531000);

I only get Berlin. The distance between them is only 530100 though. So even with 900 meters more, it is not returned. It is only returned when I change it to 531500. But that's like 1400 meters from the original point away, which is far too much.

Am I doing something wrong here?

• postgresql
• postgis

Share Improve this question Follow asked yesterday NiklasNiklas 25.3k3535 gold badges135135 silver badges183183 bronze badges 3

• How have you computed the distance of 530 100 m? – JGH Commented yesterday
• I used this one: acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon2-lon1))*6371 – Niklas Commented yesterday
• ok, this formula considers the earth as a perfect sphere – JGH Commented 22 hours ago

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

It looks like the distance used by geography is the most accurate of the two.

From st_distance doc:

For geography types defaults to return the minimum geodesic distance between two geographies in meters, compute on the spheroid determined by the SRID. If use_spheroid is false, a faster spherical calculation is used.

From st_distanceSphere doc :

Returns minimum distance in meters between two lon/lat points. Uses a spherical earth and radius derived from the spheroid defined by the SRID. Faster than ST_DistanceSpheroid, but less accurate

Comparing the computed distances using either a sphere or the more accurate spheroid, we confirm that st_dwithin also use the distance computed on the spheroid. You can still force st_dwithin to use a sphere instead of a spheroid, but it is less accurate.

SELECT st_distance(Berlin::geography,Krakow::geography) as dist_geog,
    st_distance(Berlin::geography,Krakow::geography, false) as dist_geog_sphere,
    st_distanceSphere(Berlin,Krakow) as dist_sphere,
    st_distanceSpheroid(Berlin,Krakow) as dist_spheroid,
    ST_DWithin(Berlin::geography,Krakow::geography,530200, false) dwithin_sphere
FROM geo;

    dist_geog    | dist_geog_sphere |   dist_sphere   |   dist_spheroid   | dwithin_sphere
-----------------+------------------+-----------------+-------------------+----------------
 531430.81283859 |  530123.37723304 | 530123.37723304 | 531430.8128385914 | t
(1 row)