Though not finished, Sort by distance is working perfectly wrt SELECT. Thanks again pugdog!
FYI, I did some benchmarking, and the results show that the prepare and execute
that you just helped me impliment is in some cases 5 times faster than it was
cycling through the database to gather the original list of zipcodes within distance.
AND! the ZipCodes are sorted by Distance or whatever Column is passed for that argument.
I am not finished. The 2nd query (query_sth) at the end of the routine is not paying any attention to
the fact that we just sorted the zipcodes in order of distance!
I would like to 'optimize' this routine and then add the
ability to limit results to "at or below" a specified category.
1) Can the SELECT be modified to only return the ZipCodes?
The purpose being to 'select' @zips rather than going through
the $get_links->fetchrow_hashref while loop
(which gathers just @zips) anyways?
# NOT CORRECT
my @zips = $get_links->->fetchall_list(ZipCodes);
my @zips = $get_links->select('ZipCode')->fetchall_list;
2) Can the 'if' portion of the code (at or below a category)
be simplified (faster) by doing something like below?
Or am I better to just leave it alone? It is pretty fast and works!
FYI, I don't really have @roots or @children in the TEST code below...
my $zipcode_db = $DB->table('ZipCodes') || return $GT::SQL::error;
my $start_zip_sth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref;
my $olat = $start_zip_sth->{Latitude};
my $olon = $start_zip_sth->{Longitude};
my $sth = $DB->table('Links')->select(GT::SQL::Condition->new('????' => 'IN' => [@roots, @children]));
while (my $link = $sth->fetchrow_hashref)
{
my $temp_distance = distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unit);
if($temp_distance <= $dist)
{
$count++;
push (@ids, $link->{ID});
}
}
$filter{ID} = \@ids if $count > 0;
Here's the whole (middle) of the ACTUAL ROUTINE currently in use.
It is now using args for ZipCode, Distance, sort_by and sort_order passed to the execute statement.
isValidated => 'Yes',
mh => $args->{mh},
nh => $args->{nh},
sb => $args->{sb},
so => $args->{so},
ww => 1
);
$filter{ExpiryDate} = '>=' . time if $CFG->{payment}->{enabled};
if ($catid)
{
my $catname_full = $categories_db->select( ['Full_Name'], { ID => $catid })->fetchrow;
my $cond = GT::SQL::Condition->new(
Full_Name => '=' => "$catname_full",
Full_Name => 'LIKE' => "$catname_full/%"
);
$cond->bool('OR');
my $sth = $categories_db->select( $cond ) || die $GT::SQL::error;
my $count = 0;
my $temp_distance;
while (my $hit = $sth->fetchrow_hashref) {
my $links_sth = $DB->table('CatLinks')->select( { CategoryID =>$hit->{ID}} ) || die $GT::SQL::error;
while (my $hit2 = $links_sth->fetchrow_hashref) {
my $link = $links_db->get($hit2->{LinkID});
# Get Current Links Zip Code from Main ZipCode DB
my $osth = $zipcode_db->select({ ZipCode => $link->{$opts->{'search_col'}} })->fetchrow_hashref;
my $currlat = $osth->{Latitude};
my $currlon = $osth->{Longitude};
# The actual distance search
$temp_distance = distance($olat, $olon, $currlat, $currlon, $unitcode);
if($temp_distance <= $dist)
{
$count++;
push (@ids, $link->{ID});
}
}
}
$filter{ID} = \@ids if $count > 0;
}
else
{
my @zips;
my $get_links = $zipcode_db->prepare("SELECT o.ZipCode, o.State, (3956 * (2 * ASIN(SQRT( POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) + COS(z.Latitude*0.017453293) * COS(o.Latitude*0.017453293) * POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2) )))) AS Distance FROM lsql_ZipCodes z, lsql_ZipCodes o, lsql_ZipCodes a WHERE z.ZipCode = ? AND z.ZipCode = a.ZipCode AND (3956 * (2 * ASIN(SQRT( POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) + COS(z.Latitude*0.017453293) * COS(o.Latitude*0.017453293) * POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2) )))) <= ? ORDER BY ? ?");
$get_links->execute ($query, $dist, 'Distance', 'ASC');
while (my $match = $get_links->fetchrow_hashref)
{
push (@zips, $match->{ZipCode});
}
$filter{$opts->{'search_col'}} = \@zips;
}
my $link_sth = $links_db->query_sth(\%filter);
$link_count = $links_db->hits;
Also, what do you think of my adding Longitude and Latitude columns to the Link table
and only having to do all this selecting and sorting once?
If that were implemented, the original SELECT statement we are using *COULD* ALREADY
have all the matching links ordered by distance. That is why I have to keep asking
if the "hook method" is a good one or not? See prev posts.
Thanks so much!
Anyone else can feel free to chime in here too :-)
Chris
RGB World, Inc. - Software & Web Development.
rgbworld.com
FYI, I did some benchmarking, and the results show that the prepare and execute
that you just helped me impliment is in some cases 5 times faster than it was
cycling through the database to gather the original list of zipcodes within distance.
AND! the ZipCodes are sorted by Distance or whatever Column is passed for that argument.
I am not finished. The 2nd query (query_sth) at the end of the routine is not paying any attention to
the fact that we just sorted the zipcodes in order of distance!
I would like to 'optimize' this routine and then add the
ability to limit results to "at or below" a specified category.
1) Can the SELECT be modified to only return the ZipCodes?
The purpose being to 'select' @zips rather than going through
the $get_links->fetchrow_hashref while loop
(which gathers just @zips) anyways?
# NOT CORRECT
my @zips = $get_links->->fetchall_list(ZipCodes);
my @zips = $get_links->select('ZipCode')->fetchall_list;
2) Can the 'if' portion of the code (at or below a category)
be simplified (faster) by doing something like below?
Or am I better to just leave it alone? It is pretty fast and works!
FYI, I don't really have @roots or @children in the TEST code below...
Code:
# Get Current Link Zip Code from Main ZipCode DB my $zipcode_db = $DB->table('ZipCodes') || return $GT::SQL::error;
my $start_zip_sth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref;
my $olat = $start_zip_sth->{Latitude};
my $olon = $start_zip_sth->{Longitude};
my $sth = $DB->table('Links')->select(GT::SQL::Condition->new('????' => 'IN' => [@roots, @children]));
while (my $link = $sth->fetchrow_hashref)
{
my $temp_distance = distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unit);
if($temp_distance <= $dist)
{
$count++;
push (@ids, $link->{ID});
}
}
$filter{ID} = \@ids if $count > 0;
Here's the whole (middle) of the ACTUAL ROUTINE currently in use.
It is now using args for ZipCode, Distance, sort_by and sort_order passed to the execute statement.
Code:
my %filter = ( isValidated => 'Yes',
mh => $args->{mh},
nh => $args->{nh},
sb => $args->{sb},
so => $args->{so},
ww => 1
);
$filter{ExpiryDate} = '>=' . time if $CFG->{payment}->{enabled};
if ($catid)
{
my $catname_full = $categories_db->select( ['Full_Name'], { ID => $catid })->fetchrow;
my $cond = GT::SQL::Condition->new(
Full_Name => '=' => "$catname_full",
Full_Name => 'LIKE' => "$catname_full/%"
);
$cond->bool('OR');
my $sth = $categories_db->select( $cond ) || die $GT::SQL::error;
my $count = 0;
my $temp_distance;
while (my $hit = $sth->fetchrow_hashref) {
my $links_sth = $DB->table('CatLinks')->select( { CategoryID =>$hit->{ID}} ) || die $GT::SQL::error;
while (my $hit2 = $links_sth->fetchrow_hashref) {
my $link = $links_db->get($hit2->{LinkID});
# Get Current Links Zip Code from Main ZipCode DB
my $osth = $zipcode_db->select({ ZipCode => $link->{$opts->{'search_col'}} })->fetchrow_hashref;
my $currlat = $osth->{Latitude};
my $currlon = $osth->{Longitude};
# The actual distance search
$temp_distance = distance($olat, $olon, $currlat, $currlon, $unitcode);
if($temp_distance <= $dist)
{
$count++;
push (@ids, $link->{ID});
}
}
}
$filter{ID} = \@ids if $count > 0;
}
else
{
my @zips;
my $get_links = $zipcode_db->prepare("SELECT o.ZipCode, o.State, (3956 * (2 * ASIN(SQRT( POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) + COS(z.Latitude*0.017453293) * COS(o.Latitude*0.017453293) * POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2) )))) AS Distance FROM lsql_ZipCodes z, lsql_ZipCodes o, lsql_ZipCodes a WHERE z.ZipCode = ? AND z.ZipCode = a.ZipCode AND (3956 * (2 * ASIN(SQRT( POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) + COS(z.Latitude*0.017453293) * COS(o.Latitude*0.017453293) * POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2) )))) <= ? ORDER BY ? ?");
$get_links->execute ($query, $dist, 'Distance', 'ASC');
while (my $match = $get_links->fetchrow_hashref)
{
push (@zips, $match->{ZipCode});
}
$filter{$opts->{'search_col'}} = \@zips;
}
my $link_sth = $links_db->query_sth(\%filter);
$link_count = $links_db->hits;
Also, what do you think of my adding Longitude and Latitude columns to the Link table
and only having to do all this selecting and sorting once?
If that were implemented, the original SELECT statement we are using *COULD* ALREADY
have all the matching links ordered by distance. That is why I have to keep asking
if the "hook method" is a good one or not? See prev posts.
Thanks so much!
Anyone else can feel free to chime in here too :-)
Chris
RGB World, Inc. - Software & Web Development.
rgbworld.com