I have the following table called locations with the following columns:
latitudelongitudenow 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?
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