How to combine a Distance and Keyword SQL query?

Question

Hi Folks,

I have a tables in my database called "points" and "category". A user will input info into both a location input and a keyword input text box.

Then I want to find points in my table where the keyword matches either the "title" field in the points table, or the "category" but are within a certain distance from the user's location. I want to order the results by distance.

Here are the 2 queries which btoh work independently:

$mysql = "SELECT *, ( 3959 * acos( cos( radians('$search_lat') ) * cos( radians( lat ) ) * cos( radians( longi ) - radians('$search_lng') ) + sin( radians('$search_lat') ) * sin( radians( lat ) ) ) ) AS distance FROM points HAVING distance < '$radius'";

$mysql2 =  "SELECT * FROM `points` LEFT JOIN category USING ( category_id ) WHERE (point_title LIKE '%$esc_catsearch%' OR category.title LIKE '%$esc_catsearch%')";

Here is what I tried:

$sql_search = sprintf("SELECT *,point_id FROM points WHERE point_title LIKE '%%%s%%' UNION SELECT *, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( longi ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM points HAVING distance < '%s' ORDER BY distance LIMIT %d , %d",

 $esc_catsearch,
 mysql_real_escape_string($search_lat),

 mysql_real_escape_string($search_lng),

 mysql_real_escape_string($search_lat),

 mysql_real_escape_string($radius),

 $offset,

 $rowsPerPage);

But it tells me there is no know column "distance". If I remove the "Order By" phrase then it works but I'm still not sure this is giving me the results I want. I also tried the query the other way around with the distance search first but that seems to ignore my keyword.

Any thoughts would be much appreciated!

Answer 1

Perhaps instead of using a union, you could run the distance query as a subquery, then SELECT from that (treating it as a temporary table) to get the ones which match the keyword search? In essence, something of the general form...

SELECT * FROM (SELECT * FROM ... WHERE distance < X) as distResults WHERE ....

Or potentially the other way around - run the keyword search first, then take those results and use them to run the distance search. Whichever way will eliminate the most calculation overhead.

Answer 2

I think you need to have the column alias in the first part of your union:

 $sql_search = sprintf("SELECT *,point_id as distance FROM...

Answer 3

Thanks guys. I was able to get it to work using this query:

SELECT *,point_id FROM (SELECT *, ( 3959 * acos( cos( radians('37.7749295') ) * cos( radians( lat ) ) * cos( radians( longi ) - radians('-122.4194155') ) + sin( radians('37.7749295') ) * sin( radians( lat ) ) ) ) AS distance FROM points HAVING distance < '25') as distResults LEFT JOIN category USING ( category_id ) WHERE (point_title LIKE '%test%' OR category.title LIKE '%test%') ORDER BY distance LIMIT 0 , 10

Thanks!