Selecting features within joined attribute table that show intersection between street type and range of average traffic flows using ArcMap

Question

I am trying to do a data analysis of traffic streets that would show the amount of Average Daily Traffic (ADT) as it corresponds to streets with 2 lanes. I have a feature class that shows ADT and would only like to select from this feature numbers in the range of 3500-6000 adt. I have a feature class with street types that shows streets with T2, 2 lane intersections. I would like to see how this data correlates.

The SQL operator I have tried using is:

"2018_Traffic_Flow_Counts.AWDT" >3500 AND "Traffic_Lanes.LANETYPE" = 'T2' AND NOT "2018_Traffic_Flow_Counts.AWDT" >6000

but it is showing me all T2 Traffic lanes as well as all adt between 3500-6000, instead of just showing the t2 lanes that intersect with traffic lanes with t2 and the selected adt.

Answer 1

Sounds like you need to join the two data tables together so that you know lane type and flow count for each street. Once they are joined, then you can apply your select statement.

You can join using a shared unique ID in SQL with a join statement, such as:

SELECT * FROM 2018_Traffic_Flow_Counts INNER JOIN Traffic_Lanes on 2018_Traffic_Flow_Counts.RoadID = Traffic_Lanes.RoadID

You can also join by ID in ArcMap using Add Join, or do a Spatial Join if your two feature classes don't share a unique ID. A summary of join options in ArcMap is given here.

Then end goal is that you have a single table with both AWDT and LANETYPE or an explicit connection between the two tables so that SQL is aware of both the flow count and lane type for each record.

You might also consider reformatting your SQL query to:

("2018_Traffic_Flow_Counts.AWDT" >3500 AND "2018_Traffic_Flow_Counts.AWDT" <6000) AND "Traffic_Lanes.LANETYPE" = 'T2'