1

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?

2 Answers 2

4

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:

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:

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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