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

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.

--
Michael Austin.
Database Consultant

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

Ralph

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