Jump to content


Photo

Stored Procedure for list_zip_by_miles


  • Please log in to reply
3 replies to this topic

#1 LinuxGold

LinuxGold
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 20 September 2006 - 06:39 PM

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:

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 Zi
WHERE
	Zi.`zip code`=C.ZipCode
ORDER BY
	Round(C.Miles);
+---------+----------------+----------+---------+
| 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 |
+---------+----------------+----------+---------+


#2 LinuxGold

LinuxGold
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 21 September 2006 - 01:28 PM

Anyone know how to create stored procedure?  This is the bottleneck that I'm having right now.  Any url for rookie like me?

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 September 2006 - 01:30 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 LinuxGold

LinuxGold
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 21 September 2006 - 01:44 PM

Ugh, delimiter got it.  *fixes my own eyes*
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 Zi
WHERE
	Zi.`zip code`=C.ZipCode
ORDER BY
	Round(C.Miles);
END
//
DELIMITER ;
CALL find_zip_by_miles (19943,20);





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users