RE: PL/SQL
- From: <arul.kumar@xxxxxx>
- To: <oracledbam@xxxxxxxxxxx>
- Date: Thu, 27 Jan 2005 15:54:30 -0000
I think you may have to try the tools available like Oracle - Migration
workbench
which will convert the code for you!
Thanks,
Arul.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Seema Singh
Sent: 27 January 2005 15:49
To: oracle-l@xxxxxxxxxxxxx
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]
--@decSourceLat is the starting Lat
@decSourceLat decimal(12,8),
--@decSourceLon is the starting Lon
@decSourceLon decimal(12,8),
--@decDestLat is the destination Lat
@decDestLat decimal(12,8),
--@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]
--@decSourceLat is the starting Lat
@decSourceLat decimal(12,8),
--@decSourceLon is the starting Lon
@decSourceLon decimal(12,8),
--@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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
