Search code examples
sqlpostgis

return all points within 100 miles of bounding box in postgis

I'm still learning postgis and need some help. I have 2 tables, cities and attractions. I want to return all attractions within X miles of given city. These are my own tables, so I can set them up any way that is best for this. Right now, my attractions have a geom POINT column with srid 4326 which is lat/lng. My cities table has a geom BOUNDING BOX (POLYGON) column which encloses the city boundaries. I used ST_MakeEnvelope to fill in that column, also 4326.

I've looked at ST_Expand and ST_DWithin, but I can't seem to figure out how to both search the attractions table and expand the box out, and also return miles.

Any guidance greatly appreciated!

Solution

  • I want to return all attractions within X miles of given city

    that's easy with ST_DWithin

    SELECT *
FROM attractions AS a
JOIN cities AS c
WHERE ST_DWithin(c.geom::geography, a.geom::geography, X*1604);