Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Zip code radius search

Quote Reply
Zip code radius search
I am working on implimenting a search by distance or search by radius form on my GLinks installation.

I have a separate mySQL database of "ZipCodes" complete with longitude, latidude and state.
I found the free database and example query here: http://www.kevinroth.com

I need some help with the logic and implementation. Suggestions would be greatly appreciated.

My plan is to....
Add columns for zip code, longitude and latitude to links table
When a link is added to the directory, grab the zipcode from the add.html form,
Lookup the zipcode in the separate "ZipCode" database and grab the longitude - latitude
Put this info into the links table
That should do it. I can then use a query to find...
Links within XX miles of zip 12345

I do not know how to grab the longitude and latitude from zipcodes and put into links when a user adds a link.

Will I have to edit Add.pm (called via add.cgi)?
Is this best done with a plugin and hook?

Actually, thinking out loud... I could create a global that returns longitude/latitude when given a zipcode and distance(radius).

Can someone help me create a global from this code?
I will still need to get the longitude/latitude from "zipcodes" db into links table when a link is added,
then I can use the global on the search form to find links that match query.
Code:
# 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


Please help.

Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Zip code radius search In reply to
I have started a Plugin for ZipCodeSearch. It really is only one routine.

The plugin needs to take the 'Zip' from the just added or modified link,
and find it (Zip) in a different database named 'zipcodes' which has a table named 'ZipCodes'.
Then it needs to simply copy longitude and latitude from zipcodes and put them into the link.

Questions:
Should I be using PRE or POST hooks for add_link and modify_link?
Do "I" have to write the link back to the database?
Or, can I just fill in arg->longitude and arg->latitude and let GT carry on?

My Starting code
Code:
sub modify_link_hook {
my (@args) = @_;

my $link_id = $IN->param('LinkID');
my $link = $DB->table('Links','CatLinks')->select({ ID => scalar $IN->param('ID')})->fetchrow_hashref;
my $zipdb = $DB->table("ZipCodes") || return $GT::SQL::error;
args[0]->{Longitude} = $zipdb->select('Longitude', { ZipCode => $IN->param('Zip')})->fetchrow;
args[0]->{Latitude} = $zipdb->select('Latitude', { ZipCode => $IN->param('Zip') })->fetchrow;

my $db = $DB->table('Links');

return @args;
}
Thanks for any help.

Chris
RGB World, Inc. - Software &amp; Web Development.
rgbworld.com
Quote Reply
Re: [rgbworld] Zip code radius search In reply to
A PRE hook will allow you to change data that's being passed to the actual function (if it's passed as a reference) or stop the actual function from running. A POST hook runs after the function has ran. In your case, you can do it either way - set them and let add/modify insert the data, or perform an update in a post hook. I haven't really looked at the plugin internals, so I'm not sure if modifying @_ will do the trick...

Adrian
Quote Reply
Re: [brewt] Zip code radius search In reply to
I get the following error when modifying a link with my plugin installed.
This error shows above the modify form; kind of like leaving a required field empty. i.e. "You must enter a Name"

Quote:
Longitude can not contain the value '-87.878781'


My plugin code
Code:
sub modify_link_hook {
my (@args) = @_;
my $args = shift;

my $zipdb = $DB->table('ZipCodes') || return $GT::SQL::error;
$args->{Longitude} = $zipdb->select('Longitude', { ZipCode => $IN->param('Zip') })->fetchrow;
$args->{Latitude} = $zipdb->select('Latitude', { ZipCode => $IN->param('Zip') })->fetchrow;


return @args;
}


I have not put any hidden tags on the modify form.

My table is created like this
Code:
my $creator = $DB->creator ('ZipCodes');
$creator->cols ( {
ZipCode => { pos => 1, type => 'VARCHAR', size => '5', not_null => 1, default => ''},
Latitude => { pos => 2, type => 'DOUBLE', size => '11', not_null => 1, default => '0'},
Longitude => { pos => 3, type => 'DOUBLE', size => '11', not_null => 1, default => '0' },
State => { pos => 4, type => 'CHAR', size => '2', not_null => 0, default => '' }
});

# set the primary key, auto-increment and index
$creator->pk('ZipCode','Latitude','Longitude');

# unless ($creator->create) {
# $Plugins::Pluginname::error = "Unable to add ZipCode table: $GT::SQL::error";
# return;
# }

$creator->load_table();

Actually, I created the table manually via phpMyAdmin. I was getting def file errors, so I added the table creation code to the installer and that fixed def errors. I DO NOT want to over-write the(existing) table as it has 35000 records in it!
I could use a better way of doing this than just "skipping" the error.

Regarding the error "Longitude can not contain the value '-87.878781'", maybe it is because I created table by hand and am skipping the error on creation... so, the def file doesn't know I want a "double" for the data type? just ideas

The data (-87.878781) is correct for the link I am testing, so it is getting the Longitude from the 'ZipCodes' table. Some GT code must not like the double? do I have to use signed?

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