Fare Estimator:
Many taxi companies like Uber offer fare finders where the user enters starting and ending address and the service returns an approximate fare range. Now one can do that using a single line of SQL.
-- c1 and c2 are distance, time multipliers to compute fare SELECT dist(lat1, lon1, lat2, lon2) * c1 + triptime(lat1, lon1, lat2, lon2) * c2; -- This produces fare in dollars. Estimate could be +/- 20%
Where are the nearest taxis?
A common feature in many taxi app is to provide a map of the nearest taxi. This can be done using just a few lines of SQL.
-- lat, lon is where a passenger is present -- taxi(id, lat, lon, available) current taxi information SELECT taxi.id, dist(lat, lon, taxi.lat, taxi.lon) as distance FROM taxi WHERE taxi.available = true AND distance <= 10 * 1000 --- 10 kms ORDER BY distance;
Where is my assigned taxi?
Once passenger is assigned a taxi, one wants to provide an accurate time of arrival in seconds for the customer. This also can be done using a few lines of SQL.
-- lat, lon is where a passenger is present -- taxi(id, lat, lon, available) current taxi information -- taxi_id is the assigned taxi SELECT taxi.id, triptime(lat, lon, taxi.lat, taxi.lon) as seconds_to_arrival FROM taxi WHERE taxi.id = tax_id;
Car-pooling Service:
Suppose a taxi company wants to offer a car-pooling service where the taxi picks as many passengers as possible with minimal detours. This means that frequently one needs to compute available passengers and the order in which to pick up them.
The first thing one needs to compute is a the distance matrix between all the waiting passengers. For example, if a driver has to pick up 10 passengers, we need to compute the network distance between every pair of delivery address which is then fed into an optimization algorithm. Now if one super scales the problem to 100 taxis and 1000 passengers, we can handle this query easily using your existing commodity hardware.
We can compute 1000 x 1000 distance matrix (1 million distance computations) is less than 30 seconds on a commodity server running PostgreSQL. A 100 x 100 distance matrix is just a fraction of a second.
-- waiting_passengers(id, lat, lon) is a table of passengers -- yet to be assigned to taxis. SELECT A.id as id1, B.id as id2, dist(A.lat, A.lon, B.lat, B.lon) as dist FROM waiting_passengers as A, waiting_passengers as B; -- Result is a distance matrix
Trajectory Analysis:
Taxi services collect the GPS trajectories of their taxis which becomes a rich source of analysis for optimization efficiency. Look at our detailed post on what can be done with large-scale trajectory archives.
Fare Scams by Drivers:
If you own a fleet of taxis and want to find if a driver is overcharging your passengers, you can compute the difference between the distance along the trajectories vs. the distance from start to destination. Look at our detailed post on what can be done with large-scale trajectory archives.