SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    this sql statement and mysql

    CREATE PROCEDURE zip_nearby_locations
    @lat1 float,
    @long1 float,
    @radius float
    AS
    SELECT (2*3956*ASIN(
    (SQRT( POWER(SIN(((rlat-@lat1))/2),2) + COS(@lat1) * COS(rlat)
    * POWER(SIN(((abs(rlong)-@long1))/2),2) )) )
    ) AS dist, location, zipcode
    FROM Zipcodes
    WHERE (2*3956*ASIN(
    (SQRT( POWER(SIN(((rlat-@lat1))/2),2) + COS(@lat1) * COS(rlat)
    * POWER(SIN(((abs(rlong)-@long1))/2),2) )) )
    ) < @radius
    ORDER BY dist
    GO


    I saw this code on a website. Does this work with mysql? And how would I call this procedure? I do not know how procedures work in databases.

  2. #2
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, jkh1978, you can do that in mysql

    how you call it is up to you -- php, coldfusion, whatever you want -- you just can't parameterize it into a stored proc, but that doesn't mean you can't use it

    just construct the query "on the fly" in your calling program by assembling a string containing your latitude, longitude, and radius values in the appropriate places

    SQRT, POWER, ASIN, SIN, and COS are mysql functions, so if you believe the website where this was posted (i.e. you believe the accuracy of the formula), then go ahead and use it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •