Coldfusion radius search with Geocodes using MySQL 5 DB
$100-500 USD
In Progress
Posted almost 16 years ago
$100-500 USD
Paid on delivery
We need a radius search to be performed that will return a list of items that are located within the radius that the user will specify in a dropdown list. (50 miles, 100 miles, 150 miles, etc) of a specific geocode. This is for a real estate website. It should work like this:
1. The end user performs a search on a table called “listings??. Example of the search: "I am looking for listings that are in the city of Dallas." The database runs its search and returns 15 homes, for example. WE ALREADY HAVE THIS PROGRAMMED. These results are ALREADY geocoded. No geocoding is required by you.
**Example of our existing query:**
SELECT *
FROM listings
WHERE city = ‘#Dallas#’ AND has_gecode = ‘1’
2. Based on the 15 results (returned in Step 1), we need you to perform a Query of Queries on only ONE of these results. This step here does not have to be a Coldfusion Query of Query. Write what you want that is the most efficient. Now, based on only one single latitude and longitude coordinate that was returned in step 1 above, you write code that take these geocoordinates and then query a second table called “customers?? for items in it that are within the user-specified radius (50, 100, 150 miles). The customers table contains addresses that are already geocoded.
3. All results from your radius search in Step 2 should be returned to us in a simple <cfoutput query="x">#x.ID# #x.company_name# #x.address#</cfoutput>.
4. The radius search does not need to be complicated. We are not interested in pinpoint precise results that are accurate to within a millimeter. :) A simple radius search is fine.
5. The radius calculation should be performed in the actual SQL statement for speed. It must be compatible with MySQL 5.
Our SQL statements to create these two tables will be given to you.
## Deliverables
Here are the SQL statements to create our two tables, listings and customers. Upon acceptance of your bid, we will send you INSERT statements so you have sample data to test the radius search against.
CREATE TABLE `listings` (
`id` int(11) NOT NULL auto_increment,
`street_num` varchar(100) default '0',
`street_dir` varchar(100) default '0',
`street_name` varchar(100) default '0',
`unit_num` varchar(100) default '0',
`city` varchar(100) default '0',
`state` char(2) default NULL,
`zip` varchar(10) default '0',
`has_geocode` char(1) default '0',
`latitude` varchar(20) default '',
`longitude` varchar(20) default '',
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=226 DEFAULT CHARSET=latin1;
CREATE TABLE `customers` (
`id` int(11) NOT NULL auto_increment,
`longitude` varchar(255) default NULL,
`latitude` varchar(255) default NULL,
`has_geocode` char(1) default '0',
`description` longtext,
`date_created` datetime default NULL,
`last_signin` datetime default '2008-01-01 01:00:00',
`ip` varchar(50) default NULL,
`clientid` varchar(50) default NULL,
`last_name` varchar(20) default NULL,
`first_name` varchar(20) default NULL,
`company_name` varchar(35) default NULL,
`address_1` varchar(25) default NULL,
`address_2` varchar(25) default NULL,
`city` varchar(15) default NULL,
`state` varchar(5) default NULL,
`zipcode` varchar(5) default NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=latin1;