LinuxGold Posted September 20, 2006 Share Posted September 20, 2006 I am trying to create a procedure that lists a lists of zip codes within given miles and local zip code. After reading manual online I am unable to understand how to create it. I use mysql command line interface to create stored procedure. How do I get around to it? I want to do list_zip_by_miles (19943,20) as following with result afterwards:[code]SET @targetzip='19943'; /*Local Zip Code*/SET @rangemiles='20'; /*Mile(s) range limit */SELECT C.ZipCode, Zi.City, Zi.State, Round(C.Miles) AS 'Mile(s)'FROM ( SELECT Lat_A, Long_A, Lat_B, Long_B, ZipCode, (degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 AS 'Miles' FROM ( SELECT CAST(latitude AS decimal(8,6)) AS Lat_A, CAST(longitude AS decimal(8,6)) AS Long_A FROM zips WHERE `zip code`=@targetzip ) AS A, ( SELECT CAST(latitude AS decimal(8,6)) AS Lat_B, CAST(longitude AS decimal(8,6)) AS Long_B, `zip code` AS ZipCode FROM zips ) AS B WHERE (degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 <= @rangemiles ) AS C, zips ZiWHERE Zi.`zip code`=C.ZipCodeORDER BY Round(C.Miles);[/code][code]+---------+----------------+----------+---------+| ZipCode | City | State | Mile(s) |+---------+----------------+----------+---------+| 19943 | Felton | Delaware | 0 || 19979 | Viola | Delaware | 3 || 19980 | Woodside | Delaware | 4 || 19934 | Camden Wyoming | Delaware | 6 || 19962 | Magnolia | Delaware | 6 || 19946 | Frederica | Delaware | 7 || 19952 | Harrington | Delaware | 7 || 19954 | Houston | Delaware | 8 || 19964 | Marydel | Delaware | 10 || 19942 | Farmington | Delaware | 10 || 19904 | Dover | Delaware | 11 || 19901 | Dover | Delaware | 11 || 19963 | Milford | Delaware | 11 || 21636 | Goldsboro | Maryland | 11 || 19902 | Dover Afb | Delaware | 11 || 19953 | Hartly | Delaware | 11 || 21640 | Henderson | Maryland | 12 || 21639 | Greensboro | Maryland | 12 || 21649 | Marydel | Maryland | 12 || 19950 | Greenwood | Delaware | 13 || 19955 | Kenton | Delaware | 15 || 19960 | Lincoln | Delaware | 15 || 21644 | Ingleside | Maryland | 16 || 21660 | Ridgely | Maryland | 16 || 21629 | Denton | Maryland | 16 || 19941 | Ellendale | Delaware | 17 || 21607 | Barclay | Maryland | 17 || 19938 | Clayton | Delaware | 18 || 19933 | Bridgeville | Delaware | 19 || 21668 | Sudlersville | Maryland | 19 || 21641 | Hillsboro | Maryland | 20 || 19977 | Smyrna | Delaware | 20 |+---------+----------------+----------+---------+[/code] Quote Link to comment Share on other sites More sharing options...
LinuxGold Posted September 21, 2006 Author Share Posted September 21, 2006 Anyone know how to create stored procedure? This is the bottleneck that I'm having right now. Any url for rookie like me? Quote Link to comment Share on other sites More sharing options...
fenway Posted September 21, 2006 Share Posted September 21, 2006 I haven't come across a really good tutorial/resource, mainly becuase I never use SPs -- but I don't see you ever resetting the delimiter. Quote Link to comment Share on other sites More sharing options...
LinuxGold Posted September 21, 2006 Author Share Posted September 21, 2006 Ugh, delimiter got it. *fixes my own eyes*[code]delimiter //CREATE PROCEDURE find_zip_by_miles ( targetzip INT, rangemiles INT)/**/BEGIN/* DECLARE @targetzip=int; DECLARE @rangemiles int;*/ SET @targetzip=targetzip; SET @rangemiles=rangemiles;SELECT C.ZipCode, Zi.City, Zi.State, Round(C.Miles) AS 'Mile(s)'FROM ( SELECT Lat_A, Long_A, Lat_B, Long_B, ZipCode, (degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 AS 'Miles' FROM ( SELECT CAST(latitude AS decimal(8,6)) AS Lat_A, CAST(longitude AS decimal(8,6)) AS Long_A FROM zips WHERE `zip code`=@targetzip ) AS A, ( SELECT CAST(latitude AS decimal(8,6)) AS Lat_B, CAST(longitude AS decimal(8,6)) AS Long_B, `zip code` AS ZipCode FROM zips ) AS B WHERE (degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 <= @rangemiles ) AS C, zips ZiWHERE Zi.`zip code`=C.ZipCodeORDER BY Round(C.Miles);END//DELIMITER ;CALL find_zip_by_miles (19943,20);[/code] Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.