FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

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