FreeTDS Development Group

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

look at SQL server books online: functions, user-defined

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of craigs
Sent: 11 March 2005 16:21
To: FreeTDS Development Group
Subject: Re: [freetds] dbnextrow() question


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