Gossamer Forum: Products: Gossamer Links: Discussions: Zip code radius search

May 19, 2005, 10:54 AM

rgbworld

User (325 posts)

May 19, 2005, 10:54 AM

Post #1 of 4

Shortcut

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 & Web Development.
rgbworld.com

May 19, 2005, 9:52 PM

rgbworld

User (325 posts)

May 19, 2005, 9:52 PM

Post #2 of 4

Shortcut

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 & Web Development.
rgbworld.com

May 19, 2005, 10:41 PM

brewt

Staff (4101 posts)

May 19, 2005, 10:41 PM

Post #3 of 4

Shortcut

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

May 19, 2005, 11:54 PM

rgbworld

User (325 posts)

May 19, 2005, 11:54 PM

Post #4 of 4

Shortcut

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 & Web Development.
rgbworld.com