remove_city_centers.sh

#!/bin/bash
regions="ak al ar az ca co ct dc de fl ga hi ia id il in ks ky la ma md me mi mn mo ms mt nc nd ne nh nj nm nv ny oh ok or pa ri sc sd tn tx ut va vt wa wi wv wy australia austria belgium canada denmark france germany india italy netherlands new_zealand spain switzerland turkey united_kingdom"
for region in $regions; do
    echo $region
    psql -U postgres -d geocodesource -c "delete from ${region}_clean where (latitude, longitude) in (select distinct latitude, longitude from ${region}_full where replace(upper(address), ' ', '') like '%P.O.BOX%' or replace(upper(address), ' ', '') like '%POBOX%' or address is null)"
done