Postgis, get the points that are x meters near another point, in meters

Question

I'm trying to find the points within a certain distance, in meters, from another point.

I'm using the function ST_Buffer, but I don't get how to express the distance in meters.

I'm trying with something like this:

select
1 as cartodb_id,
ST_Buffer(
  ST_Transform(
    ST_GeomFromText(
      POINT(-58.38145 -34.60368)'
      , 4326
    )
    , 3857
  )
  , 500
) as the_geom_webmercator

I have the coordinates from google maps (in wgs84, 4326) then I transform them to webmercator (3857) and then I pass 500 as parameter, and I get something that looks like the desired area, but after comparing it with what google maps says it falls short for a non neglectable distance.

Is this the right way to achieve it?

note: you can try the sql query in cartodb

--

From this question: https://gis.stackexchange.com/a/44481/19461 I came with the following code (I use SRID 31997 to calculate the buffer, and then go back to webmercator)

select
1 as cartodb_id,
ST_Transform(
  ST_Buffer(
    ST_Transform(
      ST_GeomFromText(
        'POINT(-58.427185 -34.624592)'
        , 4326
      )
      , 31997
    )
    , 2000
  ), 3857
) as the_geom_webmercator

Now if falls short for no more than 20 meters. I thought mercator (EPSG 3857) was in metric units.

Answer 1

If you want to test whether some points (or shapes, etc) fall within a given distance of a given location, use the geographic version of ST_DWithin(). From https://postgis.net/docs/ST_DWithin.html:

boolean ST_DWithin
  (geography gg1, geography gg2, double precision distance_meters, boolean use_spheroid);

Thus,

SELECT
  ST_DWithin(
      ST_GeomFromText(
        'POINT(-58.7 -34.2)' -- given point
        , 4326
      )::geography
      , 
      ST_GeomFromText(
        'POINT(-59.1 -33.6)' -- another point
        , 4326
      )::geography
      , 20000 -- given distance (m)
      , true -- for greater accuracy (false for greater speed)
    )

returns false but

SELECT
  ST_DWithin(
      ST_GeomFromText(
        'POINT(-58.7 -34.2)' -- given point
        , 4326
      )::geography
      , 
      ST_GeomFromText(
        'POINT(-59.1 -33.6)' -- another point
        , 4326
      )::geography
      , 80000 -- given distance (m)
      , true -- for greater accuracy (false for greater speed)
    )

returns true (The second example has a longer test distance.)

Answer 2

Best way is to use the geographic type.

geography ST_Buffer(geography g1, float radius_of_buffer_in_meters);

SELECT
1 as cartodb_id,
ST_Transform(
  ST_Buffer(
      ST_GeomFromText(
        'POINT(-58.427185 -34.624592)'
        , 4326
      )::geography
    )
    , 2000
  ), 3857
) as the_geom_webmercator

By casting to geography the st_buffer will work on meters, as you can see on the signature of the function.