*From*: "Seema Singh" <oracledbam@xxxxxxxxxxx>*To*: oracle-l@xxxxxxxxxxxxx*Date*: Thu, 27 Jan 2005 10:49:26 -0500

Hi, I want to convert T-SQL query into oracle pl/sql.These query are uses Latitude and Longitude data for calculating near by destination and relevant information based on distance. Here is 2 piece of code: =============== Stored procedure written in T-SQL script to calculate the distance in miles between 2 sets of decimal coordinates. =============== --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)) * sin(radians(@decDestLat)) + cos(radians(@decSourceLat)) * cos(radians(@decDestLat)) * cos(radians(@decDestLon) - radians(@decSourceLon)), 10)) as int) =============== spRadiusSearch.sql : ( Stored proceedure written in T-SQL script for returning all points within a specified radiues ) =============== --FINDS ALL CITIES WITHIN A SPECIFIED RADIUS FROM A STARTING POINT --Assumes the source table name is City with at least the fields CategoryID, Lat, and Lon --Stored Procedure runs faster with the following indexes on the Lat and Lon fields of table City --CREATE INDEX [ndxLat] ON [dbo].[City] ([Lat]) WITH DROP_EXISTING ON [PRIMARY] --CREATE INDEX [ndxLon] ON [dbo].[City] ([Lon]) WITH DROP_EXISTING ON [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 = @intRadius / 70.0 set @decLonDiff = @intRadius / 70.0 --Create temp table #Result to hold results create table #Result (CategoryID int, Lat decimal(12, 8), Lon decimal(12,8), Distance int) --Select into temp table #Result from Citiy all CategoryIDsthat fall within the box defined with --our source point at the center and plus/minus our lat and lon distances 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 + @decLatDiff) and C.Lon between (@decSourceLon - @decLonDiff) and (@decSourceLon + @decLonDiff) --Calculate the exact distance for each entry in #Result from our source ZIP code update #Result set Distance = cast(3958.75 * acos(round(sin(radians(@decSourceLat)) * sin(radians(#Result.Lat)) + cos(radians(@decSourceLat)) * cos(radians(#Result.Lat)) * cos(radians(#Result.Lon) - 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 = 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

- » PL/SQL
- » RE: PL/SQL
- » RE: PL/SQL