1

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:

  1. Order deliveries by distance from HQ
  2. Draw line between furthest out delivery and HQ, which moves to through each point of delivery in order as determined earlier at 1
  3. 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

closed as unclear what you're asking by Paul White Jun 4 '16 at 11:10

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

OK, then this is the query, SQL Server 2012+ . I'll use test data instead of your query and omit all customer, order details but orderid. The query also doesn't use spatial features which doesn't help much here as we use no geography but plain geometry distance.

with points as(
select orderid, id, Coord_X, Coord_Y,
    rn = ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY SQUARE (t.Coord_X) + SQUARE (t.Coord_Y) DESC)
from (
    values 
        (1, 1, 10, 20), 
        (1, 2, 15, 30), 
        (1, 3, 25, 5),
        (2, 1, 15, 20), 
        (2, 2, 17, 30), 
        (2, 3, 27, 8)
    ) t(orderid, id, Coord_X, Coord_Y)
)
select pfr.orderid, pfr.rn, pfr.id, pto.id,
    dist = SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)),
    cumdist = SUM(SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)))
        OVER (PARTITION BY pfr.orderid ORDER BY pfr.rn)
from points pfr
join points pto on pto.orderid = pfr.orderid and pto.rn=pfr.rn+1

May I say that this cumdist is far from being shortest in many cases.

Trying to use the second query from the question as a data source, not tested:

with points as(
select orderid, id, Coord_X, Coord_Y,
    rn = ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY SQUARE (t.Coord_X) + SQUARE (t.Coord_Y) DESC)
from (
   -- data source
    SELECT odrerid = 1, -- the data source returns single transportation order or route i suppose
        id = t_Addresses.ID -- probably you have it. It is intended to be a separate id for every point to be visited
        Coord_X = t_Addresses.Coord_X, 
        Coord_Y = t_Addresses.Coord_Y 
    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)) )
    --    
    ) t
)
select pfr.orderid, pfr.rn, pfr.id, pto.id,
    dist = SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)),
    cumdist = SUM(SQRT(SQUARE(pfr.Coord_X - pto.Coord_X) + SQUARE (pfr.Coord_Y - pto.Coord_Y)))
        OVER (PARTITION BY pfr.orderid ORDER BY pfr.rn)
from points pfr
join points as pto on pfr.orderid = pto.orderid and  pto.rn=pfr.rn+1

I also changed the WHERE to:

WHERE t_Addresses.Cust_ID IN (..) AND ( (..) OR (..) )

My guess is you need proper Cust_ID AND points within a quadrant.

I made some assumptions, please correct me if they are wrong.

Not the answer you're looking for? Browse other questions tagged or ask your own question.