kawuat
|
| Posted: 04/10/2005, 11:11 PM |
|
I'm new to CCS and am trying to add a Store Locator to my project, can anyone suggest a direction for pulling my dealer info from the database using distance calutation from zipcode passed as parameter
|
|
|
 |
kawuat
|
| Posted: 04/29/2005, 12:21 AM |
|
I found a solution based in the code of phpdealer locator (free version) which was built with CCS.
I've included it below for any one looking to execute this in the future.
Using two tables, one for contractors and the other for zipcode data. In the query builder for your results form use "SQL" for the data source type and enter a query based on the following, modify to match your tables.
SELECT
(3963 * acos(truncate(sin(zipcodesearch.latitude / 57.2958) * sin(zipcode_data.latitude / 57.2958) + cos(zipcodesearch.latitude / 57.2958) * cos(zipcode_data.latitude / 57.2958) * cos(zipcode_data.longitude / 57.2958 - zipcodesearch.longitude / 57.2958), 8))) AS search_radius,
contractor.comp_name,
contractor.contact_name,
contractor.address1,
contractor.address2,
contractor.url,
contractor.city,
contractor.state,
contractor.zip,
contractor.phone,
contractor.email
FROM
contractor,
zipcode_data,
zipcode_data zipcodesearch
WHERE
(comp_name LIKE '%{s_comp_name}%') AND
(contractor.state_id = '{s_state_id}' ) OR
(zipcodesearch.ZIPCode = '{s_zip}') AND
(contractor.zip = zipcode_data.zipcode) AND
(( 3963 * acos( truncate(sin( zipcodesearch.latitude / 57.2958 ) * sin( zipcode_data.latitude / 57.2958 ) + cos( zipcodesearch.latitude / 57.2958 ) * cos( zipcode_data.latitude / 57.2958 ) * cos( zipcode_data.longitude / 57.2958 - zipcodesearch.longitude / 57.2958 ),8) ) ) <= '{s_distance}')
ORDER BY
search_radius
This returns all entries from the contractor table within the radius of "s_distance"
The zipcode data base takes the following form
CREATE TABLE `zipcode_data` (
`zip_id` int(11) unsigned NOT NULL auto_increment,
`zipcode` varchar(5) NOT NULL default '',
`latitude` double(7,5) default NULL,
`longitude` double(7,5) default NULL,
PRIMARY KEY (`zip_id`,`zipcode`),
KEY `latitude` (`latitude`),
KEY `longitude` (`longitude`)
) TYPE=MyISAM
You will of course have to find sources for your zipcode data, there are pay and some free that offer limited code databases
Thanks to PHPPro - www.yourphppro.com for their ealier version of the free locator,
|
|
|
|
|
