Postgresql – PostGIS radius query

postgispostgresqlspatial

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 Answer

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).

Order by distance

Related Solutions

PostGIS – Removing Latitude and Longitude from Table

The point of reference comes from the cafe in the center, so you can use a subquery to retrieve it from the addresses table instead of the manual input:

SELECT c.*, a.*, ST_Distance(t.lonlat, a.lonlat) AS distance -- pick columns you need
FROM   addresses a
JOIN   cafes     c ON c.id = a.cafe_id
    , (SELECT lonlat
       FROM   addresses
       WHERE  cafe_id = 10
       AND    type = 'SearchAddress') t -- center cafe
WHERE  ST_DWithin(t.lonlat, a.lonlat, 4828)
ORDER  BY distance;

Table alias was missing: cafes c.
Makes no sense to have a.cafe_id, c.id in the SELECT list, since those are the same by definition. Pick the columns you actually need.
The columns type or state shouldn't be type text (or character varying(255)), I suggest an enum for a hand full of types or a adr_type look-up table and adr_type_id int REFERENCES adr_type (adr_type_Id in addresses.
Either way, I also suggest a partial, covering index for a faster look-up of the center coordinate. Building on "There can only be one of those addresses with that type.":
```
CREATE addresses_search_idx ON addresses (cafe_id, lonlat)
WHERE type = 'SearchAddress';
```
A lot more details discussed in chat (don't know for how long the link will be good).

PostgreSQL PostGIS – Finding Nearby Locations by Zip Code or City

What you want is ST_DWithin. That will use an index.

SELECT ascii_name
FROM geoname
WHERE
ST_DWithin(
  ST_SetSRID(ST_MakePoint(-122.67621 45.52345), 4326),
  the_geom,
  16093
);

You probably want to use geography rather than geometry.

Best Answer

Related Solutions

PostGIS – Removing Latitude and Longitude from Table

PostgreSQL PostGIS – Finding Nearby Locations by Zip Code or City

Related Question