18/1/2018

America’s Wall: How we built a detailed map of the U.S.-Mexico border wall

 

By Brandon Quester, inewsource

The steel and concrete fencing that stretches along the U.S.-Mexico border is a familiar, albeit relatively new sight when you live less than 20 miles from the border.

In San Diego – and dozens of other cities that span the nearly 2,000-mile division – the border wall is part of daily life, the culture of what makes our communities distinctive. And it’s a common topic among reporters working in the area.

So when a colleague with KPBS in San Diego, an inewsource news partner, searched the desert east of California for dead and missing immigrants, she asked U.S. Customs and Border Protection for records detailing each piece of fencing and where it was located. She wanted to know how the fencing was affecting immigration patterns.

The agency’s answer was no. Officials said the files were unreleasable because the details are “law enforcement sensitive”.

Because inewsource and KPBS have a unique partnership – both nonprofit newsrooms run independently but housed in the same space – the KPBS reporter reached out to inewsource to help craft a Freedom of Information Act request. inewsource is a data driven, investigative newsroom, so our expertise in dealing with federal gatekeepers and the data they hold helped spur this ambitious collaboration.

The request asked for specific GIS shapefiles of physical fencing along the entire U.S.-Mexico border. Seven months later, our reporters received responsive records in the form of shapefiles. This included metadata within each of the more than 3,600 individual polygons that comprise today’s modern wall.

We believe these previously undisclosed records to be the most comprehensive look at the border wall to date, prompting what would become the collaborative project, “America’s Wall: Decades-long struggle to secure U.S.-Mexico border”.

Beyond the fencing’s impact on migratory flows, our reporters wanted to know what President Donald Trump’s campaign promise to spend billions of dollars fortifying the current wall meant for people living on each side of the border.

To do that, we took a deep look into how and when today’s modern wall was constructed. We then layered that with additional data such as illegal immigration patterns, Border Patrol staffing and enforcement actions over time.

Combined, this data formed the foundation of our reporting. And, as with any major data project, there were limitations, cleanup and adjustments required. Here’s how we did it:

U.S. Customs and Border Protection shapefiles

The core documents supporting this reporting were obtained through a Freedom of Information Act request to U.S. Customs and Border Protection (CBP), asking specifically for shapefiles and/or GIS files showing the location and makeup of each piece of fencing that comprises the U.S.-Mexico border wall.

CBP grouped fencing under two umbrella types based on what the barrier was intended to stop: pedestrians or vehicles. Those umbrella types were broken down further into more specific subtypes: pedestrian fencing consisted of primary, secondary and tertiary sections; and vehicle fencing was divided into permanent and temporary structures.

The first challenge was that the files were in a custom geodatabase format, which required conversion into a program used in our newsroom. We use an open-source GIS program called Quantum GIS, or QGIS.

Using UTF-8 encoding and the OpenFileGDB file type, we were able to open this custom shapefile in the QGIS interface.


Knowing ahead of time that our goal was to conduct a spatial analysis, including lengths of individual fencing polygons, we needed to make sure we were using the appropriate mapping projection.

Much of this depends on the size of the area you are mapping or analyzing, the specific geolocation and the intended goal of your final product. Luckily, it’s easy to identify the native mapping projection of the shapefile and then convert it to the most appropriate coordinate reference system for the ensuing analysis.

The hard part is choosing the correct projection. But that’s another post entirely. You can find an excellent overview of mapping projections here.

Once the shapefiles and accompanying metadata were loaded into our mapping software, our reporters were able to start reviewing specific attributes contained within the database file of fencing polygons.

Each piece of fencing included a “DATE_IN” attribute, which details when construction of that piece of fencing was completed. But in our vetting process of the data, we came across sections that either did not have a specific year attached or which had an estimate for the year of completion.

We handled the fencing that had estimates on a case-by-case basis. For example, sections of fencing built in the 1970s were grouped in a broad estimate, which we counted as 1970, the earliest possible date. Other sections gave a specific year but estimated the month of completion, or indicated a fiscal year but not a calendar date. We limited our analysis to years.

The finding that stood out from this analysis is that today’s modern wall is relatively new: nearly 90 percent of it was built in the past 12 years. This was highlighted at the top of our interactive map, using the Highcharts javascript library in combination with Mapbox. That was then tied to an information box below the map detailing significant events and layered data for that year, which we’ll describe further below:

The CBP database also included information such as type of fencing, and the state and Border Patrol sector in which it was built. These attributes required further cleanup to maintain consistency throughout the database and provide relevant explanations in our interactive visualizations. For example, each Border Patrol sector was defined with a three letter acronym, such as SDC, which represented the San Diego Sector, stretching from the Pacific Ocean to the neighboring El Centro Sector.

After standardizing and cleaning the underlying data, our reporters were able to take an extraordinarily detailed look at the fencing that makes up the U.S-Mexico border.

However, some elements were not included. Most notably, the shapefiles provided the length of each section of fencing in an “internal unit” – something other than feet or meters.

Using the QGIS field calculator, we were able to extract the length in feet for each section of wall using the $length expression:

This expression creates a new field based on the geometry of each feature, in this case calculating the length of each piece of fencing.

From this, our reporters were able to begin calculating the total distance covered by fencing – 653 discontinuous miles that dot the 1,954-mile U.S.-Mexico border.

This also enabled reporters to identify that the strength of the wall varies as well. Roughly 37 miles of fencing are backed up by secondary fencing, with the most fortified areas in California, Arizona and New Mexico. The least fenced is Texas, where vast swaths of land remain unfenced.

Additional data

In addition to the detailed map data, we layered decades of other CBP statistics to add context. Much of this analysis was done using a combination of Excel and relational database software such as SQLite and Navicat.

Apprehension data showed how many people were detained annually from 1960 through 2016 for attempting to enter the U.S. illegally. This did not include deportations handled by U.S. Immigration and Customs Enforcement, or migrants who tried to enter the U.S. at a port of entry but were declared inadmissible.

We visualized the overall number of apprehensions, by year, with the miles of fencing built by year to show how fencing affected the overall flow of illegal entry:

Although additional factors such as the economy play an important role in the flow of illegal immigration into the United States, this chart visualizes a portion of the significant construction boom of border fencing and how it affected apprehensions.

Taking this a step further, our reporters used apprehension data over time to calculate each Border Patrol sector’s share of total apprehensions. The apprehension data CBP provided was broken down by the nine Border Patrol sectors along the Southwest border: two in California (San Diego and El Centro); two in Arizona (Yuma and Tucson); four in Texas (Big Bend, Del Rio, Laredo and Rio Grande Valley); and one that covers all of New Mexico and part of Texas (El Paso).

This graphic showed key shifts in where the majority of border crossers were apprehended, further explaining how fencing construction did not stop illegal immigration but rather shifted it to less protected areas.

This data was also layered with key immigration enforcement efforts such as Operation Gatekeeper and Operation Hold The Line. As enforcement ramped up in specific Border Patrol sectors, illegal entry again shifted elsewhere:

Because the building of the wall often was accompanied by an increase in Border Patrol staffing, we also showed on the map the number of agents for each year from 1992 through 2016.

One obvious data layer missing from this project is drug seizures. Although there is some data available, it did not span the timeframe or scope to make for a meaningful analysis. inewsource had requested that data but it was not released prior to publication. We’ve since received those records and you can expect to see more as we continue our coverage of America’s Wall.

To read the full report, including accompanying photos, video and audio from the project, go to border.inewsource.org or to our partner’s page at KPBS.

If you have questions or comments about the map, the data or our America’s Wall project, email us at .(JavaScript must be enabled to view this email address).

About the author

Brandon Quester is the Director of Data and Visuals for inewsource, a nonprofit accountability newsroom in San Diego, where he works to further the role of data in the organization and develop innovative ways to present investigative content. He plays a key role in expanding inewsource's partnerships with individuals, businesses and organizations. Quester is also the founder and former executive director and editor of the Arizona Center for Investigative Reporting, where he led the newsroom as it focused on statewide investigative projects in Arizona and throughout the Southwest.

Explore America's Wall here.

Comments