Using Queries and Joins
to Answer Questions About
Airports in the United States
Embedded Files
Ryan Grammer
GIST 602B
Questions
Attribute Queries:
Attribute Queries:
- How many public primary (10,000+ enplanements) airports are there in the United States?
- Which states have a population greater than 5 million people and also have a population density greater than 500 people per square mile?
Spatial Queries:
Spatial Queries:
- How many airports are there in California?
- How many airports are there within a 75 mile range of Riverside, CA?
Spatial Joins:
Spatial Joins:
- Which county in California has the most airports?
- Which airports are located in San Diego County?
Combination of the above:
Combination of the above:
- How many public primary (10,000+ enplanements) airports are there in California?
- What is the population of the county in which the Palm Springs Airport (PSP) is located?
Data
The study area of this analysis is the United States, as well at the states of California and Texas. We will look at the entire United States for some questions. Other questions will focus on California and Texas, as well as the counties and cities in California.
The study area of this analysis is the United States, as well at the states of California and Texas. We will look at the entire United States for some questions. Other questions will focus on California and Texas, as well as the counties and cities in California.
There are three datasets that are used in this analysis. There is the US Airports layer, which lists the locations of the airports in the United States along with attributes such as enplanements, elevation, number of arrivals and departures, among others. The California counties layer shows the outline of each county in California, along with the 2024 population of each county. The US States layer shows the outline of each state in the United States, along with the 2024 population, square mileage of each state, along with other attributes.
There are three datasets that are used in this analysis. There is the US Airports layer, which lists the locations of the airports in the United States along with attributes such as enplanements, elevation, number of arrivals and departures, among others. The California counties layer shows the outline of each county in California, along with the 2024 population of each county. The US States layer shows the outline of each state in the United States, along with the 2024 population, square mileage of each state, along with other attributes.
Map of the study area
Metadata table (USA Airports)
Metadata table (US State Boundaries)
Metadata table (California County Boundaries)
Methods
The projection that I used for the continental United States is WGS 1984 Web Mercator, while I used the NAD 1927 California (Teale) Albers (Meters) projection when doing analysis in the state of California. I used the WGS 1984 Web Mercator because it represents the continental United States well in maps. I used the projection NAD 1927 California (Teale) Albers (Meters) because it covers the entire state of California and is used regularly for mapping by agencies such as the US Forest Service.
The projection that I used for the continental United States is WGS 1984 Web Mercator, while I used the NAD 1927 California (Teale) Albers (Meters) projection when doing analysis in the state of California. I used the WGS 1984 Web Mercator because it represents the continental United States well in maps. I used the projection NAD 1927 California (Teale) Albers (Meters) because it covers the entire state of California and is used regularly for mapping by agencies such as the US Forest Service.
Attribute Queries
Attribute Queries
To find out how many public primary (10,000+ enplanements) airports there are in the United States, I used the Select by Attribute tool in ArcGIS Pro. Using US Airports as the input rows, I created 2 queries combined using the AND operator:
To find out how many public primary (10,000+ enplanements) airports there are in the United States, I used the Select by Attribute tool in ArcGIS Pro. Using US Airports as the input rows, I created 2 queries combined using the AND operator:
Select By Attributes tool (ArcGIS Pro)
To find out which states have a population greater than 5 million people and also have a population density greater than 500 people per square mile, I used the Select by Attribute tool in ArcGIS Pro. Using US States Boundaries as the input rows, I created 2 queries combined using the AND operator:
To find out which states have a population greater than 5 million people and also have a population density greater than 500 people per square mile, I used the Select by Attribute tool in ArcGIS Pro. Using US States Boundaries as the input rows, I created 2 queries combined using the AND operator:
Select By Attributes tool (ArcGIS Pro)
Spatial Queries
Spatial Queries
To find out how many airports there are in California, I used the Spatial Join tool in ArcGIS Pro. Using US Airports as the Input Features, I looked for features within California using the California County Boundaries layer as a state boundary.
To find out how many airports there are in California, I used the Spatial Join tool in ArcGIS Pro. Using US Airports as the Input Features, I looked for features within California using the California County Boundaries layer as a state boundary.
Select By Location tool (ArcGIS Pro)
To find out how many airports there are within a 75 mile range of Riverside, CA, I used the Select By Location tool in ArcGIS Pro. Using US Airports as the Input Features, I looked for features intersecting a 75 mile buffer from a point located at the center of the city.
To find out how many airports there are within a 75 mile range of Riverside, CA, I used the Select By Location tool in ArcGIS Pro. Using US Airports as the Input Features, I looked for features intersecting a 75 mile buffer from a point located at the center of the city.
Select By Location tool (ArcGIS Pro)
Spatial Joins
Spatial Joins
To find out which county in California has the most airports, I used the Spatial Join tool in ArcGIS Pro. Using California County Boundaries as the Target Features, and US Airports as the Join Features, I created a new feature layer with an attribute table that tells me the join count (number of airports) for each county in the layer.
To find out which county in California has the most airports, I used the Spatial Join tool in ArcGIS Pro. Using California County Boundaries as the Target Features, and US Airports as the Join Features, I created a new feature layer with an attribute table that tells me the join count (number of airports) for each county in the layer.
Spatial Join tool (ArcGIS Pro)
To find out which which airports are located in San Diego County, I used the Spatial Join tool in ArcGIS Pro. Using US Airports as the Target Features, and California County Boundaries as the Join Features, I created a new feature layer with an attribute table that tells me the join count (whether the airport is in a California county) for each county in the layer. I can then sort by county name and find all airports that are in San Diego County.
To find out which which airports are located in San Diego County, I used the Spatial Join tool in ArcGIS Pro. Using US Airports as the Target Features, and California County Boundaries as the Join Features, I created a new feature layer with an attribute table that tells me the join count (whether the airport is in a California county) for each county in the layer. I can then sort by county name and find all airports that are in San Diego County.
Spatial Join tool (ArcGIS Pro)
Combination
Combination
To find out how many public primary (10,000+ enplanements) airports there are in California, I used the Select By Attributes tool in conjunction with the Select By Location tool in ArcGIS Pro. For the Select By Attribute tool, I used US Airports as the input rows and created 2 queries combined using the AND operator like before. I then used the Select By Location tool with the input as the US Airports with the selected records.
To find out how many public primary (10,000+ enplanements) airports there are in California, I used the Select By Attributes tool in conjunction with the Select By Location tool in ArcGIS Pro. For the Select By Attribute tool, I used US Airports as the input rows and created 2 queries combined using the AND operator like before. I then used the Select By Location tool with the input as the US Airports with the selected records.
Select By Attribute tool (ArcGIS Pro)
Select By Location tool (ArcGIS Pro)
To find out the population of the county in which the Palm Springs Airport (PSP) is located, I used the Spatial Join tool in ArcGIS Pro. Using US Airports as the Target Features, and California County Boundaries as the Join Features, I created a new feature layer with an attribute table that has the county and the population of the county where the airport is located. I then can use the Select By Attributes tool to search "City is equal to Palm Springs".
To find out the population of the county in which the Palm Springs Airport (PSP) is located, I used the Spatial Join tool in ArcGIS Pro. Using US Airports as the Target Features, and California County Boundaries as the Join Features, I created a new feature layer with an attribute table that has the county and the population of the county where the airport is located. I then can use the Select By Attributes tool to search "City is equal to Palm Springs".
Spatial Join tool (ArcGIS Pro)
Results
There are 334 public primary (10,000+ enplanements) airports in the United States.
There are 334 public primary (10,000+ enplanements) airports in the United States.
Output table (ArcGIS Pro)
The states that have a population greater than 5 million people and also have a population density greater than 500 people per square mile are: New Jersey, Massachusetts, and Maryland
The states that have a population greater than 5 million people and also have a population density greater than 500 people per square mile are: New Jersey, Massachusetts, and Maryland
Output table (ArcGIS Pro)
There are 54 airports in California.
There are 54 airports in California.
Output table (ArcGIS Pro)
There are 11 airports within a 75 mile radius of Riverside, CA.
There are 11 airports within a 75 mile radius of Riverside, CA.
Output table (ArcGIS Pro)
Los Angeles county has the most airports in California with 5.
Los Angeles county has the most airports in California with 5.
Output table (ArcGIS Pro)
The airports located in San Diego County are: Miramar Marine Corps Air Station, North Island Naval Air Station, San Diego International Airport, and Brown Field Municipal Airport
The airports located in San Diego County are: Miramar Marine Corps Air Station, North Island Naval Air Station, San Diego International Airport, and Brown Field Municipal Airport
Output table (ArcGIS Pro)
There are 25 public primary (10,000+ enplanements) airports in California.
There are 25 public primary (10,000+ enplanements) airports in California.
Output table (ArcGIS Pro)
The population of the county in which the Palm Springs Airport (PSP) is located is 2,510,643.
The population of the county in which the Palm Springs Airport (PSP) is located is 2,510,643.
Output table (ArcGIS Pro)
Conclusion
We used many different queries and joins to answer our original questions of the analysis. We used attribute queries to find records in the attribute table of the layer that matched our clauses. We used spatial queries to find points that lay inside a polygon. We used spatial joins to find the count of points in a specific polygon within a larger dataset. Finally, we combined attribute queries, spatial queries, and spatial joins to answer more complex questions. There were not any limitations to this analysis, as it was pretty straightforward. The only mention would be that I only included the Lower 48 states, excluding Hawaii and Alaska, but included Washinton, DC. Here are the questions, along with the answers to them:
We used many different queries and joins to answer our original questions of the analysis. We used attribute queries to find records in the attribute table of the layer that matched our clauses. We used spatial queries to find points that lay inside a polygon. We used spatial joins to find the count of points in a specific polygon within a larger dataset. Finally, we combined attribute queries, spatial queries, and spatial joins to answer more complex questions. There were not any limitations to this analysis, as it was pretty straightforward. The only mention would be that I only included the Lower 48 states, excluding Hawaii and Alaska, but included Washinton, DC. Here are the questions, along with the answers to them:
How many public primary (10,000+ enplanements) airports are there in the United States?
How many public primary (10,000+ enplanements) airports are there in the United States?
There are 334 public primary (10,000+ enplanements) airports in the United States.
There are 334 public primary (10,000+ enplanements) airports in the United States.
Which states have a population greater than 5 million people and also have a population density greater than 500 people per square mile?
Which states have a population greater than 5 million people and also have a population density greater than 500 people per square mile?
The states that have a population greater than 5 million people and also have a population density greater than 500 people per square mile are: New Jersey, Massachusetts, and Maryland
The states that have a population greater than 5 million people and also have a population density greater than 500 people per square mile are: New Jersey, Massachusetts, and Maryland
How many airports are there in California?
How many airports are there in California?
There are 54 airports in California.
There are 54 airports in California.
How many airports are there within a 75 mile range of Riverside, CA?
How many airports are there within a 75 mile range of Riverside, CA?
There are 11 airports within a 75 mile radius of Riverside, CA.
There are 11 airports within a 75 mile radius of Riverside, CA.
Which county in California has the most airports?
Which county in California has the most airports?
Los Angeles county has the most airports in California with 5.
Los Angeles county has the most airports in California with 5.
Which airports are located in San Diego County?
Which airports are located in San Diego County?
The airports located in San Diego County are: Miramar Marine Corps Air Station, North Island Naval Air Station, San Diego International Airport, and Brown Field Municipal Airport
The airports located in San Diego County are: Miramar Marine Corps Air Station, North Island Naval Air Station, San Diego International Airport, and Brown Field Municipal Airport
How many public primary (10,000+ enplanements) airports are there in California?
How many public primary (10,000+ enplanements) airports are there in California?
There are 25 public primary (10,000+ enplanements) airports in California.
There are 25 public primary (10,000+ enplanements) airports in California.
What is the population of the county in which the Palm Springs Airport (PSP) is located?
What is the population of the county in which the Palm Springs Airport (PSP) is located?
The population of the county in which the Palm Springs Airport (PSP) is located is 2,510,643.
The population of the county in which the Palm Springs Airport (PSP) is located is 2,510,643.
Page updated
Report abuse