Skip to Content.
Sympa Menu

freetds - Re: [freetds] dbnextrow() question

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Re: [freetds] dbnextrow() question

Chronological Thread  
    < Chronological >       < Thread >
  • From: craigs <s.craig AT andronics.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] dbnextrow() question
  • Date: Mon, 14 Mar 2005 16:22:55 +0000
Yeah its a huge <D'oh!> alright!

So i cant use UDF's then, can i just rename all the functions to procedures? Can i call a stored procedure from inside another stored procedure, god i hope so!


Thompson, Bill D (London) wrote:

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
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds




Archive powered by MHonArc 2.6.24.

Top of Page