For a given point (x,y), I would like to find all the points in the neighborhood (defined by myRadius, a number, in meters).

For now, I'm using a circle around each point, but some reason, I'd like to draw a square around each point instead of a circle (square in the orientation of x-y coordinates).

Command I use for the circle (distance):

SELECT * FROM table_of_points WHERE (ST_Transform(the_geom,21781) && ST_expand(ST_SetSRID(ST_GeomFromText('Point(x y)'), 21781), myRadius )

For the square, can I use:

SELECT * FROM table_of_points WHERE (ST_Transform(the_geom,21781) && ST_MakeEnvelope(x-myRadius, y-myRadius, x+myRadius, y+myRadius, 21781 ) )

It works, but I'm just wondering if it's the fastest way of doing this.


To find the points at a given distance the straighter way is use one of the available ditance operators

The use of this operators is equivalent to search which points are inside a circle.

For the square search i think that you are using a correct method.

  • Thanks. Are the distance operators faster than what I'm using? I read "The reason why expand combined with distance is much faster than distance alone is that it can utilize gist indexes since it does compares between boxes so therefore reduces the set of geometries that the distance function needs to check." (bostongis.com/postgis_extent_expand_buffer_distance.snippet) Yes, the square search works. But is it fast? – Antonin Jul 19 '13 at 13:58
  • 1
    @Antonin Use EXPLAIN to check if it uses the indices, if it does then probably it's as fast as it can be. – Jakub Kania Jul 20 '13 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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