# Computing US airport connections and route interpolations

<div align="left"><figure><img src="https://3015558743-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FFEElAdsRIl9DzfMhbRlB%2Fuploads%2FUx7fNjcfw9KvGNf1JTaW%2Fadvanced%20banner.png?alt=media&#x26;token=ea2ec56e-2c6a-4c54-bae4-561b2fa33b7b" alt="Advanced difficulty banner" width="175"><figcaption></figcaption></figure></div>

## Requirements

To run this example you'll need:

* The latest version of the [Analytics Toolbox C](broken-reference)[ore](broken-reference) Native App installed in your Snowflake database
* _Optional:_ An active CARTO organization to visualize the results in a map

## Example

### Generating connections <a href="#generating-connections" id="generating-connections"></a>

In this example we will showcase how easily we can compute all the paths that interconnect the main four US airports using the Analytics Toolbox.

```sql
WITH data AS(
  SELECT 'SEA' AS abbrev, TO_GEOGRAPHY('POINT(-122.302289722924 47.4435819127259)') AS geog UNION
  SELECT 'MIA', TO_GEOGRAPHY('POINT(-80.2789718277441 25.7949407212406)') UNION
  SELECT 'LAX', TO_GEOGRAPHY('POINT(-118.402468548522 33.9441742543586)') UNION
  SELECT 'JFK', TO_GEOGRAPHY('POINT(-73.7863268609295 40.6459595584081)')
)
SELECT CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_GREATCIRCLE(t1.geog, t2.geog, 25) AS geom
FROM data AS t1
CROSS JOIN data AS t2
WHERE t1.abbrev != t2.abbrev
```

This query first creates all the possible combinations between airports and then generates the paths between them using the `ST_GREATCIRCLE` function. The resulting paths contain 25 points, but you can set the number of points in order to make the lines smoother if needed (you could also not include this parameter).

The result is displayed in this visualization. Notice that we are not using straight lines to interconnect the different airports, but great circles instead.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/pLYkijV0XxMuvf7QDmaT/sf_computing_map1.png" alt=""><figcaption></figcaption></figure>

### Routes interpolation <a href="#routes-interpolation" id="routes-interpolation"></a>

Now let’s put to the test how to perform line interpolations using the `ST_LINE_INTERPOLATE_POINT` function. In this example we will compute the airplane position after taking off from the different airports and travelling a certain distance.

```sql
WITH data AS(
  SELECT 'SEA' AS abbrev, TO_GEOGRAPHY('POINT(-122.302289722924 47.4435819127259)') AS geog UNION
  SELECT 'MIA', TO_GEOGRAPHY('POINT(-80.2789718277441 25.7949407212406)') UNION
  SELECT 'LAX', TO_GEOGRAPHY('POINT(-118.402468548522 33.9441742543586)') UNION
  SELECT 'JFK', TO_GEOGRAPHY('POINT(-73.7863268609295 40.6459595584081)')
)
SELECT CONCAT(t1.abbrev, ' - ', t2.abbrev) AS route,
  CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_LINE_INTERPOLATE_POINT(CARTO_ANALYTICS_TOOLBOX_CORE.CARTO.ST_GREATCIRCLE(t1.geog, t2.geog, 25), 500) AS geom
FROM data AS t1
CROSS JOIN data AS t2
WHERE t1.abbrev != t2.abbrev
```

This query uses the `ST_LINE_INTERPOLATE_POINT` function over each great circle in order to calculate the location of the plane after travelling 500 kilometers. In the following visualization you can see the resulting locations as well as their origin and destination airports.

<figure><img src="https://content.gitbook.com/content/FEElAdsRIl9DzfMhbRlB/blobs/mwrw7L5hY7Cm7jDySRvx/sf_computing_map2.png" alt=""><figcaption></figcaption></figure>
