Scott, I think you want something like this, then, assuming you still
want to limit by radius from a given ZIP.
SELECT b.zipcode,
sum( b.inc_level1 ), sum( b.inc_level2 ),
FROM zipcodes a, zipcodes b
WHERE a.zipcode = "94949"
AND (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))))) <= 10
Scott Haneda wrote:
Scott, can you expound on what 1 row would be returned, ideally? The
one with the shortest distance? Or a row with the sums of inc_level1
... inc_level7 ?
Looks to me like you're trying to locate all the ZIP codes within a
given radius of (in this case) ZIP 94949 with the query below. Yes/no?
Dan
This is some older code, that did just that, find all zip codes in a certain
radius. I need to modify it somewhat. You are correct, I want the sum
of.... Let me show you...
SELECT b.zipcode,
b.inc_level1, b.inc_level2,
(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"
+---------+------------+------------+----------+
| zipcode | inc_level1 | inc_level2 | distance |
+---------+------------+------------+----------+
| 94949 | 164 | 156 | 0.000000 |
| 94903 | 227 | 231 | 3.241369 |
| 94947 | 268 | 323 | 3.393376 |
| 94945 | 132 | 152 | 4.120687 |
| 94960 | 60 | 77 | 5.588795 |
| 94930 | 55 | 62 | 5.847434 |
| 94973 | 88 | 70 | 6.533081 |
| 94901 | 339 | 317 | 6.603527 |
| 94904 | 51 | 68 | 7.685091 |
| 94963 | 22 | 19 | 8.085156 |
| 94946 | 21 | 20 | 8.495255 |
| 94939 | 38 | 49 | 8.640175 |
| 94933 | 48 | 28 | 8.865090 |
| 94938 | 30 | 16 | 9.367796 |
| 94925 | 46 | 65 | 9.750440 |
+---------+------------+------------+----------+
That gives me 15 rows, but I only need one, which would be the total of each
of the income level columns, distance is not important to me, that's the one
row I want back
--
Dan Buettner
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
