ST_DWithin is returning all the data in the table

coordinate systemnearest neighborpostgispostgresqlst-dwithin

I am new to PostGIS and currently struggling with ST_DWithin function.

I have a table stores, which stores lat and long of locations in Denmark in the location column as geometry and with SRID 4326.

Here's the screenshot of the table:

enter image description here

I am trying to find the nearest stores withing the 5 km radius from a specific lat and long.
Here's my query

SELECT  id, location 
FROM    stores 
WHERE   ST_DWithin(
            location, 
            ST_SetSRID(ST_MakePoint(12.5053379,55.7655287), 4326), 
            5000);

But the problem is that this query returns all of the records in the table.

I thought this might be due to the fact that unit of SRID 4326 is degrees and I am trying to find in meters. So I tried to reproject it with SRID 5643 which projects on the map of Europe and because my data is from Denmark.

The query for this is:

SELECT  * 
FROM    stores
WHERE   ST_DWithin(
            ST_Transform(location, 5643), 
            ST_SetSRID(ST_MakePoint(12.5053379,55.7655287), 5643),
            5000);

But this query is returning empty records, i.e. no record is returned.

I don't know what am I doing wrong. Could someone point me in the right direction?

Best Answer

If you need to use meters, cast your geometry to geography in the ST_DWithin function:

SELECT * 
FROM   stores 
WHERE  ST_DWithin(location::geography, (ST_SetSRID(ST_MakePoint(12.5053379,55.7655287), 4326))::geography, 5000);

A side note: You can't use ST_SetSRID to transform coordinates. The point coordinates you have are in SRID 4326. What you could do, if it were necessary is this:

ST_Transform(ST_SetSRID(ST_MakePoint(12.5053379,55.7655287), 4326),5643)
Related Question