Perfect! As you likely noticed in the previous exercise, the results differed by roughly 2 meters, or 0.04% of the actual result. The error was very small and thus negligible in practice.
Now, we will check what happens with the accuracy of our answer when we work with bigger territories. To illustrate this, we will try to calculate the distance between the capitals of California and Oregon. First, though, we'll show you how you can compare two or more values from the same column of the same table. Take a look:
SELECT
ST_Intersects(
state1.boundaries,
state2.boundaries)
FROM usa_states state1
JOIN usa_states state2
ON ST_Intersects(
state1.boundaries,
state2.boundaries)
WHERE state1.name = 'California'
AND state2.name = 'Oregon';
The query above will check if California and Oregon are neighboring states by returning either true or false.
Note how we joined the usa_states with another instance of the usa_states (a self join). Each time, we provided a different alias for the table (state1 and state2). Thanks to this, we were able to compare two rows from the same table.
The column named boundaries in usa_states is a geography column, which means the calculations above will be very accurate.
Mark has started planning his trip for the entire United States. First, he wants to check the distance between two particular capitals.
Calculate the distance between the capital cities of Florida and Alaska. Use the accurate geography column type. Provide the answer in kilometers.
Round the result to whole kilometers with the ROUND() function.
Use a self join, as shown in the explanation. Use the following ON clause:
ON state1.name = 'Florida' AND state2.name = 'Alaska'
Remember that ST_Distance will return a value in meters, so you have to divide it by 1000.
