地理距离搜索 MYSQL

发布于 2024-11-07 18:57:36 字数 1058 浏览 3 评论 0原文

我正在使用空间扩展使用 mysql 进行地理搜索。因为我做了一个存储过程（当我像这样在 mysql 的命令行上尝试时，它工作得非常好

mysql>称呼 test2(GeomFromText('点(-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

但问题是我需要从 php.ini 调用它。当我这样做时，返回一个错误说

PROCEDURE test2 无法返回结果在给定上下文中设置

那么..我如何使用转身来返回一组数据？

谢谢

原文

I'm doing a geo search with mysql using the spatial extension. FOr that i did a stored procedure (thats work really well when i tri on command line in mysql like that

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 that from php. and when i do it, an error is return said that

PROCEDURE test2 can't return a result
set in the given context

So.. How can i use a turn around to return a set of data ?

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

仅此而已 2024-11-14 18:57:36

您可以将最后一部分更改为

/*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;

然后您可以从 temp_table 中进行选择并在那里找到结果集。

You can change the last part into

/*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 the temp_table and find resultset there.

回复收藏 0 原文

~没有更多了~