Geocoding CBP apprehensions using Python and OpenStreetMap

The purpose of this repository is to geocode the place of origin of migrants from Central America and Mexico from Border Patrol apprehensions data.

Contributors

• Fabien Cottier, Climate School, Columbia (lead)

Methodology

• Use openStreetMap Nominatim API to geocode place of origin of migrants with structured query
• Basic structured query: {"county":"county_name","state":"state_name","country":"country_name"}
• if geocoding not successful, try the following:
  • Replace county with city in structured query
  • check for phonetic confusion in spelling: Z <-> S, C <-> S, J <-> G, Y <-> LL, V <-> B, I <-> Y
  • If still not successful, try street address using neighborhoods tags.
• If not, geocode admin1 (state) level

Processing

The code is called with the file slurm_SeqJob_CBP_geo.sh, which launches a series of sequential SLURM jobs to geocode the data. Given existing Openstreetmap API limitation (1 request / second) and 12 hours wall time on Columbia HPC Terromoto, the apprehensions data is chunked in segments of 40,000 observations.

The program starts by calling the script sbatch_SeqJob_CBP_init.sh, which chunks the data. It then iteratively calls a series of 127 sbatch_SeqJob_CBP_geo_SEQ.sh jobs, which geocode the data. The output is then concatenated in a single file with the final script sbatch_SeqJob_CBP_final_SEQ.sh. Each of these three files calls in turn a series of python scripts.

During processing, the script will create a series of files in the folder stored in the scratch/_tmp directory. This folder contains the chunk data, the chunk geocoded and the slurm output file for each job (the latter can be used for debugging).

In addition, the following files are created in the scratch directory: log.txt (provides feedback on each geocoded record), geocodingResults.txt (summary information) and geo_CBP_apprehensions.csv (final geocoded dataset). Finally, the file progress_geocoding.txt tracks the overall geocoding progress while the program is running. The file is updated every 0.1%.

file tree

The repository is organized as follows:

Geocoding_CBP_migration/
├── slurm_SeqJob_CBP_geo.sh
├── environment.yml
├── codebook.md
├── data/
├── _slurm/ │ ├── sbatch_CBP_init_SEQ.sh
│ ├── sbatch_CBP_geo_SEQ.sh
│ └── sbatch_CBP_final_SEQ.sh
├── _python/
│ ├── geo_final.py
│ ├── geo_init.py
│ └── geo_processing.py \

Monitoring slurm job

The following command allows to monitor the progress of the slurm job:

• Current and pending jobs:

squeue -u $USER --sort=i --format="%.8u %.10i %.15j %.5P %.2t %.10r %.8M %.8l %.5D %.4C %.8m %.20E"

• Terminated jobs: (replace jobid_init and jobid_final by the first and last jobid)

sacct -j $(printf "%s," {JOBID_INIT..JOBID_FINAL} | sed 's/,$//') | grep -v '\.ba\+'

• monitor progress (from within scratch directory):

cat progress_geocoding.txt # print progress bar
tail log.txt # print last 10 lines of log file (use -n NUMBER to choose line)
grep -o -i geocoded log.txt | wc -l # count number of geocoded records
grep -o -i "not cached - not geolocated" log.txt | wc -l # count number of not geocoded records
sed -n '/^110001$/,/^120000$/p' log.txt | grep -o -i geocoded | wc -l # same for specific range
sed -n '/^110001$/,/^120000$/p' log.txt | grep -o -i "not cached - not geolocated" | wc -l # same for specific range

Miscellaneous commands:

• Access scratch directory

cd /moto/free/users/fsc2122/CBP_apprehensions

• clean scratch folder:

rm -rf _tmp pro* log* geo*

Data sources

• CBP apprehensions at US Southwestern border (initially using a 200-observation sample; later expanded to a 2,000-observation test sample dataset, as well as a larger test file containing 1,000 observations per year).

software Citation

Cottier, F. Geocoding CBP apprehensions using Python and OpenStreetMap. Available at https://github.com/cottierf/Geocoding_CBP_migration.

Related publication

Cottier, F., Wrathall, D., Devine, J. A., Arellano-Thompson, E., Gustine, R., Leblang, D., Magliocca, N., Nakamura, J., Tellman, E.,  de Sherbinin, A. (2026, June 8). Heterogeneous Effects of Climate, Narco-Trafficking, and Violence on Central American Migration to the United States. Retrieved from osf.io/preprints/socarxiv/kyu9x_v1

version history

1.3: July 2026

Minor update related to the readme file:

• Modified suggested citations for the python script.

1.2: April 2026

Documentation updates in preparation for public release:

• Added environment.yml for conda environment setup
• Added codebook.md documenting all variables in the final output (geo_CBP_apprehensions.csv)
• Updated file tree in README
• Fixed DataFrame.map() → .apply(axis=1) for geo_lvl nullification on geocoder failure in geo_final.py (line 125)

1.1: February 22, 2025

minor update to solve following issues:

• incorrect handling of geocoder_failure variable
• incorrect assignment of geo_lvl variable if geocoder failure
• incorrect recording of multiple responses when query city but no state of birth available
• changed the exception dictionary key system from failed query to iteration number to prevent duplicated key errors with a python dictionary
• incorrect handling of geocodingResults.txt (wrong total municipal/city level numbers)

Note: in addition the geo_lvl identifier for "city, no state", was relabeled city_noState to ease handling of the string variable.

1.0: November 19 2024

Initial release