MySQL Spatial

consulta.sql

SET @center = GeomFromText('POINT(20 20)'); 
SET @radius = 30; 
SET @bbox = CONCAT('POLYGON((', 
X(@center) - @radius, ' ', Y(@center) - @radius, ',', 
X(@center) + @radius, ' ', Y(@center) - @radius, ',', 
X(@center) + @radius, ' ', Y(@center) + @radius, ',', 
X(@center) - @radius, ' ', Y(@center) + @radius, ',', 
X(@center) - @radius, ' ', Y(@center) - @radius, '))' 
); 
SELECT name, AsText(location), SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) AS distance 
FROM Points 
WHERE Intersects( location, GeomFromText(@bbox) ) 
AND SQRT(POW( ABS( X(location) - X(@center)), 2) + POW( ABS(Y(location) - Y(@center)), 2 )) < @radius 
ORDER BY distance; 

datos.sql

CREATE TABLE IF NOT EXISTS `points` (
  `name` varchar(20) NOT NULL,
  `location` point NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`name`),
  SPATIAL KEY `location` (`location`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

gistfile1.txt

DELIMITER //
CREATE PROCEDURE fill_points( 
IN size INT(10) 
) 
BEGIN 
DECLARE i DOUBLE(10,1) DEFAULT size; 

DECLARE lon FLOAT(7,4); 
DECLARE lat FLOAT(6,4); 
DECLARE position VARCHAR(100); 

-- Deleting all. 
DELETE FROM Points; 

WHILE i > 0 DO 
SET lon = RAND() * 360 - 180; 
SET lat = RAND() * 180 - 90; 

SET position = CONCAT( 'POINT(', lon, ' ', lat, ')' ); 

INSERT INTO Points(name, location) VALUES ( CONCAT('name_', i), GeomFromText(position) ); 

SET i = i - 1; 
END WHILE; 
END //
DELIMITER ;

gistfile2.txt

CALL fill_points(1000000); 

gistfile3.txt

SELECT name, AsText(location) FROM Points; 

http://howto-use-mysql-spatial-ext.blogspot.mx/

gistfile4.txt

SET @center = GeomFromText('POINT(20 20)'); 
SET @radius = 30; 
SET @bbox = CONCAT('POLYGON((', 
X(@center) - @radius, ' ', Y(@center) - @radius, ',', 
X(@center) + @radius, ' ', Y(@center) - @radius, ',', 
X(@center) + @radius, ' ', Y(@center) + @radius, ',', 
X(@center) - @radius, ' ', Y(@center) + @radius, ',', 
X(@center) - @radius, ' ', Y(@center) - @radius, '))' 
); 
SELECT * FROM points WHERE MBRContains(GeomFromText(@bbox), location);

gistfile5.txt

Start here but note that the solution isn't very fast:

PHP Zip Code Range and Distance Calculation

Now to make it fast - we are going to replace the lookup to use a spatial index :)

Use MySQL

Add a column to the database called location and make it type POINT

Make sure it accepts nulls right now

Run the following SQL Query

UPDATE zip_code SET location = PointFromText(CONCAT('POINT(',lon,' ',lat,')'));

Now, make the column not accept nulls

Add a spatial index to the location column

In the code from the above project replace the function 'get_zips_in_range' with the following:

gistfile6.txt

function get_zips_in_range($zip, $range, $sort=1, $include_base) 
     {


    // returns an array of the zip codes within $range of $zip. Returns
    // an array with keys as zip codes and values as the distance from
    // the zipcode defined in $zip.

    $this->chronometer();                     // start the clock

    $details = $this->get_zip_point($zip);  // base zip details
    if ($details == false) return false;

    // This portion of the routine  calculates the minimum and maximum lat and
    // long within a given range.  This portion of the code was written
    // by Jeff Bearer (http://www.jeffbearer.com). This significanly decreases
    // the time it takes to execute a query.  My demo took 3.2 seconds in
    // v1.0.0 and now executes in 0.4 seconds!  Greate job Jeff!

    // Find Max - Min Lat / Long for Radius and zero point and query
    // only zips in that range.
    $lat = $details[0];
    $lon = $details[1];

    $return = array();    // declared here for scope

    $first = true;
    $radius = $range/69.172;
    $boundary = "POLYGON((";
    for($i=0; $i <= 360; $i += 360/24)
    {
        if($first)
        {
            $first = false;
        }
        else
        {
            $boundary .= ', ';
        }

        $clon = $radius*cos(deg2rad($i)) + $lon;
        $clat = $radius*sin(deg2rad($i)) + $lat;
        $boundary .= "$clon $clat" ;
    }

    $boundary  .= '))';

    $sql = "SELECT zip_code, city, county, state_name, state_prefix, area_code, time_zone, lat, lon FROM zip_code WHERE MBRContains(GeomFromText('$boundary'), location);";

    //echo $sql;
    $r = mysql_query($sql);

    if (!$r) {    // sql error

        $this->last_error = mysql_error();
        return false;

    } else {

        while ($row = mysql_fetch_row($r)) {

            // loop through the results to get the milage from src
            $dist = $this->calculate_mileage($details[0],$row[7],$details[1],$row[8]);
            if ($this->units == _UNIT_KILOMETERS) $dist = $dist * _M2KM_FACTOR;
            $return[str_pad($row[0].', '.$row[1], 5, "0", STR_PAD_LEFT)] = round($dist, $this->decimals);

        }
        mysql_free_result($r);
    }

    // sort array
    switch($sort)
    {
        case _ZIPS_SORT_BY_DISTANCE_ASC:
            asort($return);
            break;

        case _ZIPS_SORT_BY_DISTANCE_DESC:
            arsort($return);
            break;

        case _ZIPS_SORT_BY_ZIP_ASC:
            ksort($return);
            break;

        case _ZIPS_SORT_BY_ZIP_DESC:
            krsort($return);
            break;
    }

    $this->last_time = $this->chronometer();

    if (empty($return)) return false;
    return $return;
   }