Is there a way to do that without cursor ?

[Ralph]

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
--

Ralph

[Michael Austin]

Ralph wrote:

From:
http://jehiah.com/archive/spatial-proximity-searching-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.

--
Michael Austin.
Database Consultant

[Ralph]

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
--

Ralph

[Michael Austin]

Ralph wrote:

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)