Mysql 4.0.18
+------------+---------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+----------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| zipcode | char(5) | | MUL | | |
| inc_level1 | int(11) | | | 0 | |
| inc_level2 | int(11) | | | 0 | |
| inc_level3 | int(11) | | | 0 | |
| inc_level4 | int(11) | | | 0 | |
| inc_level5 | int(11) | | | 0 | |
| inc_level6 | int(11) | | | 0 | |
| inc_level7 | int(11) | | | 0 | |
| latitude | double(12,6) | | | 0.000000 | |
| longitude | double(12,6) | | | 0.000000 | |
| created | timestamp(14) | YES | | NULL | |
+------------+---------------+------+-----+----------+----------------+
My query is this:
SELECT b.zipcode,
b.inc_level1, b.inc_level2, b.inc_level3,
b.inc_level4, b.inc_level5, b.inc_level6,
b.inc_level7,
(3956 * (2 *
ASIN(SQRT(POWER(SIN(((a.latitude-b.latitude)*0.017453293)/2),2) +
COS(a.latitude*0.017453293) * COS(b.latitude*0.017453293) *
POWER(SIN(((a.longitude-b.longitude)*0.017453293)/2),2))))) AS distance
FROM zipcodes a, zipcodes b
WHERE a.zipcode = "94949"
GROUP BY distance having distance <= "10"
Describe yields:
+-------+------+---------------+----------+---------+-------+-------+-------
----------------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+----------+---------+-------+-------+-------
----------------------------------------------------+
| a | ref | position | position | 5 | const | 1 | Using
where; Using index; Using temporary; Using filesort |
| b | ALL | NULL | NULL | NULL | NULL | 38623 |
|
+-------+------+---------------+----------+---------+-------+-------+-------
----------------------------------------------------+
Its pretty quick, even 500 miles is under half a second, if there is
anything I can do to add indexes and such, let me know, the main issue is,
in a 500 mile search, I don't need back 1839 rows as in this case, I need
just one, where each of the b.inc_level1-7 are added up, just adding in
SUM(b.inc_level7) still yields me 1839 rows in this case.
To do this at the application level is painfully slow, I can shove the data
into a temp table at get it pretty easy, but I thought there would be a
simple way.
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
