Thread: Problem with function...
Hi, I hope my question is appropriate for this list. I'm trying to create a function that calculates the distance between a pair of latitude/longitude points. This is what I have: /* latlon_distance.pgsql* by Jamu Kakar <jkakar@expressus.com>, Dec 18, 2000.** Calculates the distance between 2 lat/lonpairs. Syntax:* distance (lat1, lon1, lat2, lon2) where parameters are in decimal degrees.*/ CREATE FUNCTION distance (float8, float8, float8, float8) RETURNS float8 AS ' DECLARE radius constant float8 := 6378; distance float8; lat1 ALIAS FOR $1; lon1 ALIAS FOR $2; lat2 ALIAS FOR $3; lon2 ALIAS FOR $4; BEGIN distance :=radius * acos ((sin (radians (lat1)) * sin (radians (lat2))) + (cos (radians (lat1)) * cos(radians (lat2)) * cos (radians (lon1) - radians (lon2)))); RETURN ''distance''; END; ' LANGUAGE 'plpgsql'; When I try a: select distance(49.0,-122.0,50.0,-123.0) as distance; I get: ERROR: Bad float8 input format 'distance' I've tried a variety of ways of specifying the values and I've hunted through the mailing lists but haven't turned up anything useful. Any help would be appreciated. Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. jkakar@expressus.com 708-1641 Lonsdale Avenue V: (604) 988-6999 North Vancouver, BC, V7M 2J5
I don't think you return should have '' around them, i.e. RETURN distance; Michael Fork - CCNA - MCP - A+ Network Support - Toledo Internet Access - Toledo Ohio On Mon, 18 Dec 2000 jkakar@expressus.com wrote: > Hi, > > I hope my question is appropriate for this list. I'm trying to create > a function that calculates the distance between a pair of > latitude/longitude points. This is what I have: > > /* latlon_distance.pgsql > * by Jamu Kakar <jkakar@expressus.com>, Dec 18, 2000. > * > * Calculates the distance between 2 lat/lon pairs. Syntax: > * distance (lat1, lon1, lat2, lon2) where parameters are in decimal degrees. > */ > > CREATE FUNCTION distance (float8, float8, float8, float8) RETURNS float8 AS ' > DECLARE > radius constant float8 := 6378; > distance float8; > lat1 ALIAS FOR $1; > lon1 ALIAS FOR $2; > lat2 ALIAS FOR $3; > lon2 ALIAS FOR $4; > BEGIN > distance := radius * acos ((sin (radians (lat1)) * sin (radians (lat2))) + > (cos (radians (lat1)) * cos (radians (lat2)) * > cos (radians (lon1) - radians (lon2)))); > RETURN ''distance''; > END; > ' LANGUAGE 'plpgsql'; > > When I try a: > > select distance(49.0,-122.0,50.0,-123.0) as distance; > > I get: > > ERROR: Bad float8 input format 'distance' > > I've tried a variety of ways of specifying the values and I've hunted > through the mailing lists but haven't turned up anything useful. Any > help would be appreciated. > > Thanks, > Jamu. > > -- > Jamu Kakar (Developer) Expressus Design Studio, Inc. > jkakar@expressus.com 708-1641 Lonsdale Avenue > V: (604) 988-6999 North Vancouver, BC, V7M 2J5 >
Hi, there, modify the code as following. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com On Mon, 18 Dec 2000 jkakar@expressus.com wrote: > Hi, > > I hope my question is appropriate for this list. I'm trying to create > a function that calculates the distance between a pair of > latitude/longitude points. This is what I have: > > /* latlon_distance.pgsql > * by Jamu Kakar <jkakar@expressus.com>, Dec 18, 2000. > * > * Calculates the distance between 2 lat/lon pairs. Syntax: > * distance (lat1, lon1, lat2, lon2) where parameters are in decimal degrees. > */ > > CREATE FUNCTION distance (float8, float8, float8, float8) RETURNS float8 AS ' > DECLARE > radius constant float8 := 6378; > distance float8; > lat1 ALIAS FOR $1; > lon1 ALIAS FOR $2; > lat2 ALIAS FOR $3; > lon2 ALIAS FOR $4; > BEGIN > distance := radius * acos ((sin (radians (lat1)) * sin (radians (lat2))) + > (cos (radians (lat1)) * cos (radians (lat2)) * > cos (radians (lon1) - radians (lon2)))); > -- RETURN ''distance''; -- here means text instead of float8 which -- you defined. ===> RETURN distance; > END; > ' LANGUAGE 'plpgsql'; > > When I try a: > > select distance(49.0,-122.0,50.0,-123.0) as distance; > > I get: > > ERROR: Bad float8 input format 'distance' > > I've tried a variety of ways of specifying the values and I've hunted > through the mailing lists but haven't turned up anything useful. Any > help would be appreciated. > > Thanks, > Jamu. > > -- > Jamu Kakar (Developer) Expressus Design Studio, Inc. > jkakar@expressus.com 708-1641 Lonsdale Avenue > V: (604) 988-6999 North Vancouver, BC, V7M 2J5 >