FreeTDS Development Group

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

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