SQL advanced -> Calculate distance between two points

The task is to measure distance to order's destinations . Destination of the order is defined by customer's address. First join is familiar and is made by CustomerID between "Invoices" and "Customers" table. Then we add one more table "Locations" to the join that is referenced by fields: country, city. "Locations" table stores coordinates of city centers. This is enough to calculate aproximate distance to shipment destinations. We suppose that orders are shipped from Berlin (Germany) and are sent all over the world. In order to measure distance following geometric formula is used (here written in JetSQL syntax):
SQR((X1 - X2) ^ 2 + (Y1 - Y2) ^ 2)

Select distance from "Colombo Ltd" Berlin center to 5 closest orders in 1999:
SELECT TOP 5 a.DocDate, a.CustomerID, b.City,
(SQR((1490200-c.X)^2+(6859400-c.Y)^2))/1000 AS 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 (SQR((1490200-c.X)^2+(6859400-c.Y)^2))/1000;

/* Berlin city center coordinates are: X=1490200 Y=6859400 aproximately */


The result will be like this:
DocDateCustomerIDCityDistance_Km
14/02/998berlin0
02/12/9916warsaw850
08/10/999bordeaux2020
18/01/994saragosa2382
...........


Here we see that customer from Berlin is in zero distance from "Colombo Ltd" location. Next closest lives in Warsaw. We should like to know distances for calculating delivery prices for example.


sqlexamples.info