using intersects with muli boundary in spatial index
Posted by:
mac jun
Date: December 26, 2010 07:52AM
SET @dlat = 5, @dlong = 5;
SET @center1 = GeomFromText('POINT(10 10)');
SET @bbox1 = CONCAT('POLYGON((',
X(@center1) - @dlat, ' ', Y(@center1) - @dlong, ',',
X(@center1) + @dlat, ' ', Y(@center1) - @dlong, ',',
X(@center1) + @dlat, ' ', Y(@center1) + @dlong, ',',
X(@center1) - @dlat, ' ', Y(@center1) + @dlong, ',',
X(@center1) - @dlat, ' ', Y(@center1) - @dlong, '))'
);
SET @center2 = GeomFromText('POINT(20 20)');
SET @bbox2 = CONCAT('POLYGON((',
X(@center2) - @dlat, ' ', Y(@center2) - @dlong, ',',
X(@center2) + @dlat, ' ', Y(@center2) - @dlong, ',',
X(@center2) + @dlat, ' ', Y(@center2) + @dlong, ',',
X(@center2) - @dlat, ' ', Y(@center2) + @dlong, ',',
X(@center2) - @dlat, ' ', Y(@center2) - @dlong, '))'
);
SET @center2 = GeomFromText('POINT(30 30)');
SET @bbox2 = CONCAT('POLYGON((',
X(@center3) - @dlat, ' ', Y(@center3) - @dlong, ',',
X(@center3) + @dlat, ' ', Y(@center3) - @dlong, ',',
X(@center3) + @dlat, ' ', Y(@center3) + @dlong, ',',
X(@center3) - @dlat, ' ', Y(@center3) + @dlong, ',',
X(@center3) - @dlat, ' ', Y(@center3) - @dlong, '))'
);
select
min(SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 ))) as distance,
(select max(idx) from goods where user_id=tbl.user_id ) as idx
from cs_xy as tbl
where intersects( location, GeomFromText(@bbox1, @bbox2, @bbox3) )
group by user_id order by distance asc limit 4;
in this query, an error occures in GeomFromText(@bbox1, @bbox2, @bbox3)
no error when GeomFromText(@bbox1)
does anybody know how to use multi boundary in intersects?