при попытке поиска со сравненим дистанции в индексе
SELECT id, weight() as real_weight, timestamp_created, GEODIST(0.55078861166627,0.59535898534089,lat_radians,long_radians) as distance FROM rt_matchWHERE distance <=115000.00 and max_distance<distance;
получаю ошибку:
sphinxql: syntax error, unexpected IDENT, expecting CONST_INT or CONST_FLOAT or '-' near 'distance'
в индексе таблица вида:
index rt_match
{
type = rt
path = /var/lib/sphinx/rt_match
rt_attr_timestamp = timestamp_created
....
rt_attr_float = lat_radians
rt_attr_float = long_radians
rt_attr_float = max_distance
....
docinfo = extern
morphology = none
min_word_len = 1
min_infix_len = 2
infix_fields = main_title
html_strip = 1
charset_type = utf-8
enable_star = 1
rt_mem_limit = 256M
}
кругом один флоат проблем недолжно быть
попробовал другой вариант
SELECT id, weight() as real_weight, timestamp_created, GEODIST(0.55078861166627,0.59535898534089,lat_radians,long_radians) as distance FROM rt_user_match WHERE distance <=115000.00 and max_distance-distance&rt;0
получил ошибку:
sphinxql: syntax error, unexpected '-', expecting BETWEEN (or 8 other tokens) near '-distance&rt;0'
после снизошло озарение что sphinx не хочет высчитывать значение в where
вынес вычисление в select
SELECT id, weight() as real_weight, timestamp_created, GEODIST(0.55078861166627,0.59535898534089,lat_radians,long_radians) as distance, max_distance-distance as hu FROM rt_user_match WHERE distance <=115000.00 and hu&rt;0.00;
снизошла благодать:
+------+-------------+-------------------+---------------+---------------+
| id | real_weight | timestamp_created | distance | hu |
+------+-------------+-------------------+---------------+---------------+
| 2097 | 1 | 1334076081 | 101040.648438 | 386959.343750 |
| 2103 | 1 | 1334076082 | 48350.027344 | 155649.968750 |
| 2104 | 1 | 1334076082 | 92743.218750 | 334256.781250 |
| 2106 | 1 | 1334076083 | 63040.796875 | 223959.203125 |
+------+-------------+-------------------+---------------+---------------+