nationalparks

Snapshot of the united states national park service data from their api.

Data retrieved from the National Park Service.

Api Guide:

https://www.nps.gov/subjects/developer/guides.htm

Terms of Use:

https://www.nps.gov/aboutus/disclaimer.htm

Copyright law does not protect “any work of the U.S. Government” where “a work prepared by an officer or employee of the U.S. Government as part of that person's official duties” (See, 17 U.S.C. §§ 101, 105). Thus, material created by the NPS and presented on this website, unless otherwise indicated, is generally considered in the public domain. It may be distributed or copied as permitted by applicable law.

Run

1. First create a .env file based on the example and replace the NPS_API_KEY

cp .env.example .env

2. Run the fetch and generate the sql

make run && cd data

3. Start sqlite

sqlite3 nps.sqlite

4. Execute the queries

.read ./sqlite/create.sql
.read ./insert/create.sql
.read ./insert/parks.sql
.read ./insert/campgrounds.sql
.read ./insert/tours.sql

5. Validate sqlite

select count(1) from amenity_season;
select count(1) from campground;
select count(1) from duration;
select count(1) from fee;
select count(1) from park;
select count(1) from park_fee;
select count(1) from state_code;
select count(1) from tour;

Expected output:

sqlite> select count(1) from amenity_season;
4
sqlite> select count(1) from campground;
638
sqlite> select count(1) from duration;
4
sqlite> select count(1) from fee;
16
sqlite> select count(1) from park;
471
sqlite> select count(1) from park_fee;
523
sqlite> select count(1) from state_code;
54
sqlite> select count(1) from tour;
618

6. Start duckdb

duckdb nps.duckdb

7. Execute the queries

.read ./duckdb/create.sql
.read ./insert/create.sql
.read ./insert/parks.sql
.read ./insert/campgrounds.sql
.read ./insert/tours.sql

5. Validate duckdb

select count(1) from amenity_season;
select count(1) from campground;
select count(1) from duration;
select count(1) from fee;
select count(1) from park;
select count(1) from park_fee;
select count(1) from state_code;
select count(1) from tour;

Expected output:

D select count(1) from amenity_season;
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│        4 │
└──────────┘
D select count(1) from campground;
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│      637 │
└──────────┘
D select count(1) from duration;
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│        4 │
└──────────┘
D select count(1) from fee;
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│       16 │
└──────────┘
D select count(1) from park;
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│      471 │
└──────────┘
D select count(1) from park_fee;
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│      524 │
└──────────┘
D select count(1) from state_code;
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│       54 │
└──────────┘
D select count(1) from tour;
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│      618 │
└──────────┘