Skip to Content.
Sympa Menu

freetds - RE: [freetds] dbnextrow() question

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <LowdenJK AT bernstein.com>
  • To: <ml AT freetds.org>
  • Cc:
  • Subject: RE: [freetds] dbnextrow() question
  • Date: Mon, 14 Mar 2005 15:08:46 -0500

> From: craigs
> Sent: Monday, March 14, 2005 10:13 AM
>
> 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

"Top 1": Do you want just any row? You might want an ORDER BY clause,
but ISTM you're looking for the PlaceName with the minimum distance....

To answer your UDF question: yes, it's a feature of SQL Server 2000.
You can nest stored procedures, and you can get almost the same
modularity using OUTPUT parameters. Just remember to use the OUTPUT tag
both in the procedure body and when you call it.

IMHO, you're better off with a single procedure, because (lacking
functions) the code will be easier to follow than with OUTPUT
parameters. I've attached one that might do the job.

> CREATE FUNCTION AsRadians( @x float )

There is a built-in PI() function, no?

> CREATE FUNCTION Arcsin( @x float )

Does the function asin() do what you want?

> 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

So here goes. I'm just trying to restate your query. I'm sure it can
be simplified; it may even be wrong.

It may also be quite slow (although still faster than transmitting the
whole table to the client) because the server has no useful index an
must examine every row. To speed it up, the best thing would be to
restrict the number of rows examined. For example, would it be the case
that 90% of the time the PlaceName will be located at +/- 1 degree (or
some other threshold)? If so, you could restrict the innermost query
that way, and, if it fails to find one, fall back to scanning the whole
table.

Another optimization would be to cache the results in a global temporary
table. That way, once a PlaceName is determined as the solution for a
given latitude/longitude pair, it can be re-retrieved without being
recalculated.

HTH.

--jkl


CREATE PROC GetPostcodeRecord
@lat float
, @lon float
as
BEGIN
create table #distances (PlaceName varchar(30), Distance float)

insert #distances
select PlaceName, distance
from ( -- distances
select PlaceName
, 6367 * 2
* asin( case when 1.0 < sqrt(intermed_a)
then 1.0
else sqrt(intermed_a)
end
) as distance
from ( -- intermediate "a"
select PlaceName
, ( (1 - cos(2 * ((lat_radians -
Lat_radians) / 2))) / 2 )
-- | |^^^^^^^^^^ 1/2 delta
^^^^^^^^^^| |
-- |^^^^^^^^^^^^^ Sin2
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^|
+ cos(Lat_radians)
* cos(lat_radians)
* ( (1 - cos(2 * ((lon_radians -
Lon_radians) / 2))) / 2 )
as intermed_a
from ( -- radians
select PlaceName
, (PI() / 180) * @lat
as lat_radians
, (PI() / 180) * PC.Latitude
as Lat_radians
, (PI() / 180) * @lon
as lon_radians
, (PI() / 180) * PC.Longitude
as Lon_radians
FROM PostCodes as PC
) as Radians
) as intermediates
) as Distances
where distance < 1

SELECT PlaceName, Town, Country, Latitude, Longitude
FROM PostCodes
WHERE PlaceName in (
select min(PlaceName)
from #distances
where Distance = (
select min(Distance)
from #distances
)
)
END

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






Archive powered by MHonArc 2.6.24.

Top of Page