- selected appropriate fields from data download page, I took all of them, just in case they might be useful
- did this for tables for 1995-2009, I really would have appreciated a way to do multiple years at once.
- go through each column to make sure I understand what it symbolizes
- rowid – added by SQL to keep records straight
- trucks- number of incoming trucks
- trains – number of incoming trains
- loaded and empty truck containers – refers to those arriving, containers are used for commercial purposes
- loaded and empty rail containers, same as above, but for trains
- personal vehicles – Number Of Privately Owned Vehicles (POVs) Arriving At A Particular Port. Includes Pick-Up Trucks, Motorcycles, Recreational Vehicles, Taxis, Snowmobiles, Ambulances, Hearses, And Other Motorized Private Ground Vehicles.
- passengers in personal vehicles – Persons Entering The United States At A Particular Port By Private Automobiles, Pick-Up Trucks, Motorcycles, Recreational Vehicles, Taxis, Ambulances, Hearses, Tractors, Snowmobiles And Other Motorized Private Ground Vehicles.
- Buses – Number Of Arriving Buses At A Particular Port, Whether Or Not They Are Carrying Passengers.
- Bus passengers – Number of Persons Arriving By Bus Requiring U.S. Customs Processing.
- pedestrians – The Number Of Persons Arriving On Foot Or By Certain Conveyance (Such As Bicycles, Mopeds, Or Wheel Chairs) Requiring U.S. Customs Processing.
- Train passengers – Number Of Passengers And Crew Arriving By Train And Requiring U.S. Customs Processing.
- Border – border code, not super in-depth, it’s either “C’ for Canada, or “M” for Mexico
- state – state abbreviation
- state fips – code, useful if I decide to make a .svg map
- state name – written out name of state
- port – name of port
- year – self-explanatory
- month – ditto
QUESTIONS:
- starting with 2009, just because it’s a starting point, once I find something interesting, will see if it’s true over time
- Top five in number of passengers crossing the border in a car
- SELECT * FROM b_2009 ORDER BY PASSENGERS_PERSONAL_VEHICLES DESC
- Most goes to San Ysidro, Calif., which takes up most of the top spots with various months, second highest title goes to El Paso, Texas
- What if I sort by trucks?
- SELECT * FROM b_2009 ORDER by TRUCKS desc
- It’s a distinct answer from the others. The most came through Laredo, Texas in various months. Detroit came in second, and Buffalo came in third. Big cities are probably prominent for trucks passing through with industrial or commercial items
- If you run this query going back through the years, the two cities on the top remain the same, but more big cities are prominently featured. It might be a story to find out if people went there more often, or what
- Sorting for personal vehicles in 2009 yields about the same results as how many passengers there are. That makes sense, but it would have been interesting, if there was some sort of discrepancy
- That accounts for people traveling by car, and how that changes over time, but what about passengers traveling by bus?
- Laredo, Texas, which came in the top ten, but not in the top 1 or 2 in car passengers, is top here fairly often. Even in 2009, Buffalo continues to get a lot of bus traffic. This makes me start to wonder if the number of people going to mid-sized cites like Buffalo didn’t decrease, but started transitioning to traveling by bus more often. Something to look into.
- SELECT * FROM b_2009 ORDER BY bus_passengers desc
- But how many pedestrians are crossing the border in 2009? My theory would be that more people are crossing the borders by Canada and Mexico, in less populated areas (not Buffalo) where mass transit is less readily available. Survey shows…
- El Paso, Texas and San Ysidro are top here, same top two for passing through by car in 2009. Laredo, Texas also continues to be up high. It’s interesting that Calexico is the fourth city mentioned here, although it’s not mentioned that people are passing through it either by bus or car? What makes it so popular?
- Because it seems relevant, let’s take a closer look at how many people are coming through per car, on average, at each of the different ports.
- SELECT ( passengers_personal_vehicles/personal_vehicles), * FROM b_2009 ORDER BY 1 desc
- Are more people crammed into a car in one port?
- In 2009, Piegan, Mt. is interesting, because an average of 4.5 passengers were in each car. That’s a whole person more than the next highest entries, in the 3.5 arena. Those included Presido, Texas, Rio Grande City, Texas and Northgate, North Dakota.
- Were as many people squishing into cars from those areas in 1995?
- I think the first entry here is flawed It says there were only 8 vehicles, but more than 15,000 passengers who passed through. I would research this more, or disregard this statement that applies to Norton, Vermont.
- Hidalgo, Texas is next with an average of 12 people, and I’m not sure I believe this number either. Blaine, Wash. And Sault Ste. Marie in Mich. Both had months where their average of people per car was four. As I think about it, this data isn’t that helpful, since I’m seeing discrepancies, so I can’t confirm that more people were coming in a car in a single year. But it does confirm my point that because of the variance, measuring a trend is better done by passengers than by counting vehicles.
- How many people passed through all ports via passenger vehicle, bus and on foot in all years. Let’s look every 5, just to get a sense of this.
- 1995: “260236692″
- SELECT sum(passengers_personal_vehicles+pedestrians+bus_passengers) FROM b_1995
- 2000: “339381053″
- 2005: “270820486″
- Interesting, it hit a high point in the millennium, but has gone back down.
- All years:
- 1995: 260236692
- 1996: “269587425″
- 1997: “320918540″
- 1998: “321727182″
- 1999: “341786837″
- 2000: “339381053″
- 2001: “310684867″
- 2002: “297335810″
- 2003: “283304722″
- 2004: “278246195″
- 2005: “270820486″
- 2006: “265129982″
- 2007: “244651753″
- 2008: “231840012″
- 2009: “111546687″
- Hmm, looks like it went markedly down during the mortgage crisis/recession of 2007, and contiues to drop. And the first dip is around the dot-com bust. Could be part of an interesting comparison of how these two economic events affected travel around the country.
- But how does the use of different types of transportation break down? Are less people using cars because of gas issues?
- Year Personal Vehicle Pedestrians Bus Passengers
- 1995: “265959174″,”33533935″,”5101362″
- 1996: “272593220″,”34717351″,”5813778″
- 1997: “307001980″,”44461186″,”6896919″
- 1998: “314295857″,”45060023″,”7608484″
- 1999: “330891505″,”48801064″,”7725590″
- 2000: “329841500″,”47674833″,”8338859″
- 2001: “284076951″,”52251126″,”7823231″
- 2002: “268007308″,”51359963″,”8139017″
- 2003: “255834018″,”49601266″,”7526855″
- 2004: “254206275″,”48910252″,”7278897″
- 2005: “248568824″,”46434951″,”7024637″
- 2006: “242241051″,”46785153″,”6686385″
- 2007: “222782079″,”49980029″,”7073686″
- 2008: “215383063″,”45341306″,”6859935″
- 2009: “109780729″,”23744307″,”2876675″
- SELECT sum(passengers_personal_vehicles), sum(pedestrians), sum(bus_passengers) FROM b_2009
- See a Many Eyes visualization of all this info here, a little too complicated to parse in text form.
- Group this info by state to see how the total number of bus, pedestrian and car passengers passed through each state, in all months. Tables follow.
- SELECT sum(passengers_personal_vehicles), sum(pedestrians), sum (bus_passengers), state_nm FROM b_1995 GROUP BY state_nm ORDER BY 1 DESC
- 1995:
- By Personal Ve Pedestrians Bus State
- “110825112″,”15443565″,”1297859″,”Texas”
- “36264970″,”9662965″,”249220″,”California”
- “32425278″,”34623″,”753545″,”Michigan”
- “24583106″,”361408″,”1624284″,”New York”
- “21560000″,”7621087″,”24103″,”Arizona”
- “18900506″,”92902″,”526195″,”Washington”
- “9883323″,”119625″,”74209″,”Maine”
- “3407874″,”22981″,”164802″,”Vermont”
- “3049219″,”39083″,”103763″,”Minnesota”
- “1974786″,”10483″,”134326″,”North Dakota”
- “1717032″,”12710″,”52985″,”Montana”
- “595033″,”3370″,”9456″,”Idaho”
- “502347″,”108355″,”138″,”New Mexico”
- “270588″,”778″,”86477″,”Alaska”
- 1996:
- “118131508″,”16925341″,”1651822″,”Texas”
- “34868938″,”33099″,”791884″,”Michigan”
- “31211452″,”9548097″,”260816″,”California”
- “26097291″,”266917″,”1879633″,”New York”
- “21474592″,”7491274″,”30778″,”Arizona”
- “19708130″,”104891″,”576941″,”Washington”
- “9535133″,”113198″,”66287″,”Maine”
- “3541119″,”21894″,”179514″,”Vermont”
- “3028004″,”35668″,”95801″,”Minnesota”
- “1860578″,”10813″,”116853″,”North Dakota”
- “1638808″,”18365″,”45317″,”Montana”
- “704934″,”144652″,”281″,”New Mexico”
- “533482″,”2361″,”11093″,”Idaho”
- “259251″,”781″,”106758″,”Alaska”
- 1997:
- “123849996″,”18640034″,”1617727″,”Texas”
- “66727612″,”17536073″,”1120631″,”California”
- “27689733″,”15285″,”670598″,”Michigan”
- “27578975″,”225496″,”2194799″,”New York”
- “23182567″,”7614629″,”33528″,”Arizona”
- “17948455″,”105374″,”612747″,”Washington”
- “9216109″,”112392″,”61258″,”Maine”
- “3275049″,”23317″,”177016″,”Vermont”
- “2781857″,”37639″,”99893″,”Minnesota”
- “1699591″,”10140″,”116693″,”North Dakota”
- “1660747″,”15617″,”45905″,”Montana”
- “594816″,”120575″,”780″,”New Mexico”
- “539943″,”3963″,”12281″,”Idaho”
- “256530″,”652″,”133063″,”Alaska”
- 1998:
- “129346286″,”18960732″,”2384596″,”Texas”
- “72113553″,”17757504″,”1194702″,”California”
- “29633612″,,”766900″,”Michigan”
- “26082793″,”305951″,”1948197″,”New York”
- “23974390″,”7601268″,”58055″,”Arizona”
- “14099556″,”74095″,”549669″,”Washington”
- “8548623″,”122024″,”110401″,”Maine”
- “3042219″,”21965″,”174233″,”Vermont”
- “2882280″,”45426″,”93372″,”Minnesota”
- “1616426″,”15869″,”44043″,”Montana”
- “1577235″,”9980″,”119163″,”North Dakota”
- “578441″,”142050″,”1459″,”New Mexico”
- “497022″,”2583″,”13756″,”Idaho”
- “303421″,”576″,”149938″,”Alaska”
- 1999:
- “139779387″,”21355816″,”2040085″,”Texas”
- “75215939″,”18278225″,”1215618″,”California”
- “29456488″,,”864090″,”Michigan”
- “25477936″,”312779″,”2245133″,”New York”
- “25221458″,”8379549″,”100838″,”Arizona”
- “15803125″,”66927″,”573158″,”Washington”
- “8176381″,”120816″,”60274″,”Maine”
- “3302277″,”28802″,”179728″,”Vermont”
- “2931562″,”26147″,”99645″,”Minnesota”
- “1806294″,”21197″,”53520″,”Montana”
- “1628695″,”8002″,”117442″,”North Dakota”
- “1305526″,”199644″,”1577″,”New Mexico”
- “526134″,”2728″,”18248″,”Idaho”
- “260303″,”432″,”156234″,”Alaska”
- 2000:
- “136785813″,”19910809″,”1626748″,”Texas”
- “74569309″,”18596679″,”1670733″,”California”
- “32470866″,,”1157136″,”Michigan”
- “26856458″,”8390803″,”167035″,”Arizona”
- “25302257″,”286693″,”2475160″,”New York”
- “14239259″,”102167″,”566670″,”Washington”
- “7968478″,”121807″,”64023″,”Maine”
- “3123217″,”21835″,”192395″,”Vermont”
- “3040019″,”27888″,”98449″,”Minnesota”
- “1675262″,”7303″,”111875″,”North Dakota”
- “1582972″,”191351″,”1400″,”New Mexico”
- “1453161″,”14418″,”39930″,”Montana”
- “510001″,”2864″,”18177″,”Idaho”
- “264428″,”216″,”149128″,”Alaska”
- 2001:
- “116614151″,”20620863″,”1786362″,”Texas”
- “67410517″,”21699797″,”1402404″,”California”
- “24369654″,”421180″,”2080114″,”New York”
- “23726701″,”8994847″,”174718″,”Arizona”
- “21976050″,”1203″,”1269017″,”Michigan”
- “12567027″,”136717″,”497602″,”Washington”
- “6828027″,”117928″,”52946″,”Maine”
- “2945693″,”23186″,”175345″,”Vermont”
- “2733406″,”28897″,”91355″,”Minnesota”
- “1508709″,”9625″,”98554″,”North Dakota”
- “1354477″,”185814″,”3311″,”New Mexico”
- “1306536″,”8358″,”36259″,”Montana”
- “484420″,”2393″,”16311″,”Idaho”
- “251583″,”318″,”138933″,”Alaska”
- 2002:
- “102258073″,”21703683″,”1924657″,”Texas”
- “68180103″,”18628200″,”1813716″,”California”
- “26895469″,”9682233″,”177830″,”Arizona”
- “25641308″,”825447″,”2019817″,”New York”
- “18345404″,,”1200584″,”Michigan”
- “9930974″,”93643″,”430370″,”Washington”
- “6053625″,”101470″,”50034″,”Maine”
- “2911578″,”19530″,”154757″,”Vermont”
- “2557751″,”25605″,”75812″,”Minnesota”
- “1687047″,”264165″,”9951″,”New Mexico”
- “1576205″,”7459″,”92536″,”North Dakota”
- “1309704″,”6056″,”27653″,”Montana”
- “403961″,”2225″,”19850″,”Idaho”
- “256106″,”247″,”141450″,”Alaska”
- 2003:
- “96894839″,”21056220″,”1942990″,”Texas”
- “70757903″,”18193283″,”1576737″,”California”
- “24424403″,”9154958″,”209897″,”Arizona”
- “21197261″,”662036″,”1699214″,”New York”
- “16503867″,,”1194294″,”Michigan”
- “9489064″,”109539″,”376886″,”Washington”
- “6084947″,”105011″,”38298″,”Maine”
- “2716645″,”16158″,”129926″,”Vermont”
- “2663918″,”29189″,”76477″,”Minnesota”
- “1620337″,”259312″,”17261″,”New Mexico”
- “1533132″,”6289″,”75932″,”North Dakota”
- “1343832″,”7018″,”26539″,”Montana”
- “361905″,”1907″,”14837″,”Idaho”
- “241965″,”346″,”147567″,”Alaska”
- 2004:
- “97828112″,”20440329″,”1845676″,”Texas”
- “66393907″,”18197094″,”1315400″,”California”
- “25114067″,”9186005″,”209100″,”Arizona”
- “21255079″,”549740″,”1655590″,”New York”
- “16112329″,,”1268237″,”Michigan”
- “10154189″,”102652″,”427738″,”Washington”
- “6719885″,”115011″,”47765″,”Maine”
- “2860300″,”29769″,”81033″,”Minnesota”
- “2635854″,”12804″,”140737″,”Vermont”
- “1600521″,”260807″,”18341″,”New Mexico”
- “1563139″,”5298″,”80602″,”North Dakota”
- “1362559″,”4893″,”30417″,”Montana”
- “352633″,”1784″,”12393″,”Idaho”
- “253701″,”4066″,”145868″,”Alaska”
- 2005:
- “95157818″,”19017249″,”1616738″,”Texas”
- “66531176″,”16462335″,”1289332″,”California”
- “22539153″,”10074501″,”242861″,”Arizona”
- “20569589″,”372805″,”1797241″,”New York”
- “16396285″,,”1157162″,”Michigan”
- “10149614″,”89286″,”392196″,”Washington”
- “6835788″,”87153″,”53984″,”Maine”
- “2760452″,”26103″,”78850″,”Minnesota”
- “2146056″,”14485″,”104549″,”Vermont”
- “1839301″,”275527″,”20848″,”New Mexico”
- “1559860″,”5189″,”81999″,”North Dakota”
- “1476812″,”4533″,”29268″,”Montana”
- “368475″,”1796″,”11976″,”Idaho”
- “238445″,”3989″,”147633″,”Alaska”
- 2006:
- “91493127″,”19153797″,”1357967″,”Texas”
- “65345181″,”15517700″,”1425872″,”California”
- “20922635″,”348551″,”1299995″,”New York”
- “20382103″,”11328799″,”378829″,”Arizona”
- “15695808″,”8435″,”1106154″,”Michigan”
- “10644242″,”71074″,”549391″,”Washington”
- “6696129″,”57286″,”51883″,”Maine”
- “2739696″,”10985″,”151489″,”Vermont”
- “2576831″,”21924″,”68423″,”Minnesota”
- “2034603″,”251118″,”24614″,”New Mexico”
- “1576888″,”2955″,”26751″,”Montana”
- “1535121″,”7300″,”75722″,”North Dakota”
- “387082″,”1702″,”12446″,”Idaho”
- “211605″,”3527″,”156849″,”Alaska”
- 2007:
- “84407511″,”20914686″,”1808452″,”Texas”
- “57991451″,”16553220″,”1230642″,”California”
- “20126570″,”278797″,”1564239″,”New York”
- “19593375″,”11806206″,”309531″,”Arizona”
- “14761680″,”12250″,”1147139″,”Michigan”
- “10138893″,”64040″,”443474″,”Washington”
- “5035200″,”48188″,”48866″,”Maine”
- “2719317″,”7479″,”138226″,”Vermont”
- “2541925″,”264851″,”40430″,”New Mexico”
- “2166183″,”15466″,”59138″,”Minnesota”
- “1458744″,”11334″,”79258″,”North Dakota”
- “1241714″,”2471″,”23334″,”Montana”
- “391634″,”109″,”10357″,”Idaho”
- “207882″,”932″,”170600″,”Alaska”
- 2008:
- “84299143″,”19033233″,”2136534″,”Texas”
- “53228320″,”15064432″,”1022271″,”California”
- “19382944″,”336146″,”1460680″,”New York”
- “18427240″,”10517482″,”259714″,”Arizona”
- “13539625″,”16202″,”1013033″,”Michigan”
- “10859357″,”73071″,”421686″,”Washington”
- “5000479″,”45007″,”47707″,”Maine”
- “3056284″,”4453″,”129343″,”Vermont”
- “2139051″,”16348″,”58559″,”Minnesota”
- “2027136″,”226493″,”37111″,”New Mexico”
- “1531113″,”5359″,”73809″,”North Dakota”
- “1173809″,”2106″,”22145″,”Montana”
- “528450″,”261″,”10199″,”Idaho”
- “190112″,”713″,”167144″,”Alaska”
- 2009:
- “41651723″,”10922287″,”928282″,”Texas”
- “28272138″,”8317236″,”378101″,”California”
- “9591798″,”137738″,”696227″,”New York”
- “9465716″,”4191105″,”120635″,”Arizona”
- “6878072″,,”252222″,”Michigan”
- “5999170″,”35334″,”216415″,”Washington”
- “2478619″,”16614″,”17683″,”Maine”
- “1499798″,”1845″,”68985″,”Vermont”
- “1186211″,”9321″,”28789″,”Minnesota”
- “1175028″,”107661″,”18182″,”New Mexico”
- “677367″,”2042″,”32691″,”North Dakota”
- “524755″,”972″,”7577″,”Montana”
- “246190″,”246″,”4448″,”Idaho”
- “134144″,”1906″,”106438″,”Alaska”
- I isolated border crossings in Texas, because they seem to have some of the consistently highest numbers. This visualization looks at trends in Texas for bus and vehicle passenger, and pedestrian traffic across years.
- http://manyeyes.alphaworks.ibm.com/manyeyes/visualizations/4d3a2edee53211de92ba000255111976/comments/4d3e8a24e53211de92ba000255111976
- I thought an especially interesting part is that the use of buses went up between 2006-2008, just as the recession hit. It looks like it went down in 2009, but it’s important to consider that the records for 2009 are incomplete. In fact, that’s an important point whenever looking at 2009 across the board in this data set, there are recorded months that only go up through month 7 (July). So we only have half of the information for the year.
- Does this bus trend occur in other states? Let’s try California, another state where there is a lot of action on border ports.
- That visualization is here: http://manyeyes.alphaworks.ibm.com/manyeyes/visualizations/a632a24ce53611de9869000255111976/comments/a6369aa0e53611de9869000255111976
- Bus use didn’t go up in California. Actually, all modes of transportation in California seem to have increased/decreased mostly equally.
- This is probably silly, but of the total number of people coming in and out over time, is there more action on the Canadian or Mexican border? Hypothesis: Mexico.
- See a viz of the tables here: http://manyeyes.alphaworks.ibm.com/manyeyes/visualizations/764be41ce52c11deba74000255111976/comments/764fba6ae52c11deba74000255111976
- What I learned: Canada has been lower than Mexico all along, but lost a much higher percentage of people passing through it. Perhaps it’s considered more indispensible. There have been fewer people going to Mexico over time. The drop seems to have hit Mexico harder in 2008, and even in 2007, than it did during the techno bust in 2000. It probably wasn’t as relevant to the people going back and forth to Mexico.
- Year Canada Mexico
- 1995: “”67633867″ “192602825″
- 1996: “70423702″ “199163723″
- 1997: “67536237″ “253382303″
- 1998: “57141992″ “264585190″
- 1999: “58438684″ “283348153″
- 2000: “56828967″ “282552086″
- 2001: “54288800″ “256396067″
- 2002: “49888264″ “247447546″
- 2003: “44402317″ “238902405″
- 2004: “43470499″ “234775696″
- 2005: “41616428″ “229204058″
- 2006: “41473643″ “223656339″
- 2007: “34581175″ “210070578″
- 2008: “33589494″ “198250518″
- 2009: “16358557″ “95188130″
Recent Comments — Join the Discussion!