view release on metacpan or  search on metacpan

lib/FWS/V2/Geo.pm  view on Meta::CPAN

    #
    # if state is passed constrain to that
    #    
    if ( $autoComplete ) { $whereStatement .= " and city like '" . $autoComplete . "%'" }
    
    #
    # if state is passed constrain to that
    #    
    if ( $state ) { $whereStatement .= " and stateAbbr like '" . $state . "'" }

    #
    # if city is passed constrain to that
    #    
    if ( $city ) { $whereStatement .= " and city like '" . $city . "'" }

    #
    # if keywords is passed constrain to that
    #    
    if ( $keywords ) { 
        $score = "match(city) against('" . $keywords . "' IN NATURAL LANGUAGE MODE) as score";
        $whereStatement .= " and match(city) against('" . $keywords . "' IN NATURAL LANGUAGE MODE)";
    }

    my @zipcodes = @{$self->runSQL( SQL =>  "select city, " . $score . ",zipCode, stateAbbr,areaCode,UTC,latitude,longitude from zipcode where " . $whereStatement . " order by score desc" )};

    my @returnArray;
    while ( @zipcodes ) {
        my %zipHash;
        $zipHash{city}        = shift( @zipcodes );
        $zipHash{score}       = shift( @zipcodes );
        $zipHash{zip}         = shift( @zipcodes );
        $zipHash{state}       = shift( @zipcodes );
        $zipHash{areaCode}    = shift( @zipcodes );
        $zipHash{UTC}         = shift( @zipcodes );
        $zipHash{latitude}    = shift( @zipcodes );
        $zipHash{longitude}   = shift( @zipcodes );
        push( @returnArray, {%zipHash} );
    }
    
    return @returnArray;
}


=head2 updateZipArray

Recompile a zip code array adding extra keys that relate to the passed zip code.

The following keys will be added:
    zipState
    zipStateAbbr
    zipAreaCode
    zipUTC
    zipLatitude
    zipLongitude
    zipCounty
    zipDistance

=cut

sub updateZipArray {
    my ( $self, $fromZip, @dataArray ) = @_;
    #
    # clean the from zip
    #
    $fromZip = $self->safeSQL( $fromZip );

    #
    # get the zipArray and create a , delemented string or the sql
    #
    my @zipArray;
    for my $i ( 0 .. $#dataArray ) {
        if( $dataArray[$i]{zip} =~ /^\d{5}$/ ) {push( @zipArray,$dataArray[$i]{zip} ) }
    }

    #
    # convert the zip into an array
    #
    my $destIn = join( ',', @zipArray );

    #
    # trap to make sure its not blank
    #
    $destIn ||= '0';

    #
    # build the SQL
    #
    my $SQL = "select distinct destination.zipCode,destination.stateAbbr,destination.areaCode,destination.UTC,";
    $SQL .= "destination.latitude,destination.longitude,";
    $SQL .= " round(3956 * 2 * ASIN(SQRT( ";
    $SQL .=     " POWER(SIN((origin.latitude - destination.latitude) * 0.0174532925 / 2), 2) +";
    $SQL .=     " COS(origin.latitude * 0.0174532925) * ";
    $SQL .=     " COS(destination.latitude * 0.0174532925) * ";
    $SQL .=     " POWER(SIN((origin.longitude - destination.longitude) * 0.0174532925 / 2), 2) "; 
    $SQL .= " ))) as distance ";
    $SQL .= " from zipcode origin, zipcode destination ";
    $SQL .= " where origin.zipCode = '" . $self->safeSQL( $fromZip ) . "' and destination.zipCode in (" . $self->safeSQL( $destIn ) .") ";


    
    #
    # execute the array
    #
    my @distanceArray = @{$self->runSQL( SQL => $SQL )};
       
    #
    # loop though the array creating an hash array
    # 
    my %zipHash;
    while ( @distanceArray ) {
        my $zip                   = shift( @distanceArray );
        $zipHash{$zip}{state}     = shift( @distanceArray );
        $zipHash{$zip}{areaCode}  = shift( @distanceArray );
        $zipHash{$zip}{UTC}       = shift( @distanceArray );
        $zipHash{$zip}{latitude}  = shift( @distanceArray );
        $zipHash{$zip}{longitude} = shift( @distanceArray );
        $zipHash{$zip}{distance}  = shift( @distanceArray );

        if ( $zip eq $fromZip ) { $zipHash{$zip}{distance} = 2 }