How to fetch nearby "records" using join in php?

Question

I want to get all "nearby merchants" from database using join (filterproducts.paramId is primary key and merchants.merchantid is foregin key)

Here is my table "filterproducts "

id              FilterType              paramId         
1               merchant                1  
2               merchant                2
3               merchant                3 
4               tag                     4

Here is my table "merchants"

merchantId              latitude            longitude           merchantName
1                       30.696231           76.856701           abc
2                       30.740102           76.792538           xyz
3                       30.741345           76.790943           jay    
4                       30.722829           76.768137           hya
5                       30.739897           76.782707           aeb

How can i get nearby merchants using join ?

Please define "nearby" more precisely. It's not clear what the expected result of your query should be. Also have you made any attempt yourself? See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query). See also the [tour], [ask] and how to create a [mre]. — ADyson, Jul 21 '21 at 10:46
`filterproducts.paramId is primary key and merchants.merchantid is foregin key`...are you sure it's not the other way round? That would make more sense. — ADyson, Jul 21 '21 at 10:53

score 0 · Answer 1 · answered Jul 23 '21 at 01:57

0

Put a "bounding box" in the WHERE. And have

INDEX(lat, lng)
INDEX(lng, lat)

More discussion: http://mysql.rjweb.org/doc.php/latlng

answered Jul 23 '21 at 01:57

Rick James

135,179
13
127
222

Siva Koteswara Rao · Answer 2 · 2021-07-23T04:05:04.117

0

Haversine

formula: a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2)

c = 2 ⋅ atan2( √a, √(1−a) )

d = R ⋅ c

where φ is latitude, λ is longitude, R is earth’s radius (mean radius = 6,371km);

note that angles need to be in radians to pass to trig functions

https://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql#:~:text=Here's%20a%20MySQL%20function%20that,the%20poles%20and%20the%20equator.

edited Jul 23 '21 at 04:05

answered Jul 23 '21 at 03:27

Siva Koteswara Rao

129
1
6

https://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql#:~:text=Here's%20a%20MySQL%20function%20that,the%20poles%20and%20the%20equator. – Siva Koteswara Rao Jul 23 '21 at 04:04

score 0 · Answer 3 · answered Jul 23 '21 at 03:52

$latitude = '30.696232';
$longitude = '76.856702';

SELECT
    filterproducts.*, merchants.*, (
      6371 * acos (
      cos ( radians($latitude) )
      * cos( radians( merchants.latitude ) )
      * cos( radians( merchants.longitude ) - radians($longitude) )
      + sin ( radians($latitude) )
      * sin( radians( merchants.latitude ) )
    )
) AS distance
FROM merchants
LEFT JOIN filterproducts
ON merchants.merchantId = filterproducts.paramId
ORDER BY distance;

How to fetch nearby "records" using join in php?

3 Answers3