Working with Attribute Data

 Working with Attribute Data using the FAA Airman Database


This post will incorporate the FAA Airman Database and understanding how tables can be joined using common fields. By using table joins, we will perform attribute based queries and generate new feature classes with those queries. 

When we first open the data in ArcGIS Pro, there are 4 different features: 
  1. airman_pts_count_us
  2. Fishnet_10km_cont_us
  3. us_zipcode_cont
  4. continental_us_counties_lambert
Figure 84: Four Different Features


Now if we look at the projection information for each of these features, we can see that a couple of them are different.
  1. airman_pts_count_us: WGS_1984
  2. Fishnet_10km_cont_us: GCS_1983
  3. us_zipcode_cont: Lambert
  4. continental_us_counties_lambert: Lambert
Projections are a mathematical transformation that takes latitude and longitude coordinates and transform them to an XY coordinate system. This helps you to create a map that accurately shows distances, areas, or directions. This means you can accurately work with the data to calculate areas and distances and measure directions. Projections are transformations from spherical coordinates to XY coordinates systems and transformations from one XY coordinate system to another. Lambert conformal conic is a great way to represent the continental U.S. and is what we will be using for this project.

In order to re-project the features:
  1. Under analysis tab, click tools
  2. Search for "Project"
  3. Since airman_pts and Fishnet_10km are the only two not in Lambert, set both of those as the input. You can select a feature class that has the lambert conformal conic by selecting that layer in the output coordinate system list. I used continental_us_counties_lambert.
  4. For Output, be sure to add Lambert on the end so you know that one has been re-projected
  5. Once both has been re-projected, you can remove the originals from contents 
Next we are going to look at tables and how we can use table joins to perform attribute based queries and generate new feature classes with those queries. There is a table in the Standalone Tables section in the contents called "PILOT_CERT" (Figure 84). That table contains valuable information that we can link to the airmen_pts layer. 

Figure 85: PILOT_CERT Standalone Table


In the table, there is a column called "Type" and below I've listed out the different Cert type letters along with the definitions of each one.
  • P - Pilot 
  • F - Flight Instructor 
  • A - Authorized Aircraft Instructor
  • U - Remote Pilot
  • G - Ground Instructor
  • E - Flight Engineer 
  • H - Special Purpose Flight Instructor 
  • X - Foreign Based Flight Engineer
  • M - Mechanic 
  • T - Control Tower Operator 
  • R - Repairman 
  • I - Repairman Experimental Aircraft Builder 
  • L - Repairman Light Sport Aircraft 
  • W - Master/Senior Parachute Rigger
  • D - Dispatcher 
  • N - Flight Navigator 
  • J - Special Purpose Flight Navigator
In order to link the table and our airman_pts, we need to find a common field between the two, also known as Join Field. Leave the PILOT_CERT table open, then right click airman_pts and select "Attribute Table". While scrolling through both tables, you will find the common field between the two is "Unique ID" we will use this to join the tables.
  1. Right click airman_pts_count_us, go to Joins and Relates and select Add Join
  2. Ensure PILOT_CERT is the join table
  3. Set Input Join Field to Unique ID
Now that we have our tables joined, we can start doing queries. This is where we can start making a series of maps using different criteria. When you turn on the airman_pts, you can see all the points on the map; but now that the tables are joined, we can query out the different "Types"
  1. Right click airman_pts and select symbology 
  2. Change single point to unique values
  3. Change Field 1 to "TYPE" 
Figure 86: Airman Points By Different Types


You can right click any of the other Types and remove them if you don't want them in the dataset. You can also change the color scheme for a different look if you want to as well. So if you wanted a map showing all the pilots in the US by different types, it would look like this:

Figure 87: All Pilots In The United States


If you want all commercial pilots in the United States it would look like this:

Figure 88: All Commercial Pilots In The United States


Now let's say that you want quarry out different attributes. For example, if you wanted only airman in Indiana, you could quarry out points that aren't in Indiana.
  1. Open airman_pts_count_us attribute table
  2. Find the column "Region" and you can see all the different states where airmen live
  3. Close the table and under Feature Layer, Data, and click "Export Features"
  4. For Input Feature: airmen_pts_cont_us_Lambert
  5. Set output name to airmen_Indiana
  6. Click new expression and set the following parameters:
    1. Where: Region
    2. Is Equal To
    3. Indiana
  7. Click Okay
Now if you unselect the total airman_pts and only have the airmen_Indiana selected, you will only see the points in Indiana.

Figure 89: Airmen In Indiana



You could go even further and quarry out the Indiana counties from the continental_us_counties_lambert layer. Follow the same steps for quarrying Indiana airmen points but for Indiana counties. Then we can create a map with only Indiana airmen by counties.
  1. Where: STATEP
  2. Is equal to 
  3. 18
Click Okay

Figure 90: All Pilots In Indiana By Counties



Now we understand how tables can be joined using common fields; and how using table joins, we can perform attribute based queries and generate new feature classes with those queries to create cartographic maps.

Comments

Post a Comment