!/usr/bin/perl
use strict;
use warnings;
use lib 'lib';
use Geo;

##</code><code>##

use autodie;
use DBD::SQLite;
use Data::Dumper;
use lib 'lib';
use Geo;


my $dbh = DBI->connect(
                "dbi:SQLite:dbname=us_counties.db",
                "",
                "",
                { RaiseError => 1, AutoCommit => 1 },
                );

my $states = $dbh->selectall_hashref( "SELECT id, code FROM state", 'id' );

my $range = .3;   # at US latitudes .3 degree is about 20 miles of surface dist

while(<DATA>) {
        chomp;
        s/"//g; 

        my ($lat,$long) = split /,/;

        my $range = .3;   # at US latitudes .3 degree is about 20 miles of surface distance

        my $county_ref = Geo::find_near_counties( $dbh, $lat, $long, $range );

        my $print_stuff = defined $county_ref ?  scalar @$county_ref : "None found ";
        print $print_stuff, " counties found for $lat and $long\n";

}

__DATA__
85,-110
46,-115
27,-81
43,-74
45,-93
48,-104
40,-85

##</code><code>##

package Geo;

sub find_near_counties {

##</code><code>##

        my (  $dbh, $lat, $long, $range ) = @_;

        my $increment = .3;
        my @sql_parms =  ( $lat - $range, $lat + $range, $long - $range, $long + $range ) ;
        my $query = "SELECT fips FROM county WHERE latitude  > ? AND latitude  < ? AND longitude > ? AND longitude < ? ";

        my $county_ref = $dbh->selectcol_arrayref($query, {}, @sql_parms );

        if( ! scalar @$county_ref ) {
                if ( $range > 3 ) { 
                        return undef;
                }
                return find_near_counties( $lat, $long, $range+$increment );
        }

        return $county_ref;
}
1;