How can I find all objects within a radius of another object?
There are a few ways to do a radius search, but the key here is to do an efficient search.
There is a function purpose-built for this query, ST_DWithin().
SELECT * FROM mytable WHERE ST_DWithin(geom, 'POINT(1000 1000)', 100.0)
SELECT * FROM mytable WHERE ST_Intersects(ST_Buffer(geom, 100.0), 'POINT(1000 1000)')
This will also work and return the same results as the other approaches, but is frequently slower, because constructive functions like ST_Buffer()](docs/ST_Buffer.html) can be very expensive. However, sometimes for cases with a complex literal on one side that gets buffered, and a simple test set like a table of points, the buffer approach can actually be faster than ST_DWithin().