Week 8.2 - How walkable is my favorite location in the US?

Upload you SQL script named mywalkability_yourfullname.sql with your answer to the questions to Canvas: https://ucsb.instructure.com/courses/32934/assignments/477543

In this assignment, you will investigate the walkability of a chosen location in the United States and compare its score against regional averages. Your final submission will be a DuckDB SQL script uploaded to Canvas that executes the following steps:

Initialize a new DuckDB database.
Import State and County names from the fips_state_county.csv, saving it as a table named Fips.
Load the Walkability Index data for your selected state from walkability_wgs84.parquet into a table named Walkability_mystate (for example, Walkability_ca for California).
Identify the specific Walkability Index value for your location’s US Census Group Block.
Conduct a comparison between this value and the average scores for its respective Census Tract and County.
Export the resulting table to a csv file format.

To do so, we will need to use two extensions from DuckDB spatial and httpfs:

-- INSTALL spatial;
LOAD spatial;
-- INSTALL httpfs;
LOAD httpfs;

Choosing you location

Select a US-based location to research, such as your hometown or a favorite destination.
Determine the latitude and longitude coordinates for your chosen site using Google Maps or a similar tool.
Include these geographic details (name, lat, long, …) as a comment within your SQL script.

Data import (15pts)

There are two files we need to import:

Create at table named Fips from the State and County Name: fips_state_county.csv (5pts)
Create a table named Walkability_mystate (e.g. Walkability_ca for California) from the Walkability national database in WGS84 projection: walkability_wgs84.parquet by selecting the following columns: GEOID10, STATEFP, COUNTYFP, TRACTCE, BLKGRPCE, CBSA, CBSA_Name, TotPop, NatWalkInd, geom_wgs84 and adding a WHERE close for your state (10pts)

Good news, those files are ready on Workbench! You can import them using the following URL + the filename you want to import: https://apps.bren.ucsb.edu/eds213-data/walkability/

Hint: Look at the DuckDB documentation (https://duckdb.org/docs/current/data/overview) for read_csv and read_parquet.

Your schema should look like this:

───────────────────────── main ────────────────────────── 
┌──────────────────────────────────┐┌─────────────────────┐
│          Walkability_ca          ││        Fips         │
│                                  ││                     │
│ GEOID10    varchar               ││ STATEFP     varchar │
│ STATEFP    varchar               ││ State_name  varchar │
│ COUNTYFP   varchar               ││ COUNTYFP    varchar │
│ TRACTCE    varchar               ││ County_name varchar │
│ BLKGRPCE   varchar               ││                     │
│ CBSA       varchar               ││      3154 rows      │
│ CBSA_Name  varchar               │└─────────────────────┘
│ TotPop     integer               │
│ NatWalkInd double                │
│ geom_wgs84 geometry('ogc:crs84') │
│                                  │
│            23212 rows            │
└──────────────────────────────────┘

Join Tables (10pts)

Join the two tables and store the results in a VIEW named Walkind_mystate

Our schema should like something like this:

 ──────────────────────────────────────────── main ──────────────────────────────────────────── 
┌──────────────────────────────────┐┌───────────────────────────────────┐┌─────────────────────┐
│          Walkability_ca          ││            Walkind_ca             ││        Fips         │
│                                  ││                                   ││                     │
│ GEOID10    varchar               ││ GEOID10     varchar               ││ STATEFP     varchar │
│ STATEFP    varchar               ││ STATEFP     varchar               ││ State_name  varchar │
│ COUNTYFP   varchar               ││ COUNTYFP    varchar               ││ COUNTYFP    varchar │
│ TRACTCE    varchar               ││ TRACTCE     varchar               ││ County_name varchar │
│ BLKGRPCE   varchar               ││ BLKGRPCE    varchar               ││                     │
│ CBSA       varchar               ││ CBSA        varchar               ││      3154 rows      │
│ CBSA_Name  varchar               ││ CBSA_Name   varchar               │└─────────────────────┘
│ TotPop     integer               ││ TotPop      integer               │
│ NatWalkInd double                ││ NatWalkInd  double                │
│ geom_wgs84 geometry('ogc:crs84') ││ geom_wgs84  geometry('ogc:crs84') │
│                                  ││ State_name  varchar               │
│            23212 rows            ││ County_name varchar               │
└──────────────────────────────────┘└───────────────────────────────────┘

Walkability index at your location (15pts)

You are now ready to execute your initial spatial query! Your objective is to identify the US Census Group Block containing your chosen point of interest, following the methodology we practiced in class.

Determine the walkability index for your location. In your script, include a comment that evaluates this value against the walkability index scale and note whether the findings align with your expectations.

Hint: Keep in mind that the latitude and longitude sequence in Google Maps may differ from that required by the ST_Point function.

Average Walkability index at your Census Tract (10pts)

Now, compute the average Walkability index at the Census tract level and the number of Group Block within this Tract, getting your Tract ID from the previous query.

Hint: A Tract ID is only unique within one specific County and the County FIPS is only unique within a specific State!!

Your results should look like this:

┌─────────┬─────────────┬───────────────────┐
│ TRACTCE │ Block_count │ Walkind_tract_avg │
│ varchar │    int64    │      double       │
├─────────┼─────────────┼───────────────────┤
│ 000400  │           5 │              11.7 │
└─────────┴─────────────┴───────────────────┘

Average Walkability index at your County Level (10pts)

Now, compute the average Walkability index at the County level and the total number of Group Blocks in that County. Get the necessary information from previous queries.

┌──────────┬───────────────┬─────────────┬────────────────────┐
│ COUNTYFP │  County_name  │ Block_count │ Walkind_county_avg │
│ varchar  │    varchar    │    int64    │       double       │
├──────────┼───────────────┼─────────────┼────────────────────┤
│ 083      │ SANTA BARBARA │         313 │ 10.495740149094784 │
└──────────┴───────────────┴─────────────┴────────────────────┘

Discuss your results (5pts)

Add a comment in your SQL script to discuss how your favorite location’s walkability index compares to its surrounding. From your knowledge about the area, does that make sense?

Export your results (20pts)

It is time to export your results! We want a table with (15pts):

Only Observations for the Tract of your location of interest
Add the average Tract walkability as a column (same value repeating)
Add the average County walkability as a column (same value repeating)

Save it as a csv file. Inspect this csv file, was the geometry column saved? Wouls another file format allowed us to keep this information? Comment in your script (5pts).

No need to submit your csv file

Credit: 85 points