Best way to create a linear schedule
Posted: Fri Feb 19, 2010 7:21 pm
My website is essentially a database of bars and restaurants, and the events happening at them (like happy hours, etc). I'm working on a new feature that will automatically create a bar crawl schedule for the user based on what kind of events/venues they want, how long they want to spend at each place, how long they're willing to travel, etc. So far, that's working great.
My one issue is, say the user wants to make 4 stops. The system does not take into account the linear aspect of the bar crawl, so it may have the first and third stops near each other, with the second stop a mile away. This, obviously, would still be within the parameters that the user specified, but it would make more sense to switch the order of the stops to follow a natural progression of travel.
So, how would you guys go about ensuring that this is the case? I have the longitude and latitude of each place stored in my database, and use a distance function in my MySQL query to only fetch events/venues that are within X distance of the previous one. From all the results returned, one is chosen at random using ORDER BY RAND() LIMIT 1. My query also includes event/venue types, and dates/times that they are open and/or happening, but the only reason why these would be applicable to my question is if I switched two stops around after the queries have all been performed, and the system would potentially tell the user to go to a place before it's open, or after it closes.
I hope all this makes sense. Just looking for some theoretical approaches to accomplishing this, because the way I'm seeing it I'd have to do multiple layers of queries to get everything just right, but I'm sure there is an easier way.
Thanks.
My one issue is, say the user wants to make 4 stops. The system does not take into account the linear aspect of the bar crawl, so it may have the first and third stops near each other, with the second stop a mile away. This, obviously, would still be within the parameters that the user specified, but it would make more sense to switch the order of the stops to follow a natural progression of travel.
So, how would you guys go about ensuring that this is the case? I have the longitude and latitude of each place stored in my database, and use a distance function in my MySQL query to only fetch events/venues that are within X distance of the previous one. From all the results returned, one is chosen at random using ORDER BY RAND() LIMIT 1. My query also includes event/venue types, and dates/times that they are open and/or happening, but the only reason why these would be applicable to my question is if I switched two stops around after the queries have all been performed, and the system would potentially tell the user to go to a place before it's open, or after it closes.
I hope all this makes sense. Just looking for some theoretical approaches to accomplishing this, because the way I'm seeing it I'd have to do multiple layers of queries to get everything just right, but I'm sure there is an easier way.
Thanks.