SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Return a result set from a table AND a stored procedure together?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/25/2003 :  07:40:30  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Richard writes "I have store proc that calculates distance in miles between 2 sets of latitudes and longitudes and I also have a table called [places] that contain zipcodes, lats, & longs. I need to combine the two in some way so I can pass 2 parameters such as zipcode and radius (let's say 11214, 3) and return a record set of all zipcodes within that given mile radius (range.) The output should be something like the following if I pass it 11214, 3:

11214 1
11228 3
11219 2
etc.... Please help!? I am running MS-SQL2000 and will be calling this code from ASP.

Thanks!

Richard"

SamC
White Water Yakist

USA
3464 Posts

Posted - 11/25/2003 :  08:26:26  Show Profile  Reply with Quote
It's easier to show the modifications if you post some of your code.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/25/2003 :  08:40:24  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
just edit your stored proc (or create a new copy) to include a join to this table. but, as Sam says, some code would be helpful.

- Jeff

Edited by - jsmith8858 on 11/25/2003 08:40:51
Go to Top of Page

anteros511
Starting Member

USA
3 Posts

Posted - 11/26/2003 :  15:25:48  Show Profile  Send anteros511 an AOL message  Reply with Quote
--**************************************
-- Name: Procedure to calc miles distance from two points on earth
-- Description:This procedure is used to calc miles distance from
-- two points on earth. You can get latitude and longitude for places on
-- earth, then just use this procedure to calc how many miles are in between.
--
-- This code is copyrighted and has limited warranties.
-- Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.644/lngWId.5/qx/vb/scripts/ShowCode.htm
-- for details.
--**************************************

CREATE Proc dbo.sp_MilesLatLong (
@P_Lat1 decimal(8,4) =0 ,
@P_Long1 decimal(8,4) =0 ,
@P_Lat2 decimal(8,4) =0 ,
@P_Long2 decimal(8,4) =0 ,
@P_DiffMiles decimal(10,2) =0 output
)
As
Begin
/*************************************************************/
/* Routine to calc miles distance from two points on earth */
/* specified in latitude/longitude pairs.*/
/* Inputs are "degrees.minutes" ie: 33.0654 */
/*************************************************************/
set nocount on
declare @Lat1 decimal(28,10)
declare @Long1 decimal(28,10)
declare @Lat2 decimal(28,10)
declare @Long2 decimal(28,10)
declare @d decimal(28,10)

/* Default return value */
set @P_DiffMiles = 0

/* Convert to radians */
set @Lat1 = @P_Lat1 / 57.2958
set @Long1 = @P_Long1 / 57.2958
set @Lat2 = @P_Lat2 / 57.2958
set @Long2 = @P_Long2 / 57.2958

/* Calc distance */
set @d = (Sin(@Lat1) * Sin(@Lat2)) + (Cos(@Lat1) * Cos(@Lat2) * Cos(@Long2 - @Long1))

/* Convert to miles */
if @d <> 0
begin
set @P_DiffMiles = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d);
end
end
GO

/* DECLARE @miles int
/* EXEC @miles = sp_MilesLatLong 41.598994, -73.996119, 41.768420, -73.960450
/* SELECT miles = @miles
Go to Top of Page

anteros511
Starting Member

USA
3 Posts

Posted - 11/26/2003 :  15:26:46  Show Profile  Send anteros511 an AOL message  Reply with Quote
Sample data from my 'Places' table:


AL 35085 32.965120 -86.744053
AL 35087 34.303718 -86.583234
AL 35089 32.941708 -86.060988
AL 35091 33.771090 -86.806727
AL 35094 33.530698 -86.555065
AL 35096 33.605233 -86.120796
AL 35097 33.896526 -86.630569
AL 35098 34.120006 -87.050758
AL 350HH 33.464476 -86.577571
AL 350XX 32.978292 -86.499261
AL 35111 33.279946 -87.101488
AL 35112 33.681899 -86.482375
AL 35114 33.225377 -86.864434
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 11/26/2003 :  19:51:48  Show Profile  Reply with Quote

CREATE TABLE places
(
	statecode char(2),
	zipcode char(5),
	lat float,
	long float
)

insert into places select 'AL', '35085' ,32.965120 ,-86.744053
union all select 'AL', '35087', 34.303718 ,-86.583234
union all select 'AL', '35089', 32.941708 ,-86.060988
union all select 'AL', '35091', 33.771090 ,-86.806727
union all select 'AL', '35094', 33.530698 ,-86.555065
union all select 'AL', '35096', 33.605233 ,-86.120796
union all select 'AL', '35097', 33.896526 ,-86.630569
union all select 'AL', '35098', 34.120006 ,-87.050758
union all select 'AL', '350HH', 33.464476 ,-86.577571
union all select 'AL', '350XX', 32.978292 ,-86.499261
union all select 'AL', '35111', 33.279946 ,-87.101488
union all select 'AL', '35112', 33.681899 ,-86.482375
union all select 'AL', '35114', 33.225377 ,-86.864434
GO

CREATE PROC up_FindZipCodesWithinRadius

	@ZipCode char(5) ,
	@GivenMileRadius int
AS
SET NOCOUNT ON

DECLARE @lat1 float, 
	@long1 float

SELECT  @lat1= lat,
        @long1 = long 
FROM places
WHERE zipcode = @ZipCode

SELECT ZipCode ,MileRadius
FROM
(
	SELECT  ZipCode,3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((long/57.2958) - (@Long1/57.2958)))), 2)) / 
		((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((long/57.2958) - (@Long1/57.2958)))))) MileRadius
	FROM Places
) a
WHERE a.MileRadius <= @GivenMileRadius
AND ZipCode <> @ZipCode
ORDER BY MileRadius

GO

EXEC up_FindZipCodesWithinRadius '35085',20
GO
DROP PROC up_FindZipCodesWithinRadius
--DROP TABLE places


Edited by - ehorn on 11/26/2003 20:04:35
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 11/26/2003 :  20:06:28  Show Profile  Reply with Quote
quote:
This code is copyrighted and has limited warranties.


Oh please!!! Copywriting Maths now are we.. F$^%&ing morons....

Look out ehorn, Pythagoras is going to start suing!!!!

DavidM

"SQL-3 is an abomination.."
Go to Top of Page

anteros511
Starting Member

USA
3 Posts

Posted - 11/28/2003 :  23:05:02  Show Profile  Send anteros511 an AOL message  Reply with Quote
DUDE!! You rock-- thank you sssooo much!!!

quote:
Originally posted by ehorn


CREATE PROC up_FindZipCodesWithinRadius

	@ZipCode char(5) ,
	@GivenMileRadius int
...


Go to Top of Page

SqlStar
Posting Yak Master

USA
121 Posts

Posted - 11/29/2003 :  03:35:11  Show Profile  Reply with Quote
hi anteros511,

Dont give prefix as "sp_", when create stored procedure.Its not adviseable.


":-) IT Knowledge is power :-)"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000