Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

Need help with Sort by distance

Quote Reply
Need help with Sort by distance
This is for my ZipCodeSearch Plugin.

I want to sort the results ASC by distance, but 'distance' is not column in my table.
How can I do the sort?
Code:
my $link_db = $DB->table('Links') || return $GT::SQL::error;
$link_db->select_options ('ORDER BY Add_Date ASC') || return $GT::SQL::error;

my $sth = $link_db->select( GT::SQL::Condition->new(
isValidated => '=' => 'Yes',
ExpiryDate => '>=' => time


) );

my $temp_distance;

while (my $link = $sth->fetchrow_hashref)
{

while (my $link = $sth->fetchrow_hashref)
{
$temp_distance = distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unitcode);

if($temp_distance <= $dist)
{
$link_count++;
$link->{distance} = $temp_distance;
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}
}
}

Thanks
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
Ooops, an extra 'while' got in there somehow Crazy

This is the code
Code:
my $link_db = $DB->table('Links') || return $GT::SQL::error;
$link_db->select_options ('ORDER BY Add_Date ASC') || return $GT::SQL::error;

my $sth = $link_db->select( GT::SQL::Condition->new(
isValidated => '=' => 'Yes',
ExpiryDate => '>=' => time
) );

while (my $link = $sth->fetchrow_hashref)
{
$temp_distance = distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unitcode);

if($temp_distance <= $dist)
{
$link_count++;
$link->{distance} = $temp_distance;
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}
}
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
I had a similar problem, and I chose to use a somewhat less-than-elegant solution.

For my needs, it worked on a per-page basis.

What I did, was loop through the returned links, and calculate a distance and create a hash of ID=>distance, then sorted it.

In your case, maybe looping through the links, adding a new field "distance" to the link record, then inserting the records in a new array, sorted by distance, and sending that to the output loop would work.

It's not elegant, it's not pretty, but because you can't store those values (they vary with each user) the only way is to calculate them "on the fly"

If you don't have a huge amount of such searches, maybe using temporary tables. Select those links into a new table, insert the distance field, select them back out by distance, delete the table.

It would be easy, also, to by pass the links loop routine (but I'm trying *NOT* to do that).

If your plugin would take the links_loop, loop through it, create the output html, then insert it into a hash with ID, Distance & HTML in int, you could then sort that hash by Distance, loop through it, concatenate the html and simply output that.

A few different suggestions. Which is "better" would depend on your specific situation.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Need help with Sort by distance In reply to
Pugdog and others, your input please as I keep going back and forth on this issue and cannot finish this :-)

In the sample code above (and currently in my plugin), I have added to Links table, both Longitude and Latitude fields. These 2 fields are updated when links are added or modified via hooks. That being done, when doing a search, I can do so without ever accessing the ZipCodeDB which has 35,000 records in it. I just cycle through the links (possibly narrowed by category) checking their distance by using their own local "copy" of the Lat and long fields. If within requested distance, I have the link. I would think there would be an easy way to sort the list. I have filled in a temp var with the distance which I have while looping, can't I just sort my results on that?

Pros to current method
- The list is narrowed by category first. reducing calculations
- Probably faster than method below
- Only cycles through all links in directory once
- Never accesses large zipcode db

Cons to Current Method
- If zipcode not in db when added, it is empty and will only be updated on next modify or "Update Zips"
( I was going to write an "Update Zips" util that would cycle through Links and update long/lat from source zipcode db)
- Requires the hooks on add/modify.
- Requires adding Long and Lat fields to Links table.
- When ZipCodeDB is updated, Need to run "Update Zips" on Links Table


Alternate search method, I have gathered an array of zipcodes and use that as a condition to search.
Would this be easier to sort by distance?
Code:
my @zips = matching_zips($query, $unitcode, $dist);

my $link_db = $DB->table('Links') || return $GT::SQL::error;
$link_db->select_options ('ORDER BY Add_Date ASC') || return $GT::SQL::error;
my $sth = $link_db->select(GT::SQL::Condition->new('Zip' => 'IN' => \@zips));

while (my $link = $sth->fetchrow_hashref)
{
$link_count++;
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}
Pros to Alternate method
- All of the "Cons" to Current Method, literally
- Always "in sync" or current seems it accesses ZipCodeDB on each search
- No hooks or Adding columns to Link table

Cons to Alternate method
- Has to build an array of zip codes from Lg ZipCodeDB on each search
- Has to compare an "array" of Zips to each links zip code (slower?)

Here's my matching_zips sub. If I sorted this list by distance, wouldn't the links come in order?
Code:
sub matching_zips {
# -----------------------------------------------------------------------------
#
#
my ($query, $unit, $dist) = @_;
my @output;

my $zipcode_db = $DB->table('ZipCodes') || return $GT::SQL::error;
my $osth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref;

if($osth)
{
my $olat = $osth->{Latitude};
my $olon = $osth->{Longitude};

my $sth = $zipcode_db->select;

while (my $link = $sth->fetchrow_hashref)
{
$temp_distance = distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unit);

if( $temp_distance <= $dist)
{
$link->{Distance} = $temp_distance;
push (@output, $link->{ZipCode});
}
}
}

return @output;

}


Thanks again,
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
Ok, well forget the pro/cons. Don't waste your energy or time.
I still need to sort though Smile

I managed to get ZipCodeSearch working (3rd rev) so that...
- Each search accesses main zipcode database so all lookups are as current as database
- No add/modify hooks
- No columns added to Links table (Must have a Zip column though)
- Search narrowed by category first if catID is given

There are still 2 methods used to do the searching, 1 with catID, 1 without.
Snippet
Code:
# Get the starting Zip Code we are searching for
my $zipcode_db = $DB->table('ZipCodes') || return $GT::SQL::error;
my $osth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref;

# Return if no match for starting zip code. It's not in the database
if(! $osth)
{
# TO DO - Add missing zip codes to a different table, or just log them
return { error => Links::language('ZIP_MISSING', $query), term => $query };
}

# If we made it this far, grab Latitude and Longitude for starting zip
my $olat = $osth->{Latitude};
my $olon = $osth->{Longitude};
my $link_count = '0';
my @link_results_loop;

# Get the category id if there is one
my $catid = $IN->param('catid');
my $catname = 'Links';
my $temp_distance;

if ($catid)
{
# This is just the short category name for display on template results
$catname = $DB->table('Category')->select( ['Name'], { ID => $catid } )->fetchrow;

# We want all links in catid and below. Found at GT forums under <%load_lower_links($ID)%>
my $catname_full = $DB->table('Category')->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 = $DB->table('Category')->select( $cond ) || die $GT::SQL::error;

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 = $DB->table('Links')->get($hit2->{LinkID});

# Get Current Links Zip Code from Main ZipCode DB
my $osth = $zipcode_db->select({ ZipCode => $link->{Zip} })->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)
{
$link_count++;
$link->{distance} = $temp_distance;
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}
}
}
}
else
{
my @zips = matching_zips($query, $unitcode, $dist);

my $link_db = $DB->table('Links') || return $GT::SQL::error;
$link_db->select_options ('ORDER BY Add_Date ASC') || return $GT::SQL::error;
my $sth = $link_db->select(GT::SQL::Condition->new('Zip' => 'IN' => \@zips));

while (my $link = $sth->fetchrow_hashref)
{
$link_count++;
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}

}

matching_zips subroutine is same as posted earlier.

I am wondering this... In the 'else' statement above (no category given).
Am I better off using the condition (which is an array of Zip Codes within given radius - possibly hundreds?).
i.e. ['12345', '12346', '12347', '12348', etc...]
OR
Get each links zip code, do a lookup for it's Latitude/Longitude, then compare distance like this
Code:
# Get Current Links Zip Code from Main ZipCode DB
my $osth = $zipcode_db->select({ ZipCode => $link->{Zip} })->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)
{
$link_count++;
$link->{distance} = $temp_distance;
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}



This is more like the method used when given a catid.

Thanks
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
I'm having a lot of trouble with the advanced editor today :( I wish they would put a javascript block on navigating away from the page without hitting one of the "post" buttons.

I didn't realize you could select a list of zipcodes "near" another. Using "IN" in that case is probably the fastest, and according the docs, you can do it efficiently with constants:

http://dev.mysql.com/...rison-operators.html


and, potentially, with subqueries:

http://dev.mysql.com/...some-subqueries.html

SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Need help with Sort by distance In reply to
 
Ok, so how can I use this code to perform a search and still make use of Paging, mh, and category searches etc?
btw, I still need to SORT by Distance Cool

Code:
my @zips = matching_zips($query, $unitcode, $dist);

my $link_db = $DB->table('Links') || return $GT::SQL::error;
$link_db->select_options ('ORDER BY Add_Date ASC') || return $GT::SQL::error;
my $sth = $link_db->select(GT::SQL::Condition->new('Zip' => 'IN' => \@zips));

while (my $link = $sth->fetchrow_hashref)
{
$link_count++;
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}

I have read that I should be using $db->query or maybe $db->query_sth in order to maintain paging etc...
I found this code here
Code:
my $hits = $db->query ( { Field_A => 'x', Field_B => 'y' } );
if ($db->hits) {
foreach my $hit (@{$hits}) {
$hash = $db->array_to_hash($hit);
...
}
}

So I think I need to write rev 4 of this plugin using something like

Code:
my $sth = $db->query_sth(GT::SQL::Condition->new('Zip' => 'IN' => \@zips));

Will $dbquery_sth accept a condition?
Maybe I need to be going Back to an actual hook, just change $args? Then let search continue?
I will figure this out, but I am on the slow boat to China.

Any suggestions on the best way to do a search for
- catid = x
- Column = 'Zip'
- value = ['12345', '12346', '12347', etc...]

Remember this is a plugin, so I can do just about anything needed, but can't I just get the results and return them (as a hook). Let GT sort out the paging and category stuff? Currently I am writing all the code, and have not been able to get paging to work, nor sort :-)

Thanks
Chris

RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
Ok, well I'm in the East China Sea, nearing Tiawan Cool

I still seem 2 be stuck with 2 methods of searching. 1 if given a catid, and 1 if no catid is given.
The else statement (no catid) works PERFECTLY... paging options, mh etc... are all functional and working. Yeah!

"With" cat id does *not* have paging, groupby or any other options that come with using 'query_sth'.

Can someone help me to 'join' the if and else statements, so that the results can be filtered by category.
In the else statement, I have "All Links within xx miles of 12345".

What I need is (sudo code)
Code:
if(catid) {
"All Links at or below catid within xx miles of 12345".
}
else {
"All Links within xx miles of 12345".
}


Here's what I have...
Code:
if ($catid)
{
# This is just the short category name for display on template results
$catname = $categories->select( ['Name'], { ID => $catid } )->fetchrow;

# We want all links in catid and below. Found at GT forums under <%load_lower_links($ID)%>
my $catname_full = $categories->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->select( $cond ) || die $GT::SQL::error;

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->get($hit2->{LinkID});

# Get Current Links Zip Code from Main ZipCode DB
my $osth = $zipcode_db->select({ ZipCode => $link->{Zip} })->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)
{
$link_count++;
$link->{distance} = $temp_distance;
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}
}
}
}
else
{
my @zips = matching_zips($query, $unitcode, $dist);

my %filter = (
isValidated => 'Yes',
mh => $args->{mh},
nh => $args->{nh},
sb => $args->{sb},
so => $args->{so},
ww => 1,
Zip => \@zips
);

# Should not select unpaid and expired links if payment is enabled
$filter{ExpiryDate} = '>=' . time if $CFG->{payment}->{enabled};

my $link_sth = $links->query_sth(\%filter);
$link_count = $links->hits;

# Format the link results.
if ($link_count) {
my $results = $link_sth->fetchall_hashref;
@link_results_loop = map Links::SiteHTML::tags('link', $_) => @$results unless $CFG->{build_search_gb};
if ($CFG->{build_search_gb}) {
my @ids = map { $_->{ID} } @$results;
my $catlink = $DB->table('CatLinks','Category');
my %names = $catlink->select('LinkID', 'Full_Name', { LinkID => \@ids })->fetchall_list;
foreach my $link (@$results) {
push @{$link_output{$names{$link->{ID}}}}, Links::SiteHTML::tags('link', $link);
}
}
}

# Join the link results by category if we are grouping.
if ($CFG->{build_search_gb}) {
foreach my $cat (sort keys %link_output) {
$link_output{$cat}->[0]->{title_linked} = sub { Links::Build::build('title_linked', { name => $cat, complete => 1, home => 0 }) };
$link_output{$cat}->[0]->{title_loop} = Links::Build::build('title', $cat);
push @link_results_loop, @{$link_output{$cat}};
}
}
}

Oh, and I still need to sort by Distance Unsure

Thanks,
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
Well here it is.. Everything works except Sort by distance
The gathering of link ids at or below a given category could probably be shortened, but I don't know how.

Snippet
Code:
my @ids;

my %filter = (
isValidated => 'Yes',
mh => $args->{mh},
nh => $args->{nh},
sb => $args->{sb},
so => $args->{so},
ww => 1
);

# Should not select unpaid and expired links if payment is enabled
$filter{ExpiryDate} = '>=' . time if $CFG->{payment}->{enabled};

if ($catid)
{
# This is just the short category name for display on template results
$catname = $categories_db->select( ['Name'], { ID => $catid } )->fetchrow;

# We want all links in catid and below. Found at GT forums under <%load_lower_links($ID)%>
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';
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->{Zip} })->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 = matching_zips($query, $unitcode, $dist);
$filter{Zip} = \@zips;
}

my $link_sth = $links_db->query_sth(\%filter);
$link_count = $links_db->hits;

if ($link_count) {
my $results = $link_sth->fetchall_hashref;
.....
}

Sort by distance plays a weird role, especially if grouping by category.
Now I can sort by Zip or any other existing coulumn, and I still wonder if I can't just sort the zipcodes in order of 'distance' when gathering the array of zips.

See sub matching_zips earlier in this thread (here)

Thanks again for any help in finalizing this.
It is nearly perfect and can be seen in use here
http://www.supportmusicians.com/...cgi?p=zipcode_search

A good zip code to search around is 53142. Try 4 miles, 20 miles, 400 miles and watch the results increase!

Thanks
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com

Last edited by:

rgbworld: May 29, 2005, 4:43 PM
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
Hi,

You can do almost anything, but the idea is how complex a datastructure do you want?

I still think, working within the confines of "links", you'd be better off pulling the matching links into a temporary table, inserting the value for distance, and then performing your selects on that table. Because the "distance" field is something that is only relevant to the users "zip" and then to the "keywords", this is where caching for performance on a busy system would come into play.

If you are doing distance by zipcode, you could perhaps set up a hash with "zip=>distance".

When you do the select, immediately add (or update) the field distance by setting it equal to zip_hash{$zip}.

At this point, they have not been grouped by category, that happens in a later routine.

Now that you have the field "distance" set for that selection, you can operate on it.

But, really, the *best* way to do this, is to do your select and insert into a temporary table.
When you do the select on that table, you can "order by Distance"

Grouping by category, then distance, adds a layer of complexity that is probably not needed by the user -- they want to know how far something is, not what category you have it placed in.

The *problem* is that because "distance" is not part of the criteria for selecting links, when you do the initial search, within 20 miles, you get 100 links back. You want the closest first, but the original query *can't* return that (eg: provide paging).

If you use a temporary table, subsequent selects can operate on it, as if it was a primary select, including paging (and even group by category)


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Need help with Sort by distance In reply to
Ok, I will attempt the temporary table. I have a few questions right off the bat...

This 'temporary' table... is it a physical table that exists or is it temporary as in ram?
If it is a real table say named links_temp, and I actually copy data into it while doing a search, what prevents 2 concurrent searches from overwriting each others temp data?

I start here... This will give me a list of all zipcodes (from main ZipCode db) within the specified distance.
Code:
sub matching_zips {
# -----------------------------------------------------------------------------
#
#
my ($query, $unit, $dist) = @_;
my @output;

my $zipcode_db = $DB->table('ZipCodes') || return $GT::SQL::error;
my $osth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref;

if($osth)
{
my $olat = $osth->{Latitude};
my $olon = $osth->{Longitude};
my $sth = $zipcode_db->select;

while (my $link = $sth->fetchrow_hashref)
{
my $temp_distance = distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unit);

if( $temp_distance <= $dist)
{
push (@output, $link->{ZipCode});
}
}
}

return @output;

}

Easy enough... Now I select ALL links that match ANY of the Zip Codes from above
Code:
my @zips = matching_zips($query, $unitcode, $dist);

my $link_db = $DB->table('Links') || return $GT::SQL::error;
my $sth = $link_db->select(GT::SQL::Condition->new('Zip' => 'IN' => \@zips));

I need to dump all these links into a 'temp' table here and add a column for 'distance'.
I could use an Example Please :-)

Now cycle through these links filling in 'distance'. No comparison needed we know they are all within distance already.
Code:
my $temp_db = $DB->table('temp') || return $GT::SQL::error;
my $sth = $temp_db->select;

while (my $link = $sth->fetchrow_hashref)
{
my $temp_distance = distance($olat, $olon, $link->{Latitude},$link->{Longitude}, $unit);

Update each row with $temp_distance????;
}

Once temp table is completed, Do I still call 'query' to add the paging options?
or do I have to roll my own paging? I can add the toolbar, but don't understand how to grab the 'next'
page of links on a subsequent request for the next page.

This would work? If so I can finish from the end of this snippet onword
Code:
my %filter = (
mh => $args->{mh},
nh => $args->{nh},
sb => $args->{sb}, # Can Now use Distance here
so => $args->{so},
ww => 1
);

my $temp_db = $DB->table('temp') || return $GT::SQL::error;
my $temp_sth = $temp_db->query(\%filter);
$link_count = $temp_db->hits;
my $results = $temp_sth->fetchall_hashref;

Please let me know if this sounds right and fill in any blanks that you can :-)

Cacheing would probably be a good idea too :-)

Thanks
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com

Last edited by:

rgbworld: May 31, 2005, 12:45 AM
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
I have not used temporary tables in recent versions of Links, so it might not work as expected, using the higher level calls, but here is a nice book excerpt on it (see the bottom of the article for the .pdf link)

http://dev.mysql.com/...l-db-design-ch5.html

You can also check the MySQL docs (http://mysql.org) for temporary table and get some extra quirks/bugs.

or here,
http://dev.mysql.com/...en/create-table.html
in the create table page.

Temporary tables _should_ work, since MySQL actually uses them internally for complex selects, so the "user session" should not be maintained beyond script iterations in Glinks (that's my only concern here).

Take a look at that short doc, above, and if you still have questions, I'll try to come up with some code later on, but it won't be until I'm awake, and that usually doesn't occur until well after dinner time for most people :{

But this is also where tricky caching can come into play. If you then query the database, for each of the available pages, (assuming there are 2 or 3) then stuff that result into a cache, when the user requests the next page, you check the cache first, then return the data, without re-doing the select/insert/select process. In exchange for a bit of disk space, you can cut repeat queries like this by thinking ahead.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Need help with Sort by distance In reply to
Just FYI, this was the orig code I had found which has some interesting stuff like ORDER BY DISTANCE.
There was no distance column and yet this worked via phpMyAdmin. When trying to rewrite, I had some probs with SIN or COS not being available.

So If I could rewrite this, it may be better than doing a "select all" and then looping thru the entire list of zips in order to come up with initial list of matching zipcodes within distance.

# demo query for SQL searches for zipcodes
# up to 50 miles away from zip 54915
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 ZipCodes z,
ZipCodes o,
ZipCodes a

WHERE z.ZipCode = 54915 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)
)))) <= 50

ORDER BY Distance

Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
That's some pretty high-level math, that's beyond me <G>

I did go through the MySQL docs, and all those functions are available, so what error did you actually get?

http://dev.mysql.com/...tical-functions.html

With complex selects, calculations, and such, I'm not sure if it would save computer time or not. Sometimes, a single complex query is *not* as efficient as several smaller less complex ones. Other times, it can be.

If you got an error trying to use that code "as is", add some spaces to the equations, between the operators, and such, sometimes the parser chokes if they aren't there.

ex: POWER(SIN(((z.Latitude - o.Latitude) * 0.017453293)/2), 2) +

*also* to get meaningful answers, make sure your data is in the units the functions expect. SIN and COS are expecting radians, and if the units are in degrees, you need to convert:

Quote:

RADIANS(X)
Returns the argument X, converted from degrees to radians.


ASIN is expecting a SIN, so if any of the other functions fail, and return bogus data, the expression will fail, and will bomb on several levels.

If you format the request, you can manually try it out in MySQLMan

The other thing I don't get,

Quote:
FROM ZipCodes z,
ZipCodes o,
ZipCodes a


Is that part of the code.

From my understanding of SQL, that is simply aliasing z. to mean the table ZipCodes, o. to be the table ZipCodes, and a. to be the table ZipCodes.

So, in the equations, z.Latitude is the same as o.Latitude, and when subtracted, give 0, which might be part of the problem, since anything * 0 is 0, and 0 / anything is 0

I could be wrong... this sort of math was never my thing.


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Need help with Sort by distance In reply to
Yea, beyond me too <g>, so I didn't analyze it.

It worked perfectly when pasted into the sql query within phpMyAdmin.
I had to add the table prefix of lsql_ but otherwise it works fine.
I just wasn't able to rewrite it into GLinks.

I can come back to this as all it does is gets the orig set of zipcodes, and i don't think that putting them in order of distance is going to help at all with the final order of the results.

To grab the 1st array of zip codes within distance. I thought this might help. They are calling a function.

mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name
-> FROM mytable ORDER BY full_name;

So can I do this?
Code:
my $zipcode_db = $DB->table('ZipCodes') || return $GT::SQL::error;
my $osth = $zipcode_db->select({ distance($olat, $olon, Latitude, Longitude, $unit) => '<' 50 })->fetchrow_hashref;

or maybe something like this 1/2 converted mess?
mysql> SELECT * FROM ZipCodes WHERE distance($olat, $olon, Latitude, Longitude, $unit) < 50

For the temporary table all I have is scraps so far...
# For an empty copy of a table use LIKE
CREATE TEMPORARY TABLE IF NOT EXISTS new_tbl LIKE orig_tbl;

This is going to take a while :-) So I better keep at it!

Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
Does the query work when you paste it into MySQLMan?

If so, then maybe using the "old" lower level calling convention:


$get_links = $LINKDB->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 ZipCodes z, ZipCodes o, 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) )))) <= 50 ORDER BY Distance ");

$get_links->execute ("54915");


might work?


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Post deleted by rgbworld In reply to
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
I hard-coded in a zipcode '53142' where you had a '?' and now I get this

Code:
$VAR = bless(
{
'_debug' => '0',
'_err_pkg' => 'GT::SQL::Table',
'_errargs' => [
'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 = 53142 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) )))) <= 50 ORDER BY Distance ',
'called with 1 bind variables when 0 are needed'
],
'_errcode' => 'CANTEXECUTE',
'_error' => [
'Failed to execute query: \'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 = 53142 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) )))) <= 50 ORDER BY Distance \': called with 1 bind variables when 0 are needed'
],
'dbh' => bless(
{},
'DBI::db'
),
'do' => 'SELECT',
'hints' => {},
'name' => 'lsql_ZipCodes',
'query' => '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 = 53142 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) )))) <= 50 ORDER BY Distance ',
'schema' => bless(
{
'ai' => '',
'cols' => {
'Latitude' => {
'default' => '0',
'not_null' => '1',
'pos' => '2',
'type' => 'DOUBLE'
},
'Longitude' => {
'default' => '0',
'not_null' => '1',
'pos' => '3',
'type' => 'DOUBLE'
},
'State' => {
'default' => '',
'not_null' => '0',
'pos' => '4',
'size' => '2',
'type' => 'CHAR'
},
'ZipCode' => {
'default' => '',
'not_null' => '1',
'pos' => '1',
'size' => '5',
'type' => 'VARCHAR'
}
},
'fk' => {},
'fk_tables' => [],
'index' => {},
'pk' => [
'ZipCode',
'Latitude',
'Longitude',
'ZipCode',
'Latitude',
'Longitude',
'ZipCode',
'Latitude',
'Longitude'
],
'subclass' => {
'table' => {}
},
'unique' => {}
},
'GT::Config'
),
'sth' => bless(
{},
'DBI::st'
)
},
'GT::SQL::Driver::MYSQL::sth'
);
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
Yes it works! GT::Dumper::Dumper(\@output); produced the sample results further below.
The code I have now is below. Can I add a 2nd argument for distance? 3rd arg for ORDER BY Distance?
Code:
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) )))) <= 50
ORDER BY Distance ");

$get_links->execute ($query);
my @output;
while (my $match = $get_links->fetchrow_hashref)
{
push (@output, $match);
}

Sample dump of @output.
Code:
$VAR = [
{
'Distance' => '0.000000',
'State' => 'WI',
'ZipCode' => '53142'
},
{
'Distance' => '2.150737',
'State' => 'WI',
'ZipCode' => '53158'
},

... Plus many others here

{
'Distance' => '49.849130',
'State' => 'IL',
'ZipCode' => '60187'
},
{
'Distance' => '49.952437',
'State' => 'WI',
'ZipCode' => '53033'
}
];

This is step 1 complete. Thanks so much!
I have a list of matching zipcodes within 20 miles of 53142. (yet again)
This time it is sorted by distance though!

There are 2 ways of continuing from here. I'll refer to them as the Hook Method and Non-Hook Method.

Hook Method
It would be very easy to modify the above query to execute on Links table rather than on ZipCodes table.
Doing so would mean I would already have the matching links. All I have now is zipcodes ordered by Distance.
I could place a hook on add/modify to copy Longitude/Latitude from ZipCodes table to Links Table.
Write a util to add/update Links table from ZipCodes table. (Admin function)

Instead of grabbing [Distance, State, ZipCode] from ZipCodes table, I could be grabbing [Distance, LinkID] if I use the hook method. Then I would have an array of IDs already sorted by Distance.

Then do something like this? I like using query because it adds paging and allows override of sort_order and sort_by. I am sure it is redundent to do a query when i already have the results though (hook method)
Code:
my %filter = (
mh => $args->{mh},
nh => $args->{nh},
sb => $args->{sb},
so => $args->{so},
ww => 1,
ID => \@ids
);

my $link_sth = $links_db->query_sth(\%filter);
$link_count = $links_db->hits;

Non-Hook Method
This method has the advantage of *not* adding any hooks or columns to Links table. The disadvantage is that I have to now query Links table for links that match any of the zipcodes, and i don't think query will keep them in order even though the zipcodes are in order?

I have to go to bed, I will have to sleep on this and finish tomorrow.

Thanks again
Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
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...
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 &amp; Web Development.
rgbworld.com

Last edited by:

rgbworld: Jun 2, 2005, 12:53 AM
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
Hi,

I don't think adding in the long/lat to the links table is going to gain you anything, on the way you search.

*BUT* that said, creating a caching & distance table *might*

Right now you do a lot of calculations per request. For certain zips, and areas, this is wasteful. Especially ones in areas that get a lot of similar requests.

If you keep a list of zip near zip queries that are made on your system, then generate the distances for them and store those in a

ZIP1:ZIP2:Distance

table, where ZIP1 is always less than ZIP2, then you would check the cache, to see if you had generated a ZIP1 near ZIP2 search before, if so, increment it's request count by 1, for tracking purposes.

Then, make the request

select ZIP2 from ZipCode_Cache where ZIP1 = "12345" and Distance <= '50

that catches all zips where ZIP1 < ZIP2

Then, the second query:

select ZIP1 from ZipCode_Cache where ZIP2 = "12345" and Distance <= '50

(If you double up the entries in the cache table, you can avoid the second select, but compared to the calculations and join, this is low overhead)

You have two lists of zips that are within '50' miles of the requested zip.

Now, merge the arrays, add the requested zip to it, and do an "IN" request on the links table. According the the docs, this is *very* fast with a scalar list.


You could of course, pre-generate the list of zips near all zips, but the odds of someone wanting to find how far a place was from an LA zip to a NY or FL zip is not really probably going to happen often <G>

If you know 90% of all your searches are for under 100 miles, you could run a job that generates all the zips distances, then delete any that are over say, 150 miles.

You would only need the ZIP1:ZIP2:Distance table, and if the select turned up no "hits" for ZIP:ZIP:Distance, or if distance was > 150 miles, you'd know you had to do a manual sweep of the zip tables before returning an error or "nothing found"

*or* you could limit it to searches that had a distance of 150 miles or less, and avoid any "error" conditions beyond that.

(BTW: autogenerating the distance table, and not making sure ZIP1 was always less than ZIP2 would automatically created the duplicate entries, so you'd only need one select statement)


PUGDOG� Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] Need help with Sort by distance In reply to
I'm baaack Laugh

I don't expect many are able to help with this, so if some of you gurus wouldn't mind taking
a close look at this.

I have everything working exactly the way I want, except for the option to only show links
at or below a specified category. The 'if($catid)' statement below contains a *not* working idea
of what I want to do. I do not want to organize by category, just limit results to a category
and the category's children.

The 'if' is completely wrong. The results are not sorted by Distance, but they *are* within Distance.

So, 1 of 2 options:
1) Fix 'if' to somehow "ORDER by Distance" using the 'else' code as an example.

2) Modify 'else' by adding an additional AND to the SELECT statement used in the prepare statement.
The AND would have to be something like ( { FatherID => ID_NUMBER }, ['ID','Full_Name'] ).

Here's the relevent code. I have also attached the entire file if that makes it easier.
Code:
if ($catid)
{
# This is just the short category name for display on template results
$catname = $categories_db->select( ['Name'], { ID => $catid } )->fetchrow;

# We want all links in catid and below. Found at GT forums under <%load_lower_links($ID)%>
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 = $links_db->select({ $opts->{'dest_zipcode_col'} => $link->{$opts->{'dest_zipcode_col'}} })->fetchrow_hashref;
my $currlat = $osth->{$opts->{'dest_latitude_col'}};
my $currlon = $osth->{$opts->{'dest_longitude_col'}};

# The actual distance search
$temp_distance = distance($olat, $olon, $currlat, $currlon, $unitcode);

if($temp_distance <= $dist)
{
$link_count++;
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}
}
}
}
else
{
my $dest_zipcode_col = $opts->{'dest_zipcode_col'};
my $dest_latitude_col = $opts->{'dest_latitude_col'};
my $dest_longitude_col = $opts->{'dest_longitude_col'};
my $sort_order = 'ORDER BY Distance ';
$sort_order .= $args->{so};
$sort_order .= ',';
$sort_order .= $args->{sb};

my $code = "SELECT DISTINCT o.ID, o.Title, o.Add_Date, (3956 * (2 * ASIN(SQRT( POWER(SIN(((z.DEST_LAT_COL-o.DEST_LAT_COL)*0.017453293)/2),2) + COS(z.DEST_LAT_COL*0.017453293) * COS(o.DEST_LAT_COL*0.017453293) * POWER(SIN(((z.DEST_LONG_COL-o.DEST_LONG_COL)*0.017453293)/2),2) )))) AS Distance FROM lsql_Links z, lsql_Links o, lsql_Links a WHERE z.DEST_ZIP_COL = ? AND z.DEST_ZIP_COL = a.DEST_ZIP_COL AND (3956 * (2 * ASIN(SQRT( POWER(SIN(((z.DEST_LAT_COL-o.DEST_LAT_COL)*0.017453293)/2),2) + COS(z.DEST_LAT_COL*0.017453293) * COS(o.DEST_LAT_COL*0.017453293) * POWER(SIN(((z.DEST_LONG_COL-o.DEST_LONG_COL)*0.017453293)/2),2) )))) <= ? SORT_ORDER";
$code =~ s/DEST_ZIP_COL/$dest_zipcode_col/g;
$code =~ s/DEST_LAT_COL/$dest_latitude_col/g;
$code =~ s/DEST_LONG_COL/$dest_longitude_col/g;
$code =~ s/SORT_ORDER/$sort_order/;

my $offset = ($args->{nh} - 1) * $args->{mh};
my $sth = $links_db->prepare($code);
$sth->execute ($query, $dist);

while (my $link = $sth->fetchrow_hashref)
{
if($link_count >= $offset and ( $link_count < ($offset + $args->{mh})) ) {
$link = Links::SiteHTML::tags('link', $link);
push (@link_results_loop, $link);
}
$link_count++;
}
}

Thanks in advance for any assistance. Really this is it, if I can figure this out, I am done.

Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Need help with Sort by distance In reply to
Did you get this plugin finished? Could it be adapted to UK postcodes (I've got the database of lat/long).

Have you got a demo? please can you PM me with a URL if you have?