Professional Web Applications Themes

Is there a way to do that without cursor ? - MySQL

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

  1. #1

    Default Is there a way to do that without cursor ?

    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 Guest

  2. #2

    Default Re: Is there a way to do that without cursor ?

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

  3. #3

    Default Re: Is there a way to do that without cursor ?

    Michael Austin 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.
    >[/ref]

    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 Guest

  4. #4

    Default Re: Is there a way to do that without cursor ?

    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.
    >>[/ref]
    >
    > 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[/ref]

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

Similar Threads

  1. Cursor bug?
    By Tom Lane in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 17th, 09:25 AM
  2. cursor help
    By Guy Brown in forum Macromedia Director Basics
    Replies: 2
    Last Post: November 19th, 05:14 PM
  3. cursor 200-problem on mac but not pc? how to swap cursor image?
    By nickelsock webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 5
    Last Post: August 2nd, 10:58 AM
  4. Change the "web hand" cursor in normal arrow cursor?
    By FB1976 in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: July 9th, 07:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139