What is a query that returns a list of businesses closest to a user within a certain mile radius and sorted by closest proximity?
Address
| id | address | address_unit | city | state | zip_code | country | lat | long |
|---|---|---|---|---|---|---|---|---|
| 56baedbb-1567-4157-b19d-a6b034852f30 | 724 Alder St | Edmonds | WA | 98065 | USA | 47.535892984613724 | -121.87656196852986 | |
| 03d97857-ab93-43b1-b710-00fb395fa09c | 9514 8th Ave S | Seattle | WA | 98108 | USA | 47.517627356151756 | -122.32318504563332 | |
| a613c8ed-b28d-4b14-a019-972d8973c86c | 886 Henry Ln | Camano Island | WA | 98282 | USA | 48.17915401336697 | -122.53134903615869 | |
| c80c4909-9e55-443e-89b9-dfc03977f7bf | 9816 51st Ave SW | Seattle | WA | 98136 | USA | 47.5148539885482 | -122.39510203823218 | |
| e7dda3d1-7b61-4427-89f5-59b63d7eabb8 | 89 Park Dr | Boston | MA | 02215 | USA | 42.34228833221937 | -71.09627319085239 |
User
| id | phone_number | first_name | last_name | address_id | |
|---|---|---|---|---|---|
| e10ce9ab-c1ba-4e28-84b6-33bb31b9d894 | [email protected] | 1 (622) 691-7741 | Ronnie | May | c80c4909-9e55-443e-89b9-dfc03977f7bf |
Business
| id | name | primary_phone_number | secondary_phone_number | website_url | address_id |
|---|---|---|---|---|---|
| 7647dd2b-38a1-4192-9797-c17f62a4f890 | Gourmello | 1 (454) 877-7147 | 1 (454) 374-4781 | www.example.com | a613c8ed-b28d-4b14-a019-972d8973c86c |
| 76f1cf98-1594-41e9-893e-e59a614b2bf6 | Elizaveta | 1 (633) 490-0044 | www.example1.com | 03d97857-ab93-43b1-b710-00fb395fa09c | |
| 9a0f754c-8d76-4ffb-8147-49b067b5d870 | Tasty Fruits | 1 (592) 418-3779 | www.example2.com | 56baedbb-1567-4157-b19d-a6b034852f30 | |
| aae19e0f-85f2-4856-80ab-a4887a1042ef | Arbubbles | 1 (355) 954-7076 | www.example3.com | e7dda3d1-7b61-4427-89f5-59b63d7eabb8 |
CodePudding user response：
First you need to store your coordinates in a geometry or geography column - see which one suits your use case best.
ALTER TABLE address ADD COLUMN geom geography (point,4326);
Then update this new column with geometries:
UPDATE address SET geom = ST_MakePoint(long,lat);
After that you might be able to query your data using ST_DWithin, e.g.
WITH j AS (
SELECT *
FROM users u
JOIN address a ON a.id = u.address_id
WHERE u.id = 'e10ce9ab-c1ba-4e28-84b6-33bb31b9d894'
)
SELECT b.*
FROM business b
JOIN address a ON a.id = b.address_id
JOIN j ON ST_DWithin(j.geom,a.geom,8046.72) -- 8046.72 metres ~ 5 miles;
In this other answer I go in more details on how to use ST_DWithin