www.webdeveloper.com
Results 1 to 2 of 2

Thread: Getting distance between two coordinate points

  1. #1
    Join Date
    Feb 2006
    Location
    Atlanta
    Posts
    218

    Getting distance between two coordinate points

    Hello all. I have one table, locations, with the following columns: id (auto inc), latitude, and longitude.

    Here is the stored procedure I'm using to find the distance between the two, it can be originally found in this slideshow: HERE.

    Code:
    CREATE PROCEDURE geodist (IN userid int, IN dist int) BEGIN  
    declare mylon double;  declare mylat double;  
    declare lon1 float;  declare lon2 float; 
    declare lat1 float; declare lat2 float;
    
    -- get the original lon and lat for the userid
    select longitude, latitude into mylon, mylat from users5 where id=userid limit 1;
    
    -- calculate lon and lat for the rectangle:
    set lon1 = mylon-dist/abs(cos(radians(mylat))*69); 
    set lon2 = mylon+dist/abs(cos(radians(mylat))*69); 
    set lat1 = mylat-(dist/69);  set lat2 = mylat+(dist/69);
    
    -- run the query:
    SELECT  destination.*,  
    3956 * 2 * ASIN(SQRT(  POWER(SIN((orig.lat - dest.lat) * pi()/180 / 2), 2) +  
    COS(orig.lat * pi()/180) *  COS(dest.lat * pi()/180) *  POWER(SIN((orig.lon -dest.lon) * pi()/180 / 2), 2)  )) as
    distance FROM  users destination,  users origin WHERE  origin.id=userid
    and  destination.longitude between lon1 and lon2 
    and destination.latitude between lat1 and lat2 
    having distance < dist ORDER BY Distance limit 10;
    END $$
    However, as you can see, this is using more tables than I have, and I'm not sure what format it is using.

    Could someone please help me correct the names of tables, etc to line up with one table I have? I want to input a single id (auto inc from the table) and distance and have the function search the locations table without looping through every single one (what the function is doing.)

    Thanks in advance,

    David

  2. #2
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234

    a squential approach.

    The parameters of your stored procedure do not seem to reflect your needs, therefore some assumptions are necessary.
    Code:
      CREATE PROCEDURE geodist (IN userid int, IN dist int) BEGIN
    .
    I cannot provide the complete solution in one posting because in the abstract relation p->q->r->s, and if you write p->g then q->r->s is logically invalidated.

    As a result your one table with auto incremented id, latitude, and longitude comes under scrutiny. The only question I would pose is what exactly does this three field table represent? Rationally the answer is it represents all locations as (lon,lat) pairs.

    Given userid, you then select your anchor point A(lon.lat), from the table users5.
    Code:
    -- get the original lon and lat for the userid 
    select longitude, latitude into mylon, mylat 
    from 
    users5 where id=userid limit 1;
    interlude:: From an anchored point you can go East,West.North,South, which when flattened is rectangular as you calculate:

    Code:
    -- calculate lon and lat for the rectangle: 
    set lon1 = mylon-dist/abs(cos(radians(mylat))*69); 
    set lon2 = mylon+dist/abs(cos(radians(mylat))*69);  
    set lat1 = mylat-(dist/69); 
     set lat2 = mylat+(dist/69);
    I would hope your calculations are correct.

    At this point you have two ranges, one each for lattitude and longitude, therefore the pairs to select from your table users5, within the radius, dist, must be betweem (lon1 and lon2) and (lat1 and lat2). The query can now proceed without calculations within it...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles