正在尝试将现有查询转换为存储过程gis

Question

我正在尝试转换现有查询，该查询返回距离点(几何体(特定距离内的所有记录

我在mysql工作台中创建了一个存储过程，它使用lat-long和距离，我希望得到与查询相同的结果(返回指定距离内的所有记录。我哪里出了问题，我试图将lat-long的值传递给另一个空间函数来创建用户位置

CREATE DEFINER=`root`@`localhost` PROCEDURE `findNearby`(
IN lat VARCHAR(255),
IN longVal VARCHAR(255),
IN distanceFrom INT
)
BEGIN
DECLARE user_location geometry default ST_GeomFromText( 'POINT(0 0)', 4326 ) ;
SET user_location = ST_GeomFromText( 'POINT(lat longVal)', 4326 );
SELECT 
*,
ST_AsText(`location`) AS `pos_wkt`,
ST_Distance_Sphere(`location`, user_location) AS `distance`
FROM
`business_details`
WHERE ST_Distance_Sphere(`location`, user_location) <= distanceFrom;
END
SET @user_location = ST_GeomFromText( 'POINT(30 50)', 4326 );
SELECT 
*,
ST_AsText(`location`) AS `pos_wkt`,
ST_Distance_Sphere(`location`, @user_location) AS `distance`
FROM
`business_details`
WHERE ST_Distance_Sphere(`location`, @user_location) <= (1000*5000);

3037-为函数st_geomfromtext提供的GIS数据无效

Answer 1

设法解决了这个问题。

CREATE DEFINER=`root`@`localhost` PROCEDURE `findNearby`(
IN lat VARCHAR(255),
IN longVal VARCHAR(255),
IN distanceFrom INT
)
BEGIN
DECLARE user_location geometry default ST_GeomFromText( 'POINT(0 0)', 4326 ) ;
DECLARE pointString varchar(255);
set pointString = concat('POINT(', lat, ' ', longVal, ')');
SET user_Location = ST_GeomFromText(  pointString, 4326 );
SELECT 
*,
ST_AsText(`location`) AS `pos_wkt`,
ST_Distance_Sphere(`location`, user_location) AS `distance`
FROM
`business_details`
WHERE ST_Distance_Sphere(`location`, user_location) <= distanceFrom * 1000;
END