I have 2 tables. One of my tables with users
is:
id name longitude latitutde ----- ----- ----------- ----------- 1 Mark -76.316528 40.036027 2 John -95.995102 41.25716 3 Paul -82.337036 29.645095 4 Dave -82.337036 29.645095 5 Chris -76.316528 40.036027
I’m using SQL to detect nearby people: SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin(radians(latitude)) ) ) AS distance FROM
usersHAVING distance < 50 ORDER BY distance DESC LIMIT 20
The other matches
is like this:
id sender receiver status ----- ----- ----------- -----------
Where sender is the person who sent the invitation receiver is the person who receives is.
The SQL request should search in users
for near by people and check in the matches
for their ids. If the ids are not there as sender & receiver return the userID of the person to make a new connection.
For example Paul is searching for nearby people and Dave as we see from users
is nearby him. So Dave ID should be returned to Paul ONLY , if their ids are not in matches
table OR Paul == receiver
AND status == 0.
How this SQL Query should be written to return 20 Ids of the nearby people ?
Advertisement
Answer
I change the distance formula to something that works since mysql 5.7. You didn’t_ specify your database or presented a fiddle example to show your system, so i selected 5.7 as mini9mum and it works also in 8.0 as you see in the fiddle.
the inner query needs two things the user id who searches and his position, because he is excluded from the results and of course to calculate the distance.
The result from the query is checked in the where clause-I hope i got the right idea, so you should check that
CREATE TABLE users (`id` varchar(5), `name` varchar(5), `longitude` varchar(11), `latitude` varchar(11)) ; INSERT INTO users (`id`, `name`, `longitude`, `latitude`) VALUES ('1', 'Mark', '-76.316528', '40.036027'), ('2', 'John', '-95.995102', '41.25716'), ('3', 'Paul', '-82.337036', '29.645095'), ('4', 'Dave', '-82.337036', '29.645095'), ('5', 'Chris', '-76.316528', '40.036027'), ('6', 'Manny', '-82.338036', '29.645095'), ('7', 'Fred', '-82.338036', '29.646095') ;✓ ✓
CREATE TABLE matches (`id` int, `sender` int, `receiver` int, `status` int) ; INSERT INTO matches (`id`, `sender`, `receiver`, `status`) VALUES (1, 3, 4, 0), (2, 1, 5, 1), (3, 6, 3, 1) ;✓ ✓
SELECT id , ( 3959 * acos( cos( radians(37) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-122) ) + sin( radians(37) ) * sin(radians(latitude)) ) ) AS distance FROM users HAVING distance < 50 ORDER BY distance DESC LIMIT 20id | distance :- | -------:
SELECT a.id ,a.distance FROM (Select id, st_distance_sphere(POINT(-82.337036, 29.645095 ), POINT(`longitude`, `latitude` ))/1000 as distance FROM users u WHERE id <> 3 HAVING distance < 50 ORDER BY distance DESC LIMIT 20) a WHERE a.id in (SELECT `sender` FROM matches WHERE status = 1) OR a.id NOT IN ( SELECT `sender` FROM matches UNION ALL SELECT `receiver` FROM matches ) ORDEr BY a.distance ASCid | distance :- | ------------------: 6 | 0.09663995445895139 7 | 0.14732089261518266
db<>fiddle here