SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL PROCEDURE or PHP class?

    I have a database with coordinates to listed addresses.... I'm getting distance between these addresses and also grabbing addresses in a set radius, would it better to use MySQL procedures for all this or go with a custom PHP class?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It would be more efficient to do this in MySQL. You can get the addresses in a set radius directly with a single query; SQL is fully capable of the math required.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, so my next question would be, can I stay with using mysql functions or do I have to go to mysqli functions for call these procedures? I keep getting errors in both mysql and mysqli functions but it's working fine from the command line...

    Code SQL:
    DELIMITER $$
     
    CREATE DEFINER=`root`@`localhost` PROCEDURE `GetNearbyZipCodes`(
    	zipbase VARCHAR(10),
    	r NUMERIC(15)
    )
    BEGIN
    	DECLARE lat1 DECIMAL(9, 6);
    	DECLARE lon1 DECIMAL(9, 6);
    	SELECT latitude, longitude INTO lat1, lon1 FROM zipcodes WHERE zipcode = zipbase;
    	SELECT B.zipcode, B.city_name, GetDistance(lat1, lon1, B.latitude, B.longitude) AS distance
    		FROM zipcodes AS B
    		WHERE
    			GetDistance(lat1, lon1, B.latitude, B.longitude) <= r
    		AND
    			GetDistance(lat1, lon1, B.latitude, B.longitude) > 0
    		ORDER BY distance;
    END $$
     
    CREATE DEFINER=`root`@`localhost` FUNCTION `GetDistance`(
    	lat1 NUMERIC(9, 6),
    	lon1 NUMERIC(9, 6),
    	lat2 NUMERIC(9, 6),
    	lon2 NUMERIC(9, 6)
    ) RETURNS DECIMAL(10, 5)
    BEGIN
    	DECLARE x DECIMAL(20, 10);
    	SET x = SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) + COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(ABS(RADIANS(lon2) - RADIANS(lon1)));
    	SET x = ATAN((SQRT(1 - POWER(x, 2))) / x);
    	RETURN (1.852 * 60 * DEGREES(x) / 1.609344);
    END $$
     
    DELIMITER ;

    This is what I'm using for zipcodes so far, working my way to exact addresses

    Code:
    mysql> CALL GetNearbyZipCodes('80601', 10);
    +---------+---------------+----------+
    | zipcode | city_name     | distance |
    +---------+---------------+----------+
    | 80640   | Henderson     |  6.03482 |
    | 80022   | Commerce City |  6.72023 |
    | 80233   | Denver        |  7.27297 |
    | 80614   | Eastlake      |  8.19226 |
    | 80241   | Denver        |  8.78002 |
    | 80621   | Fort Lupton   |  8.81560 |
    | 80249   | Denver        |  8.98407 |
    | 80024   | Dupont        |  9.42712 |
    +---------+---------------+----------+
    8 rows in set (0.03 sec)

    Just to add, I'm new to using MySQL to do this stuff, so any advice on this would be greatly appreciated.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •