Creating MySQL Spatial Procedure in MySQL 5.0

Question

I found a procedure that pretty much meets exactly what I am looking for here on this page:

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

I have modified it just a little to actually work with my database, but everytime i go to create the function it throws an error. I am under the assumption that I can't actually create a precedure in MySQL 5.0 but rather I have to create a function. I just need a little help. I am very new to creating procedures for MySQL.

DELIMITER $$
DROP FUNCTION IF EXISTS `GEODIST` $$
CREATE FUNCTION `GEODIST`( mylat float, mylong float, dist int )
BEGIN
    DECLARE lon1 float; 
    DECLARE lon2 float;
    DECLARE lat1 float; 
    DECLARE lat2 float;

set lon1 = mylon - dist/abs(cos(radians(mylat))*69);
set lon2 = mylon + dist/abs(cos(radians(mylat))*69);
set lat1 = mylat - (dist/69);
set lat2 = mylat + (dist/69);

SELECT destination.*,3956 * 2 * ASIN(SQRT( POWER(SIN((orig.lat -dest.lat) * pi()/180 / 2), 2) +COS(orig.lat * pi()/180) * COS(dest.lat * pi()/180) *POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2) )) as distance FROM users destination, users origin WHERE origin.id=userid
and destination.longitude between lon1 and lon2 and destination.latitude between lat1 and lat2 
having distance < dist ORDER BY Distance limit 10;

END $$

When I run this code an error is thrown:

Error SQL query: Documentation

CREATE FUNCTION GEODIST (

mylat FLOAT, mylong FLOAT, dist INT ) BEGIN DECLARE lon1 FLOAT;

DECLARE lon2 FLOAT;

DECLARE lat1 FLOAT;

DECLARE lat2 FLOAT;

SET lon1 = mylon - dist / ABS( COS( RADIANS( mylat ) ) *69 ) ;

SET lon2 = mylon + dist / ABS( COS( RADIANS( mylat ) ) *69 ) ;

SET lat1 = mylat - ( dist /69 ) ;

SET lat2 = mylat + ( dist /69 ) ;

SELECT destination . * , 3956 *2 * ASIN( SQRT( POWER( SIN( ( orig.lat - dest.lat ) * PI( ) /180 /2 ) , 2 ) + COS( orig.lat * PI( ) /180 ) * COS( dest.lat * PI( ) /180 ) * POWER( SIN( ( orig.lon - dest.lon ) * PI( ) /180 /2 ) , 2 ) ) ) AS distance FROM users destination, users origin WHERE origin.id = userid AND destination.longitude BETWEEN lon1 AND lon2 AND destination.latitude BETWEEN lat1 AND lat2 HAVING distance < dist ORDER BY Distance LIMIT 10 ;

END $$ MySQL said: Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near 'BEGIN DECLARE lon1 float; DECLARE lon2 float; DECLARE lat1 float' at line 2

EDIT: I was advised below to change this to a PROCEDURE in which I have, but it is throwing the error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

near '' at line 3

Devart Devart · Accepted Answer · 2013-12-03T10:52:48

If you do not need to return a value from stored routine, then you need to create a procedure, just change CREATE FUNCTION to CREATE PROCEDURE.

Othervise it is needed to add RETURNS clause to specify result data type, and RETURN clause in the function's body to set result value.

CREATE PROCEDURE and CREATE FUNCTION Syntax.

Creating MySQL Spatial Procedure in MySQL 5.0

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

1 Answers