Ralph wrote:

> Michael Austin wrote:
>
>> Ralph wrote:
>>
>>> Hi
>>>
>>> As in topic.
>>>
>>> I have tree tables:
>>> zipcodes_tbl with columns - zipcode, latitude, longitude,
>>> points_tbl - id, latitude, longitude
>>> destinations_tbl - zipcode, point_id
>>>
>>> I also have SP called distance that takes 4 arguments (a_lat, a_lon,
>>> b_lat, b_lon) and gives me distance between two points.
>>>
>>> Is it possible without using Cursors (probably nested ones) to insert
>>> data to destinations_tbl storing zipcodes and all points within lets
>>> say 50miles radius.
>>>
>>> Thank you
>>
>>
>>
>> From:
>> http://jehiah.com/archive/spatial-pr...using-latlongs
>>
>> CREATE TABLE `zip` (
>> `zip` varchar(12) NOT NULL DEFAULT '',
>> `latitude` float NOT NULL DEFAULT '0',
>> `longitude` float NOT NULL DEFAULT '0',
>> `city` varchar(50) DEFAULT NULL,
>> `state` varchar(50) DEFAULT NULL,
>> `county` varchar(50) DEFAULT NULL,
>> `zip_class` varchar(50) DEFAULT NULL,
>> PRIMARY KEY (`zip`)
>> );
>>
>> SELECT user_id
>> FROM users, zip_codes
>> WHERE users.zip = zip_codes.zip
>> AND degrees(acos(
>> sin( radians(zip_codes.latitude) )
>> * sin( radians(47.604718))
>> + cos( radians(zip_codes.latitude))
>> * cos( radians(47.604718))
>> * cos( radians(zip_codes.longitude - -122.335230) )
>> ) ) * 69.09 < 50
>>
>> This is one example of how it has already been done. Figuring out how
>> to apply it is an excercise for the OP.
>>
>
> I already have the functions to compute the distance. Now I'd like to
> populate the third table based on this computations. The algorithm would
> be:
>
> 1. take the zipcode from the zipcodes_tbl table
> 2. find all the points that are within lets say 50miles from it (using
> the latitude and longitude from points_tbl table);
> 3. populate the third table destinations_tbl with all the points' ids
> and zipcode. like this:
>
> zip1 id1
> zip1 id2
> zip1 id3
>
> that would mean that withing this zipcode is 3 points of interest
>
> 4. fetch next zipcode and go to point 2.
>
> Now is it possible to do that with SQL and if yes do I need to use SP
> and cursors or maybe there is the way to do that with SP without the
> cursors?
>
> Thank you

This should "idea" should get you what you want... use a join to add the points_tbl.

mysql> insert into d values (1,2),(3,4),(5,6);
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from d;
+----+------+
| a | b |
+----+------+
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
+----+------+
3 rows in set (0.04 sec)

mysql> insert into e (a,b) select d.a*10,d.b/10 from d;
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from e;
+------+------+
| a | b |
+------+------+
| 10 | 0 |
| 30 | 0 |
| 50 | 1 |
+------+------+
3 rows in set (0.01 sec)

--
Michael Austin.
Database Consultant