3
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.

0
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);
2
  • 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.