FreeTDS Development Group

[craigs <s.craig AT andronics.com>]

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
>