FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Hi Craig,

Your functions look syntactically good to me, I can't speak for the
maths!
This is just the sort of requirement that UDF's are designed for, but
the proof is in the testing. 

AFAIK they are a SQL 2000 feature - is that a big <D'oh!> ?

Bill

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of craigs
Sent: 14 March 2005 15:13
To: brian AT bruns.com; FreeTDS Development Group
Subject: Re: [freetds] dbnextrow() question


Bill,

Could you have a look at my first attempt, first time writing stored 
procedures/functions and im sure i've got something wrong.

CREATE PROC GetPostcodeRecord ( @my_lat float, @my_long float )
AS
BEGIN
      SELECT TOP 1 PlaceName, Town, Country, Latitude, Longitude
      FROM  PostCodes PC
      WHERE LatLonDistance( @my_lat, @my_lon, PC.Latitude, PC.Longitude 
) < 1
END

************************************************************************
**************************************************************

CREATE FUNCTION LatLonDistance(@latitude float, @longitude float, 
@prev_lat float, @prev_lon float)
RETURNS float AS
BEGIN
       
    declare @radiusEarth integer
    declare @delta_lat    float
    declare    @delta_lon    float
    declare @intermed_a    float
    declare @intermed_c    float
    declare @result        float
   
        set @radiusEarth = 6367
   
    @delta_lon = AsRadians(@prev_lon) - AsRadians(@longitude);
    @delta_lat = AsRadians(@prev_lat) - AsRadians(@latitude) ;
   
    @intermed_a = Sin2(@delta_lat / 2) + cos( AsRadians( @latitude )) * 
cos( AsRadians( @prev_lat )) * Sin2( @delta_lon / 2);

    @intermed_c = 2 * Arcsin( GetMin( 1, sqrt( @intermed_a )));

    @result = @radiusEarth * @intermed_c;

    return @result;
   
END

**********************************************************

CREATE FUNCTION GetMin(@x float, @y float)
RETURNS float AS
BEGIN
        if ( @x <= @y )
        return @x;
    else
        return @y;
END

**********************************************************

CREATE FUNCTION AsRadians( @x float )
RETURNS float AS
BEGIN
    declare @my_pi        float
    declare @result        float
   
    set @my_pi = 3.14159265358979
   
    @result = @x * ( @my_pi / 180 )
    return @result
END

**********************************************************

CREATE FUNCTION Sin2( @x float )
RETURNS float AS
BEGIN
    declare @result        float
   
    @result = (1 - cos(2 * @x)) / 2;
   
    return @result;
END

**********************************************************

CREATE FUNCTION Arcsin( @x float )
RETURNS float AS
BEGIN
    declare @result        float
   
    @result = atan( @x / sqrt( -@x * @x + 1));
   
    return @result;
END
**********************************************************

Brian Bruns wrote:

>Here a good introductory article
>
>http://www.databasejournal.com/features/mssql/article.php/3348181
>
>And a tip from me, store the lon/lat in radians in your database if
>possible, it'll speed things a bit and you can use something like:
>
>select acos(cos(@lat)*cos(@lon)*cos(t.lat)*cos(t.lon) +
>cos(@lat)*sin(@lon)*cos(t.lat)*sin(t.lon) + sin(@lat)*sin(t.lat)) from
>t
>
>with or without the function.  Spatial extension for MS SQL would be
>nice, but unfortunately it looks like MS opted not to put them in 2005
>either.
>
>On Fri, 11 Mar 2005 16:21:10 +0000, craigs <s.craig AT andronics.com>
wrote:
>  
>
>>Bill,
>>
>>You'd be surprised how complicated it is to find the distance between
2
>>lat lons!  My LatLonDistance query uses a number of other functions to
>>work it out, such as pie, cos, atan, sqrt as well as many
>>multiplications and divisions.
>>
>>I think i will have to create a stored procedure function to call as
>>well as trying to reduce the results my query will return, it should
be fun!
>>
>>Is there any online resources where i can look into writing SQL
>>functions that anyone knows off, save me a bit of time searching.
>>
>>Thompson, Bill D (London) wrote:
>>
>>    
>>
>>>Hi Shaun
>>>
>>>I'm going to keep going at this because it looks interesting...
>>>
>>>Come on - how complicated is the math to work out the distance
between
>>>two lat/lon pairs ?
>>>
>>>If you need a function - you know you can code your own functions in
>>>SQL?
>>>
>>>
>>>so you could code a SQL function (called PROXIMITY in this case),
and...
>>>
>>>create proc get_postcode_record ( @my_lat float, @my_long float )
>>>AS
>>>BEGIN
>>> SELECT TOP 1 PlaceName, Town, Country, Latitude, Longitude,
>>>SweepRadius
>>> FROM  PostCodes PC
>>> WHERE PROXIMITY(PC.Latitude, PC.Longitude, @my_lat, @my_lon) <
>>>PC.SweepRadius
>>>END
>>>
>>>
>>>HTH,
>>>
>>>Bill
>>>
>>>
>>>
>>>
>>>-----Original Message-----
>>>From: freetds-bounces AT lists.ibiblio.org
>>>[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of craigs
>>>Sent: 11 March 2005 14:08
>>>To: brian AT bruns.com; FreeTDS Development Group
>>>Subject: Re: [freetds] dbnextrow() question
>>>
>>>
>>>Thanks for all the help guys.  Here's a more detailed description of
my
>>>problem.
>>>
>>>I get a current latitude, longitude value into my program, what i
have
>>>to do then is compare it with my table( called PostCodes) which has
the
>>>2.4 million records to find the record im looking for.  What i mean
by
>>>this is bit hard to explain but here goes.
>>>
>>>Table PostCodes columns include:
>>>PlaceName, Town, Country, Latitude, Longitude, SweepRadius.
>>>
>>>When i get my lat lon values i have to do a number of things, i call
a
>>>function called LatLonDistance which takes my current lat lon values
and
>>>
>>>works out the distance between the lat lon values in each Postcodes
>>>record(means i have to go into each record and work out the
distance),
>>>if the distance is less than the SweepRadius this is the record im
>>>looking for, once found i do an insert into another table and call
>>>'return NO_MORE_RESULTS' in order to stop looping around the
PostCodes
>>>table.
>>>
>>>I cant see any other way to do this query because i need to call
>>>LatLonDistance on each record until i find the one i want.
>>>
>>>Brian Bruns wrote:
>>>
>>>
>>>
>>>      
>>>
>>>>Of course, it goes without saying that you'll eventually run into
>>>>situtations where you can only get an approximate match of the data
>>>>you need due to external factors (merging with external data,
>>>>calculations that can't be done by the server), in which case you'll
>>>>need to return the smallest set you can containing your data and do
as
>>>>the loop did in this case.
>>>>
>>>>Brian
>>>>
>>>>
>>>>On Thu, 10 Mar 2005 13:39:25 -0500, Lowden, James K
>>>><LowdenJK AT bernstein.com> wrote:
>>>>
>>>>
>>>>
>>>>
>>>>        
>>>>
>>>>>>From: craigs
>>>>>>Sent: Thursday, March 10, 2005 7:03 AM
>>>>>>
>>>>>>i want to exit query when i get what i want,
>>>>>>theres over 2.4 millions records in my table so really need to get
>>>>>>this right, can someone advise me if the following code would
work.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>            
>>>>>>
>>>>>Bill is helping you get your dbcancel() code correct, and maybe
some
>>>>>other aspects of your application in the bargain.  I want to
emphasize
>>>>>
>>>>>
>>>>>          
>>>>>
>>>a
>>>
>>>
>>>      
>>>
>>>>>point he made, because it may help eventually (or sooner).  You
didn't
>>>>>ask for design advice, but even it's no use to you, it might help
>>>>>someone else.
>>>>>
>>>>>Your table has 2.4 million rows.  Based on some criterion, you want
to
>>>>>pick out one and discard the others.  That's the genesis of wanting
to
>>>>>understand dbcancel() etc.
>>>>>
>>>>>You're not the first person to have this challenge, nor the first
to
>>>>>
>>>>>
>>>>>          
>>>>>
>>>try
>>>
>>>
>>>      
>>>
>>>>>to approach it this way.  It won't work very well, though.  You'll
>>>>>require the server to send a great many rows over the wire that you
>>>>>don't need, wasting server, network, and client resources.  In the
>>>>>
>>>>>
>>>>>          
>>>>>
>>>worst
>>>
>>>
>>>      
>>>
>>>>>case, you'll need row number 2,399,999.  If every row is 100 bytes,
>>>>>
>>>>>
>>>>>          
>>>>>
>>>the
>>>
>>>
>>>      
>>>
>>>>>server would find and send 240 MB for the 100 you want.  On
average,
>>>>>
>>>>>
>>>>>          
>>>>>
>>>the
>>>
>>>
>>>      
>>>
>>>>>row you want will be midway through the result set, wasting 120 MB
per
>>>>>query.  It will be roughly 1,000,000 times slower than necessary.
>>>>>
>>>>>The right way to do it is to tell the server what you want: write a
>>>>>specific query for the row you're looking for.  The server will
search
>>>>>the data locally (much faster) send you the one fine row.  You'll
call
>>>>>dbnextrow() exactly twice: once to get the row and once to confirm
>>>>>
>>>>>
>>>>>          
>>>>>
>>>there
>>>
>>>
>>>      
>>>
>>>>>are NO_MORE_ROWS.  As Bill suggested, a nice refinement is to
package
>>>>>the SQL in a stored procedure and call that with your parameter(s).
>>>>>Send the query as "execute my_proc @my_param" or, better still, use
>>>>>
>>>>>
>>>>>          
>>>>>
>>>the
>>>
>>>
>>>      
>>>
>>>>>dbrpc functions.
>>>>>
>>>>>If you formulate your query to return only what you want, the
>>>>>
>>>>>
>>>>>          
>>>>>
>>>networking
>>>
>>>
>>>      
>>>
>>>>>and cancellation issues go away, and you can focus on your
>>>>>
>>>>>
>>>>>          
>>>>>
>>>application.
>>>
>>>
>>>      
>>>
>>>>>HTH.
>>>>>
>>>>>--jkl
>>>>>
>>>>>-----------------------------------------
>>>>>The information contained in this transmission may contain
privileged
>>>>>
>>>>>
>>>>>          
>>>>>
>>>and confidential information and is intended only for the use of the
>>>person(s) named above. If you are not the intended recipient, or an
>>>employee or agent responsible for delivering this message to the
>>>intended recipient, any review, dissemination, distribution or
>>>duplication of this communication is strictly prohibited. If you are
not
>>>the intended recipient, please contact the sender immediately by
reply
>>>e-mail and destroy all copies of the original message. Please note
that
>>>we do not accept account orders and/or instructions by e-mail, and
>>>therefore will not be responsible for carrying out such orders and/or
>>>instructions.
>>>
>>>
>>>      
>>>
>>>>>If you, as the intended recipient of this message, the purpose of
>>>>>
>>>>>
>>>>>          
>>>>>
>>>which is to inform and update our clients, prospects and consultants
of
>>>developments relating to our services and products, would not like to
>>>receive further e-mail correspondence from the sender, please "reply"
to
>>>the sender indicating your wishes.  In the U.S.: 1345 Avenue of the
>>>Americas, New York, NY 10105.
>>>
>>>
>>>      
>>>
>>>>>_______________________________________________
>>>>>FreeTDS mailing list
>>>>>FreeTDS AT lists.ibiblio.org
>>>>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>          
>>>>>
>>>>_______________________________________________
>>>>FreeTDS mailing list
>>>>FreeTDS AT lists.ibiblio.org
>>>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>        
>>>>
>>>_______________________________________________
>>>FreeTDS mailing list
>>>FreeTDS AT lists.ibiblio.org
>>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>>--------------------------------------------------------
>>>
>>>If you are not an intended recipient of this e-mail, please notify
the sender, delete it and do not read, act upon, print, disclose, copy,
retain or redistribute it. Click here for important additional terms
relating to this e-mail.     http://www.ml.com/email_terms/
>>>--------------------------------------------------------
>>>
>>>_______________________________________________
>>>FreeTDS mailing list
>>>FreeTDS AT lists.ibiblio.org
>>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>>
>>>
>>>      
>>>
>>_______________________________________________
>>FreeTDS mailing list
>>FreeTDS AT lists.ibiblio.org
>>http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>>    
>>
>_______________________________________________
>FreeTDS mailing list
>FreeTDS AT lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds
>  
>

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds