CODEWITHSUNDEEP
mysql
user547995
user547995

Reputation: 2085

Mysql Query Distance Search Fail

Hy!

My Table:

CREATE TABLE IF NOT EXISTS `USER_SETTING` (
  `USER_ID` int(11) NOT NULL,
  `LATITUDE` float(10,6) NOT NULL DEFAULT '0.000000',
  `LONGITUDE` float(10,6) NOT NULL DEFAULT '0.000000',
  `MINAGE` int(11) DEFAULT NULL,
  `GENDER` varchar(1) DEFAULT NULL,
  `DISTANCE` int(11) DEFAULT NULL,
  `MAXAGE` int(11) NOT NULL,
  PRIMARY KEY (`USER_ID`)
)

Query:

SELECT
    user_id,
    LONGITUDE,
    LATITUDE,
    MINAGE,
    MAXAGE,
    DISTANCE,
    (
      6371 * acos(
                cos(
                   radians(47.958153)
                ) * cos(
                   radians(LANGITUDE)
                ) * cos(
                   radians(LATITUDE) - radians(13.780375)
                ) + sin(
                   radians(47.958153)
                ) * sin(
                   radians(LANGITUDE)
                )
           )
    ) AS range
FROM
    USER_SETTING
HAVING
    range < 800
ORDER BY
    range
LIMIT 0, 20

Problem:

The query doesn't work.

The Error Message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'range FROM USER_SETTING HAVING range < 800 ORDER BY range LIMIT 0 , 20' at line 1

please help

Upvotes: 0

Views: 131

Answers (3)

Joshua Martell
Joshua Martell

Reputation: 7212

This presentation is the best I've seen on the topic. I includes working queries, etc, as well as performance tips and tricks for distance calculations in general.

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

Upvotes: 0

Web User
Web User

Reputation: 7736

HAVING works on GROUP'ed columns only. Since I did not notice any GROUP in your query, I modified it to use a WHERE clause.

SELECT user_id,
       LONGITUDE,
       LATITUDE,
       MINAGE,
       MAXAGE,
       DISTANCE,
       (6371 * acos( cos( radians(47.958153) ) * cos( radians(LANGITUDE)) * cos(radians(LATITUDE) - radians(13.780375)) + sin(radians(47.958153)) * sin(radians(LANGITUDE)))) AS range
FROM USER_SETTING
WHERE range < 800
ORDER BY range
LIMIT 0 , 20

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

You need to add a Group by clause in order to use HAVING, otherwise use:

Where range < 800

Upvotes: 0

Related Questions