|
|
In the following query we calculate distance to delivery destination of invoice
from Colombo Ltd headquaters in Berlin. Destination coordinates is defined as
aproximate center of the city mentioned in Customer Address field. Non-Earth
coordinates (X,Y) of the cities are stored in Locations table.
The formula used is: Distance = SquareRoot(dX * dX + dY * dY)
where dX is delta of X coordinates, and dY is delta of Y coordinates.
Coordinates of Berlin city center are taken as X=1490200, Y=6859400. Two Math
functions are used SQRT() and
POWER():
SELECT a.DocNo AS InvoiceNo, a.DocDate AS Invoice_Date, a.CustomerName, b.City,
SQRT(POWER((1490200-c.X),2)+POWER((6859400-c.Y),2) )/1000
AS Delivery_Distance_Km
FROM (Invoice AS a INNER JOIN Customers AS b ON a.CustomerID=b.CustomerID)
INNER JOIN Locations AS c ON (b.city=c.city) AND (b.country=c.country)
ORDER BY SQRT(POWER((1490200-c.X),2)+POWER((6859400-c.Y),2) )/1000
|
The results will be as following:
| InvoiceNo | Invoice_Date | CustomerName | City | Delivery_Distance_Km |
| 3 |
1999-02-14 |
Enrice Durance |
berlin |
0 |
| 22 |
1999-12-02 |
Valentina Mazepa |
warsaw |
850.849375624147 |
| 11 |
1999-10-08 |
Maddalena Corvaglia |
bordeaux |
2020.538039731 |
| 2 |
1999-01-22 |
Pestiana Oliviera |
saragosa |
2382.39490639147 |
| 19 |
1999-01-18 |
Pestiana Oliviera |
saragosa |
2382.39490639147 |
| 6 |
1999-04-02 |
John Depp |
Keflavik |
4694.04020114869
|
| 14 |
1999-11-11 |
Eva Longoria |
santiago |
12076.6208224818 |
| 12 |
1999-10-15 |
Famke Bacher |
santiago |
12076.6208224818 |
| 13 |
1999-10-21 |
Aida Yespica |
wellington |
21542.5513393841 |
|