1

Say I have these two tables:

the_schedule_table:

+-----+---------------------+---------------------+-----------+------------+
| id  | start_date          | end_date            | latitude  | longitude  |
+-----+---------------------+---------------------+------------------------+
| 1   | 2017-07-17 12:00:00 | 2017-07-17 14:00:00 | 42.330078 | -83.045074 |
| 2   | 2017-07-17 8:00:00  | 2017-07-17 11:00:00 | 42.330380 | -83.037918 |
| 3   | 2017-07-17 13:00:00 | 2017-07-17 15:00:00 | 42.351314 | -83.067020 |
| 4   | 2017-07-17 11:00:00 | 2017-07-17 13:00:00 | 42.474879 | -83.241544 |
| 5   | 2017-07-17 15:00:00 | 2017-07-17 17:00:00 | 42.271986 | -83.742137 |
+-----+---------------------+---------------------+-----------+------------+

teams:

+-----+--------+------+
| id  | member | mate | 
+-----+--------+------+
| 1   | 1      | 2    | 
| 2   | 1      | 3    | 
| 3   | 1      | 4    | 
| 4   | 1      | 5    | 
| 5   | 2      | 1    | 
+-----+--------+------+

And then, say Id = 1 calls the API with a specific start_date|end_date and latitude|longitude, call them target, and wants to know, who are the mates that are within X distance from his data, and their start_date->end_date DOES NOT overlap with his.

For the overlap I can do this: (StartA > EndB) and (EndA < StartB)

And for the distance I need to combine this: SQL query of Haversine formula

How do I write this sql statement?

EDIT: I have the overlapping part and I tested it and it gave correct results:

// query overlapped activites - based on data time
const myQuery = 
    "SELECT * FROM schedule " +
    "WHERE start_date > '" + toMySqlFormat(target_end_date) +
    "' AND '" + toMySqlFormat(target_start_time) + "' > end_date";

Now I get lat,lng and ID, do I check distances then? and finally who are the mates? or do I start by finding mates, then overlaps, then distance?.. etc. I think I am starting right, but I want to optimize it so it is fastest possible. basically 3 conditions:

  • they are mates (different table)

  • their time does not overlap (same table)

  • they are within x distance (same table)

The struggle is real.

Khalil Khalaf
  • 9,259
  • 11
  • 62
  • 104
  • What have you try it? What problems you have? Because looks like you have all the parts already. – Juan Carlos Oropeza Aug 22 '17 at 19:28
  • @JuanCarlosOropeza I am poor with SQL conditional statements, this one is a little complex for me. I have it working but in code - I am switching to sql operations instead of code – Khalil Khalaf Aug 22 '17 at 19:30
  • Yeah, it seems like you've figured out all the hard bits :-) – Strawberry Aug 22 '17 at 19:32
  • @Strawberry the attached Haversine has different tables structures, and the overlapping is not SQL. No I did not figure out the hard part which is the one sql statement that does all on my table structure :) – Khalil Khalaf Aug 22 '17 at 19:34
  • I see, But can you write the date overlap version at least?? Show us something so we can guide you. Otherwise looks like a "do my job" question even when I can see you did some research. You will learn more by trying than by let other do the job. – Juan Carlos Oropeza Aug 22 '17 at 19:34
  • @JuanCarlosOropeza I thought of keeping it clean as I don't want (maybe) for others to use my humble select statement which part of the answer. I added it – Khalil Khalaf Aug 22 '17 at 19:39
  • That is much better, that give me the idea of where I can help. Also tell me you need to learn about parametrized query to avoid sql injection attacks https://www.w3schools.com/php/php_mysql_prepared_statements.asp – Juan Carlos Oropeza Aug 22 '17 at 19:41

1 Answers1

2

You will have 4 parameters @member_id, @start_date, @end_date, @distance

You need to use JOIN to find out the mates of member = 1

Then you can find out if those mates doesn't overlap with the dates. The conditional in your link indicate overlap, you want the inverted conditional so add NOT.

Finally use check if the result of the formula is less than @distance

 SELECT *
 FROM member M 
 JOIN the_schedule_table S
   ON M.mate = S.id
 WHERE NOT (S.start_date < @end_date AND S.end_date > @start_date)
   AND ( { Haversine formula } ) < @distance
   AND M.member = @member_id
Khalil Khalaf
  • 9,259
  • 11
  • 62
  • 104
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118