PostGIS get geometries within a radius of n meters using WGS84 [duplicate]

Question

select *
from zones z
where st_intersects(z.geom, st_Buffer(ST_SetSRID(ST_Point(2.336031, 48.863172), 3857),1));

It looks like the 1 in the st_buffer is being interpreted as degrees, I need to find geometries within n meters of a given point.

I did find a reference to a new function (st_Buffer_Meters) but that didn't seem to do anything different.

Answer 1

If you are particularly interested in using meters as the unit for the search, you can use geography data types instead of geometry types. All geography based calculations return values in meters. Also, ST_DWITHIN is designed for this type of queries:

select *
from zones z
where ST_DWITHIN(Geography(ST_Transform(z.geom,4326)), ST_GeographyFromText('POINT(2.336031 48.863172)'),10000000);

A test zones table as follows is used. The query will return the polygons if 10,000km is used and empty if 1,000km is used as radius.

/* DROP TABLE IF EXISTS zones; */
CREATE TABLE zones (name varchar, geom geometry);

INSERT INTO zones VALUES
 ('Polygon 1', 'POLYGON((-1 1.732,1 1.732,2 0,1 -1.732,
     -1 -1.732,-2 0,-1 1.732))'),
 ('Polygon 2', 'POLYGON((-1 1.732,-2 0,-1 -1.732,1 -1.732,
     2 0,1 1.732,-1 1.732))'),
 ('Polygon 3', 'POLYGON((1 -1.732,2 0,1 1.732,-1 1.732,
     -2 0,-1 -1.732,1 -1.732))'),
 ('Polygon 4', 'POLYGON((-1 1.732,0 1.732, 1 1.732,1.5 0.866,
     2 0,1.5 -0.866,1 -1.732,0 -1.732,-1 -1.732,-1.5 -0.866,
     -2 0,-1.5 0.866,-1 1.732))'),
 ('Polygon 5', 'POLYGON((-2 -1.732,2 -1.732,2 1.732,
     -2 1.732,-2 -1.732))');

ALTER TABLE zones
  ALTER COLUMN geom
  SET DATA TYPE geometry(Geometry,4326)
  USING ST_SetSRID(geom, 4326);