Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Locator - zip working, state and city are not 1

Status
Not open for further replies.

julietsstars

Technical User
Joined
May 19, 2008
Messages
8
Location
US
Hope someone is familiar with this.
Server: Apache
Database: Postgre SQL

Issue with zips. In my postgresql I had two tables one titled "zips" and the other "zips_backup". "zips" became corrupt so I changed the perl script to pull from "zips_backup". The locator works great when searching by zip code but doesnt work if you search by city state.
Below is code
____________________________________________________________
Code:
package WeilMclainDB::Zips;

use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);

__PACKAGE__->table('zips_backup');

__PACKAGE__->add_columns(qw/zip_code zip_type city_name city_type state_name state_abbr area_code latitude longitude/);

__PACKAGE__->resultset_class('WeilMclainDB::ZipsResultSet');

1;

package WeilMclainDB::ZipsResultSet;
use base 'DBIx::Class::ResultSet';
use Geo::Distance;

sub get_zip {
	my ($self, $city, $state) = @_;
	#return undef unless ($city && $state =~ /^(\d*)$/);

	if (my $zip = $self->single({city_name => uc($city),state_abbr=>uc($state)},{columns => [qw/zip_code/]})) {
		return $zip->get_column('zip_code');
	}

	return undef;
	return $self->single({city_name=>uc($city),state_abbr=>uc($state)},{columns=>[qw/zip_code/]})->get_column('zip_code');
}

sub get_closest {
	my ($self, $zip_code, $miles) = @_;

	# First get the latitude and longititude for the given zip code
	if (my $zip = $self->single({zip_code => $zip_code})) {
		my $geo = Geo::Distance->new;
		my $locations = $geo->closest(
			dbh => $self->result_source->storage->dbh,
			table => 'zips_backup',
			lon => $zip->longitude,
			lat => $zip->latitude,
			unit => 'mile',
			distance => $miles,
			lon_field => 'longitude',
			lat_field => 'latitude',
			'sort' => 1,
			fields => [qw/zip_code/],
		);
	
		my @zips_backup;
		foreach my $result (@{$locations}) {
			push (@zips_backup, $result->{zip_code});
		}
		return \@zips_backup;
	}
	else {
		return undef;
	}
}

1;
 
Surely this should read
"zips" became corrupt so I restored it from "zips_backup"
If you corrupt "zips_backup" now (and let's face it, you don't know how the other one got corrupted) you are dead in the water.

Restore your table from the backup, and put your code back how it was before.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)[/small]
 
No, I wrote it correctly. It reads: "zips" became corrupt so I changed the perl script to pull from "zips_backup". We had a backup table of "zips" called "zips_backup" in case we ever lost our zip code information.

Now, my saying corrupt was the wrong term to use. When numeric was changed to character varying in the zip_code column(for Canadian zip codes) and the server was restarted, thats when everything started to go wacky. I tried changed character varying back to numeric and I got an error that said "pg.catalog" won't allow (something like that). I deleted that column of zip_code and re-entered it. Now everything was out of order.

So I just changed the table the script was pulling from to "zips_backup" since it was an exact replica of "zips".

I have tried to upload the backup and the server wouldn't tale it. Why it was kicked off I don't know and IT has been no help.
 
julietsstars

I know. I was just being facetious. It's just that when your table goes bad, and you have a backup, that's good news. What you don't do then is point your code at the only good backup you have.

OK, back to your problem: As you have discovered, you can change the types of certain database columns to other types. For example you can change a numeric to a character type, because you can always convert any number to a string of digits. But the database won't let you convert it back in the other direction because not all character strings can be converted to a number. For example you can't convert a Canadian style post code to a number, as it contains alphabetic characters.

It is possible that the reason you can't upload the backup is because you've changed the column type, and the old backup doesn't match the new definition.

If you know that you haven't actually loaded any non-numeric stuff into the column, what you can do is to dump out the table to a comma or tab separated file (whatever Postgres supports), drop the table, redefine it with a numeric type, and reload the data. It might be an idea to get those unhelpful guys in IT to help you.

One thing that might explain why your code doesn't work. Canadian post codes are based on British ones. Which means they have a space between the inbound and outbound parts of the code. If any of the geocoding software can't handle the space, or is expecting the contents to be only numeric, it could go wrong in strange and unexpected ways. It might not just be as trivial as changing the column type.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)[/small]
 
Thanks Steve!

Here is the bit of code I think is incorrect
Code:
sub get_zip {
    my ($self, $city, $state) = @_;
    #return undef unless ($city && $state =~ /^(\d*)$/);

    if (my $zip = $self->single({city_name => uc($city),state_abbr=>uc($state)},{columns => [qw/zip_code/]})) {
        return $zip->get_column('zip_code');
    }

The Canadian zips are not in our table at all - so right now there is no issue with those. I could never get to that point because I messed up the original "zip" table. Weird is that the zip code part of the locator is working perfectly. Its the city and state that are not. The columns that the perl is calling are named exactly like the colums are in the table. So, I'm wondering about the snippet of code above. Nothing has been changed there and I don't know enough about perl to know what exactly $zip is. Should I change that to $zip_backup or example.
 
No. The
Code:
__PACKAGE__->table('zips_backup');
ought to take care of that. What columns does your zips_backup table have on it?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)[/small]
 
Oh really? I thought that was the only bit correct.

The columns in the "zips_backup" table are:

zip_code

city_name

state_name

state_abbr

latitude

longitude
 
The bit where it says
Code:
__PACKAGE__->add_columns(qw/zip_code zip_type city_name city_type state_name state_abbr area_code latitude longitude/);
kind of looks like it's expecting a couple more columns, zip_type and city_type. Assuming you haven't dropped the original corrupt zips table yet, does it have these extra columns on it?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)[/small]
 
Oh yes. those are the "zips_backup". I forgot they were referenced in the above "add columns"

The complete list is:
zip_code

zip_type

city_name

city_type

state_name

state_abbr

latitude

longitude
 
area_code?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)[/small]
 
Here is the Controller calling up Zips:

Code:
package WeilMclain::Controller::ContractorLocator;

use strict;
use warnings;
use base 'Catalyst::Controller';

=head1 NAME

WeilMclain::Controller::ContractorLocator - Catalyst Controller

=head1 DESCRIPTION

Catalyst Controller.

=head1 METHODS

=cut


=head2 index 

=cut

sub index : Private {
    my ( $self, $c ) = @_;

    $c->stash->{template} = 'contractorlocator/index';
}

sub search : Local {

	my ( $self, $c ) = @_;

	# Figure out the zip code we are dealing with in case user gives city,state
	my $zip_code = 0;
	if (!$c->request->param('zip') && $c->request->param('city') && $c->request->param('state')) {
		$zip_code = $c->model('WeilMclainDB::Zips')->get_zip($c->request->param('city'),$c->request->param('state'));
	}
	elsif (!$c->request->param('zip')) { $c->response->redirect('/contractorlocator'); }
	else { $zip_code = $c->request->param('zip'); }

	my $zips = [$c->model('WeilMclainDB::Zips')->get_closest($zip_code,$c->request->param('miles'))];

	# Get contractors in $zips
	if (scalar @{$zips} > 0) { $c->stash->{contractors} = [$c->model('WeilMclainDB::Contractor')->locate_by_zip($zips)]; }

	$c->stash->{template} = 'contractorlocator/search';
}


=cut

1;
 
julietsstars

I'm not sure I can help you much more, other than to point you at the extensive docs on this package on CPAN:


Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)[/small]
 
Thats fine! You've been a wonderful help. One last question, does it "look" like it should be working properly? I will go through the link you sent and talk to IT again. But again, I do appreciate your help.
 
Yes, it does look OK. But if there is anything wrong with the data on the tables, it could cause the problem.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object::PerlDesignPatterns)[/small]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top