4

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?

0

1 Answer 1

6

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)
4
  • Thank you for your answer. It solved my problem. The other method that worked for me was transforming the operands into 5643 SRID. I was wondering what's the difference between converting it into geography like your suggested, and using ST_Transform to convert the SRID's. Does ::geography also converts the SRID of the coordinates? And again thanks for the answer. :) Sep 2, 2021 at 21:34
  • 1
    The geography type uses an ellipsoid to calculate distances in meters. It's not transforming the coordinates. It's useful for calculating distances in geographic coordinate systems like 4326. 5643 is a projected coordinate system or planar coordinate system. It's representing that section of the earth as a flat plane, while 4326 is representing it as an ellipsoid. In 5643, distances between points are a straight line. In 4326, the line is curved.
    – jbalk
    Sep 2, 2021 at 22:59
  • 1
    One other thing is that it's less expensive for the database to use the geography type than to transform all the coordinates to another coordinate system.
    – jbalk
    Sep 2, 2021 at 23:14
  • 1
    It's also possible to generate a covering index on Geography(location), so the ST_DWithin can leverage a spatial index.
    – Vince
    Sep 3, 2021 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.