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?

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
``````

`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:

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:

• Thanks!!! The query ran fine. Howerver, I getting the following error: NOTICE: gserialized_gist_joinsel: jointype 1 not supported Nov 9, 2015 at 19:19
• @user1187968; I think the message is safe to ignore: trac.osgeo.org/postgis/ticket/2342 Nov 9, 2015 at 20:25

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.