I have a database designed to be a proof of concept for delivery of products from a central depot to customers distributed at random locations within ten miles of the depot. The depot location is expressed in absolute coordinates (0,0) and customer locations are stored in the database in two columns with Coord_X and Coord_Y. The code I have at present to determine which deliveries are due on a particular day is as follows:
SELECT t_Orders.Order_ID, t_Orders.Del_Sess, t_Customers.Contact_Name, t_Addresses.Cust_ID, t_Addresses.Town, t_Addresses.County, t_Addresses.Coord_Y, t_Addresses.Coord_X, t_Customers.Comp_Name, t_Orders.Cust_ID, t_Orders.Del_Date FROM t_Orders JOIN t_Customers ON t_Orders.Cust_ID=t_Customers.Cust_ID AND t_Orders.Del_Sess=1 AND t_Orders.Del_Date = '2016-05-21' JOIN t_Addresses ON t_Orders.Cust_ID=t_Addresses.Cust_ID AND (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X <= 0 AND SQUARE(t_Addresses.Coord_X) < SQUARE(t_Addresses.Coord_Y)) OR (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X >= 0 AND (t_Addresses.Coord_X) < (t_Addresses.Coord_Y)) ORDER BY SQUARE (t_Addresses.Coord_X) + SQUARE (t_Addresses.Coord_Y) DESC
This returns a list of all deliveries, with the furthest away first. I'd like to be able to take the output of this query and run queries on it (I looked into temporary tables and views, but can't seem to get this into one) to first remove any duplicates (e.g. multiple deliveries to one customer on a particular day as each order is treated as one delivery) and then run a query that says:
Starting at the furthest away, calculate distance to second furthest away, then from second furthest away, calculate distance to third furthest and so on until back at HQ.
I also have a geometry column for each customer location if that helps - e.g. is there a very easy way to do this using geometry, declaring HQ as a point first and letting SQL server work out the cumulative shortest distance back?
It's travelling salesman in a way, but a very simple version of it. I'm not looking for the shortest route per se. I'm looking for:
- Order deliveries by distance from HQ
- Draw line between furthest out delivery and HQ, which moves to through each point of delivery in order as determined earlier at 1
- Measure length of that line.
This is the SQL I have at present to bring back the delivery locations and coordinates:
SELECT t_Addresses.Coord_X, t_Addresses.Coord_Y, t_Addresses.Cust_ID FROM t_Addresses WHERE t_Addresses.Cust_ID IN (SELECT DISTINCT t_Orders.Cust_ID FROM t_Orders WHERE t_Orders.Del_Date='2016-05-21' AND t_Orders.Del_Sess=1) AND (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X <= 0 AND SQUARE(t_Addresses.Coord_X) < SQUARE(t_Addresses.Coord_Y)) OR (t_Addresses.Coord_Y >= 0 AND t_Addresses.Coord_X >= 0 AND (t_Addresses.Coord_X) < (t_Addresses.Coord_Y)) ORDER BY SQUARE (t_Addresses.Coord_X) + SQUARE (t_Addresses.Coord_Y) DESC