PostGIS radius query

Question

I have a table setup with the following columns, and it contains about 100K listing of data.

Column: address   |  Type: Text
Column: geo_point | Type: geometry(Point,4326)                             

My question is for each listing, how do I write the radius search query to find all the listing within 5 miles?

Answer 1

Best use ST_DWithin() in a LATERAL join:

SELECT t.*, neighbor.*  -- or only selected columns
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT *
   FROM   tbl t1
   WHERE  ST_DWithin(t.geo_point, t1.geo_point, 5000 * 1.609344)
   ) neighbor ON true
WHERE t.address = 'my_address';  -- to restrict to a particular address

1.609344 being the factor for converting miles to kilometers. Source: Wikipedia. Assuming meter as unit! The documentation:

For Geometries: The distance is specified in units defined by the spatial reference system of the geometries. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID.

For geography units are in meters

I think your SRID 4326 uses degrees, not meters. Instructions in this related answer:

• ST_DWithin takes parameter as degree , not meters , why?

You need an index on geo_point to make this fast (used by ST_DWithin() automatically):

CREATE INDEX tbl_geo_point_gist ON tbl USING gist (geo_point);

A GiST index can also be used to identify nearest neighbors (in combination with a maximum number - LIMIT).

Related:

• Order by distance

Answer 2

You can do something like this too,

SELECT t1.*, t2.*  -- or only selected columns
FROM   tbl t1
LEFT OUTER JOIN tbl AS t2
WHERE ST_DWithin(t1.geo_point, t2.geo_point, 5000 * 1.609344);

I assume it gets planned substantially faster. You should still use the Index as suggested in Erwin's answer.