# [freetds] dbnextrow() question

craigs s.craig at andronics.com
Fri Mar 11 11:21:10 EST 2005

```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,
>  FROM  PostCodes PC
>  WHERE PROXIMITY(PC.Latitude, PC.Longitude, @my_lat, @my_lon) <
>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
>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
>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
>
>

```