|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:
The result will be like this:
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.