CODEWITHSUNDEEP
postgresqlpostgis
Andrei Stalbe
Andrei Stalbe

Reputation: 1531

Postgis query ST_DWithin against lat/long values

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

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?

Upvotes: 1

Views: 3908

Answers (1)

Mark
Mark

Reputation: 1181

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

Upvotes: 7

Related Questions