Postgis query ST_DWithin against lat/long values

Question

I have the following table called locations with the following columns:

latitude
longitude

now I would like to query all entries that are within a specific radius from a given lat/long point.

SELECT * FROM locations
WHERE ST_DWithin(
    ST_MakePoint(longitude, latitude),
    ST_MakePoint(-0.63098, 51.18291),
    100
);

The query above explains what data I have as an input and the data I have to query against.

Any thoughts?

What is your actual question? -- A point is considered within another point, if they are the exact same points. You can play with thresholds, but using ST_Distance would be more straightforward IMHO. — pozs
The question is that the query above returns all entries in the table, not just the ones within the 100m radius from the input long/lat coordinates. ST_Distance returns only the distance between 2 points, what I want to achieve, is return all locations within a specific radius from a point — Andrei Stalbe
yes, that is typically a query with WHERE ST_Distance(point_in_table, queried_point) < queried_max_distance — pozs
Tried with both ST_Distance and ST_DWithin the result is the same. Even though the distance/radius are set to 1m, some of the rows are considerable farer than that. — Andrei Stalbe
Please provide table definitions & sample data and expected output for us to be able to reproduce your findings. — pozs

Mark Mark · Accepted Answer · 2017-04-26T13:25:03

ST_DWithin can work with both geography and geometry types. ST_MakePoint returns a geometry type. When using ST_DWithin with a geometry, then it will use distance unit defined by the spatial reference system.

When you want to compare in meters, you first have to cast the values to a geography type. The query then becomes:

SELECT * FROM locations
WHERE ST_DWithin(
    ST_MakePoint(longitude, latitude)::geography,
    ST_MakePoint(-0.63098, 51.18291)::geography,
    100
);

An answer explaining more about the difference between geography and geometry is here: https://gis.stackexchange.com/questions/6681/what-are-the-pros-and-cons-of-postgis-geography-and-geometry-types

Postgis query ST_DWithin against lat/long values

1 Answers