Geek asks and answers

Geographical distance Search MYSQL

I am doing a geo-search using mysql using a spatial extension. Forr, that I made a stored procedure (this works very well when I tri in the command line in mysql like this

mysql> CALL test2 (GeomFromText ('POINT (-0.93961472 43.52843475)'), 0.6);

DROP PROCEDURE `test2`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `test2`(IN center2 point,IN dist int)
BEGIN
SET @center = center2;
SET @radius = dist;

SET @bbox = CONCAT('POLYGON((', 
 X(@center) - @radius, ' ', Y(@center) - @radius, ',', 
 X(@center) + @radius, ' ', Y(@center) - @radius, ',', 
 X(@center) + @radius, ' ', Y(@center) + @radius, ',', 
 X(@center) - @radius, ' ', Y(@center) + @radius, ',', 
 X(@center) - @radius, ' ', Y(@center) - @radius, '))' 
);

SELECT professionnels.*, AsText(coord) 
FROM professionnels 
WHERE Intersects( coord, GeomFromText(@bbox) ) 
AND SQRT(POW( ABS( X(coord) - X(@center)), 2) + POW( ABS(Y(coord) - Y(@center)), 2 )) < @radius limit 20;
end

But the problem is that I need to call this from php. and when I do this, the error returns that

PROCEDURE test2 cannot return the result set in this context

So how can I use rotation to return a dataset?

thank

+3
source share
1 answer

You can change the last part to

/*delete all rows from temp_table*/
DELETE FROM temp_table WHERE temp_table.somefield is not null;

INSERT INTO temp_table
  SELECT professionnels.*, AsText(coord) 
  FROM professionnels 
  WHERE Intersects( coord, GeomFromText(@bbox) ) 
  AND SQRT(POW( ABS( X(coord) - X(@center)), 2) 
      + POW( ABS(Y(coord) - Y(@center)), 2 )) < @radius limit 20;

Then you can select from temp_table and find the results there.

0
source

More articles:


All Articles