PHP/MySQL ->
Calculate distance between points

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:
InvoiceNoInvoice_DateCustomerNameCityDelivery_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


sqlexamples.info