I'm trying to set up a table that has the fields:

cab number
call number
latitude of cab
longtitude of cab
Latitude of call
Longtitude of call

And I'm trying to generate a column of the distance from the cab to
the call with the following:

# cREATE FUNCTION FOR GPS distance

delimiter ;

DROP FUNCTION IF EXISTS gps_distance;

delimiter $$

CREATE FUNCTION gps_distance(lat1 DECIMAL(9,6), long1 DECIMAL(9,6),
lat2 DECIMAL(9,6), long2 DECIMAL(9,6), avgradius FLOAT)
RETURNS DECIMAL(9,6)

RETURN (ACOS(COS(RADIANS(90-lat1)) *COS(RADIANS(90-lat2))
+SIN(RADIANS(90-lat1)) *SIN(RADIANS(90-lat2))
*COS(RADIANS(long1-long2))) * avgradius;

DELIMITER ;

#Now we run the select, use the function, and create a TEMP table so
that we see the result

TRUNCATE gps_match_distance;

DROP TABLE IF EXISTS answer;
DROP TABLE IF EXISTS temp;

# gps_latitude_call == lat1
# gps_longtitude_call = long1
# gps_latitude_cab = lat2
# gps_longtitude_cab = long2
# avgradius = 6371

CREATE TABLE temp
SELECT *, gps_distance(gps_latitude_call, gps_longtitude_call,
gps_latitude_cab, gps_longtitude_cab,
6371) FROM gps_match_lat_long;
{cocmpleted so far]

Can someone PLEASE tell me why the response is that 'the function
"[database].gps_distance" doesn't exist'?

Can someone tell me what I'm missing in this?

On 2010-10-26 21:15, Charles wrote:
[...]
Did the function compile successfully? Not sure it is the problem, but I
had to change your function a bit before I could get it to compile.
After that I have no trouble accessing the function:

mysql> delimiter $$
mysql>
mysql> CREATE FUNCTION gps_distance(lat1 DECIMAL(9,6), long1 DECIMAL(9,6),
-> lat2 DECIMAL(9,6), long2 DECIMAL(9,6), avgradius FLOAT)
-> RETURNS DECIMAL(9,6)
-> RETURN ACOS(COS(RADIANS(90-lat1)) *COS(RADIANS(90-lat2))
-> +SIN(RADIANS(90-lat1)) *SIN(RADIANS(90-lat2))
-> *COS(RADIANS(long1-long2))) * avgradius $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT gps_distance(1,1,1,1,6371);
+----------------------------+
1 row in set (0.07 sec)

/Lennart

On Oct 26, 12:57*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
I'm doing it in the database in an SQL window provided by phpMyAdmin
while I'm developing it.

From the first DELIMITER ; to the last DELIMITER ; in the original
code it runs without error. The second part runs without error. The
CREATE TABLE ... portion returns the error that the function doesn't
exist.

On 2010-10-26 23:38, Charles wrote:

I suggest you start with something simpler than your codesnippet (my
example for example). Your code *should* not create the function since
you don't have a $$ at the end somewhere, perhaps your admintool is
playing you a joke by not showing you the real error (that the function
where not created successfully)?


/Lennart

On Oct 26, 9:21*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
That's entirely possible, because this now give me an error message
to the effect of "syntax error somewhere on the line naming the
column:

# cREATE FUNCTION FOR GPS distance

DROP FUNCTION IF EXISTS gps_distance;
DROP TABLE IF EXISTS temp;

CREATE FUNCTION gps_distance(lat1 DECIMAL(9,6), long1 DECIMAL(9,6),
lat2 DECIMAL(9,6), long2 DECIMAL(9,6), avgradius FLOAT)
RETURNS DECIMAL(9,6)
RETURN ACOS(COS(RADIANS(90-lat1)) *COS(RADIANS(90-lat2))
+SIN(RADIANS(90-lat1)) *SIN(RADIANS(90-lat2))
*COS(RADIANS(long1-long2))) * avgradius;

#Now we run the select, use the function, and create a TEMP table so
that we see the result

# gps_latitude_call == lat1
# gps_longtitude_call = long1
# gps_latitude_cab = lat2
# gps_longtitude_cab = long2
# avgradius = 6371

CREATE TABLE temp
SELECT *, (gps_distance int) FROM gps_match_lat_long WHERE
gps_distance = gps_distance(gps_latitude_call, gps_longtitude_call,
gps_latitude_cab, gps_longtitude_cab,
6371);

Am 26.10.2010 21:15, schrieb Charles:

Here you set the delimiter to '$$'.

But here you end your CREATE FUNCTION statement (delimit it from the
next statement) with ';'.

HTH, Helmut

On Oct 27, 6:30*am, Helmut Chang <use... (AT) helmutchang (DOT) at> wrote:
And it does, lots, but UPDATE really cleaned up the code

The final routine reads:

truncate gps_match_distance;
drop table if exists answer;
drop table if exists temp;

CREATE TABLE temp SELECT * FROM gps_match_lat_long;
ALTER TABLE temp ADD gps_distance DECIMAL( 5, 2 ) NOT NULL;

UPDATE temp SET gps_distance = abs(6371 *
ACOS(COS(RADIANS(90-gps_latitude_call)) *
COS(RADIANS(90-gps_latitude_cab)) +
SIN(RADIANS(90-gps_latitude_call)) *
SIN(RADIANS(90-gps_latitude_cab)) *
COS(RADIANS(gps_longtitude_call-gps_longtitude_cab))));