1

So how do I write a query to get all rows within X meters of a certain lat/long coordinate?

I tried looking into the docs, but it's super confusing, ideally I just want to write

SELECT * 
FROM   locdat
WHERE  ST_DWithin(ST_MakePoint(-71.1043443253471, 42.3150676015829),2000, true )

but I don't understand anything of the syntax they use in the docs or something like select ST_MakePoint(loc_point) from locdat won't work either.

loc_point is data like (54.66965,20.64888) of type text.

Bas
  • 25
  • 4
  • 1
    First, you have to make sure your data all has correct spatial reference IDs. For WGS84 data that would be 4326. Second, you need to cast the 4326 geometry to geography so that ST_DWithin uses geodesic distance of 2000 meters, not Cartesian distance of 2000 degrees (or doesn't simply return false because the SRIDs are incompatible). Eventually you may have to deal with a covering index , but only if you have a lot of rows. – Vince Mar 14 '20 at 12:03
  • For reference, SELECT ST_Distance(ST_SetSRID(ST_MakePoint(-71.1043, 42.3151),4326)::geography, ST_SetSRID(ST_MakePoint(54.6697,20.6489),4326)::geography) returns 11095628.2 meters (which is larger than 2km) – Vince Mar 14 '20 at 13:28

1 Answers1

3

ST_DWithin takes 2 geometries or geographies.

To use a distance in meters, you can use the geography datatype. You may have to cast your column (and index)

select * 
from locdat
where ST_DWithin(
        locdat.geog, 
        ST_MakePoint(-71.1043443253471, 42.3150676015829)::geography,
        2000, 
        true )
JGH
  • 41,794
  • 3
  • 43
  • 89
  • 3
    Note that his assumes that locdat.geog is a geography type; otherwise it needs to be cast to geography. – Vince Mar 14 '20 at 13:33
  • my lat/long is saved as text type, but im getting an error when i try SELECT ST_GeomFromText(locdata.loc_geo, 4326) from locdata (ignore the change in table/column names) – Bas Mar 19 '20 at 04:44
  • @bas that's a different question. Once you have figured out how to convert your text to a geography, then this answer applies. Please ask a new question with enough details if you need help converting your text to a geog/geom – JGH Mar 19 '20 at 11:16