http://www.oreilly.com/catalog/oraclep3/ ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Seema Singh Sent: Thursday, January 27, 2005 8:49 AM 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 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. -- //www.freelists.org/webpage/oracle-l