RE: PL/SQL

From: Reidy, Ron <Ron.Reidy_at_arraybiopharma.com>
Date: Thu, 27 Jan 2005 08:53:38 -0700
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B4392D7@fiji.arraybp.com>

http://www.oreilly.com/catalog/oraclep3/

---

Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Seema Singh Sent: Thursday, January 27, 2005 8:49 AM To: oracle-l_at_freelists.org
Subject: PL/SQL

Hi,
I want to convert T-SQL query into oracle pl/sql.These query are uses=20 Latitude and Longitude data for calculating near by destination and = relevant=20
information based on distance.

Here is 2 piece of code:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Stored procedure written in T-SQL script to calculate the distance in = miles=20
between 2 sets of decimal coordinates.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

--RETURNS THE DISTANCE BETWEEN 2 SETS OF COORDINATES IN DECIMAL DEGREES
CREATE PROCEDURE [dbo].[spCalcDistance]
--_at_decSourceLat is the starting Lat

@decSourceLat decimal(12,8),
--_at_decSourceLon is the starting Lon

@decSourceLon decimal(12,8),
--_at_decDestLat is the destination Lat

@decDestLat decimal(12,8),
--_at_decDestLon is the destination Lon

@decDestLon decimal(12,8)
as

select cast(3958.75 * acos(round(sin(radians(@decSourceLat))   *=20
sin(radians(@decDestLat)) + cos(radians(@decSourceLat))   *=20
cos(radians(@decDestLat)) * cos(radians(@decDestLon)   -=20

radians(@decSourceLon)), 10)) as int)

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D spRadiusSearch.sql : ( Stored proceedure written in T-SQL script for=20 returning all points within a specified radiues ) =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

--FINDS ALL CITIES WITHIN A SPECIFIED RADIUS FROM A STARTING POINT

--Assumes the source table name is City with at least the fields =
CategoryID,=20
Lat, and Lon
--Stored Procedure runs faster with the following indexes on the Lat and =
Lon=20
fields of table City
--CREATE INDEX [ndxLat] ON [dbo].[City] ([Lat]) WITH DROP_EXISTING ON=20
[PRIMARY]
--CREATE INDEX [ndxLon] ON [dbo].[City] ([Lon]) WITH DROP_EXISTING ON=20
[PRIMARY] CREATE PROCEDURE [dbo].[spRadiusSearch]
--_at_decSourceLat is the starting Lat

@decSourceLat decimal(12,8),
--_at_decSourceLon is the starting Lon

@decSourceLon decimal(12,8),
--_at_intRadius is how far out in miles we wish to seach
@intRadius int
as

--Define local vars for calculations

declare @decLatDiff decimal(12,8)
declare @decLonDiff decimal(12,8)

--Set rough estimate in degrees lat/lon for the radius
set @decLatDiff =3D @intRadius / 70.0
set @decLonDiff =3D @intRadius / 70.0

--Create temp table #Result to hold results
create table #Result (CategoryID int, Lat decimal(12, 8), Lon = decimal(12,8),=20
Distance int)

--Select into temp table #Result from Citiy all CategoryIDsthat fall =
within=20
the box defined with
--our source point at the center and plus/minus our lat and lon =
distances=20
north, south, east and west
insert into #Result
select C.CategoryID, C.Lat, C.Lon, -1.0
from City C with (nolock)
where C.Lat between (@decSourceLat - @decLatDiff) and (@decSourceLat +=20 @decLatDiff)
and C.Lon between (@decSourceLon - @decLonDiff) and (@decSourceLon +=20 @decLonDiff)

--Calculate the exact distance for each entry in #Result from our source =
ZIP=20
code
update #Result set
Distance =3D cast(3958.75 * acos(round(sin(radians(@decSourceLat)) *=20 sin(radians(#Result.Lat)) + cos(radians(@decSourceLat)) *=20 cos(radians(#Result.Lat)) * cos(radians(#Result.Lon) -=20 radians(@decSourceLon)), 10)) as int)

--Remove any entries that fall outside our specified radius
delete #Result where #Result.Distance > @intRadius

--Select the distance from #Result and all matching records from City
select R.Distance, C.*
from City C with (nolock)
join #Result R on R.CategoryID =3D C.CategoryID order by R.Distance

--Drop temp #Result table

drop table #Result
GO

ANY THOUGHT WOULD BE HELPFULL.
thanks
-Seema

--

http://www.freelists.org/webpage/oracle-l

This electronic message transmission is a PRIVATE communication which = contains
information which may be confidential or privileged. The information is = intended=20
to be for the use of the individual or entity named above. If you are = not the=20
intended recipient, please be aware that any disclosure, copying, = distribution=20
or use of the contents of this information is prohibited. Please notify = the
sender of the delivery error by replying to this message, or notify us = by
telephone (877-633-2436, ext. 0), and then delete it from your system.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 27 2005 - 10:54:48 CST