3

This question already has an answer here:

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.

marked as duplicate by Mike T, user30184, Ian Turton Oct 16 '14 at 10:55

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

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);
  • select * from zones z where st_intersects(st_transform(z.geom, 4326), ST_GeographyFromText('POINT(2.336031 48.863172 )')) So that doesn't return a result, which I know is incorrect. I have the shapefile imported as srid 3857 and my lat long queries work. This radius stuff is kind of stumping me though. – Gary Oct 16 '14 at 3:21
  • Ahh, ok. when I transformed my geom coluymns to 4326 I started seeing some success – Gary Oct 16 '14 at 3:25

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